一：表空间的大小
SQL> SELECT DISTINCT TABLESPACE_NAME,SUM(BYTES/1024/1024) "TABLESPACE SIZE" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME TABLESPACE SIZE
------------------------------ ---------------
SL_DATA_TS 5120
SYSAUX 1024
UNDOTBS1 1345
USERS 8000
TEST 3096
SYSTEM 1024
UNDOTBS2 1100
BCLOG 1048
8 rows selected
二：统计数据库的每月增长量
SQL> SELECT TO_CHAR(CREATION_TIME, RRRR MONTH) "MONTH",
2 SUM(BYTES) /1024/1024/1024 "GROWTH SIZE"
3 FROM SYS.V_$DATAFILE
4 WHERE CREATION_TIME > SYSDATE - 365
5 GROUP BY TO_CHAR(CREATION_TIME, RRRR MONTH);
MONTH GROWTH SIZE
----------- -----------
2008 12月 10.12109375
三：统计数据文件的每月增长量
SQL> SELECT A.TS# AS "TABLESPACE NUMBER",
2 B.NAME AS "TABLESPACE NAME",
3 TO_CHAR (A.CREATION_TIME, RRRR MONTH) "MONTH",
4 SUM (A.BYTES) /1024/1024/1024 "GROWTH SIZE"
5 FROM SYS.V_$DATAFILE A, SYS.V_$TABLESPACE B
6 WHERE A.CREATION_TIME > SYSDATE - 365
7 AND A.TS# = B.TS#
8 GROUP BY A.TS#,B.NAME, TO_CHAR(A.CREATION_TIME,RRRR MONTH);
TABLESPACE NUMBER TABLESPACE NAME MONTH GROWTH SIZE
----------------- ------------------------------ ----------- -----------
5 UNDOTBS2 2008 12月 1.07421875
6 SL_DATA_TS 2008 12月 5
7 TEST 2008 12月 3.0234375
8 BCLOG 2008 12月 1.0234375
四.统计表空间的空闲空间
SQL> SELECT A.TABLESPACE_NAME,SUM((A.TOTS) /1024/1024) "TOTAL SIZE", SUM((A.SUMB) /1024/1024) "TOTAL FREE SIZE",
2 SUM(A.SUMB) * 100 / SUM(A.TOTS) PCT_FREE, SUM(A.LARGEST) "MAX FREE SIZE",SUM(A.CHUNKS) "FREE EXTENT NUMBER"
3 FROM (SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,MAX(BYTES) LARGEST,COUNT(*) CHUNKS FROM DBA_FREE_SPACE A
4 GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME,SUM(BYTES) TOTS, 0,0,0 FROM DBA_DATA_FILES
5 GROUP BY TABLESPACE_NAME) A
6 GROUP BY A.TABLESPACE_NAME;
TABLESPACE_NAME TOTAL SIZE TOTAL FREE SIZE PCT_FREE MAX FREE SIZE FREE EXTENT NUMBER
------------------------------ ---------- --------------- ---------- ------------- ------------------
SL_DATA_TS 5120 1355.3125 26.4709472 24117248 977
SYSAUX 1024 49.9375 4.87670898 37683200 55
UNDOTBS1 1345 1243.5625 92.4581784 500105216 720
TEST 3096 1170.25 37.7987726 1226768384 2
USERS 8000 1905.25 23.815625 880803840 898
SYSTEM 1024 72.375 7.06787109 75431936