数据字典查看

--查询Session正在执行的语句
--查看某个进程正在执行什么SQL语句.sql
SELECT b.sql_text, sid, serial#, osuser, machine
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address;
--查询前台发出的SQL语句.SQL
select user_name,sql_text
from v$open_cursor
where sid in (select sid from (select sid,serial#,username,program from v$session where status='ACTIVE'));
--根据SPID查询session
SELECT * FROM v$session WHERE paddr IN (SELECT addr FROM v$process WHERE spid=&spid);
--根据SID查询process
SELECT * FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE sid=&sid);
-- ==================================================================

--DBA如何查询其他用户所进行的操作.SQL
SELECT sql_text
FROM v$sql t1, v$session t2
WHERE t1.address = t2.sql_address
   AND t2.sid = &sid;

-- ==================================================================
--检查失效的JOB
SELECT * FROM dba_jobs WHERE broken='Y' OR failures>0 OR next_date < trunc(SYSDATE);
--检查刷新失败的mview
SELECT * FROM dba_mviews WHERE compile_state!='VALID' ORDER BY last_refresh_date;

--查看session正在等待何种系统资源.sql
SELECT gv$session.inst_id,gv$session.sid,gv$session.serial#,
       gv$session.username,gv$session.machine,gv$session.program,
       gv$session_wait.event,gv$session_wait.p1text,gv$session_wait.p1,
       gv$session_wait.p2text,gv$session_wait.p2,
       gv$session_wait.p3text,gv$session_wait.p3,
       gv$session_wait.seconds_in_wait,gv$session_wait.state
FROM gv$session,gv$session_wait
WHERE gv$session.inst_id = gv$session_wait.inst_id    AND
      gv$session.sid = gv$session.sid;
--根据等待事件查询等待对象.SQL
select owner,segment_name,segment_type,tablespace_name
from dba_extents where file_id=152
   and 239210 between block_id and block_id + blocks -1;

-- ==================================================================
-- 查看表空间使用情况
SELECT df.tablespace_name "表空间",
       (df.totalspace - NVL(fs.freespace, 0)) "已使用(MB)",
       NVL(fs.freespace, 0) "剩余(MB)",
       df.totalspace "合计(MB)",
       ROUND(100 * (1 - NVL(fs.freespace, 0) / df.totalspace), 2) "使用率(%)"
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
          FROM dba_data_files
         GROUP BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) freespace
          FROM dba_free_space
         GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY 5 DESC;

-- ==================================================================

/*
--查询表空间是否需压缩.SQL
rem 由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用FSFI--Free Space Fragmentation Index（自由空间碎片索引）值来直观体现
rem 可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1, 这样SMON便会将自由范围自动合并。也可以手工合并自由范围
rem FSFI Value Compute
column FSFI format 999,99
*/

select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by FSFI desc;

-- ==================================================================

-- 检测表空间够不够.SQL
--1、先把表空间coalesce一下。
--2、
SELECT owner, segment_name, next_extent, s.tablespace_name, max_free_bytes
FROM sys.dba_segments s,
       (SELECT tablespace_name, MAX(bytes) max_free_bytes
          FROM sys.dba_free_space
         GROUP BY tablespace_name) f
WHERE s.next_extent > f.max_free_bytes
   AND s.tablespace_name = f.tablespace_name;

-- ==================================================================

--查看哪些session正在使用哪些回滚段.sql
/*
col 回滚段名 format a10;
col SID format 9990;
col 用户名 format a10;
col 操作程序 format a80;
col status format a6 trunc;
*/
SELECT r.NAME 回滚段名,s.sid SID,s.serial# Serial,
       s.username 用户名,s.machine 机器名,
       t.start_time 开始时间,t.status 状态,
       t.used_ublk 撤消块,USED_UREC 撤消记录,
       t.cr_get 一致性取,t.cr_change 一致性变化,
       t.log_io "逻辑I/O",t.phy_io "物理I/O",
       t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) 操作程序
FROM v$session s, v$transaction t, v$rollname r,v$rollstat g
WHERE t.addr = s.taddr
   AND t.xidusn = r.usn
   AND r.usn = g.usn
ORDER BY t.used_ublk desc;

--查看回滚段的使用率.SQL
SELECT r.status "Status",
       r.segment_name "Name",
       r.tablespace_name "Tablespace",
       s.extents "Extents",
       TO_CHAR((s.bytes / 1024 / 1024), '99999990.000') "Size"
FROM sys.dba_rollback_segs r, sys.dba_segments s
WHERE r.segment_name = s.segment_name
   AND s.segment_type IN ('ROLLBACK', 'TYPE2 UNDO')
ORDER BY 5 DESC;

-- ==================================================================

--查询IO消耗大session.sql
--脚本说明：察看I/O吞吐量
SELECT a.sid,b.sql_text,a.program,c.disk_reads,c.executions,
       c.disk_reads / c.executions "Reads/Exec",
       a.username,a.osuser,a.machine,a.module
FROM v$session a, v$sqltext b, v$sqlarea c
WHERE a.sql_address = b.address
   AND a.sql_hash_value = b.hash_value
   AND a.sql_address = c.address
   AND a.sql_hash_value = c.hash_value
   AND c.disk_reads > 500
   AND c.executions > 0
ORDER BY c.disk_reads DESC, a.sid, b.piece;

--脚本说明：查看占I/O较大的正在运行的session
SELECT se.sid,se.serial#,pr.spid,se.username,se.status,se.terminal,
       se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
WHERE st.sid = se.sid
   AND st.sid = si.sid
   AND se.paddr = pr.addr
   AND se.sid > 6 　　and st.wait_time = 0
   AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC;

--察看Datafile读写情况.sql
--物理读的datafile
SELECT b.tablespace_name,b.file_name,a.*
FROM v$filestat a,dba_data_files b
WHERE a.FILE#=b.file_id AND b.tablespace_name LIKE 'TBS_OSS%'
ORDER BY 4 DESC;
--物理写的datafile
SELECT b.tablespace_name,b.file_name,a.*
FROM v$filestat a,dba_data_files b
WHERE a.FILE#=b.file_id AND b.tablespace_name LIKE 'TBS_OSS%'
ORDER BY 5 DESC;
--各表空间的读写比率
SELECT b.tablespace_name,SUM(phyrds),SUM(phywrts),COUNT(*)
FROM v$filestat a,dba_data_files b
WHERE a.FILE#=b.file_id
GROUP BY b.tablespace_name;
--表空间分类统计比率
SELECT nvl(c.tbs_type,'其他') TYPE,SUM(phyrds),SUM(phywrts),COUNT(*)
FROM v$filestat a,dba_data_files b,'&use_tbs_type' c
WHERE a.FILE#=b.file_id AND b.TABLESPACE_name=c.tablespace_name(+)
GROUP BY nvl(c.tbs_type,'其他');

-- ==================================================================

/* 数据库LOCK相关查询 */
--查看等待时间过久的Lock
SELECT * FROM v$lock WHERE sid > 12 AND ctime > 1800 ORDER BY ctime DESC;
--v$session中LOCKWAIT IS NOT NULL也表示该进程有锁
--查互相等待的锁
SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') "Lock Mode",
       sid,TYPE,id1,id2,lmode,request,ctime,BLOCK
FROM v$lock
WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM v$lock WHERE request > 0)
ORDER BY id1, request,sid;
--锁对应SQL
SELECT /*+ rule */
b.sid, b.username, b.program, a.piece, a.sql_text
FROM v$sqltext a, v$session b, v$lock c
WHERE c.BLOCK = 1
   AND c.sid = b.sid
   AND b.sql_hash_value = a.hash_value
GROUP BY b.sid, b.username, b.program, a.piece, a.sql_text
ORDER BY b.sid, a.piece;
--察看DX锁
SELECT a.sid, a.username, a.program, a.status, a.machine
FROM v$session a
WHERE a.sid IN (SELECT sid FROM v$lock WHERE TYPE = 'DX');
--察看锁住的Object
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,
       B.SESSION_ID,C.SERIAL#,C.STATUS,
       decode(B.LOCKED_MODE,'0','none',
                            '1','null (NULL)',
                            '2','row-S (SS)',
                            '3','row-X (SX)',
                            '4','share (S)',
                            '5','S/Row-X (SSX)',
                            '6','exclusive (X)') Locked_Mode,
       B.ORACLE_USERNAME User_Name,B.Process,C.Program,
       B.OS_USER_NAME OS_User,C.Machine,C.Server
FROM ALL_OBJECTS A, V$SESSION C, V$LOCKED_OBJECT B
WHERE (A.OBJECT_ID = B.OBJECT_ID)
   AND (B.PROCESS = C.PROCESS);
--找出被锁对象的等待事件
select a.owner,a.object_name,b.sid,b.serial#,
       decode(c.locked_mode,'0','none',
                            '1','null (NULL)',
                            '2','row-S (SS)',
                            '3','row-X (SX)',
                            '4','share (S)',
                            '5','S/Row-X (SSX)',
                            '6','exclusive (X)') lockmode,
       d.event,b.username,b.osuser,b.program,b.machine,b.module,b.process,b.terminal,b.command
from all_objects a,v$session b,v$locked_object c,v$session_wait d
where a.object_id=c.object_id and b.sid=c.session_id and b.sid=d.sid;
--查询被锁对象相关的SQL
select s.username username,s.sid sid,s.serial#,
       a.owner||'.'||a.object object,s.lockwait,t.sql_text sql
from v$sqltext t,v$session S,v$access a
where t.address = s.sql_address
   and t.hash_value = s.sql_hash_value
   and s.sid = a.sid and a.owner !='SYS'
   and upper(substr(a.object,1,2))!='V$';
--分类察看系统锁
SELECT v$session.osuser,
       v$session.machine,
       v$lock.sid,
       v$session.serial#,
       DECODE(v$lock.TYPE,
              'MR','Media Recovery',
              'RT','Redo Thread',
              'UN','User Name',
              'TX','Transaction',
              'TM','DML',
              'UL','PL/SQL User Lock',
              'DX','Distributed Xaction',
              'CF','Control File',
              'IS','Instance State',
              'FS','File Set',
              'IR','Instance Recovery',
              'ST','Disk Space Transaction',
              'TS','Temp Segment',
              'IV','Library Cache Invalida-tion',
              'LS','Log Start or Switch',
              'RW','Row Wait',
              'SQ','Sequence Number',
              'TE','Extend Table',
              'TT','Temp Table',
              'Unknown') locktype,
       RTRIM(object_type) || ' ' || RTRIM(owner) || '.' || object_name object_name,
       DECODE(lmode,
              0,'None',
              1,'Null',
              2,'Row-S',
              3,'Row-X',
              4,'Share',
              5,'S/Row-X',
              6,'Exclusive',
              'Unknown') lockmode,
       DECODE(request,
              0,'None',
              1,'Null',
              2,'Row-S',
              3,'Row-X',
              4,'Share',
              5,'S/Row-X',
              6,'Exclusive',
              'Unknown') requestmode,
       ctime,
       BLOCK b
FROM v$lock, all_objects, v$session
WHERE v$lock.sid > 6
   AND v$session.sid = v$lock.sid
   AND v$lock.id1 = all_objects.object_id;

-- ==================================================================

--选取Pending Transaction.sql
SELECT e.sql_text
FROM v$transaction c, v$session d, v$sqlarea e
WHERE d.taddr = c.addr
   AND e.address = d.prev_sql_addr
   AND c.xidusn = < 1st part OF TRANSACTION - id >
   AND c.xidslot = < 2nd part OF TRANSACTION - id >
   AND c.xidsqn = < 3rd part OF TRANSACTION - id >;
--dba_2pc_pending.LOCAL_TRAN_ID的以点‘.’分隔的三个部分
--处理dba_2pc_pending中残存的分布式事务
ROLLBACK / COMMIT force 'LOCAL_TRAN_ID';
--以sys用户执行如下命令，强制释放残存事务
exec dbms_transaction.purge_lost_db_entry('LOCAL_TRAN_ID');
COMMIT;

-- ==================================================================

--脚本说明：察看Segment所在Datafile.SQL
/* 可用于统计表、索引在各数据文件上的分布
DBA_EXTENTS —— describes the extents comprising all segments in the database.
Related view:
USER_EXTENTS describes extents in segments associated with objects owned
by the current user. This view does not display the OWNER, FILE_ID, BLOCK_ID,
or RELATIVE_FNO columns.
-----------------------------------------------------------------------------------------
Column         Datatype       NULL     Description
-----------------------------------------------------------------------------------------
OWNER          VARCHAR2(30)            Owner of the segment associated with the extent
SEGMENT_NAME   VARCHAR2(81)            Name of the segment associated with the extent
SEGMENT_TYPE   VARCHAR2(17)            Type of the segment: INDEX PARTITION, TABLE PARTITION
TABLESPACE_NAME VARCHAR2(30)           Name of the tablespace containing the extent
EXTENT_ID      NUMBER        NOT NULL Extent number in the segment
FILE_ID        NUMBER        NOT NULL Name of the file containing the extent
BLOCK_ID       NUMBER        NOT NULL Starting block number of the extent
BYTES          NUMBER                  Size of the extent in bytes
BLOCKS         NUMBER        NOT NULL Size of the extent in Oracle blocks
RELATIVE_FNO   NUMBER        NOT NULL Relative file number of the first extent block DBA_FREE_SPACE
PARTITION_NAME VARCHAR2(30)            Object Partition Name (Set to NULL for non-partitioned objects)
-----------------------------------------------------------------------------------------
*/
SELECT a.owner,
       a.segment_type "Type",
       a.segment_name "Name",
       b.NAME "File Name",
       round(SUM(a.bytes) / 1024 / 1024, 2) "Size(MB)",
       SUM(a.blocks) "Blocks",
       COUNT(*) "Extents"
FROM dba_extents a, v$dbfile b
WHERE a.owner LIKE '%'
   AND a.segment_name LIKE '%'
   AND a.file_id = b.file#
GROUP BY a.owner, a.segment_type, a.segment_name, b.NAME;

-- ==================================================================

--Temp表空间上进程的查询
select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text
from v$sort_usage a,v$session b,v$sqltext c
where a.session_addr = b.saddr
   and b.sql_address = c.address
order by a.tablespace,b.sid,b.serial#,c.address, c.piece;
--脚本说明：Temp表空间使用情况.sql
--Who has all the TEMP space
--This small script will provide a list of users and which processes occupy space in the TEMP tablespace.
--This is useful when users encounter the "TEMP SEGMENT MAXIMUM EXTENT EXCEEDED" error.
--This script is also useful to find out whether you need to add a datafile or not.
--We had an experience where some processes went into loops and occupied the whole TEMP tablespace (10Gb).
--After we killed those processes, the entire 10GB is reclaimed in the TEMP tablespace.
--Note: Connect as SYS, SYSTEM, or INTERNAL to run the script. (Note: Do not use INTERNAL with Oracle9i.
/*
SET pagesize 10000
SET linesize 120
col segfile# FOR 9,999 heading 'File#'
col spid FOR 9, 999 heading 'Unix|ID'
col segblk# heading 'BlockId'
col size_mb FOR 999, 990.00 heading "Used(MB)"
col username FOR a10
col program FOR a30
col sidserial FOR a10
col osuser FOR a10
col tablespace FOR a10
*/
SELECT b.segfile#,b.segblk#,a.username,a.sid||','||a.serial# sidserial,segtype,
       ROUND(((b.blocks * p.VALUE) / 1024 / 1024), 2) "Size(MB)",
       b.tablespace,a.osuser,a.program,a.machine,a.status,a.sql_address
FROM v$session a, v$sort_usage b, v$process c, v$parameter p
WHERE p.NAME = 'db_block_size'
   AND a.saddr = b.session_addr
   AND a.paddr = c.addr
ORDER BY 6 DESC;
-- ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

-- ==================================================================

--检查有无UnUsable的索引
select owner,index_name,status from dba_indexes where status='UNUSABLE'
union all
select index_owner owner,index_name||'->'||partition_name index_name,status
from dba_ind_partitions where status='UNUSABLE';
select 'alter index '||owner||'.'||index_name||' rebuild nologging online;' Rebuild_Index
from dba_indexes where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' nologging online;' Rebuild_Index
from dba_ind_partitions where status='UNUSABLE';

-- ==================================================================

--获取数据库版本信息.sql
SELECT * FROM product_component_version;
--查询隐含参数.SQL
select a.ksppinm name,b.ksppstdf default_val,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.indx = b.indx and substr(a.ksppinm,1,1) ='_'
order by a.KSPPINM

-- ==================================================================
--察看某个对象依赖于的对象：
SELECT REFERENCED_NAME FROM dba_dependencies
WHERE owner=&owner AND NAME=&name
   AND REFERENCED_TYPE IN ('TABLE','PROCEDURE','FUNCTION');

--察看某个对象是否有效：
SELECT STATUS FROM dba_objects WHERE object_name=&object_name;

-- ==================================================================
--查看那些数据库对象被修改过.sql
SELECT OBJECT_NAME,　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　OBJECT_TYPE,　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　TO_CHAR(CREATED,       'YYYY-Mon-DD HH24:MI') CREATE_TIME,　
　　　　　　TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') MOD_TIME,　　
　　　　　　TIMESTAMP,　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　STATUS　　　　　　　　　　　　　　　　　　　　　　　　　　　
FROM USER_OBJECTS
WHERE to_char(LAST_DDL_TIME,'yyyymmdd')>'&日期变量';

-- ==================================================================
/* --查询逻辑物理操作过频.SQL
次执行产生的物理I/O操作超过1000块或逻辑I/O操作超过10000块这种很大的数字。
*/
select HASH_VALUE,sql_text, EXECUTIONS, BUFFER_GETS, DISK_READS,
       BUFFER_GETS/EXECUTIONS buffer_per_exec,DISK_READS/EXECUTIONS disk_per_exec,
       VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING
from v$sqlarea
where EXECUTIONS > 200
   and (BUFFER_GETS/EXECUTIONS > 10000 or disk_reads/EXECUTIONS > 1000)
order by buffer_per_exec,disk_per_exec desc;
--察看性能差的SQL语句.sql
SELECT PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC;

-- ==================================================================
--检查数据库中长时间未提交的事务及现在运行的语句,占回滚断情况，定义执行时间30分钟为长时间事务。

SELECT A.ADDR,A.XIDUSN,B.NAME,A.START_TIME,A.START_UEXT,A.USED_UBLK,A.USED_UREC
FROM V$TRANSACTION A,V$ROLLNAME B
WHERE (SYSDATE - TO_DATE(A.START_TIME,'MM/DD/YY HH24:MI:SS') ) > ( 30.0/(24*60))
AND A.XIDUSN = B.USN;

select SQL_ADDRESS,SQL_HASH_VALUE,TADDR,SID ,SERIAL# ,PADDR ,USERNAME,STATUS ,STATUS,LOGON_TIME,
       SCHEMANAME,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,MODULE
from   v$session
where taddr In (
SELECT A.ADDR
FROM V$TRANSACTION A
WHERE (SYSDATE - TO_DATE(A.START_TIME,'MM/DD/YY HH24:MI:SS') ) > ( 30.0/(24*60)))
ORDER BY SQL_ADDRESS,SQL_HASH_VALUE;

SELECT ADDRESS,HASH_VALUE,SQL_TEXT
FROM V$SQLTEXT
WHERE (ADDRESS,HASH_VALUE ) IN
   ( SELECT SQL_ADDRESS,SQL_HASH_VALUE
     from   v$session
    where taddr In (
     SELECT A.ADDR
     FROM V$TRANSACTION A
     WHERE (SYSDATE - TO_DATE(A.START_TIME,'MM/DD/YY HH24:MI:SS') ) > ( 30.0/(24*60))))
ORDER BY ADDRESS,HASH_VALUE,PIECE ;
-- ==================================================================

-- Example of RANK()
-- Aggregate Syntax
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)

-- Analytic Syntax
RANK () OVER ( [PARTITION BY value_expr [,value_expr]...] order_by_clause )

-- ==================================================================
--只取第5条记录.SQL
select * from ( select * from table1 where rownum<=5 order by rownum desc) where rownum<=1;

-- ==================================================================

--Oracle数据库的初始化参数，主要来源于两个Oracle内部数据字典表:X$KSPPCV,X$KSPPI
--通常我们查询的v$parameter视图就来源于这两个表，只不过隐去了部分参数。
--
--通过以下脚本可以查询获得这些被隐含的参数:

set linesize 132
column name format a30
column value format a25
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ');