oracle查询表空间的空间占用情况-CSDN博客

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 percent ITPUB个人空间M"TB Da:o0[k0c
SQL>as ITPUB个人空间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>from ITPUB个人空间~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) b ITPUB个人空间O!L#]$^r5pWm z
SQL>where a.tablespace_name=b.tablespace_name ITPUB个人空间Q}nm1\/?R w
SQL>order by ((a.bytes-b.bytes)/a.bytes) desc;

SQL> select * from percent;

,p:NBdQH W#mV0 ITPUB个人空间1brf ZH-i9{/p
查看表空间的数据文件是否是自动扩展：
"|Y,Sz$Z0SQL> l ITPUB个人空间/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
...

发现表空间只有1.5M的空闲,猜测可能是表空间自动扩展失败的问题(表空间的增长量太高， ORACLE默认是50%),修改表空间文件扩展方式:

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9390331/viewspace-621938/