1、查看表空间的名称及大小
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;
SQL>
TABLESPACE_NAME TS_SIZE
------------------------------ ---------------
UNDOTBS1 25
SYSAUX 480
、、、、、、
2、查看表空间物理文件的名称及大小
select
tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from
dba_data_files
order by
tablespace_name;
SQL>
TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE
------------------------------ ---------- --------------------------------------------------------------------------- ----- -----------
SYSAUX 2 D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF 480
SYSTEM 1 D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF 360
UNDOTBS1 3 D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF 25
3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
SQL>
SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT
------------------------------ ------------------------------ ---------------- ------------- ---------- ----------- ----------
SYSTEM SYSTEM ONLINE 112 56 32765 3
_SYSSMU10_3913914216$ UNDOTBS1 ONLINE 128 64 32765 2
_SYSSMU1_2410988505$ UNDOTBS1 ONLINE 128 64 32765 12
4、查看控制文件
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF
5、查看日志文件
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
D:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_6YVTBLOP_.LOG
D:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_6YVTBGXJ_.LOG
6、查看表空间的使用情况
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND
A.TABLESPACE_NAME=C.TABLESPACE_NAME;
----------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX 503316480 470220800 32047104 93.4244791 6.3671875
UNDOTBS1 26214400 14745600 10420224 56.25 39.75
USERS 104857600 1638400 102170624 1.5625 97.4375
SYSTEM 377487360 367067136 9371648 97.2395833 2.48263888