Tablespace report
SET LINES 150
SET PAGES 50
SET FEEDBACK OFF
SET HEADING OFF
SET AUTOPRINT OFF
--SET NOTIFY OFF
SET VERIFY OFF
PROMPT ..............................................................
SELECT '** TableSpace Usage Snap For '
|| NAME
|| ' Instance Taken On '
|| RTRIM (TO_CHAR (SYSDATE, 'Day'))
|| ' '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
|| ' ** '
FROM v$database;
COMPUTE SUM LABEL 'Total' OF Tot_Spc_Alloc ON report
BREAK ON REPORT
COLUMN TSname Format A25 Heading 'Tablespace Name'
COLUMN TSid Format 999 Heading 'Tablespace#'
COLUMN Tot_Spc_Alloc Format 9,999,999,990.00 Heading 'Total Space|Allocated'
COLUMN Tot_Spc_Free Format 999,999,990.00 Heading 'Total Free|Space'
COLUMN Tot_Spc_Used Format 999,999,990.00 Heading 'Total Space|Used'
COLUMN Pct_Used Format 990.000 Heading 'Percent|Used'
COLUMN Pct_Free Format 990.000 Heading 'Percent|Free'
COLUMN FileName Format A65 Heading 'File Name'
COLUMN fileid Format 99999 Heading 'File|ID'
SET HEADING ON
SELECT tsid, tsname, pct_used, pct_free, tot_spc_alloc, tot_spc_used, tot_spc_free
FROM (SELECT c.ts# tsid,
a.tsname,
(1 - (b.tot_spc_free / a.tot_spc_alloc)) * 100 pct_used,
(b.tot_spc_free / a.tot_spc_alloc) * 100 pct_free,
a.tot_spc_alloc / 1024 / 1024 tot_spc_alloc,
(a.tot_spc_alloc - b.tot_spc_free) / 1024 / 1024 tot_spc_used,
b.tot_spc_free / 1024 / 1024 tot_spc_free
FROM (SELECT tablespace_name tsname, SUM (BYTES) tot_spc_alloc
FROM DBA_DATA_FILES
GROUP BY tablespace_name) a,
(SELECT tablespace_name tsname, SUM (BYTES) tot_spc_free, MAX (BYTES) max_b2
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) b,
v$tablespace c
WHERE a.tsname = b.tsname AND a.tsname = c.NAME
ORDER BY c.ts#);
SET HEADING ON
SELECT DISTINCT a.file_id fileid,
SUBSTR (a.file_name, 1, 60) filename,
(1 - (b.tot_spc_free / a.BYTES)) * 100 pct_used,
(b.tot_spc_free / a.BYTES) * 100 pct_free,
a.BYTES / 1024 / 1024 tot_spc_alloc,
tot_spc_free / 1024 / 1024 tot_spc_free
FROM DBA_DATA_FILES a,
(SELECT file_id, tablespace_name tsname,
SUM (BYTES) tot_spc_free, MAX (BYTES) max_b2
FROM DBA_FREE_SPACE
GROUP BY file_id, tablespace_name) b
WHERE a.file_id = b.file_id
AND a.tablespace_name in (SELECT NAME FROM v$tablespace )
order by 1;
SQL> @tablespace_info
..............................................................
** TableSpace Usage Snap For ORCL Instance Taken On Tuesday 06-DEC-2011 12:25:37 **
Percent Percent Total Space Total Space Total Free
Tablespace# Tablespace Name Used Free Allocated Used Space
----------- ------------------------- -------- -------- ----------------- --------------- ---------------
0 SYSTEM 96.168 3.832 840.00 807.81 32.19
1 SYSAUX 91.325 8.675 830.00 758.00 72.00
4 USERS 92.622 7.378 641.25 593.94 47.31
6 EXAMPLE 78.750 21.250 100.00 78.75 21.25
7 ANUJTEST 80.000 20.000 10.00 8.00 2.00
8 TSAPEXF 2.000 98.000 50.00 1.00 49.00
9 TSAPEXU 88.636 11.364 110.00 97.50 12.50
10 TEST 5.000 95.000 20.00 1.00 19.00
11 RMAN 13.875 86.125 50.00 6.94 43.06
12 DROP1 50.000 50.000 2.00 1.00 1.00
13 PERFSTAT 11.500 88.500 1,000.00 115.00 885.00
14 UNDOTBR 3.775 96.225 500.00 18.88 481.13
-----------------
Total 4,153.25
File Percent Percent Total Space Total Free
ID File Name Used Free Allocated Space
------ ----------------------------------------------------------------- -------- -------- ----------------- ---------------
1 /opt/app/oracle/oradata/orcl/system01.dbf 96.168 3.832 840.00 32.19
2 /opt/app/oracle/oradata/orcl/sysaux01.dbf 91.325 8.675 830.00 72.00
4 /opt/app/oracle/oradata/orcl/users01.dbf 92.622 7.378 641.25 47.31
5 /opt/app/oracle/oradata/orcl/example01.dbf 78.750 21.250 100.00 21.25
6 /opt/app/oracle/oradata/orcl/anujtest.dbf 80.000 20.000 10.00 2.00
7 /opt/app/oracle/oradata/orcl/tsapexf01.dbf 2.000 98.000 50.00 49.00
8 /opt/app/oracle/oradata/orcl/tsapexu01.dbf 88.636 11.364 110.00 12.50
9 /opt/app/oracle/oradata/orcl/test.dbf 5.000 95.000 20.00 19.00
10 /opt/app/oracle/oradata/orcl/rman.dbf 13.875 86.125 50.00 43.06
11 /opt/app/oracle/oradata/orcl/drop.dbf 50.000 50.000 2.00 1.00
12 /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf 11.500 88.500 1,000.00 885.00
13 /opt/app/oracle/oradata/orcl/undotbR.dbf 3.775 96.225 500.00 481.13
-----------------
Total 4,153.25