Contact Premier Online Trainings Today

Oracle DBA - Tablespace Usage sql queries

Date Added: 10 Oct.2019 Date Updated: 10 Oct.2019 Oracle DBA Full Blog

Tablespace Usage sql queries

-- Temporary Tablespace Usage.

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

 

-- Temporary Tablespace Sort Usage.
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM 
   v$sort_segment A,
(SELECT    B.name,    C.block_size,    SUM (C.bytes) / 1024 / 1024 mb_total
FROM    v$tablespace B,    v$tempfile C WHERE    B.ts#= C.ts# 
GROUP BY    B.name,    C.block_size) D
WHERE    A.tablespace_name = D.name GROUP by 
   A.tablespace_name, 
   D.mb_total
/
 
 
-- Tablespace Usage
 
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
SET VERIFY OFF
COL tablespace_name HEA 'Tablespace Name' FOR a20
COL ambytecount HEA 'Available' FOR 99999999999.999
COL umbytecount HEA '  Used   ' FOR 99999999999.999
 
SELECT ddf.tablespace_name,
       SUM( distinct ddf.ddfbytes )/1048576 ambytecount,
       SUM( NVL( ds.bytes , 0 ) / 1048576 ) umbytecount
FROM 
   ( SELECT tablespace_name, SUM( bytes ) ddfbytes
     FROM dba_data_files
     GROUP BY tablespace_name ) ddf,
   dba_segments ds
WHERE ddf.tablespace_name = ds.tablespace_name (+)
GROUP BY ddf.tablespace_name
/

 

 
-- Find the datafile size 
 
 sql>col file_name format a50;
SQL> col tablespace_Name format a10;
SQL> set pagesize 200
SQL> set linesize 200;
 
SQL> select FILE_NAME,file_id,tablespace_name,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name in ('&TABLESPACE') order by tablespace_name;
Enter value for tablespace: <'tablespace Name'>
 
SELECT B.TABLESPACE_NAME,
         ROUND (SUM (B.BYTES) / 1024 / 1024 / 1024, 2) "SIZE GB",
         ROUND (SUM (B.MAXBYTES) / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
         A.FREE_SPACE_GB "FREE SPACE GB",
         ROUND (
            (SUM (B.MAXBYTES - B.BYTES) / 1024 / 1024 / 1024) + A.FREE_SPACE_GB,
            2)
            "TOTAL FREE SPACE GB"
    FROM DBA_DATA_FILES B,
         (  SELECT TABLESPACE_NAME,
                   ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) FREE_SPACE_GB
              FROM DBA_FREE_SPACE
             WHERE TABLESPACE_NAME IN ('tablespace Name')
          GROUP BY TABLESPACE_NAME) A
   WHERE     B.TABLESPACE_NAME IN ('tablespace Name')
         AND B.TABLESPACE_NAME = A.TABLESPACE_NAME
         AND B.MAXBYTES != 0
GROUP BY B.TABLESPACE_NAME, A.FREE_SPACE_GB
ORDER BY 1;