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;
