怎么查看oracle表空间，剩余大小，表空间利用 Developer 怎么查看oracle表空间，剩余大小，表...
\u5982\u4f55\u67e5\u770boracle\u8868\u7a7a\u95f4\u5df2\u4f7f\u7528\u5927\u5c0fSelect Segment\u8f6c\uff1a
1. \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.
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.
SQL> spool out.txt
SQL> select * from v$database;
SQL> spool off
select a.tablespace_name "\u8868\u7a7a\u95f4\u540d\u79f0",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "\u5360\u7528\u7387(%)",
round(a.bytes_alloc/1024/1024,2) "\u5bb9\u91cf(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "\u7a7a\u95f2(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "\u4f7f\u7528(M)",
Largest "\u6700\u5927\u6269\u5c55\u6bb5(M)",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "\u91c7\u6837\u65f6\u95f4"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,
ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c
where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name
1、因为oracle运行在Linux系统下，首先，要连接Linux系统。
2、连上后，进行oracle控制台。输入命令： sqlplus / as sysdba;
3、在sql命令行，输入：
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 "空闲空间(M)",F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES，
FROM SYS.DBA_FREE_SPACE，GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB，
FROM SYS.DBA_DATA_FILES DD，GROUP BY DD.TABLESPACE_NAME) D，
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME，ORDER BY 1;
4、这样就可以查看到相应结果。 完成效果图。
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
给个邮箱，我给个oracle工具你用，很简单很方便的帮你解决你的问题：
在我给你的工具中一样使用用户名和密码以normal模式登陆所在的服务数据库，之后在菜单‘管理’项中有‘表空间信息’打开：
select * from dba_tablespaces
结果返回数据库所有表空间数据
select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
绛旓細1.鏌ョ湅Oracle鏁版嵁搴撲腑琛ㄧ┖闂淇℃伅鐨勫伐鍏锋柟娉: 浣跨敤oracle enterprise manager console宸ュ叿,杩欐槸oracle鐨勫鎴风宸ュ叿,褰撳畨瑁卭racle鏈嶅姟鍣ㄦ垨瀹㈡埛绔椂浼氳嚜鍔ㄥ畨瑁呮宸ュ叿,鍦...2.鏌ョ湅Oracle鏁版嵁搴撲腑琛ㄧ┖闂翠俊鎭殑鍛戒护鏂规硶: 閫氳繃鏌ヨ鏁版嵁搴撶郴缁熶腑鐨勬暟鎹瓧鍏歌〃(data dictionary tables)鑾峰彇琛ㄧ┖闂寸殑鐩稿叧淇℃伅,棣栧厛浣跨敤...
绛旓細Oracle涓煡璇㈡墍鏈夎〃鍙婂叾鎵浣跨敤鐨勮〃绌洪棿鍙互浣跨敤SQL璇彞锛歴elect Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;鍦ㄦ暟鎹簱绠＄悊鍛樼殑鏃ュ父宸ヤ綔涓紝搴旇缁忓父鏌ヨ琛ㄧ┖闂鐨勫埄鐢ㄧ巼锛屾寜鐓ф暟鎹簱绯荤粺鐨勫叿浣撴儏鍐典及绠楄〃绌洪棿鐨勫闀块噺锛屽綋琛ㄧ┖闂寸殑鍒╃敤鐜囪秴杩90%鏃讹紝瑕佸強鏃堕噰鍙栨帾鏂姐
绛旓細1.寮鍚Oracle绠＄悊宸ュ叿锛屾壘鍒癟ablespaces.2.棣栧厛瑕鐪鐨勬槸琛ㄧ┖闂鐨勬诲ぇ灏忓拰浣跨敤鐘跺喌浠ュ強褰撳墠鐘舵併3.Free Space 閲岄潰鏄悇涓〃涓殑鐩稿叧澶у皬淇℃伅(涓涓〃绌洪棿鍙兘鏈夊涓〃鍦ㄤ娇鐢)銆4.Objects 灏卞绗笁姝ヤ腑鎵鎻愬埌鐨勪娇鐢ㄨ繖涓〃绌洪棿鐨勮〃鏄庣粏銆5.Script 鏄垱寤鸿〃绌洪棿鐨凱L/SQL锛屾垜浠彲浠ュ幓浜嗚В瀹冪殑鐩稿叧淇℃伅鍜...
绛旓細select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys鐢ㄦ埛鏌ョ湅 2銆佺缉灏忎复鏃琛ㄧ┖闂澶у皬 alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF...
绛旓細浣犺鐨勬槸鏌ョ湅琛ㄧ┖闂鐨勪娇鐢ㄦ儏鍐靛惂 --鏌ヨ琛ㄧ┖闂鐨勬诲閲弒elect tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name; 涓鑸〃绌洪棿閮借缃簡鑷姩澧為暱銆傚唴瀛樹笉澶熶細鑷姩閫掑
绛旓細SELECT BLOCKS, COUNT(*)FROM DBA_FREE_SPACE D WHERE D.TABLESPACE_NAME = 'SRC_IREADTBS'GROUP BY BLOCKS 鐪媌locks鏄繛缁潡銆傚墿浣欑┖闂翠篃鍦ㄨ繖涓〃涓鏌ユ壘 bytes锛屽崟涓嶆槸杩欎釜琛ㄦ墍鏈夌┖闂撮兘鏄兘鐢ㄧ殑锛屾瘮濡備綘鐨琛ㄧ┖闂鑻ヤ笉鏄浐瀹氱敵璇疯繛缁锛屽綋绯荤粺浼氳嚜鍔ㄥ垎閰嶄竴涓硷紝浣犵殑blocks灏忎簬杩欎釜鍊兼墍鏈夌┖闂村熀鏈敤...
绛旓細鍩烘湰涓鏌ヨdba_free_space灏辫兘鏌ュ埌琛ㄧ┖闂鐨勫墿浣欓噺锛屽鏋滈潪瑕佹煡璇㈢敤浜嗙櫨鍒嗕箣澶氬皯涔嬬被鐨勶紝杩欏紶琛ㄧ粡杩囪绠楀氨鑳藉緱鍒般傚鏋滀笉鎯虫煡杩欏紶琛紝涔熷彲浠ラ氳繃sys.sm$ts_avail锛 sys.sm$ts_used 鍜 sys.sm$ts_free 涓夊紶琛ㄨ仈鍚堟煡璇㈠緱鍒般傦紙2锛夌储寮曡〃鈥斺旂┖闂寸殑浣跨敤鎯呭喌锛屼篃灏辨槸璇存煡鐨勬槸绱㈠紩鍗犵敤鐨勭┖闂寸殑鎯呭喌锛...
绛旓細1. 鏌ョ湅鎵鏈琛ㄧ┖闂澶у皬 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files 2 group by tablespace_name;2. 宸茬粡浣跨敤鐨勮〃绌洪棿澶у皬 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space 2 group by tablespace_name;3. 鎵浠ヤ娇鐢ㄧ┖闂村彲浠ヨ繖鏍疯绠 select ...
绛旓細SQL> alter tablespace temp add tempfile 鈥/u01/app/oracle/oradata/orcl/temp02.dbf鈥 size 100m;4銆佸垱寤轰复鏃琛ㄧ┖闂锛歋QL> create temporary tablespace temp1 tempfile 鈥/u01/app/oracle/oradata/orcl/temp11.dbf鈥 size 10M;5銆佹洿鏀圭郴缁熺殑榛樿涓存椂琛ㄧ┖闂达細--鏌ヨ榛樿涓存椂琛ㄧ┖闂 select * ...
绛旓細5銆佹湁浜涙煡璇㈣〃绌洪棿鐨勮鍙ラ渶瑕佹墽琛岀敤鎴烽渶瑕侀珮鐨勬潈闄愶紝濡備綍鏌ョ湅褰撳墠鐢ㄦ埛鐨勮鑹插憿锛熶娇鐢ㄥ懡浠elect * from user_role_privs鍗冲彲銆6銆佸湪鏌ヨ鍒拌〃绌洪棿鐨勪俊鎭箣鍚庯紝鎴戜滑鏈夋椂闇瑕佸垹闄ゆ病鐢ㄧ殑琛ㄧ┖闂达紝浣跨敤鍛戒护drop tablespace xxx including contents and datafiles;鍗冲彲銆傜敳楠ㄦ枃鑲′唤鏈夐檺鍏徃锛Oracle锛夋槸鍏ㄧ悆澶у瀷鏁版嵁...
