Tablespace free space can be viewed in V$TABLESPACE ✖
Tablespace free space can be viewed in DBA_TABLESPACES ✖
tbs.sql
--表空间使用率
set linesize 220 pagesize 10000
COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
ORDER BY 4 DESC;
--temp
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+);
/*
A Simple scripts to show current space usage.
*/
set linesize 400
col tablespace_name format a20
col total_Mbytes format 99999999.99
col used_Mbytes format 99999999.99
col free_Mbytes format 99999999.99
col pct_free format 99999999.99
col allocation_type format a10 heading 'ALLOCATION|TYPE'
col segment_space_management format a15 heading 'SEGMENT_SPACE|MANAGEMENT'
col initial_extent format a10 heading 'INITIAL|EXTENT'
COLUMN DUMMY NOPRINT
COMPUTE SUM OF used_Mbytes ON DUMMY
COMPUTE SUM OF free_Mbytes ON DUMMY
COMPUTE SUM OF total_Mbytes ON DUMMY
BREAK ON DUMMY
select a.tablespace_name,c.allocation_type,c.segment_space_management,
case mod(c.initial_extent,1024*1024) when 0 then c.initial_extent/1024/1024||'M'
else c.initial_extent/1024||'K' end initial_extent,
a.total_Mbytes,a.total_Mbytes - b.free_Mbytes used_Mbytes,b.free_Mbytes,
trunc(b.free_Mbytes/a.total_Mbytes * 100,2) pct_free,null dummy
from (
select tablespace_name,sum(bytes)/1024/1024 total_MBytes
from dba_data_files
group by tablespace_name
) a, (
select tablespace_name,sum(bytes)/1024/1024 free_Mbytes
from dba_free_space
group by tablespace_name
) b, dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = c.tablespace_name(+)
/
/*
A Simple scripts to show current space usage.
*/
col tablespace_name format a20
set pages 1000
col total_Mbytes format 99999999.99
col used_Mbytes format 99999999.99
col free_Mbytes format 99999999.99
col pct_free format 99999999.99
col allocation_type format a10 heading 'ALLOCATION|TYPE'
col segment_space_management format a15 heading 'SEGMENT_SPACE|MANAGEMENT'
col initial_extent format a10 heading 'INITIAL|EXTENT'
set linesize 400
set feedback off
SELECT *
FROM (SELECT A.CON_ID,
A.TABLESPACE_NAME,
C.ALLOCATION_TYPE,
C.SEGMENT_SPACE_MANAGEMENT,
CASE MOD(C.INITIAL_EXTENT, 1024 * 1024)
WHEN 0 THEN
C.INITIAL_EXTENT / 1024 / 1024 || 'M'
ELSE
C.INITIAL_EXTENT / 1024 || 'K'
END INITIAL_EXTENT,
A.TOTAL_MBYTES,
A.TOTAL_MBYTES - B.FREE_MBYTES USED_MBYTES,
B.FREE_MBYTES,
TRUNC(B.FREE_MBYTES / A.TOTAL_MBYTES * 100, 2) PCT_FREE,
NULL DUMMY
FROM (SELECT CON_ID,
TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 TOTAL_MBYTES
FROM CDB_DATA_FILES
GROUP BY TABLESPACE_NAME, CON_ID) A,
(SELECT CON_ID,
TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 FREE_MBYTES
FROM CDB_FREE_SPACE
GROUP BY TABLESPACE_NAME, CON_ID) B,
CDB_TABLESPACES C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
AND A.CON_ID = B.CON_ID
AND A.CON_ID = C.CON_ID
UNION ALL
SELECT A.CON_ID,
A.TABLESPACE_NAME,
C.ALLOCATION_TYPE,
C.SEGMENT_SPACE_MANAGEMENT,
CASE MOD(C.INITIAL_EXTENT, 1024 * 1024)
WHEN 0 THEN
C.INITIAL_EXTENT / 1024 / 1024 || 'M'
ELSE
C.INITIAL_EXTENT / 1024 || 'K'
END INITIAL_EXTENT,
A.TOTAL_MBYTES,
A.TOTAL_MBYTES - B.FREE_MBYTES USED_MBYTES,
B.FREE_MBYTES,
TRUNC(B.FREE_MBYTES / A.TOTAL_MBYTES * 100, 2) PCT_FREE,
NULL DUMMY
FROM (SELECT CON_ID,
TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 TOTAL_MBYTES
FROM CDB_TEMP_FILES
GROUP BY TABLESPACE_NAME, CON_ID) A,
(SELECT CON_ID,
TABLESPACE_NAME,
SUM(FREE_SPACE) / 1024 / 1024 FREE_MBYTES
FROM CDB_TEMP_FREE_SPACE
GROUP BY TABLESPACE_NAME, CON_ID) B,
CDB_TABLESPACES C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
AND A.CON_ID = B.CON_ID
AND A.CON_ID = C.CON_ID)
ORDER BY CON_ID
/
WITH X AS
(SELECT A.CON_ID,
A.TABLESPACE_NAME,
C.ALLOCATION_TYPE,
C.SEGMENT_SPACE_MANAGEMENT,
CASE MOD(C.INITIAL_EXTENT, 1024 * 1024)
WHEN 0 THEN
C.INITIAL_EXTENT / 1024 / 1024 || 'M'
ELSE
C.INITIAL_EXTENT / 1024 || 'K'
END INITIAL_EXTENT,
A.TOTAL_MBYTES AS TOTAL_MB,
A.TOTAL_MBYTES - B.FREE_MBYTES USED_MB,
B.FREE_MBYTES AS FREE_MB,
TRUNC(B.FREE_MBYTES / A.TOTAL_MBYTES * 100, 2) PCT_FREE,
NULL DUMMY
FROM (SELECT CON_ID,
TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 TOTAL_MBYTES
FROM CDB_DATA_FILES
GROUP BY TABLESPACE_NAME, CON_ID) A,
(SELECT CON_ID,
TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 FREE_MBYTES
FROM CDB_FREE_SPACE
GROUP BY TABLESPACE_NAME, CON_ID) B,
CDB_TABLESPACES C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
AND A.CON_ID = B.CON_ID
AND A.CON_ID = C.CON_ID
ORDER BY A.CON_ID)
SELECT CON_ID,
SUM(TOTAL_MB) AS TOTAL_MB,
SUM(USED_MB) AS TOTAL_USED_MB,
SUM(FREE_MB) AS TOTAL_FREE_MB
FROM X
GROUP BY CON_ID
/