怎么查看oracle中某个表空间下所有表的大小？ 如何查询oracle数据库某个表空间下的分区表的实际大小
oracle\u4e2d\u67e5\u8be2\u67d0\u4e2a\u5e93\u4e2d\u6240\u6709\u7684\u8868\u4ee5\u53ca\u6240\u5360\u7684\u8868\u7a7a\u95f4\u5927\u5c0f\uff0c\u6c42\u7ed9sql\u67e5\u67d0\u4e00\u7528\u6237\u4e0b\u7684\u8868
select SEGMENT_NAME,TABLESPACE_NAME,sum(BYTES/1024/1024)||'M' from USER_extents where SEGMENT_TYPE='TABLE'group by SEGMENT_NAME,TABLESPACE_NAME\u67e5\u6240\u6709\u7684\u8868
select SEGMENT_NAME,TABLESPACE_NAME,sum(BYTES/1024/1024)||'M' from dba_extents where SEGMENT_TYPE='TABLE'group by SEGMENT_NAME,TABLESPACE_NAME
SELECT segment_name, SUM(bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE segment_type = 'TABLE PARTITION' AND Tablespace_name = 'SYSTEM' GROUP BY segment_name;
用如下语句查询：
select segment_name,tablespace_name,bytes B, bytes/1024 KB, bytes/1024/1024 MB from user_segments where segment_type='TABLE' and tablespace_name='USERS'
结果：
说明，其中segment_type='TABLE'中的TABLE必须要大写，表空间的名字也必须要大写。
Sql代码
select bytes B, bytes/1024 KB, bytes/1024/1024 MB from
user_segments s where s.segment_name='TAB_PUB_CALLLOG';
结果如下：
B KB MB
---------- ---------- ----------
65536 64 0.0625
备注：表名要大写
绛旓細select * from all_all_tables 杩欐槸鏌ヨOracle涓殑鎵鏈夌殑琛紝鍖呮嫭SYS鐢ㄦ埛涓嬬殑锛屼綘鍙互鏍规嵁琛ㄧ┖闂村拰鎵灞炵敤鎴锋潵闄愬埗鏌ヨ缁撴灉 where owenr='' and tablespacename=''鎯宠鏌ュ嚭娌℃暟鎹殑璇濓紝all_all_tables涓湁涓猲um_rows瀛楁锛岃褰曡琛ㄦ暟鎹槸澶氬皯琛岀殑锛宺ows=鈥0鈥欑殑鑲畾鏄病鏁版嵁鐨勶紝select * from all_...
绛旓細yyy涓婇潰鏈夌储寮曠殑璇濋潪甯稿揩鐨勩傛垨鑰呰繕鏈夊彟澶栦竴绉嶆柟娉曪紝浣犲彲浠ヨ瘯涓涓嬨俛lter table xxx modify yyy not null ;dexter@REPO>alter table ts modify id not null ;alter table ts modify id not null*绗 1 琛屽嚭鐜伴敊璇:ORA-02296: 鏃犳硶鍚敤 (DEXTER.) - 鎵惧埌绌哄 濡傛灉鏈夌┖鍊煎氨浼氭姤閿欍
绛旓細select * from 琛ㄥ悕 where 鏌愬瓧娈 is null;鏌ヨ鏌瀛楁涓虹┖銆
绛旓細END;鐒跺悗鐐筼utput锛屾樉绀虹殑灏辨槸绌鸿〃
绛旓細oracle鏌ヨ宸叉湁鐨勮〃绌洪棿鍚嶈鍙ヤ负锛歴elect TABLESPACE_NAME from dba_tablespaces ;鏌ヨ琛绌洪棿浣跨敤鎯呭喌璇彞涓猴細SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name;鏌ョ湅琛绌洪棿鐗╃悊鏂囦欢鐨勫悕绉板強澶у皬 SELECT tablespace_name, file_id, file_...
绛旓細SQL:鏂规硶涓锛歴elect dbf.tablespace_name,dbf.totalspace "鎬婚噺(M)",dbf.totalblocks as 鎬诲潡鏁,dfs.freespace "鍓╀綑鎬婚噺(M)",dfs.freeblocks "鍓╀綑鍧楁暟",(dfs.freespace / dbf.totalspace) * 100 "绌洪棽姣斾緥"from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,sum(t....
绛旓細COUNT(*)FROM DBA_FREE_SPACE D WHERE D.TABLESPACE_NAME = 'SRC_IREADTBS'GROUP BY BLOCKS 鐪媌locks鏄繛缁潡銆傚墿浣欑┖闂翠篃鍦ㄨ繖涓琛ㄤ腑鏌ユ壘 bytes锛屽崟涓嶆槸杩欎釜琛ㄦ墍鏈夌┖闂撮兘鏄兘鐢ㄧ殑锛屾瘮濡備綘鐨勮〃绌洪棿鑻ヤ笉鏄浐瀹氱敵璇疯繛缁锛屽綋绯荤粺浼氳嚜鍔ㄥ垎閰嶄竴涓硷紝浣犵殑blocks灏忎簬杩欎釜鍊兼墍鏈夌┖闂村熀鏈敤涓嶄簡銆
绛旓細鍒ゆ柇琛ㄧ殑鎬昏鏁板拰瀛楁鏄负绌虹殑琛屾暟锛屽鏋滃瓧娈靛间负绌虹殑琛屾暟绛変簬鎬昏鏁帮紝涔熷氨鏄瀛楁娌℃湁瀛樻斁杩囨暟鎹備絾鏄笉涓瀹氬噯纭紝鍘熷洜鏄師鏉ュ瓨鍌ㄨ繃涓滆タ鍚庢潵琚疆绌轰簡锛屼篃绠楁槸娌℃湁鐢ㄨ繃鐨勶紝鐢 is null 鏉ュ垽鏂
绛旓細鏌ョ湅鐨鏂规硶鍜岃缁嗙殑鎿嶄綔姝ラ濡備笅锛1銆侀鍏堬紝鍥犱负oracle鍦↙inux绯荤粺涓嬭繍琛岋紝鎵浠ュ繀椤昏繛鎺ュ埌Linux绯荤粺锛屽涓嬪浘鎵绀猴紝鐒跺悗杩涘叆涓嬩竴姝ャ2銆佸叾娆★紝瀹屾垚涓婅堪姝ラ鍚庯紝杩炴帴鎴愬姛锛岃繘鍏Oracle鎺у埗鍙般 杈撳叆鍛戒护鈥渟qlplus / as sysdba鈥濓紝濡備笅鍥炬墍绀猴紝鐒跺悗杩涘叆涓嬩竴姝ャ3銆佹帴鐫锛屽畬鎴愪笂杩版楠ゅ悗锛屽湪sql鍛戒护琛屼笂锛...
绛旓細1.寮鍚Oracle绠＄悊宸ュ叿锛屾壘鍒癟ablespaces.2.棣栧厛瑕鐪嬬殑鏄〃绌洪棿鐨勬诲ぇ灏忓拰浣跨敤鐘跺喌浠ュ強褰撳墠鐘舵併3.Free Space 閲岄潰鏄悇涓琛ㄤ腑鐨鐩稿叧澶у皬淇℃伅(涓涓〃绌洪棿鍙兘鏈夊涓〃鍦ㄤ娇鐢)銆4.Objects 灏卞绗笁姝ヤ腑鎵鎻愬埌鐨勪娇鐢ㄨ繖涓〃绌洪棿鐨勮〃鏄庣粏銆5.Script 鏄垱寤鸿〃绌洪棿鐨凱L/SQL锛屾垜浠彲浠ュ幓浜嗚В瀹冪殑鐩稿叧淇℃伅鍜...
