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_