Shulou(Shulou.com)05/31 Report--

This article focuses on "Oracle11g new view query table space usage analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "Oracle11g new view query table space utilization analysis"!

The Oracle11g version adds a tablespace query view DBA_TABLESPACE_USAGE_METRICS, which is very convenient, unlike the previous long sql to query, but the simplicity also brings some problems, and some problems are also very hidden, so let's explain them one by one.

Set linesize 200 pagesize 99

Col tablespace_name for a36

Select TABLESPACE_NAME,ROUND ((t.TABLESPACE_SIZE * p.value) / 1024 p.value 1024) TABLESPACE_SIZE_GB,ROUND ((t.USED_SPACE * p.value) / 1024 Accord 1024) USED_SPACE_GB,USED_PERCENT

From DBA_TABLESPACE_USAGE_METRICS t

INNER JOIN v$parameter p ON p.name = 'db_block_size' order by 4 desc

The execution result is as follows

TABLESPACE_NAME TABLESPACE_SIZE_GB USED_SPACE_GB USED_PERCENT

--

UNDOTBS1 350 309 88.3769742

TBS_DATA2 7176 5252 73.1868556

TBSFSH_DATA21 10224 7294 71.3478941

TBSFSH_DATA2 7049 4230 60.0061867

SYSAUX 64 24 38.0566778

TBSFSH_DATA22 3683 1046 28.3976447

SYSTEM 64 6 9.74960792

TEMP 976 40 4.11586419

UNDOTBS2 448 0. 110953248

Although this view brings simplicity, it also has some points to pay attention to.

1. Its size is calculated in block, so you need to associate the block size of the database.

2. If the total capacity of the tablespace is an automatically expandable datafile, then the result is the sum of the maximum max values of all datafile

3. The view is unstable and may not be refreshed in time after add datafile. It may take some time.

4. When the tablespace is drop datafile, the total capacity of the view will not decrease due to bug, so the instance needs to be restarted.

5. After a tablespace truncate a table, sometimes the utilization rate will not decrease, but as a workaround, the view will be refreshed after manually creating a table and then drop

At this point, I believe you have a deeper understanding of the "Oracle11g new view query tablespace utilization analysis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.