一些常用的Oracle SQL 之一

 

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