如何查看oracle表空间已使用大小
\u5982\u4f55\u67e5\u770boracle\u8868\u7a7a\u95f4\u5df2\u4f7f\u7528\u5927\u5c0f1. \u67e5\u770b\u6240\u6709\u8868\u7a7a\u95f4\u5927\u5c0f
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
2 group by tablespace_name;
2. \u5df2\u7ecf\u4f7f\u7528\u7684\u8868\u7a7a\u95f4\u5927\u5c0f
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
2 group by tablespace_name;
3. \u6240\u4ee5\u4f7f\u7528\u7a7a\u95f4\u53ef\u4ee5\u8fd9\u6837\u8ba1\u7b97
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
4. \u4e0b\u9762\u8fd9\u6761\u8bed\u53e5\u67e5\u770b\u6240\u6709segment\u7684\u5927\u5c0f\u3002
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
5. \u8fd8\u6709\u5728\u547d\u4ee4\u884c\u60c5\u51b5\u4e0b\u5982\u4f55\u5c06\u7ed3\u679c\u653e\u5230\u4e00\u4e2a\u6587\u4ef6\u91cc\u3002
SQL> spool out.txt
SQL> select * from v$database;
SQL> spool off
1. \u7ed9\u4f60\u4e2a\u6700\u5b9e\u7528\u7684\u5de5\u5177\uff1aSQL Developer \u8fd9\u4e2a\u662foracle\u5b98\u65b9\u7684\u5de5\u5177
\u4e0b\u8f7d\u5730\u5740\uff1ahttp://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
\u4e0b\u8f7d\u5b8c\u6210\u8fde\u63a5\u4e86\u6570\u636e\u5e93\u4e4b\u540e \u53f3\u952e \u70b9\u51fb\u7ba1\u7406\u6570\u636e\u5e93\u5c31\u53ef\u4ee5\u770b\u5230\u6240\u6709\u7684\u8868\u7a7a\u95f4\u5b9e\u7528\u60c5\u51b5
2.\u4e5f\u53ef\u4ee5\u4f7f\u7528 Toad for Oracle
3.\u5f53\u7136\u4f60\u8fd8\u53ef\u4ee5\u5728\u4f60\u4f7f\u7528\u7684sql\u5de5\u5177\u4e2d\u7528sql\u67e5\u8be2 \u6bd4\u5982plsql
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
绛旓細鍩烘湰涓鏌ヨdba_free_space灏辫兘鏌ュ埌琛ㄧ┖闂鐨勫墿浣欓噺锛屽鏋滈潪瑕佹煡璇㈢敤浜嗙櫨鍒嗕箣澶氬皯涔嬬被鐨勶紝杩欏紶琛ㄧ粡杩囪绠楀氨鑳藉緱鍒般傚鏋滀笉鎯虫煡杩欏紶琛紝涔熷彲浠ラ氳繃sys.sm$ts_avail锛 sys.sm$ts_used 鍜 sys.sm$ts_free 涓夊紶琛ㄨ仈鍚堟煡璇㈠緱鍒般傦紙2锛夌储寮曡〃鈥斺旂┖闂寸殑浣跨敤鎯呭喌锛屼篃灏辨槸璇存煡鐨勬槸绱㈠紩鍗犵敤鐨勭┖闂寸殑鎯呭喌锛...
绛旓細1. 鍏ㄩ儴琛ㄧ┖闂鐨勫ぇ灏 select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;TABLESPACE_NAME SUM(BYTES)/1024/1024 --- --- UNDOTBS1 ...
绛旓細1銆鏌ョ湅Oracle鏁版嵁搴撲腑鏁版嵁鏂囦欢淇℃伅鐨勫伐鍏锋柟娉曪細浣跨敤涓婇潰浠嬬粛杩囩殑鏂规硶鐧诲綍oracle enterprise manager console宸ュ叿锛岄夋嫨‘瀛樺偍’ 鏁版嵁鏂囦欢锛屼細鐪嬪埌濡備笅鐨勭晫闈紝璇ョ晫闈㈡樉绀轰簡鏁版嵁鏂囦欢鍚嶇О锛琛ㄧ┖闂鍚嶇О锛屼互鍏嗕负鍗曚綅鐨勬暟鎹枃浠跺ぇ灏忥紝宸蹭娇鐢ㄧ殑鏁版嵁鏂囦欢澶у皬鍙婃暟鎹枃浠跺埄鐢ㄧ巼銆
绛旓細鏌ョ湅涓存椂琛ㄧ┖闂鐨勪娇鐢ㄦ儏鍐碉紙GV_$TEMP_SPACE_HEADER瑙嗗浘蹇呴』鍦╯ys鐢ㄦ埛涓嬫墠鑳鏌ヨ锛塆V_$TEMP_SPACE_HEADER瑙嗗浘璁板綍浜嗕复鏃惰〃绌洪棿鐨勪娇鐢ㄥぇ灏忎笌鏈娇鐢ㄧ殑澶у皬dba_temp_files瑙嗗浘鐨刡ytes瀛楁璁板綍鐨勬槸涓存椂琛ㄧ┖闂寸殑鎬诲ぇ灏:SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", ...
绛旓細鏌ョ湅Oracle鏁版嵁搴撲腑鏁版嵁鏂囦欢淇℃伅鐨勫伐鍏锋柟娉 浣跨敤涓婇潰浠嬬粛杩囩殑鏂规硶鐧诲綍oracle enterprise manager console宸ュ叿 閫夋嫨 瀛樺偍 鈥斺旀暟鎹枃浠 浼氱湅鍒板涓嬬殑鐣岄潰 璇ョ晫闈㈡樉绀轰簡鏁版嵁鏂囦欢鍚嶇О 琛ㄧ┖闂鍚嶇О 浠 鍏 涓哄崟浣嶇殑鏁版嵁鏂囦欢澶у皬 宸蹭娇鐢ㄧ殑鏁版嵁鏂囦欢澶у皬鍙婃暟鎹枃浠跺埄鐢ㄧ巼 鏌ョ湅Oracle鏁版嵁搴撲腑鏁版嵁鏂囦欢淇℃伅鐨勫懡浠ゆ柟娉 閫氳繃...
绛旓細Oracle涓煡璇㈡墍鏈夎〃鍙婂叾鎵浣跨敤鐨勮〃绌洪棿鍙互浣跨敤SQL璇彞锛歴elect Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;鍦ㄦ暟鎹簱绠＄悊鍛樼殑鏃ュ父宸ヤ綔涓紝搴旇缁忓父鏌ヨ琛ㄧ┖闂鐨勫埄鐢ㄧ巼锛屾寜鐓ф暟鎹簱绯荤粺鐨勫叿浣撴儏鍐典及绠楄〃绌洪棿鐨勫闀块噺锛屽綋琛ㄧ┖闂寸殑鍒╃敤鐜囪秴杩90%鏃讹紝瑕佸強鏃堕噰鍙栨帾鏂姐
绛旓細SELECT UPPER(F.TABLESPACE_NAME) "琛ㄧ┖闂鍚",D.TOT_GROOTTE_MB "琛ㄧ┖闂村ぇ灏(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "宸蹭娇鐢ㄧ┖闂(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "浣跨敤姣",F.TOTAL_BYTES "绌洪棽绌洪棿(...
绛旓細鍙互浣跨敤涓嬮潰鐨勮剼鏈 FROM ASKMACLEAN;REM tablespace reportset linesize 200select a.tablespace_name, round(a.bytes_alloc / 1024 / 1024) megs_alloc, round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free, round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024...
绛旓細b.file_name, b.bytes order by b.tablespace_name;鏌ヨ缁撴灉锛氳В璇伙細璇ヨ鍙ラ氳繃鏌ヨdba_free_space锛宒ba_data_files锛宒ba_tablespaces杩欎笁涓暟鎹瓧鍏歌〃锛屽緱鍒颁簡琛ㄧ┖闂鍚嶇О锛岃〃绌洪棿绫诲瀷锛屽尯绠＄悊绫诲瀷锛屼互鈥濆厗鈥濅负鍗曚綅鐨勮〃绌洪棿澶у皬锛屽凡浣跨敤鐨勮〃绌洪棿澶у皬鍙婅〃绌洪棿鍒╃敤鐜囥俤ba_free_space琛ㄦ弿杩颁簡琛ㄧ┖闂寸殑...
绛旓細鍙互閫氳繃浠ヤ笅璇彞鏌ョ湅鎵鏈夌殑琛ㄧ┖闂澶у皬锛歴ql锛歋ELECT C.TABLESPACE_NAME,A.BYTES/1048576 MEGS_TOTAL,(A.BYTES-B.BYTES)/1048576 MEGS_USED,B.BYTES/1048576 MEGS_FREE,(A.BYTES-B.BYTES)/A.BYTES * 100 PCT_USED, B.BYTES/A.BYTES * 100 PCT_FREE FROM (SELECT TABLESPACE_NAME,SUM(A....
