Check Tablespace Space Usage by Querying DBA_TABLESPACE_USAGE_METRICS

Quick way to know tablespace space usage by querying “DBA_TABLESPACE_USAGE_METRICS”.

DBA need know a tablespace space usage.  There are a number of ways to do so by joining below different views:

DBA_TABLESPACE
DBA_DATA_FILES
DBA_FREE_SPACE

..
.

There is a quicker way to do so by querying DBA_TABLESPACE_USAGE_METRICS.

SQL> desc DBA_TABLESPACE_USAGE_METRICS
 Name               Null?   Type
 ----------------  -------  -----------
 TABLESPACE_NAME            VARCHAR2(30)
 USED_SPACE                 NUMBER
 TABLESPACE_SIZE            NUMBER
 USED_PERCENT               NUMBER

SQL> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME  USED_SPACE   TABLESPACE_SIZE   USED_PERCENT
---------------- ------------ ----------------- ------------
PSAPP                6255360            7635328 81.9265394
PSAPPIDX             7025984           11028864 63.7054188
PSHUGE01            28452352           41633280 68.3404046
PSHUGE01IDX         33479424           46721792 71.6569775
PSHUGE02             4750856           11776000 40.3435462
PSHUGE02IDX          3838600            9728000 39.4592928
PSIBTRAN             1822832            2621440 69.5355225

Please note :

  •  USED_SPACE and  TABLESPACE_SIZE are in blocks.
  • TABLESPACE_SIZE  is the maximum possible size if AUTO extended on,  not the current size.  The same applies to USED_PERCENT.

Joining DBA_TABLESPACE_USAGE_METRICS with DBA_TABLESPACE,  we can get easily readable size in MB instead of default blocks.

SQL>SELECT a.tablespace_name,
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name;
 

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT
---------------- -------------- ------------------- ------------
PSHUGE02IDX            29989.06            76000           39.46
PSHUGE02               37116.06            92000           40.34
PSAPPIDX               54890.5             86163           63.71
PSHUGE01              222284              325260           68.34
...
..
.

Sometimes we see “USED_PERCENT” is not refreshed immediately after datafiles are added or dropped. in this case, alter tablespace offline then online helps, if only you can do it.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL       23.06          123                 18.75

SQL> alter tablespace TESTUSER_TBL offline;
Tablespace altered.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL        22                  22            100

How To Format Empty Failing (Index) Pages Marked by DBVERIFY

Formatting empty failing pages to make clean DBVERIFY results.

ISSUE

Following another post “ORA-00600 internal error code arguments [ktbdchk1: bad dscn] after DG switchover”, there is another smaller file number 8 with 22 indexes pages identified by dbverify as failing pages as below:

Total Pages Failing   (Index): 22

Here we are going to demonstrate how to format those failing index pages.

SOLUTION

Identify the index segments and rebuild them online accordingly.

From the dbverify log below and run the query to get index segments owner and names for rebuilding:

itl[20] has higher commit scn(0x0001.ff7137bc) than block scn (0x0001.0651314d)
Page 15795600 failed with check code 6056

SQL>SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =8 and 15795600 between block_id AND block_id + blocks-1;

After rebuilt all the impacted indexes and run dbverify and sql query above, all the failing pages(blocks) are returned to freelist queue. They can be verified by query against dba_free_space view as well.

SQL> select name, bytes/1024/1024/1024 from v$datafile where file#=8;

NAME                                          BYTES/1024/1024/1024
--------------------------------------------  --------------------
+DG1/testdb/datafile/users_tbl.583.845563563  188

Create a table to use all free space, big pctfree to speed up inserting to make use of all available free space.

SQL> create table EMPTY( n number ) tablespace users_tbl pctfree 99;

Table created.

To get min/max free block size.

SQL> select TABLESPACE_NAME,FILE_ID,BLOCKS,count(*)
from dba_free_space
where TABLESPACE_NAME='USERS_TBL'
group by TABLESPACE_NAME,FILE_ID,BLOCKS 
order by 2;

TABLESPACE_NAME BLOCKS COUNT(*)
-------------- ------- ----------
USERS_TBL         8       3
USERS_TBL        16       3
USERS_TBL        32       2
USERS_TBL        40       1
USERS_TBL        48       2
.........
..
USERS_TBL     75904       1

21 rows selected.

To get how many times of minimum free space blocks in total available free space.

SQL> select sum(BLOCKS)/8  
       from dba_free_space
      where TABLESPACE_NAME='USERS_TBL';

SUM(BLOCKS)/8
-----------
66944

Turn datafile autoextend off.

SQL> alter database datafile '+DG1/testdb/datafile/USERS_TBL.583.845563563' autoextend off;

Database altered.

Allocate all available free space to table EMPTY with smallest freespace blcoks.

SQL>BEGIN
      for i in 1..66944 loop
        EXECUTE IMMEDIATE 'alter table EMPTY allocate extent ( size 64K) ';
      end loop;
    END;
    /

Insert data into table EMPTY until encountering unable  to allocate space ORA- error.

SQL> Begin
       FOR i IN 1..100000000 loop
         for j IN 1..10000 loop
           Insert into EMPTY VALUES(i+j);
        end loop;
        commit;
      END LOOP;
   END;
  /

Now drop table to release the free space.

SQL> drop table EMPTY;

Table dropped.

Turn datafile autoextend on.

SQL> alter database datafile '+DG1/tetsdb/datafile/users_tbl.583.845563563' autoextend on;

Database altered.

Run DBVEIFY to confirm all pages are all right now with ZERO pages failing.

DBVERIFY - Verification starting : FILE = +DG1/testdb/datafile/users_tbl.583.845563563

DBVERIFY - Verification complete
Total Pages Examined : 26869760
Total Pages Processed (Data) : 4391230
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 682945
Total Pages Failing (Index): 0
Total Pages Processed (Lob) : 20668189
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 49949
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1077447
Total Pages Marked Corrupt : 0