一些常用的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


阅读更多
个人分类: Oracle 管理 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭