oracle中的经典SQL.docx

oracle中的经典SQL.docx

《oracle中的经典SQL.docx》由会员分享，可在线阅读，更多相关《oracle中的经典SQL.docx（19页珍藏版）》请在冰点文库上搜索。

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;

2、查看表空间物理文件的名称及大小

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME,

ROUND（BYTES/（1024*1024）,0） TOTAL_SPACE

FROM DBA_DATA_FILES

ORDER BY TABLESPACE_NAME;

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 ;

4、查看控制文件

SELECT NAME FROM V$CONTROLFILE;

5、查看日志文件

SELECT MEMBER FROM V$LOGFILE;

6、查看表空间的使用情况

SELECT SUM（BYTES）/（1024*1024） AS FREE_SPACE,TABLESPACE_NAME FROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME;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; 

7、查看数据库库对象

SELECT OWNER, OBJECT_TYPE, STATUS, COUNT（*） COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;

8、查看数据库的版本

SELECT VERSION FROM PRODUCT_COMPONENT_VERSION 

WHERE SUBSTR（PRODUCT,1,6）='ORACLE';

9、查看数据库的创建日期和归档方式

SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE; 

10、捕捉运行很久的SQL

COLUMN USERNAME FORMAT A12 

COLUMN OPNAME FORMAT A16 

COLUMN PROGRESS FORMAT A8 

SELECT USERNAME,SID,OPNAME, 

       ROUND（SOFAR*100 / TOTALWORK,0） || '%' AS PROGRESS, 

       TIME_REMAINING,SQL_TEXT 

FROM V$SESSION_LONGOPS , V$SQL 

WHERE TIME_REMAINING <> 0 

AND SQL_ADDRESS = ADDRESS 

AND SQL_HASH_VALUE = HASH_VALUE 

/

11。

查看数据表的参数信息

SELECT   PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,

         NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS,

         FREELIST_GROUPS, LOGGING, BUFFER_POOL, NUM_ROWS, BLOCKS,

         EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,

         LAST_ANALYZED

    FROM DBA_TAB_PARTITIONS

   --WHERE TABLE_NAME = :

TNAME AND TABLE_OWNER = :

TOWNER

ORDER BY PARTITION_POSITION

12.查看还没提交的事务

SELECT * FROM V$LOCKED_OBJECT;

SELECT * FROM V$TRANSACTION;

13。

查找OBJECT为哪些进程所用

SELECT 

P.SPID,

S.SID,

S.SERIAL# SERIAL_NUM,

S.USERNAME USER_NAME,

A.TYPE  OBJECT_TYPE,

S.OSUSER OS_USER_NAME,

A.OWNER,

A.OBJECT OBJECT_NAME,

DECODE（SIGN（48 - COMMAND）,

1,

TO_CHAR（COMMAND）, 'ACTION CODE #' || TO_CHAR（COMMAND） ） ACTION,

P.PROGRAM ORACLE_PROCESS,

S.TERMINAL TERMINAL,

S.PROGRAM PROGRAM,

S.STATUS SESSION_STATUS   

FROM V$SESSION S, V$ACCESS A, V$PROCESS P   

WHERE S.PADDR = P.ADDR AND

      S.TYPE = 'USER' AND   

      A.SID = S.SID   AND

   A.OBJECT='SUBSCRIBER_ATTR'

ORDER BY S.USERNAME, S.OSUSER

14。

回滚段查看

SELECT ROWNUM, SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME NAME, V$ROLLSTAT.EXTENTS 

EXTENTS, V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS, 

V$ROLLSTAT.GETS GETS, V$ROLLSTAT.WAITS WAITS, V$ROLLSTAT.WRITES WRITES, 

SYS.DBA_ROLLBACK_SEGS.STATUS STATUS FROM V$ROLLSTAT, SYS.DBA_ROLLBACK_SEGS, 

V$ROLLNAME WHERE V$ROLLNAME.NAME（+） = SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME AND 

V$ROLLSTAT.USN （+） = V$ROLLNAME.USN ORDER BY ROWNUM

15。

耗资源的进程（TOP SESSION）

SELECT S.SCHEMANAME SCHEMA_NAME,   DECODE（SIGN（48 - COMMAND）, 1, 

TO_CHAR（COMMAND）, 'ACTION CODE #' || TO_CHAR（COMMAND） ） ACTION,   STATUS 

SESSION_STATUS,  S.OSUSER OS_USER_NAME,  S.SID,         P.SPID ,        S.SERIAL# SERIAL_NUM,  

NVL（S.USERNAME, '[ORACLE PROCESS]'） USER_NAME,  

S.TERMINAL TERMINAL,   

S.PROGRAM PROGRAM,  ST.VALUE CRITERIA_VALUE  FROM V$SESSTAT ST,  V$SESSION S  , V$PROCESS P   

WHERE ST.SID = S.SID AND  ST.STATISTIC# = TO_NUMBER（'38'） AND  （'ALL' = 'ALL' 

OR S.STATUS = 'ALL'） AND P.ADDR = S.PADDR ORDER BY ST.VALUE DESC,  P.SPID ASC, S.USERNAME ASC, S.OSUSER ASC

16。

查看锁（LOCK）情况

SELECT /*+ RULE */ LS.OSUSER OS_USER_NAME,  LS.USERNAME USER_NAME,  

DECODE（LS.TYPE, 'RW', 'ROW WAIT ENQUEUE LOCK', 'TM', 'DML ENQUEUE LOCK', 'TX', 

'TRANSACTION ENQUEUE LOCK', 'UL', 'USER SUPPLIED LOCK'） LOCK_TYPE,  

O.OBJECT_NAME OBJECT,  DECODE（LS.LMODE, 1, NULL, 2, 'ROW SHARE', 3, 

'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', NULL） 

LOCK_MODE,   O.OWNER,  LS.SID,  LS.SERIAL# SERIAL_NUM,  LS.ID1,  LS.ID2    

FROM SYS.DBA_OBJECTS O, （  SELECT S.OSUSER,  S.USERNAME,  L.TYPE,   

L.LMODE,  S.SID,  S.SERIAL#,  L.ID1,  L.ID2  FROM V$SESSION S,   

V$LOCK L  WHERE S.SID = L.SID ） LS  WHERE O.OBJECT_ID = LS.ID1 AND   O.OWNER 

<> 'SYS'   ORDER BY O.OWNER, O.OBJECT_NAME

17。

查看等待（WAIT）情况

SELECT V$WAITSTAT.CLASS, V$WAITSTAT.COUNT COUNT, SUM（V$SYSSTAT.VALUE） SUM_VALUE 

FROM V$WAITSTAT, V$SYSSTAT WHERE V$SYSSTAT.NAME IN （'DB BLOCK GETS', 

'CONSISTENT GETS'） GROUP BY V$WAITSTAT.CLASS, V$WAITSTAT.COUNT

18。

查看SGA情况

SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

19。

查看CATCHED OBJECT

SELECT OWNER,              NAME,              DB_LINK,              NAMESPACE,  

            TYPE,              SHARABLE_MEM,              LOADS,              EXECUTIONS,   

           LOCKS,              PINS,              KEPT        FROM V$DB_OBJECT_CACHE

20。

查看V$SQLAREA

SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, 

VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, 

USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,

 BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA

21。

查看OBJECT分类数量

SELECT DECODE （O.TYPE#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 , 

'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ） OBJECT_TYPE , COUNT（*） QUANTITY FROM 

SYS.OBJ$ O WHERE O.TYPE# > 1 GROUP BY DECODE （O.TYPE#,1,'INDEX' , 2,'TABLE' , 3 

 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ） UNION SELECT 

'COLUMN' , COUNT（*） FROM SYS.COL$ UNION SELECT 'DB LINK' , COUNT（*） FROM 

22。

按用户查看OBJECT种类

SELECT U.NAME SCHEMA,  SUM（DECODE（O.TYPE#, 1, 1, NULL）） INDEXES,  

SUM（DECODE（O.TYPE#, 2, 1, NULL）） TABLES,  SUM（DECODE（O.TYPE#, 3, 1, NULL）） 

CLUSTERS,  SUM（DECODE（O.TYPE#, 4, 1, NULL）） VIEWS,  SUM（DECODE（O.TYPE#, 5, 1, 

NULL）） SYNONYMS,  SUM（DECODE（O.TYPE#, 6, 1, NULL）） SEQUENCES,  

SUM（DECODE（O.TYPE#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1）） 

OTHERS   FROM SYS.OBJ$ O, SYS.USER$ U   WHERE O.TYPE# >= 1 AND   U.USER# = 

O.OWNER# AND  U.NAME <> 'PUBLIC'   GROUP BY U.NAME    ORDER BY 

SYS.LINK$ UNION SELECT 'CONSTRAINT' , COUNT（*） FROM SYS.CON$

23。

有关CONNECTION的相关信息

1）查看有哪些用户连接

SELECT S.OSUSER OS_USER_NAME,   DECODE（SIGN（48 - COMMAND）, 1, TO_CHAR（COMMAND）,

 'ACTION CODE #' || TO_CHAR（COMMAND） ） ACTION,   P.PROGRAM ORACLE_PROCESS,   

STATUS SESSION_STATUS,   S.TERMINAL TERMINAL,   S.PROGRAM PROGRAM,   

S.USERNAME USER_NAME,   S.FIXED_TABLE_SEQUENCE ACTIVITY_METER,   '' QUERY,   

0 MEMORY,   0 MAX_MEMORY,     0 CPU_USAGE,   S.SID,  S.SERIAL# SERIAL_NUM    

FROM V$SESSION S,   V$PROCESS P   WHERE S.PADDR=P.ADDR AND   S.TYPE = 'USER'  

 ORDER BY S.USERNAME, S.OSUSER

2）根据V.SID查看对应连接的资源占用等情况

SELECT N.NAME, 

V.VALUE, 

N.CLASS,

N.STATISTIC#  

FROM  V$STATNAME N, 

V$SESSTAT V 

WHERE V.SID = 71 AND 

V.STATISTIC# = N.STATISTIC# 

ORDER BY N.CLASS, N.STATISTIC#

3）根据SID查看对应连接正在运行的SQL

SELECT /*+ PUSH_SUBQ */

COMMAND_TYPE, 

SQL_TEXT, 

SHARABLE_MEM, 

PERSISTENT_MEM, 

RUNTIME_MEM, 

SORTS, 

VERSION_COUNT, 

LOADED_VERSIONS, 

OPEN_VERSIONS, 

USERS_OPENING, 

EXECUTIONS, 

USERS_EXECUTING, 

LOADS, 

FIRST_LOAD_TIME, 

INVALIDATIONS, 

PARSE_CALLS, 

DISK_READS, 

BUFFER_GETS, 

ROWS_PROCESSED,

SYSDATE START_TIME,

SYSDATE FINISH_TIME,

'>' || ADDRESS SQL_ADDRESS,

'N' STATUS 

FROM V$SQLAREA

WHERE ADDRESS = （SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = 71）

24．查询表空间使用情况

SELECT A.TABLESPACE_NAME "表空间名称",

100-ROUND（（NVL（B.BYTES_FREE,0）/A.BYTES_ALLOC）*100,2） "占用率（%）",

ROUND（A.BYTES_ALLOC/1024/1024,2） "容量（M）",

ROUND（NVL（B.BYTES_FREE,0）/1024/1024,2） "空闲（M）",

ROUND（（A.BYTES_ALLOC-NVL（B.BYTES_FREE,0））/1024/1024,2） "使用（M）",

LARGEST "最大扩展段（M）",

TO_CHAR（SYSDATE,'YYYY-MM-DD HH24:

MI:

SS'） "采样时间" 

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

25. 查询表空间的碎片程度 

SELECT TABLESPACE_NAME,COUNT（TABLESPACE_NAME） FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME 

HAVING COUNT（TABLESPACE_NAME）>10; 

ALTER TABLESPACE NAME COALESCE; 

ALTER TABLE NAME DEALLOCATE UNUSED; 

CREATE OR REPLACE VIEW TS_BLOCKS_V AS 

SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,'FREE SPACE' SEGMENT_NAME FROM DBA_FREE_SPACE 

UNION ALL 

SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,SEGMENT_NAME FROM DBA_EXTENTS; 

SELECT * FROM TS_BLOCKS_V; 

SELECT TABLESPACE_NAME,SUM（BYTES）,MAX（BYTES）,COUNT（BLOCK_ID） FROM DBA_FREE_SPACE 

GROUP BY TABLESPACE_NAME;

26.查看有哪些实例在运行：

SELECT * FROM V$ACTIVE_INSTANCES;

 :

EM02:

  :

EM02:

  :

EM03:

  :

EM03:

 1、表空间统计

 A、   脚本说明：

这是我最常用的一个脚本，用它可以显示出数据库中所有表空间的状态，如表空间的大小、已使用空间、使用的百分比、空闲空间数及现在表空间的最大块是多大。

B、脚本原文:

SELECTupper（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_