深入oracle
数据库查看篇：
-- ==================================================================
--查询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, ' _', ' ');
--这些参数通常不建议修改，仅作参考目的，供学习用。
数据库管理篇：
-- ================= SQL分析相关 ====================================
对于oracle，可以通过 v$sesstat 来查询 cpu 的消耗 V$STATNAME可查看列名
对于消耗的多的session可以做 trace
session追踪:
实例级会话追踪: 设置参数SQL_TRACE = TRUE.
会话内追踪: ALTER SESSION SET SQL_TRACE = TRUE;
会话外追踪: DBA权限 使用SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE-FALSE);
true-开始跟踪/false-结束跟踪
这种情况下跟踪文件生成在udump目录下
然后用TKPROF查看TRACE文件即可
--trace文件中有绑定变量的值
dbms_system.set_ev(sid,serial#,ev_num,ev_level,nm);
/*
ev_num-10046
ev_level-Level Waits Binds
0 结束跟踪/1 False False/4 False True/8 True False/12 True True
nm-NULL,VARCHAR2 typr,nm=>''
*/
/*
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAIN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
*/
--自动分析SQL语句
在当前用户创建一个表plan_table，用来存储分析SQL语句的结果。
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30));
在SQL/PLUS的窗口运行以下命令开启自动分析
set time on; (说明：打开时间显示）
set autotrace on; (说明：打开自动分析统计，并显示SQL语句的运行结果）
set autotrace traceonly; (说明：打开自动分析统计，不显示SQL语句的运行结果）
接下来就可以运行测试SQL语句，看到其分析统计结果了
关闭以上功能，在SQL/PLUS的窗口运行以下命令
set time off; (说明：关闭时间显示）
set autotrace off; (说明：关闭自动分析统计）
-- =================== Oracle 启动/关闭 =============================
1-startup nomount
非安装启动，这种方式启动下可执行：重建控制文件、重建数据库
读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。
2-startup mount dbname
安装启动，这种方式启动下可执行：
数据库日志归档、数据库介质恢复、使数据文件联机或脱机，重新定位数据文件、重做日志文件。
执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，
但此时不对数据文件和日志文件进行校验检查。
3-startup open dbname
先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，
这种方式下可访问数据库中的数据。
4-startup，等于以下三个命令
startup nomount
alter database mount
alter database open
5-startup restrict
约束方式启动
这种方式能够启动数据库，但只允许具有一定特权的用户访问
非特权用户访问时，会出现以下提示：
ERROR：ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用
6-startup force
强制启动方式
当不能关闭数据库时，可以用startup force来完成数据库的关闭
先关闭数据库，再执行正常启动数据库命令
7-startup pfile=参数文件名
带初始化参数文件的启动方式
先读取参数文件，再按参数文件中的设置启动数据库
例：startup pfile=E:\Oracle\admin\oradb\pfile\init.ora
8-startup EXCLUSIVE
1-shutdown normal
正常方式关闭数据库。默认方式。不允许新的login，等待所有Session退出后关闭数据库。
2-shutdown transactional [local]
不允许新的login，等待事务提交或者回滚后断开Session的连接，待所有Session都退出后关闭数据库。
local参数允许值等待本地实例
3-shutdown immediate
立即方式关闭数据库。
在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，
而是在Oracle执行某些清除工作后才关闭（终止会话-释放会话资源），
当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。
4-shutdown abort
直接关闭数据库，正在访问数据库的会话会被突然终止，
如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。
下次启动时Oracle会自动进行实例恢复操作（其他三种方式都不用）
-- ==================== Kill Session/PID ============================
--从unix杀Oracle中killed的session对应的进程, select出来的内容放到unix shell下执行
select 'kill -9 '||spid "KILL"
from v$process
where addr in (select paddr from v$session where status='KILLED');
--取出已经释放session但还存在process的应用进程
select * from v$process
where addr not in (select paddr from v$session)
and program like 'oracle%' and BACKGROUND is null;
-- ==================================================================
--temp表空间的手工整理，其中4为temp表空间序号TS#+1
select ts#, name from sys.ts$ where name='TEMP';
alter session set events 'immediate trace name DROP_SEGMENTS level 4';
--也可以通过修改temp表空间的存储参数来促使表空间收缩
prompt 修改时会对正在使用temp表空间的session有严重影响
alter tablespace temp default storage (maxextents unlimited);
/*
另外，可以观察v$sort_segment中的FREE_EXTENTS是否有较大值来判断确实有空余temp空间使用
如果TOTAL_BLOCKS很大而FREE_EXTENTS很小的话，则说明系统temp空间不足，需要紧急处理
*/
-- ==================================================================
SYSTEM表空间碎片整理
可以通过修改$ORACLE_HOME/rdbms/admin目录下的sql.bsq文件中的存储参数即可
-- ==================================================================
Windows客户端检查操作系统级Oracle汉字显示的字符集
运行regedit，定位到：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn
找到以下字符串
NLS_LANG
检查是否以下内容，如不是，改之
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
NLS_CHARACTERSET=ZHS16GBK
NLS_NCHAR_CHARACTERSET=ZHS16GBK
-- ==================================================================
exp/imp相关
--含QUERY条件的export.sh
此时不能使用DIRECT=Y
exp userid=oss_ddms/ossddms123@osshn file=TF_B_AGENTTRADE.dmp indexes=n grants=n constraints=n query=\"where acceptdate \>= add_months\(trunc\(sysdate,\'MM\'\),-1\) and acceptdate \< trunc\(sysdate\)\" tables=oss_sa_hn.TF_B_AGENTTRADE
--只导建表定义.SQL
1.在将EMP表卸出并删除后执行imp命令时使用indexfile参数：
imp userid=scott/tiger file=emp.dmp indexfile=emp.sql Oracle把表和索引的创建信息写到指定的文件，而不是把数据写回。
2.打开emp.sql文件：
REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
REM VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE,
REM "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL
REM 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS
REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" ;
REM ... 14 rows
对它进行编辑，去除"REM"等信息，找到Initial参数，根据需要改变它。
3.在SQL*plus中执行emp.sql。
4.装入数据：
imp userid=scott/tiger ignore=y file=emp.dmp
需要注意的是，ignore参数必须设为Y.
-- ==================================================================
RBS表空间的收缩，只能通过收缩rollback segment来降低整个RBS表空间的使用率
alter rollback segment <rbs_name> shrink;
如果shrink无效，那么可以将该回滚段offline之后，再online后作shrink
如果offline也无效，则可以修改rbs的存储参数来促使命令立即生效
如: alter rollback segment BOSS_RBS1 STORAGE ( MAXEXTENTS unlimited);
注意: 整理前必须要确定已经没有session在使用该RBS了!
-- ==================================================================
--字符集相关
set line 1000
select name,value$ from sys.props$;
1这样的操作会对已经存在的数据产生致命问题,所以只能对空库作这样的操作
create database &db_name character set ZHS16GBK;
create database &db_name national character set ZHS16GBK;
create database &db_name character set US7ASCII;
create database &db_name national character set US7ASCII;
--character_set=ZHS16GBK
--national_character_set=ZHS16GBK
--NLS_CHARACTERSET这个参数应该是ZHS16GBK，如不是，改为它。
SQL*Plus中修改方法：
update sys.props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';
update sys.props$ set value$='ZHS16GBK' where name='NLS_NCHAR_CHARACTERSET';
--操作系统中修改方法：
--约束方式启动状态下进行Alter Database字符集的操作
startup restrict
connect internal
alter database &db_name character set ZHS16GBK;
alter database &db_name national character set ZHS16GBK;
--exp/imp的字符集问题
1、exp时客户端的nls_lang的设置最好和数据库端的一致，否则可能exp不出来
2、如果不一样，即使exp成功，也是进行了possible charset conversion
3、imp时，需要将nls_lang设置成和dmp文件中的一致。
4、否则也需要进行possible charset conversion
5、如果dmp文件的字符集和数据库的字符集不匹配，可能会出现乱码
exp时客户端的字符集必须是和服务端相同或超集，若是超级，则不能设置direct=y
-- ==================================================================
--分布式事务锁的处理
--在进行全局事务处理时，会出现一些2阶段事务处理异常，处理方法见下：
Select * from dba_2pc_pending;
COMMIT FORCE 'your local transactionID on this node',
'highest SCN from already committed site';
--或者：
ROLLBACK FORCE 'your local transactionID on this node',
'highest SCN from already committed site';
--建议异常事务作强制回滚
Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('your local transactionID on this node');
Commit;
-- ==================================================================
--在数据库中建立定时执行的JOB
注意，自动执行的过程不能有返回参数
VARIABLE jobno NUMBER;
BEGIN
dbms_job.submit(:jobno,'过程名字;',sysdate,'trunc(sysdate+1)+3/24');
commit;
END;
-- ==================================================================
--oracle and tuxedo.SQL
一、ORACLE的的配置
1．用internal用户（缺省的口令是oracle）进入SQLPLUS
C:\>sqlplus internal/oracle
2．运行ORACLE的安装路径下的/rdbms/admin/xaview.sql
SQL> @d:\ora81\rdbms\admin\xaview.sql
3．授权
SQL>grant select on v$xatrans$ to public with grant option;
SQL>grant select on v$pending_xatrans$ to public with grant option;
4. 用system用户（缺省的口令是manager）连接并授权
SQL>connect system/manager
SQL>grant select any table to public;
-- ==================================================================
--建立一个与现存数据库相同但不含数据的空库.sql
exp system/manager full=Y rows=N compress=n direct=y file=full.dmp
imp system/manager full=Y rows=N buffer=51200000 file=full.dmp
-- ==================================================================
--怎样确定固定对象：
SELECT * FROM V$DB_OBJECT_CACHE;
--可以通过包DBMS_SHARED_POOL.KEEP/UNKEEP来固定对象。
-- ==================================================================
--检查是否有对象扩展数接近最大扩展数
SELECT * FROM dba_segments
WHERE MAX_EXTENTS>100 AND
EXTENTS>=MAX_EXTENTS-20;
ALTER TABLE <TABLE NAME> STORAGE (MAXEXTENTS &VALUE);
ALTER INDEX <INDEX NAME> STORAGE (MAXEXTENTS &VALUE);
--修改该参数不会影响表的索引及相关过程
--也可以使用move来修改,效果一样
-- ==================================================================
--打开/取消数据库归档模式
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG/NOARCHIVELOG;
ALTER DATABASE OPEN;
归档命令：
启动自动归档模式，系统重起后，将按init.ora中的参数log_archive_start的值设置归档方式
SVRMGR> archive log start
启动手工归档模式
SVRMGR> archive log stop
查看归档信息：重做日志是否归档方式、是自动归档还是手工归档、归档路径、最旧的联机日志循序号...
SVRMGR> archive log list
归档一个已满，但没有归档的联机重做日志
SVRMGR> archive log next
归档所有已满，但没有归档的联机重做日志
SVRMGR> archive log all
注意：一个事务即使不被提交，也会被写入到重做日志中
--init$ORACLE_SID.ora中要设置自动归档
log_archive_start = true
log_archive_dest_1 = "location=<directory>"
log_archive_format = $ORACLE_SID_%t_%S.arc
-- ==================================================================
