Quick Checking Tablespace Usage by Querying DBA_TABLESPACE_USAGE_METRICS

DBA need query a tablespace 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.5355225A

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
...
..
.

 

Advertisements
This entry was posted in SQL Scripts For DBA and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s