oracle查詢表空間的空間佔用情況

select a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes - b.bytes)/a.bytes)*100,2) percent_used
sG N.S9e0from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,ITPUB個人空間 jg!NHk"c5bL
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
a){%|3Q+Q0where a.tablespace_name=b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc

 

select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",ITPUB個人空間1gH9U!m.A_!q]LB$H5P/v
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"ITPUB個人空間5`~V;]9S(KD:?T
fromITPUB個人空間rj}8s P UFm
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
b,D)h;vb:^'A!Z H0(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
D+BB"sbD z+nA!q-I0where a.tablespace_name=b.tablespace_name
]@p~E7k\0order by ((a.bytes-b.bytes)/a.bytes) desc

@J t&Pd-Lf8[0查詢所有表空間的總容量、已經使用、剩餘、已經使用的百分比！

select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",ITPUB個人空間'S;c*x8`5b |-X i
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"ITPUB個人空間:B}efq
from
4a}I$uE!b;C xv%fi0(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,ITPUB個人空間+HNjQ*]M6x0ky}
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
F&JkI$Lw ` n0where a.tablespace_name=b.tablespace_nameITPUB個人空間#Qz$q_c m
order by ((a.bytes-b.bytes)/a.bytes) desc

ITPUB個人空間 u'o$\{v5l7qEYE?
一般來說可以把上面的複雜的查詢語句放入一個檔案中，需要時再呼叫，或者建立一個試圖，需要時可以查詢。
&n-~2G R7]-nz01  寫入檔案:#vi /home/mzl/percent_used_tablespace.sql
K;OBr FYpR0內容：
/|4i|9Z2P@)r0select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",ITPUB個人空間j8X F9`sA8^{)_k
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
9B1CU*n6^-C0fromITPUB個人空間 g2mI }OeC,`
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
&M*tWf4G N4hd'e+jBBQ0(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) bITPUB個人空間VD&b4IJGV~e
where a.tablespace_name=b.tablespace_nameITPUB個人空間 c(I3n$rC3ICv
order by ((a.bytes-b.bytes)/a.bytes) desc

2 匯入：
|f;{*NA8r0SQL> @/home/mzl/percent_used_tablespace.sqlITPUB個人空間+tO5\_.S
SQL> l
*_&Sys.dS0  1  select a.tablespace_name,a.bytes "Sum",a.bytes-b.bytes "used",b.bytes "free",
a? v2w#]'P6u0  2  round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"ITPUB個人空間Q:[r rrK ?7G
  3  fromITPUB個人空間 @ [BSv"e6_s%ENS-f
  4  (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,ITPUB個人空間9uC \ d{-`6P)u yU
  5  (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
f9V]C5t*Q gE p0  6  where a.tablespace_name=b.tablespace_name
`/zDq6bV*oO'O6h0  7* order by ((a.bytes-b.bytes)/a.bytes) desc
#J[ N,@&oc6E2O8_Y9w0SQL> /

?!i7SRv/K8Z0
WmEG O)WI-j9`6`0或者建立檢視：ITPUB個人空間2U5T Y,^ r9g%Q+uH9B
SQL>create view percentITPUB個人空間M"TB Da:o0[k0c
SQL>asITPUB個人空間G;C*{q?b `'d6T JP
SQL>select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",ITPUB個人空間h P^VN
SQL>round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
)[8P.m#g f&~i0SQL>fromITPUB個人空間~0D?8| [z$v
SQL>(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,ITPUB個人空間4b'r"\(r]2}?.A
SQL>(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) bITPUB個人空間O!L#]$^r5pWm z
SQL>where a.tablespace_name=b.tablespace_nameITPUB個人空間Q}nm1\/?R w
SQL>order by ((a.bytes-b.bytes)/a.bytes) desc;

SQL> select * from percent;

,p:NBdQH W#mV0ITPUB個人空間1brf ZH-i9{/p
檢視錶空間的資料檔案是否是自動擴充套件：
"|Y,Sz$Z0SQL> lITPUB個人空間/gJ6T+ZE"W)sLg2~
  1* select file_name,tablespace_name,autoextensible from dba_data_files
,^:n7A({%r*| J$Sx3u0SQL> /

FILE_NAME                                     TABLESPACE_NAME                AUT
7f4xd~p5i8F!x5w+H0--------------------------------------------- ------------------------------ ---ITPUB個人空間} Q6N)\A j0}O~&g
/u01/app/oracle/oradata/orcl/risenet.dbf      RISENET
z#c Ay0~0t'w0/u01/app/oracle/oradata/orcl/perfstat.dbf     PERFSTAT                       NOITPUB個人空間"Nl@ K,pG6@h
/u01/app/oracle/oradata/orcl/example01.dbf    EXAMPLE                        YES
"CU vo&Qe$lE0/u01/disk1/users01.dbf                        USERS                          YESITPUB個人空間(|:j(gk,hs
/u01/app/oracle/oradata/orcl/sysaux01.dbf     SYSAUX                         YESITPUB個人空間"jwF~!C;SH(_
/u01/app/oracle/oradata/orcl/undotbs01.dbf    UNDOTBS1
;I(o$a'b"^9\0/u01/disk2/system01.dbf                       SYSTEM                         YES
"[Y)BgT0/u01/app/oracle/oradata/orcl/undotbs02.dbf    UNDOTBS2                       NO
(oqJ/Ru9G h5w0/u01/disk1/pioneer_data.dbf                   PIONEER_DATA                   YESITPUB個人空間;f;r6rug
/u01/disk2/pioneer_indx.dbf                   PIONEER_INDX                   NO
-U-l[ t;tj0/u01/disk3/pioneer_undo.dbf                   PIONEER_UNDO                   NO

FILE_NAME                                     TABLESPACE_NAME                AUT
Qi"L Ad1Y"n!Cm0--------------------------------------------- ------------------------------ ---ITPUB個人空間wL%VE7X\.]S
/u01/app/oracle/oradata/orcl/paul01.dbf       PAUL                           NO
%dsY1\%U0/u01/disk1/wenchuan.dbf                       WENCHUAN                       NO

13 rows selected.

ITPUB個人空間^sn8{Q%gtA#|*k{
比如表空間PIONEER_INDX已經用了83.33％，資料檔案不能自動擴充套件，可以修改成自動擴充套件，以免資料寫滿資料檔案。ITPUB個人空間v V/{$?gwSz-N
SQL> alter databaseITPUB個人空間:C-lJE q
  2  datafile '/u01/disk2/pioneer_indx.dbf'  autoextend on;

Database altered.

SQL> select file_name,tablespace_name,autoextensible from dba_data_files     ITPUB個人空間 p"bk:Qfj{"@t
  2  where tablespace_name='PIONEER_INDX';

FILE_NAME                                     TABLESPACE_NAME                AUT
*T/M6gD\8N L0--------------------------------------------- ------------------------------ ---ITPUB個人空間b#W V|7Vyg/P~
/u01/disk2/pioneer_indx.dbf                   PIONEER_INDX                   YES

ITPUB個人空間Q,u~ rZ0J!A
或者給表空間多加一個自動擴充套件的資料檔案,如果有多個硬碟，可以增加多個資料檔案（這樣多資料庫系統的併發性比較好）ITPUB個人空間8U.G}nn
SQL> alter tablespace pioneer_indx
"r$e#~#I)\5b&q4G'j0  2  add datafile size 30M;

Tablespace altered.

SQL> select file_name,tablespace_name,bytes/1024/1024 "MB"  from dba_data_filesITPUB個人空間 Yb;mCP `-Y'F
  2  where tablespace_name='PIONEER_INDX';

FILE_NAME                                     TABLESPACE_NAME
Jh"\LM5l2AbT0--------------------------------------------- ------------------------------ITPUB個人空間.d^|OoCS'I
        MB
.RdfT7w)Kr!D0----------ITPUB個人空間`XT,~\ Uk*OY
/u01/disk2/pioneer_indx.dbf                   PIONEER_INDXITPUB個人空間A X;h o/}.u
         6

/u01/disk5/ORCL/datafile/o1_mf_pioneer__45dpy PIONEER_INDX
n%q%aK2J5p(AB^0fty_.dbf
]p8yo ~0        30
cV+tB.x0^0

----查詢表空間使用情況---
knA V3Paa#{l0使用DBA許可權登陸ITPUB個人空間Us"|*m$v^;E;I
SELECT UPPER(F.TABLESPACE_NAME) "表空間名",ITPUB個人空間4Ej1kw1f#Hj \0B Y
D.TOT_GROOTTE_MB "表空間大小(M)",
VT8n:HjI%z0D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
H h1^"ZZnV0TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
O8@w#k!oIO0QZX0F.TOTAL_BYTES "空閒空間(M)",ITPUB個人空間J5}fj'cn p;Z
F.MAX_BYTES "最大塊(M)"ITPUB個人空間;hxWf;ThT/vN;u
FROM (SELECT TABLESPACE_NAME,ITPUB個人空間3aJx FU1Y
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
JwK{%aT0ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
p)WjB7?^kY D0FROM SYS.DBA_FREE_SPACEITPUB個人空間4x q#D:U7q T.Io
GROUP BY TABLESPACE_NAME) F,ITPUB個人空間*F A Ye,t-BfA-hz
(SELECT DD.TABLESPACE_NAME,ITPUB個人空間M.V\7L}t*Wc'F/y
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBITPUB個人空間'K-l&y \ dk:c
FROM SYS.DBA_DATA_FILES DD
HG,L-j.O.]v"FG0GROUP BY DD.TABLESPACE_NAME) D
F DU0pMA0WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
y!q.t.i$} RL"lb0ORDER BY 4 DESC;
;d)^8TuofkW5X\+U0表空間名                       表空間大小(M) 已使用空間(M) 使用比 空閒空間(M) 最大塊(M)
V-?x8hRD0------------------------------ ------------- ------------- ------- ----------- ----------
Xrz8jy3]^-qF0...ITPUB個人空間 s5T+re${(u5S|"x2K
CCEN                                      10           8.5   85.00         1.5        .94ITPUB個人空間*eyOFia+t
...

l9P9zJ Q)Pyv0SQL>ALTER DATABASEITPUB個人空間.O@'TQ%K1S
    DATAFILE '/u01/oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND
2o0lp6p:j,F[j0    ON NEXT 50M MAXSIZE UNLIMITED
#E i&]3K(Xo0問題解決.
9Hze\;a1]0
1q%n#D7D8U3D0
-E8S9S'ew/o&[9Z0檢視錶空間是否具有自動擴充套件的能力ITPUB個人空間I;})PZ;O
SELECT T.TABLESPACE_NAME,D.FILE_NAME,ITPUB個人空間 W/Z A/Czuq9At
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
](VljO0x e ^ Jk0FROM DBA_TABLESPACES T,DBA_DATA_FILES DITPUB個人空間 ]'ER$v!k.c
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAMEITPUB個人空間2W Q.v1c4r C8D
ORDER BY TABLESPACE_NAME,FILE_NAME;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9390331/viewspace-621938/，如需轉載，請註明出處，否則將追究法律責任。