oracle数据中怎么查看表空间的名称及大小？ 如何在Oracle中查看各个表，表空间占用空间的大小
oracle\u600e\u4e48\u67e5\u8be2\u6240\u6709\u7684\u8868\u7a7a\u95f4\u7684\u540d\u79f0?oracle\u6570\u636e\u5e93\u4e2d\uff0c\u67e5\u8be2\u7d20\u6709\u8868\u7a7a\u95f4\u7684\u540d\u79f0\u53ea\u9700\u8981\u4e00\u6761sql\u8bed\u53e5\u5373\u53ef\uff1a
select tablespace_name from user_tablespaces\uff1b
\u7ed3\u679c\u8f93\u51fa\u5982\u4e0b\u56fe\uff1a
\u5728\u4e0a\u5f0f\u7684sql\u4e2d\uff0c\u201cuser_tablespaces\u201d\u5373\u4e3a\u8868\u7a7a\u95f4\u4fe1\u606f\u6240\u5728\u8868\uff0c\u6240\u9700\u7684\u8868\u7a7a\u95f4\u4fe1\u606f\u9700\u8981\u4ece\u8be5\u8868\u4e2d\u83b7\u53d6\uff0c\u201ctablespace_name\u201d\u5373\u4e3a\u8868\u7a7a\u95f4\u540d\u79f0,
\u5982\u679c\u5e0c\u671b\u67e5\u8be2\u6240\u6709\u8868\u7a7a\u95f4\u540d\u79f0\u548c\u5176\u4ed6\u76f8\u5173\u4fe1\u606f\uff0c\u53ef\u4ee5\u5c06\u4f7f\u7528\u5982\u4e0bsql\u8bed\u53e5\uff1a
select * from user_tablespaces\uff1b
\u7ed3\u679c\u8f93\u51fa\u5982\u4e0b\uff1a
\u6269\u5c55\u8d44\u6599\uff1a
\u9488\u5bf9\u8868\u7a7a\u95f4\uff0c\u8fd8\u6709\u5176\u4ed6\u7684\u67e5\u8be2\u53ef\u4f9b\u53c2\u8003\uff1a
1\u3001\u67e5\u770b\u8868\u7a7a\u95f4\u7684\u540d\u79f0\u53ca\u5bf9\u5e94\u5927\u5c0f
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;
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\uff1a
2\u3001\u67e5\u770b\u8868\u7a7a\u95f4\u7269\u7406\u6587\u4ef6\u7684\u540d\u79f0\u53ca\u5927\u5c0f
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files ORDER BY tablespace_name;
\u8f93\u51fa\u7ed3\u679c\u4e3a\uff1a
3\u3001\u67e5\u8be2\u5f53\u524d\u7528\u6237\u6240\u6709\u8868\u540d\u53ca\u5176\u6240\u5c5e\u8868\u7a7a\u95f4
select table_name \u8868\u540d ,tablespace_name \u6240\u4f7f\u7528\u8868\u7a7a\u95f4 from user_tables;
\u8f93\u51fa\u7ed3\u679c\u4e3a\uff1a
\u5728Oracle\u4e2d\u67e5\u770b\u5404\u8868\u53ca\u8868\u7a7a\u95f4\u5360\u7528\u7a7a\u95f4\u5927\u5c0f\u53ef\u7528sql\u8bed\u53e5\u6267\u884c\u67e5\u770b\u3002
Oracle\u7248\u672c\uff1aOracle 10g
\u4e00\u3001\u67e5\u770b\u8868\u5360\u7528\u7a7a\u95f4\u5927\u5c0f\u8bed\u53e5\uff1a
select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "\u5360\u7528\u7a7a\u95f4(M)"from dba_segments twhere t.segment_type='TABLE'group by OWNER, t.segment_name, t.segment_type;\u67e5\u8be2\u7ed3\u679c\uff1a
\u4e8c\u3001\u67e5\u770b\u8868\u7a7a\u95f4\u5360\u7528\u7a7a\u95f4\u5927\u5c0f\u8bed\u53e5\uff1a
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc\u67e5\u8be2\u7ed3\u679c\uff1a
可用如下语句：
select b.file_name 物理文件名,b.tablespace_name 表空间,b.bytes / 1024 / 1024 大小M,(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name,b.file_name, b.bytes
order by b.tablespace_name;
查询结果：
解读：
该语句通过查询dba_free_space，dba_data_files，dba_tablespaces这三个数据字典表，得到了表空间名称，表空间类型，区管理类型，以”兆”为单位的表空间大小，已使用的表空间大小及表空间利用率。dba_free_space表描述了表空间的空闲大小，dba_data_files表描述了数据库中的数据文件，dba_tablespaces表描述了数据库中的表空间。
上面语句中from子句后有三个select语句，每个select语句相当于一个视图，视图的名称分别为a、b、c，通过它们之间的关联关系，我们得到了表空间的相关信息。
查询当前表空间：
select username,default_tablespace from user_users
查询所有表空间：
select tablespace_name from dba_tablespaces
查询所有表空间大小：
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name
绛旓細if i_qty = 0 then 鍒欎负绌
绛旓細1銆侀鍏鏌ヨ涓氫富琛ㄤ腑鐨勫湴鍧id锛屼笉閲嶅鏄剧ず銆2銆佺劧鍚庡t_account鏍囨寜浣跨敤閲忚繘琛屽崌搴忔帓搴忥紝榛樿鏄崌搴忥紝asc鍙互鐪佺暐銆3銆佹帴鐫瀵箃_account琛ㄦ寜浣跨敤閲忚繘琛岄檷搴忔帓搴忋4銆佺劧鍚庨氳繃rownum浼垪鍙互闄愬埗鏌ヨ缁撴灉闆嗕腑杩斿洖鐨勮鏁般5銆佹渶鍚庣┖鍊兼煡璇⑩斺斺憼鏌ヨt_pricetable琛ㄤ腑maxnum涓虹┖鐨勮褰曪紱鈶℃煡璇_pricetable琛...
绛旓細select * from all_all_tables 杩欐槸鏌ヨOracle涓鐨勬墍鏈夌殑琛紝鍖呮嫭SYS鐢ㄦ埛涓嬬殑锛屼綘鍙互鏍规嵁琛ㄧ┖闂村拰鎵灞炵敤鎴锋潵闄愬埗鏌ヨ缁撴灉 where owenr='' and tablespacename=''鎯宠鏌ュ嚭娌℃暟鎹殑璇濓紝all_all_tables涓湁涓猲um_rows瀛楁锛岃褰曡琛ㄦ暟鎹鏄灏戣鐨勶紝rows=鈥0鈥欑殑鑲畾鏄病鏁版嵁鐨勶紝select * from all_...
绛旓細瑙ｈ锛氳璇彞閫氳繃鏌ヨdba_free_space锛宒ba_data_files锛宒ba_tablespaces杩欎笁涓鏁版嵁瀛楀吀琛紝寰楀埌浜嗚〃绌洪棿鍚嶇О锛岃〃绌洪棿绫诲瀷锛屽尯绠＄悊绫诲瀷锛屼互鈥濆厗鈥濅负鍗曚綅鐨勮〃绌洪棿澶у皬锛屽凡浣跨敤鐨勮〃绌洪棿澶у皬鍙婅〃绌洪棿鍒╃敤鐜囥俤ba_free_space琛ㄦ弿杩颁簡琛ㄧ┖闂寸殑绌洪棽澶у皬锛宒ba_data_files琛ㄦ弿杩颁簡鏁版嵁搴撲腑鐨勬暟鎹枃浠讹紝dba_tablespac...
绛旓細select count(*) from dates濡傛灉缁撴灉涓0灏辫鏄庤〃涓棤璁板綍銆
绛旓細SELECT BLOCKS, COUNT(*)FROM DBA_FREE_SPACE D WHERE D.TABLESPACE_NAME = 'SRC_IREADTBS'GROUP BY BLOCKS 鐪媌locks鏄繛缁潡銆傚墿浣欑┖闂翠篃鍦ㄨ繖涓〃涓煡鎵 bytes锛屽崟涓嶆槸杩欎釜琛ㄦ墍鏈夌┖闂撮兘鏄兘鐢ㄧ殑锛屾瘮濡備綘鐨勮〃绌洪棿鑻ヤ笉鏄浐瀹氱敵璇疯繛缁锛屽綋绯荤粺浼氳嚜鍔ㄥ垎閰嶄竴涓硷紝浣犵殑blocks灏忎簬杩欎釜鍊兼墍鏈夌┖闂村熀鏈敤...
绛旓細oracle 鏁版嵁搴撻噷鏌ョ湅琛绌洪棿浣跨敤鐘跺喌锛oracle琛绌洪棿鐨勪簨鎯呯姸鍐佃缁忓父鏌ョ湅锛屼竴鑸┖闂叉瘮渚嬭繃浣庣殑鏃跺欏氨搴旇鑰冭檻澧炲ぇ琛ㄧ湅绌洪棿浜嗐傛煡鐪嬫柟娉曞涓媠ql:鏂规硶涓锛歴elect dbf.tablespace_name,dbf.totalspace "鎬婚噺(m)",dbf.totalblocks as 鎬诲潡鏁,dfs.freespace "鍓╀綑鎬婚噺(m)",dfs.freeblocks "鍓╀綑鍧楁暟",(dfs....
绛旓細1銆佸垱寤鸿〃娣诲姞鏁版嵁锛歝reate table test(id int,ttime date);insert into test values (1,sysdate);insert into test values (2,sysdate+1);insert into test values (3,null);commit;2銆佹墽琛岃鍙ワ細select * from test where ttime is null;3銆佽繖鏍蜂互鍚庯紝ID涓3鐨勯偅鏉℃暟鎹槸绌猴紝灏变細琚瓫閫夊嚭鏉...
绛旓細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) megs_used, round((nvl(b....
绛旓細鍒ゆ柇琛ㄧ殑鎬昏鏁板拰瀛楁鏄负绌虹殑琛屾暟锛屽鏋滃瓧娈靛间负绌虹殑琛屾暟绛変簬鎬昏鏁帮紝涔熷氨鏄瀛楁娌℃湁瀛樻斁杩鏁版嵁銆備絾鏄笉涓瀹氬噯纭紝鍘熷洜鏄師鏉ュ瓨鍌ㄨ繃涓滆タ鍚庢潵琚疆绌轰簡锛屼篃绠楁槸娌℃湁鐢ㄨ繃鐨勶紝鐢 is null 鏉ュ垽鏂
