check table space sql-优快云博客

本文介绍了一个查询Oracle数据库中各表空间使用情况的方法，包括自由空间大小、数据文件大小及百分比。通过SQL查询，展示了不同表空间的具体数据，并提供了获取特定表空间下数据文件详细信息的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> select tablespace_name,
2 sum(free_space) as free_space,
3 sum(data_files) as data_files,
4 round(sum(free_space)/sum(data_files)*100,2) as percent
5 from
6 (
7 select tablespace_name, sum(bytes)/1048576 as free_space, 0 as data_files
8 from dba_free_space
9 group by tablespace_name
10 union all
11 select tablespace_name, 0, sum(bytes)/1048576 as data_files
12 from dba_data_files
13 group by tablespace_name
14 )
15 group by tablespace_name
16 order by 1
17 /

TABLESPACE_NAME FREE_SPACE DATA_FILES PERCENT
------------------------------ ---------- ---------- ----------
CHG_IDX_TS1 9545.375 16136 59.16
CHG_IDX_TS2 942.6875 8192 11.51
CHG_TS1 5141.75 21504 23.91
CHG_TS2 986.75 1024 96.36
CONV 12425.0625 16384 75.84
COR_IDX_TS1 1063.9375 25088 4.24
COR_IDX_TS2 1366.75 6656 20.53
COR_IDX_TS3 495.875 11364 4.36
COR_TS1 1105.3125 25856 4.27
COR_TS2 900.875 4096 21.99
COR_TS3 778.75 3072 25.35

TABLESPACE_NAME FREE_SPACE DATA_FILES PERCENT
------------------------------ ---------- ---------- ----------
CWMLITE 150.9375 300 50.31
ECC_TS1 432.875 1024 42.27
FND_IDX_TS1 518.4375 3584 14.47
FND_TS1 936.3125 4096 22.86
GLO_IDX_TS1 1259.0625 2560 49.18
GLO_TS1 940.75 3072 30.62
HIS_TS1 844.75 6784 12.45
OUTLN 9.9375 10 99.38
PERF 1570.6875 2304 68.17
SYSTEM 731.25 1500 48.75
TOOLS 27.9375 50 55.88

TABLESPACE_NAME FREE_SPACE DATA_FILES PERCENT
------------------------------ ---------- ---------- ----------
UNDOTBS_A 6939.4375 7168 96.81
UNDOTBS_B 6869.5 7168 95.84
UNDOTBS_C 4667.4375 4768 97.89
USERS 2455.625 14336 17.13
WOA_IDX_TS1 353.1875 1024 34.49
WOA_TS1 1964.0625 2048 95.9
WOI_IDX_TS1 985.0625 2048 48.1
WOI_TS1 1468.0625 2048 71.68
WOM_IDX_TS1 519.4375 9984 5.2
WOM_TS1 670.5625 8192 8.19

32 rows selected.

SQL> select df.NAME,dfs.BYTES,df.CREATE_BYTES From dba_free_space dfs,v$datafile
df,V$TABLESPACE ts
2 where dfs.TABLESPACE_NAME = ts.NAME
3 and ts.TS#=df.TS#;

NAME BYTES CREATE_BYTES
---------------------------------------- ---------- ------------
/app/oradata/feng/system01.dbf 89718784 262144000
/app/oradata/feng/system01.dbf 65536 262144000
/app/oradata/feng/undotbs01.dbf 137297920 209715200
/app/oradata/feng/drsys01.dbf 20905984 20971520
/app/oradata/feng/indx01.dbf 26148864 26214400
/app/oradata/feng/tools01.dbf 10420224 10485760
/app/oradata/feng/users01.dbf 26083328 26214400
/app/oradata/feng/xdb01.dbf 20905984 20971520

8 rows selected.