http://testerp.tencent.com:8003/dev60cgi/f60cgi?lookAndFeel=ORACLE&colorScheme=OLIVE&lang=ZHS&env=NLS_LANG='SIMPLIFIED%20CHINESE_CHINA.UTF8'+FORMS60_USER_DATE_FORMAT='DD-MON-RRRR'+FORMS60_USER_DATETIME_FORMAT='DD-MON-RRRR%20HH24%3AMI%3ASS'+NLS_DATE_LANGUAGE='AMERICAN'+NLS_SORT='BINARY'+NLS_NUMERIC_CHARACTERS=

(11.5.9+)

确认当前系统使用的dbc文件.

select host_name||'_'||instance_name from v$instance;

dbc文件的的位置和权限位设置:

ls –al $FND_SECURE/*.dbc (11.5.10+).

owner最好为 applmgr，权限伟644.

用AdminAppServer 验证dbc文件.

java oracle.apps.fnd.security.AdminAppServer apps/vecentli STATUS DBC=/u01/dev/devappl/fnd/11.5.0/secure/testdb.tencent.com_dev.dbc

返回的status必须为valid..

当然，你还要确认系统是否使用这个dbc文件.

在路径$APPL_TOP/admin/[SID]_[host].xml
查找_dbc_file_name,和上面说的dbc文件匹配即可.

修正dbc文件就重新autoconfig了.也可以运行..

$COMMON_TOP/admin/install/adgendbc.sh

1:

SELECT
TO_NUMBER(SUBSTRdbms_session.unique_session_id,1,4),'XXXX') FROM DUAL ;

备注:

以下方法获取的不是当前用户的sid.

select SYS_CONTEXT('USERENV','SESSIONID') sessionid
from dual

而是audit session id。

SYS_CONTEXT('USERENV','SESSIONID') = v$session.AUDSID

参见:

http://www.itpub.net/showthread.php?=&postid=2888443#post2888443

测试过程：
1、建立测试表
CREATE TABLE student
(
id NUMBER,
name VARCHAR2(30),
sex VARCHAR2(10),
address VARCHAR2(100),
postcode VARCHAR2(10),
birthday DATE,
photo LONG RAW
)
/

2、建立带ref cursor定义的包和包体及函数：
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor;

--函数申明
function get(intID number) return myrctype;
end pkg_test;
/

CREATE OR REPLACE
package body pkg_test as
--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试，直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值，用:w_id来申明该变量从外部获得
sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
open rc for sqlstr using intid;
end if;

return rc;
end get;

end pkg_test;
/

3、用pl/sql块进行测试：
declare
w_rc pkg_test.myrctype; --定义ref cursor型变量

--定义临时变量，用于显示结果
w_id student.id%type;
w_name student.name%type;
w_sex student.sex%type;
w_address student.address%type;
w_postcode student.postcode%type;
w_birthday student.birthday%type;

begin
--调用函数，获得记录集
w_rc := pkg_test.get(1);

--fetch结果并显示
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
dbms_output.put_line(w_name);
end;

4、测试结果：
通过。

在上述的online document中有相当多的内容，包括怎样在pro*c传递动态参数给存储过程、ref cusor的使用限制、open cursor for的4中不同方法等等。

这是我一直以来对hard parse和soft parse的理解。。
以下是5个步骤。。

1：语法是否合法。(sql写法)
2：语义是否合法。(权限，对象是否存在)

3:检查该sql是否在公享池中存在.

//如果存在存在,直接跳过3和4,运行sql. 此时算soft parse。

//如果5个步骤全做,这就叫hard parse.
4：选择执行计划。
5：产生执行计划。

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

Cocuments and Settingsvecentli>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 7月 13 09:32:28 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn sys/********@oravis as sysdba
已连接。
SQL> grant select any table to ap;

授权成功。

SQL>

SQL> conn ap/********@oravis;
已连接。
SQL> desc tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID

SQL> select count(*)
2 from tab;

COUNT(*)
----------
255


SQL> desc scott.tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER

SQL> select *
2 from scott.tab
3 ;
from scott.tab
*
ERROR 位于第 2 行:
ORA-00942: 表或视图不存在

SQL>

SQL> conn sys/************@oravis as sysdba
已连接。
SQL> desc tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER

SQL> desc scott.tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER

SQL> select *
2 from scott.tab;
from scott.tab
*
ERROR 位于第 2 行:
ORA-00942: 表或视图不存在


SQL>

dba权限的用户也不可以。。
只能desc其他用户的tab，而不能select其他用户的tab。。
看来不是权限的问题，难道oracle限制了这个做法？

----回过头来看看视图定义。

select o.name,
decode(o.type#, 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM'), t.tab#
from sys.tab$ t, sys.obj$ o
where o.owner# = userenv('SCHEMAID')
and o.type# >=2
and o.type# <=5
and o.linkname is null
and o.obj# = t.obj# (+);


肯定这个 userenv('SCHEMAID') 破玩意在作怪！

SQL> select userenv('SCHEMAID')
2 from dual;

USERENV('SCHEMAID')
-------------------
0

SQL>

SCHEMAID的解释.
SCHEMAID returns the id of the schema for the current user. This id is used, for example, in obj$ (column owner#).
select userenv('SCHEMAID') from dual;

--可是为什么会报 表或视图不存在的错误呢？应该是未选定行才对啊?!

猜测:

根椐下面的试验，我猜测DESC时如果指定了方案限定词时是查找用户的表，视图，私有同义词或者公有同义词，而SELECT的时候，如果指定了方案限定词，不会去查找公有同义词，所以才有这样的差别。

SQL> show user
USER is "WYQ"
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
PLAN_TABLE
T

SQL> desc t
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------

X NOT NULL NUMBER
Y VARCHAR2(128)

SQL> create synonym syn_t for t;

Synonym created.

SQL> conn sys/wyq as sysdba
Connected.
SQL> desc wyq.syn_t
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------

X NOT NULL NUMBER
Y VARCHAR2(128)

SQL> select count(*) from wyq.syn_t;

COUNT(*)
----------
100

SQL> conn wyq/wyq
Connected.
SQL> drop synonym syn_t;

Synonym dropped.

SQL> create public synonym syn_t for t;

Synonym created.

SQL> conn sys/wyq as sysdba
Connected.
SQL> desc wyq.syn_t;
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------

X NOT NULL NUMBER
Y VARCHAR2(128)

SQL> select count(*) from wyq.syn_t;
select count(*) from wyq.syn_t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

－－做个trace验证一把。

sqlplus " /as sysdba"
alter session set events '10046 trace name context forever,level 12'
;
desc sys.TT (there's no object name TT in my db)
exit

some thing in the trace file.

PARSING IN CURSOR #1 len=198 dep=1 uid=0 oct=3 lid=0 tim=2205811854802 hv=2703824309 ad=
'91cfeb40'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$
where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is nu
ll and subname is null
END OF STMT
PARSE #1:c=0,e=3248,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2205811854790
BINDS #1:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d069640 bln=22 avl=03 flg=05
value=0
bind 1: dty=1 mxl=32(02) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=ffffffff7d069608 bln=32 avl=02 flg=05
value="TT"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d0695d8 bln=24 avl=02 flg=05
value=1
EXEC #1:c=10000,e=3068,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2205811858366
FETCH #1:c=0,e=310,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,tim=2205811858772
BINDS #1:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d069640 bln=22 avl=02 flg=05
value=1
bind 1: dty=1 mxl=32(02) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=ffffffff7d069608 bln=32 avl=02 flg=05
value="TT"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d0695d8 bln=24 avl=02 flg=05

上面红色部分是owner# 的bind 值

1 select name,user#
2* from user$ where user# in (0,1)
SQL> /
PUBLIC 1
SYS 0

可以看出，Oracle先根据“SYS”(owner#=0) 找，没找到，于是就到“public” (owner#=1) 里找
....
--继续做select的trace。

SQL> alter session set events '10046 trace name context forever,level 12'
2 /

Session altered.

SQL> select * from sys.TT
2 /
select * from sys.TT
*
ERROR at line 1:
ORA-00942: table or view does not exist

可以方向在trace file里只找了"SYS”(owner#=0) 的object
PARSING IN CURSOR #3 len=198 dep=1 uid=0 oct=3 lid=0 tim=2206623598500 hv=2703824309 ad=
'91cfeb40'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$
where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is nu
ll and subname is null
END OF STMT
PARSE #3:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2206623598489
BINDS #3:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d068d78 bln=22 avl=01 flg=05
value=0
bind 1: dty=1 mxl=32(02) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=ffffffff7d068d40 bln=32 avl=02 flg=05
value="TT"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d068d10 bln=24 avl=02 flg=05
value=1
EXEC #3:c=0,e=621,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2206623599523
FETCH #3:c=0,e=252,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,tim=2206623599848
=====================
PARSE ERROR #2:len=21 dep=0 uid=0 oct=3 lid=0 tim=2206623600042 err=942
select * from sys.TT

conclusions:

DESC时如果指定了方案限定词时是查找用户的表，视图，私有同义词或者公有同义词，而SELECT的时候，如果指定了方案限定词，不会去查找公有同义词，所以才有这样的差别。

出现ora_04031 非常有可能是共享池碎片严重。或者shared_pool/java pool太

小。一般来说，重启或者执行 alter system flush shared_pool;语句就可

以解决。建议做个statspack看看，查找可能的原因。

前段时间看到了pub上有人问如何跟踪绑定变量的值。当时也没有想到办法，今天再看跟踪事件，
发现了设置10046事件并且level大于4能显示绑定变量的详细信息，但是否有绑定变量的值呢？我试了一下。结果如下:
对当前session,设置10046事件,ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
执行如下pl/sql，

declare
v_birth date:=sysdate;
v_accounts char(10):= 'lijietz' ;
begin
insert into lijietz values(v_birth, v_accounts);
end;
然后到user_dump_dest下找trace文件.大概内容如下:


PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=2 lid=0 tim=18446744073296766171 hv=507792077 ad='65e33520'
INSERT into lijietz values(:b2, :b1)
END OF STMT
PARSE #2:c=0,e=286,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,

tim=18446744073296766163
BINDS #2:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=8 offset=0
bfp=09a201f4 bln=07 avl=07 flg=09
value="5/31/2005 15:12:9"
bind 1: dty=96 mxl=32(10) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=09a201d8 bln=32 avl=10 flg=09
value="lijietz"
EXEC #2:c=31250,e=26379,p=0,cr=1,cu=2,mis=0,r=1,dep=1,

og=4,tim=18446744073296810934
EXEC #1:c=46875,e=52807,p=0,cr=1,cu=2,mis=0,r=1,dep=0,

og=4,tim=18446744073296814766

解析后的语句 INSERT into lijietz values(:b2, :b1) ，
搜索值'lijietz', value="lijietz",相同的找到了value="5/31/2005 15:12:9",注意tkprof
以后就找不到了绑定变量的值了。
最后关闭跟踪事件alter session set events '10046 trace name context off';

联接地址:http://www.itpub.net/showthread.php?s=&postid=2574881#post2574881

一：SQL tuning 类

1:列举几种表连接方式
答:mj,hj,nl


2:不借助第三方工具，怎样查看sql的执行计划
答:autotrace /utlxplan.sql


3:如何使用CBO,CBO与RULE的区别


答:在初始化参数里面设置optimizer_mode=choose/all_rows/first_row等可以使用cbo.

rbo会选择不合适的索引,cbo需要统计信息。


4:如何定位重要(消耗资源多)的SQL

答:根据v$sqlarea 中的逻辑读/disk_read。



5:如何跟踪某个session的SQL

答:先找出对应的'sid,serial',然后调用system_system.set_sql_trace_in_session(sid,serial,true);


6:SQL调整最关注的是什么

答:逻辑读。



7:说说你对索引的认识（索引的结构、对dml影响、对查询影响、为什么提高查询性能）

答:默认的索引是b-tree.
对insert的影响.(分裂，要保证tree的平衡)
对delete的影响.(删除行的时候要标记改节点为删除).
对update的影响，如果更新表中的索引字段，则要相应的更新索引中的键值。

查询中包含索引字段的键值和行的物理地址。



8:使用索引查询一定能提高查询的性能吗？为什么

答:不能。如果返回的行数目较大，使用全表扫描的性能较好。



9:绑定变量是什么？绑定变量有什么优缺点？

答:通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少只有变量值不同的
语句的解析。



10:如何稳定(固定)执行计划
答:使用stored outline.


11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么


答:8i：使用sort_area_size，hash_area_size,每个session分配相同的值，不管有无使用。
9i: 使用pga_aggregate来统一管理。

临时表空间的作用:
在sort_area_size中不能完成的部分在临时表空间完成，临时表空间在重建索引，创建临时表等都要用到。
还有hash join不能完成的也在临时表空间中做。


12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql

select a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;


二：数据库基本概念类

1ctused and pctfree 表示什么含义有什么作用

答:表示数据块什么时候重联接或者从freelist中删除。
pctused:如果数据块的使用小于pctused的值，则该数据块重新加入到fresslist中。
pctused：如果数据块的空闲空间小于pctfree的值，则该数据块从freelist中删除。



2:简单描述table / segment / extent / block之间的关系

答:table是一个逻辑上的概念。
segment表示结构的相同的一段空间。
extent。多个block组成一个extent，便于dbms分配。
block，多个os块组成一个block，是oracle i/o的单位。


3:描述tablespace和datafile之间的关系

答:
tablespace是逻辑上的概念，datafile是物理上的概念。
一个tablespace可以由多个datafile组成，一个datafile不能跨越多个tablespace。

4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点
答:一个使用freelist管理，一个使用位图管理。


5:回滚段的作用是什么

答：保存数据的前像，保证数据读取的时间点一致性。


6:日志的作用是什么

答：纪录对数据库的操作，便与恢复。



7:SGA主要有那些部分，主要作用是什么
答：db_cache(缓存数据块),shared_pool(缓存sql,执行计划，数据字典信息等),large_pool(rman要用到),java pool(java程序时要用到)


8：racle系统进程主要有哪些，作用是什么

答：smon（合并空间，实例恢复）,pmon（清理失败的进程）,归档进程（负责在日志切换的时候归档日志文件）,lgmr（日志书写器进程,负责

写日志咯）,
ckpt(检查点进程，触发检查点),dbwr(数据库写入器，负责把数据写入导datafile)





三：备份恢复类

1:备份如何分类
答：逻辑备份(exp)与物理备份。
或者冷备份与热备份。



2:归档是什么含义
答：把日志文件放到另一个地方。


3:如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复
答：
拷贝备份，
recove database until time 2004-08-04 10:30:00
alter database open resetlogs;

4:rman是什么，有何特点
答：rman叫恢复管理器.
特点很多。

1：热备份。
2：可以存储脚本。
3：可以增量备份。
4：自动管理备份集。


5:standby的特点
答：利用传输重做日志来达到同步的目的。



6:对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略
答：每天一个全备份。



四：系统管理类

1:对于一个存在系统性能的系统，说出你的诊断处理思路

答:做一个statspack，根据top 5,system load,top sql等来做相应的调整。




2:列举几种诊断IO、CPU、性能状况的方法

答:
hp-unix:iostat -x 1 5;
top/vmstat/glance


3:对statspack有何认识

答:
一个性能诊断工具而已咯，其本质就是在两个时间点采样两个系统数据。(动态性能视图)
然后根据两个snapshot，产生一个报告。



4:如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
答:

1:增大sort_area_size(8i)/pga_aggregate_target(9i)值。
2:用并行的方式来建。
3:系统空闲的时候建。



5:对raid10 和raid5有何认识
答:raid5写入慢。raid10不了解。



五：综合随意类

1:你最擅长的是oracle哪部分?
答:性能/sql 调优。



2:喜欢oracle吗？喜欢上论坛吗？或者偏好oracle的哪一部分？
答:http://www.itpub.net/forum2.html



3:随意说说你觉得oracle最有意思的部分或者最困难的部分

答:性能调优。


4:为何要选择做DBA呢?
答:自己爱好加上稍高的工资

如何在SQL*PLUS中使用Autotrace？

sqlplus system
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
SQL> conn sys
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce
SQL> grant plustrace to public;

SET AUTOTRACE OFF | ON EXPLAIN | ON STATISTICS | ON | TRACEONLY | TRACE EXPLAIN

AUTOTRACE是要实际执行一个查询的。

要启用远程控制数据库的功能，

 SQLNET.AUTHENTICATION_SERVICES =none

and

remote_login_passwordfile='EXCLUSIVE'

就可以了。

Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

Values：Authentication Methods Available with Oracle Net Services:
·NONE for no authentication methods. A valid username and password can be used to access the database.
·ALL for all authentication methods
·NTS for Windows NT native authentication

纪录于此,供以后查阅。

1 红帽群集套件配置和管理群集
 http://www.dvdshop.com.cn/manual/redhat_AS_3.0_CS/
2 红帽企业Linux4各体系的安装指南
 http://www.dvdshop.com.cn/manual/rhel-ig-x8664-multi-zh_cn-4/
3 红帽企业Linux4安全指南
 http://www.dvdshop.com.cn/manual/rhel-sg-zh_cn-4/
4 红帽企业Linux4用于IBM体系的安装指南
 http://www.dvdshop.com.cn/manual/rhel-ig-s390-multi-zh_cn-4/
5 红帽企业Linux4系統管理導論
 http://www.dvdshop.com.cn/manual/rhel-isa-zh_tw-4/

truncate,delete,drop的比较.

  
注意:这里说的delete是指不带where子句的delete语句

相同点:truncate和不带where子句的delete, 以及drop都会删除表内的数据 

不同点:  
1. truncate和 delete只删除数据不删除表的结构(定义)  
     drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.  

2.delete语句是DML,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.  
    truncate,drop是DDL, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.  

3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动  
   显然drop语句将表所占用的空间全部释放  
   truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始).  

4.速度,一般来说: drop> truncate > delete  

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及  
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.  
想删除表,当然用drop  
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.  
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据  

       等待事件（wait event）是oracle核心代码的一个命名部分，有两种类型的等待事件：空闲事件（idle event）与非空闲事件（non-idle event）,空闲事件指oracle正在等待某种工作，常见的空闲等待事件：client message、null event、pipe get、pmon/smon timer、rdbms rpc message及sql*net等；非空闲等待事件：buffer busy waits、db file scattered read、db file sequential read、enqueue、free buffer waits、latch free、log file sync、log file paralle write等。
什么是瓶颈？一旦熟悉了系统的等待事件，就能够把握问题的关键，并能够用相应的方法去处理阻塞系统的瓶颈，一定不要随意的进行优化，否则一波不息一波又起，可以通过v$system_event获取系统总的等待情况，然后通过v$session_event查看系统中session的等待情况，最后通过v$session_wait定位瓶颈对象。v$session_wait是会话级的，它包含session的实时信息，最重要的是：它显示了等待事件与相应资源的更深入信息，可确定出产生瓶颈的类型及其对象。
v$session_wait的p1、p2、p3告诉我们等待事件的具体含义，如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然后通过dba_extents即可确定出热点对象；如果是latch free的话,p2为闩锁号，它指向v$latch。

--求等待事件及其对应的latch

col event format a32
col name format a32
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = l.latch#(+);

--求等待事件及其热点对象

col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
from dba_extents
where file_id = &file_id and &block_id between block_id
and block_id + &blocks - 1;

--综合以上两条sql，同时显示latch及热点对象(速度较慢)

select sw.sid,event,l.name,de.segment_name
from v$session_wait sw,v$latch l,dba_extents de
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = l.latch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1;

--如果是非空闲等待事件，通过等待会话的sid可以求出该会话在执行的sql

select sql_text
from v$sqltext_with_newlines st,v$session se
where st.address=se.sql_address and st.hash_value=se.sql_hash_value
and se.sid =&wait_sid order by piece;

通过等待事件找出系统中消耗资源较严重的sql，是dba进行系统诊断的手段之一。只是过程稍嫌烦琐，由于session是动态的、瞬息万变、不可捕获，当你想捕获时，该session可能已经释放，但这种捕获很有针对性；也可以通过对v$sql或v$sqlarea进行过滤，找出存在性能问题的sql,长时间地对v$sql进行监控，并对捕获的sql进行优化处理，可以在很大程度上解决系统的性能问题。

１．shared pool latch和library cache latch：

共享池基本上由库缓存和字典缓存构成
ＳＱＬ语句的操作过程中服务器进程需要访问共享池和高速缓存，如：发出一个ＳＱＬ语句时，服务器进程首先要向共享池的库缓存中查看是否缓存有已编译过的版本，如果没有，就要重新分析，重新申请一个共享的ＳＱＬ区放置这个语句的执行计划等等，这些操作都需要申请shared pool latch和library cache latch. 　如果本来可以共享的ＳＱＬ语句因为没有使用绑定变量，而频繁被重新分析，这样势必导致这些申请过多。还有一方面原因，如果library cache 太小，就会引起语句游标被挤出内存的现象。因此需要调整共享池，更有效解决方法是查看ＳＱＬ，避免低效率的ＳＱＬ。
如果使用共享服务器连接方式，这种连接方式在ＳＧＡ方面要设置的内存高一些，用户个人ＵＧＡ可以小一些。设置大池，可以使共享池更加专用于缓存sql 和pl/sql, 这样有大内存调用要求时，如ＲＭＡＮ备份时，启用多个Ｉ／Ｏslaves，并行处理时，不会影响共享中缓存的经常被访问的而没有被cache进内存的块。如果连接数太多时，应改变连接方式，使用专有服务器连接。

２．cache buffer lru chain latch

到ＳＱＬ语句执行阶段，比如一个更新语句，更新某个数据块，服务器进程这时以要向高速缓存发出请求查询其中是否已经缓存有它要找的数据，如果没有，要从硬盘上把相应的数据块拷贝一份放进高速缓存，这样还要搜索ＬＲＵ列表，找到一个自由buffer放置服务器进程从磁盘上读来的拷贝。这就需要申请cache buffers lru latch。当脏块需要被ＤＢＷｎ进程写入磁盘时，ＤＢＷｎ进程需要访问ＬＲＵ列表以便写脏块，具体的不必赘述。　全表扫描过的表会被放在ＬＲＵ列表的lru端很快地被剔除出内存，会导致经常访问的数据块在高速缓存中停留的时间很短，因此建议经常访问的小型表要cache在内存里。不适当的索引导致优化器不会去选择索引扫描而是选择全表扫描。　同样高速缓存设置得太大，导致ＤＢＷｎ进程经常疲于搜索ＬＲＵ列表去写脏块。因此，这方面的锁冲突要看具体情况，如果因为全表扫描太多，那么就需要创建合适的索引，如果高速缓存太大，就调整调整缓存，如果高速缓存不能减少的话，而且你有多个ＣＰＵ的话，可以增加ＤＢＷｎ进程。如果只有一个ＣＰＵ，那么使用dbwr I/O slaves进程效果要差些，因为slaves进程只能帮助Ｉ／Ｏ写脏块，不能帮助ＤＢＷＲ进程搜索ＬＲＵ列表。

在unix平台:

#!/bin/sh

FILE=$1

ftp -i -in <open 192.168.7.9 21
user username password
cd /
mput $FILE
bye
!


在win平台

.txt:
open ftp服务器地址 端口 （如果端口为默认的21，那么可以省略端口）
ftp用户名
ftp密码
运行的命令
bye

b.bat
ftp -s:a.txt（输入没有空格的绝对路径，如：-s:c:a.txt）

今天要写个ftp教本,就上网收集了一下ftp命令信息，记录在此，便于以后查用。

FTP的命令行格式为：
ftp -v -d -i -n -g [主机名] ，其中

-v 显示远程服务器的所有响应信息；

-n 限制ftp的自动登录，即不使用；.n etrc文件；

-d 使用调试方式；

-g 取消全局文件名。

FTP使用的内部命令如下(中括号表示可选项):

1.![cmd[args]]：在本地机中执行交互shell，exit回到ftp环境，如：!ls*.zip

2.$ macro-ame[args]： 执行宏定义macro-name。

3.account[password]： 提供登录远程系统成功后访问系统资源所需的补充口令。

4.append local-file[remote-file]：将本地文件追加到远程系统主机，若未指定远程系统文件名，则使用本地文件名。

5.ascii：使用ascii类型传输方式。
6.bell：每个命令执行完毕后计算机响铃一次。

7.bin：使用二进制文件传输方式。
8.bye：退出ftp会话过程。

9.case：在使用mget时，将远程主机文件名中的大写转为小写字母。

10.cd remote-dir：进入远程主机目录。

11.cdup：进入远程主机目录的父目录。

12.chmod mode file-name：将远程主机文件file-name的存取方式设置为mode，如：chmod 777 a.out。

13.close：中断与远程服务器的ftp会话(与open对应)。

14.cr：使用asscii方式传输文件时，将回车换行转换为回行。

15.delete remote-file：删除远程主机文件。

16.debug[debug-value]：设置调试方式， 显示发送至远程主机的每条命令，如：deb up 3，若设为0，表示取消debug。

17.dir[remote-dir][local-file]：显示远程主机目录，并将结果存入本地文件
18.disconnection：同close。

19.form format：将文件传输方式设置为format，缺省为file方式。

20.get remote-file[local-file]： 将远程主机的文件remote-file传至本地硬盘的local-file。

21.glob：设置mdelete，mget，mput的文件名扩展，缺省时不扩展文件名，同命令行的-g参数。

22.hash：每传输1024字节，显示一个hash符号(#)。

23.help[cmd]：显示ftp内部命令cmd的帮助信息，如：help get。
24.idle[seconds]：将远程服务器的休眠计时器设为[seconds]秒。

25.image：设置二进制传输方式(同binary)。

26.lcd[dir]：将本地工作目录切换至dir。

27.ls[remote-dir][local-file]：显示远程目录remote-dir， 并存入本地文件local-file。

28.macdef macro-name：定义一个宏，遇到macdef下的空行时，宏定义结束。
29.mdelete[remote-file]：删除远程主机文件。
30.mdir remote-files local-file：与dir类似，但可指定多个远程文件，如 ：mdir *.o.*.zipoutfile 。

31.mget remote-files：传输多个远程文件。

32.mkdir dir-name：在远程主机中建一目录。

33.mls remote-file local-file：同nlist，但可指定多个文件名。
34.mode[modename]：将文件传输方式设置为modename， 缺省为stream方式。
35.modtime file-name：显示远程主机文件的最后修改时间。

36.mput local-file：将多个文件传输至远程主机。

37.newer file-name： 如果远程机中file-name的修改时间比本地硬盘同名文件的时间更近，则重传该文件。
38.nlist[remote-dir][local-file]：显示远程主机目录的文件清单，并存入本地硬盘的local-file。
39.nmap[inpattern outpattern]：设置文件名映射机制， 使得文件传输时，文件中的某些字符相互转换， 如：nmap $1.$2.$3[$1，$2].[$2，$3]，则传输文件a1.a2.a3时，文件名变为a1，a2。 该命令特别适用于远程主机为非UNIX机的情况。
40.ntrans[inchars[outchars]]：设置文件名字符的翻译机制，如ntrans1R，则文件名LLL将变为RRR。

41.open host[port]：建立指定ftp服务器连接，可指定连接端口。

42.passive：进入被动传输方式。

43.prompt：设置多个文件传输时的交互提示。

44.proxy ftp-cmd：在次要控制连接中，执行一条ftp命令， 该命令允许连接两个ftp服务器，以在两个服务器间传输文件。第一条ftp命令必须为open，以首先建立两个服务器间的连接。
45.put local-file[remote-file]：将本地文件local-file传送至远程主机。
46.pwd：显示远程主机的当前工作目录。

47.quit：同bye，退出ftp会话。

48.quote arg1，arg2...：将参数逐字发至远程ftp服务器，如：quote syst.
49.recv remote-file[local-file]：同get。

50.reget remote-file[local-file]：类似于get， 但若local-file存在，则从上次传输中断处续传。

51.rhelp[cmd-name]：请求获得远程主机的帮助。

52.rstatus[file-name]：若未指定文件名，则显示远程主机的状态， 否则显示文件状态。

53.rename[from][to]：更改远程主机文件名。

54.reset：清除回答队列。

55.restart marker：从指定的标志marker处，重新开始get或put，如：restart 130。
56.rmdir dir-name：删除远程主机目录。

57.runique：设置文件名只一性存储，若文件存在，则在原文件后加后缀.1， .2等。

58.send local-file[remote-file]：同put。

59.sendport：设置PORT命令的使用。

60.site arg1，arg2...：将参数作为SITE命令逐字发送至远程ftp主机。

61.size file-name：显示远程主机文件大小，如：site idle 7200。

62.status：显示当前ftp状态。

63.struct[struct-name]：将文件传输结构设置为struct-name， 缺省时使用stream结构。

64.sunique：将远程主机文件名存储设置为只一(与runique对应)。

65.system：显示远程主机的操作系统类型。

66.tenex：将文件传输类型设置为TENEX机的所需的类型。

67.tick：设置传输时的字节计数器。

68.trace：设置包跟踪。

69.type[type-name]：设置文件传输类型为type-name，缺省为ascii，如:type binary，设置二进制传输方式。

70.umask[newmask]：将远程服务器的缺省umask设置为newmask，如：umask 3

71.user user-name[password][account]：向远程主机表明自己的身份，需要口令时，必须输入口令，如：user anonymous my@email。
72.verbose：同命令行的-v参数，即设置详尽报告方式，ftp 服务器的所有响 应都将显示给用户，缺省为on.

73.?[cmd]：同help.                              

在会话级，查询视图V$SESSION_WAIT时如果有该事件存在，那么该视图中的

P1－表示Latch地址，也就是进程正在等待的latch地址。

P2－表示Latch编号，对应于视图V$LATCHNAME中的latch#。

P3－表示为了获得该latch而尝试的次数。

五部分、ORACLE网络与安全
[Q]如何限定特定IP访问数据库
[A]可以利用登录触发器、cmgw或者是在$OREACLE_HOME/network/admin下新增一个protocol.ora文件（有些os可能是. protocol.ora），9i可以直接修改sqlnet.ora：
增加如下内容：
tcp.validnode_checking=yes
#允许访问的ip
tcp.inited_nodes=(ip1,ip2,……)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,……)
[Q]如何穿过防火墙连接数据库
[A]这个问题只会在WIN平台出现，UNIX平台会自动解决。
解决方法：
在服务器端的SQLNET.ORA应类似
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
TRACE_LEVEL_CLIENT = 16
注册表的HOME0加[HKEY_LOCAL_MACHINE]
USE_SHARED_SOCKET=TRUE
[Q]如何利用hostname方式连接数据库
host name方式只支持tcp/ip协议的小局域网
修改listener.ora中的如下信息
(SID_DESC =
(GLOBAL_DBNAME = ur_hostname) --你的机器名
(ORACLE_HOME = E:oracleora92) --oracle home
(SID_NAME = orcl) --sid name
)
然后在客户端
的sqlnet.ora中，确保有
NAMES.DIRECTORY_PATH= (HOSTNAME)
你就可以利用数据库服务器的名称访问数据库了
[Q]dbms_repcat_admin能带来什么安全隐患
[A]如果一个用户能执行dbms_repcat_admin包，将获得极大的系统权限。
以下情况可能获得该包的执行权限：
1、在sys下grant execute on dbms_repcat_admin to public[|user_name]
2、用户拥有execute any procedure特权（仅限于9i以下，9i必须显示授权）
如果用户通过执行如下语句：
exec sys.dbms_repcat_admin.grant_admin_any_schema('user_name');
该用户将获得极大的系统特权
可以从user_sys_privs中获得详细信息
[Q]在不知道用户密码的时候，怎么样跳转到另外一个用户执行操作后并不影响该用户?
[A]我们通过如下的方法，可以安全使用该用户，然后再跳转回来，在某些时候比较有用
需要Alter user权限或DBA权限：
SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.
[Q]如何加固你的数据库
[A]要注意以下方面
1. 修改sys, system的口令。
2. Lock，修改，删除默认用户： dbsnmp,ctxsys等。
3. 把REMOTE_OS_AUTHENT改成False，防止远程机器直接登陆。
4. 把O7_DICTIONARY_ACCESSIBILITY改成False。
5. 把一些权限从PUBLIC Role取消掉。
6. 检查数据库的数据文件的安全性。不要设置成666之类的。检查其他dba 用户。
7. 把一些不需要的服务（比如ftp, nfs等关闭掉）
8. 限制数据库主机上面的用户数量。
9. 定期检查Metalink/OTN上面的security Alert。比如：
http://otn.oracle.com/deploy/security/alerts.htm

10. 把你的数据库与应用放在一个单独的子网中，要不然你的用户密码很容易被sniffer去。或者采用advance security，对用户登录加密。
11. 限止只有某些ip才能访问你的数据库。
12. lsnrctl 要加密码，要不然别人很容易从外面关掉你的listener。
13. 如果可能，不要使用默认1521端口
[Q]如何检查用户是否用了默认密码

[A]如果使用默认密码，很可能就对你的数据库造成一定的安全隐患，那么可以使用如下的查询获得那些用户使用默认密码
select username "User(s) with Default Password!"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'D4DF7931AB130E37') -- system
/
[Q]如何修改默认的XDB监听端口

[A] Oracle9i默认的XML DB把HTTP的默认端口设为8080，这是一个太常用的端口了，很多别的WebServer都会使用这个端口，
如果我们安装了它，最好修改一下，避免冲突，如果不使用呢，就最好不要安装
提供三种修改的方法
1.dbca，选择你的数据库，然后Standard Database Features->Customize->Oracle XML DB option，进入这个画面你应该就知道怎么改了。
2.OEM console，在XML Database 的配置里面修改
3.用oracle提供的包：
-- 把HTTP/WEBDAV端口从8080改到8081
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',8081))
/
-- 把FTP端口从2100改到2111
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()',2111))
/
SQL> commit;
SQL> exec dbms_xdb.cfg_refresh;
-- 检查修改是否已经成功
SQL> select dbms_xdb.cfg_get from dual;
[Q]怎么捕获用户登录信息，如SID，IP地址等
[A]可以利用登录触发器，如
CREATE OR REPLACE TRIGGER tr_login_record
AFTER logon ON DATABASE
DECLARE
miUserSid NUMBER;
mtSession v$session%ROWTYPE;
CURSOR cSession(iiUserSid IN NUMBER) IS
SELECT * FROM v$session
WHERE sid=iiUserSid;
BEGIN
SELECT sid INTO miUserSid FROM v$mystat WHERE rownum

四部分、性能调整
[Q]如果设置自动跟踪
[A]用system登录
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用，则
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想让自动跟踪的角色让每个用户都能使用，则
SQL> grant plustrace to public;
通过如下语句开启/停止跟踪
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
[Q]如果跟踪自己的会话或者是别人的会话
[A]跟踪自己的会话很简单
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟踪别人的会话，需要调用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）
SELECT p1.value||''||p2.value||'_ora_'||p.spid||'.ora' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')
最后，可以通过Tkprof来解析跟踪文件，如
Tkprof 原文件 目标文件 sys=n
[Q]怎么设置整个数据库系统跟踪
[A]其实文档上的alter system set sql_trace=true是不成功的
但是可以通过设置事件来完成这个工作，作用相等
alter system set events
'10046 trace name context forever,level 1';
如果关闭跟踪，可以用如下语句
alter system set events
'10046 trace name context off';
其中的level 1与上面的8都是跟踪级别
level 1：跟踪SQL语句，等于sql_trace=true
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件
[Q]怎么样根据OS进程快速获得DB进程信息与正在执行的语句
[A]有些时候，我们在OS上操作，象TOP之后我们得到的OS进程，怎么快速根据OS信息获得DB信息呢？
我们可以编写如下脚本：
$more whoit.sh
#!/bin/sh
sqlplus /nolog 100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
[Q]怎么样快速重整索引
[A]通过rebuild语句，可以快速重整或移动索引到别的表空间
rebuild有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数
语法为
alter index index_name rebuild tablespace ts_name
storage(……);
如果要快速重建整个用户下的索引，可以用如下脚本，当然，需要根据你自己的情况做相应修改
SQL> set heading off
SQL> set feedback off
SQL> spool d:index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = USER
SQL>spool off
另外一个合并索引的语句是
alter index index_name coalesce，这个语句仅仅是合并索引中同一级的leaf block
消耗不大，对于有些索引中存在大量空间浪费的情况下，有一些作用。
[Q]如何使用Hint提示
[A] 在select/delete/update后写/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之间不能有空格
如用hint指定使用某个索引
select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;
如果索引名或表名写错了，那这个hint就会被忽略;
[Q]怎么样快速复制表或者是插入数据
[A]快速复制表可以指定Nologging选项
如：Create table t1 nologging
as select * from t2;
快速插入数据可以指定append提示，但是需要注意
noarchivelog模式下，默认用了append就是nologging模式的。
在archivelog下，需要把表设置程Nologging模式。
如insert /*+ append */ into t1
select * from t2
注意：如果在9i环境中并设置了FORCE LOGGING，则以上操作是无效的，并不会加快，当然，可以通过如下语句设置为NO FORCE LOGGING。
Alter database no force logging;
是否开启了FORCE LOGGING，可以用如下语句查看
SQL> select force_logging from v$database;
[Q]怎么避免使用特定索引
[A]在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免使用不该使用的索引，如：
表test,有字段a,b,c,d，在a,b,c上建立联合索引inx_a(a,b,c)，在b上单独建立了一个索引Inx_b(b)。
在正常情况下，where a=? and b=? and c=?会用到索引inx_a，
where b=?会用到索引inx_b
但是，where a=? and b=? and c=? group by b会用到哪个索引呢？在分析数据不正确（很长时间没有分析）或根本没有分析数据的情况下，oracle往往会使用索引inx_b。通过执行计划的分析，这个索引的使用，将大大耗费查询时间。
当然，我们可以通过如下的技巧避免使用inx_b，而使用inx_a。
where a=? and b=? and c=? group by b||'' --如果b是字符
where a=? and b=? and c=? group by b+0 --如果b是数字
通过这样简单的改变，往往可以是查询时间提交很多倍
当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b
[Q]Oracle什么时候会使用跳跃式索引扫描
[A]这是9i的一个新特性跳跃式索引扫描(Index Skip Scan).
例如表有索引index(a,b,c)，当查询条件为
where b=?的时候，可能会使用到索引index(a,b,c)
如，执行计划中出现如下计划：
INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:
1 优化器认为是合适的。
2 索引中的前导列的唯一值的数量能满足一定的条件（如重复值很多）。
3 优化器要知道前导列的值分布(通过分析/统计表得到)。
4 合适的SQL语句
等。
[Q]怎么样创建使用虚拟索引
[A]可以使用nosegment选项，如
create index virtual_index_name on table_name(col_name) nosegment;
如果在哪个session需要测试虚拟索引，可以利用隐含参数来处理
alter session set "_use_nosegment_indexes" = true;
就可以利用explain plan for select ……来看虚拟索引的效果
利用@$ORACLE_HOME/rdbms/admin/utlxpls查看执行计划
最后，根据需要，我们可以删除虚拟索引，如普通索引一样
drop index virtual_index_name;
注意：虚拟索引并不是物理存在的，所以虚拟索引并不等同于物理索引，不要用自动跟踪去测试虚拟索引，因为那是实际执行的效果，是用不到虚拟索引的。
[Q]怎样监控无用的索引
[A]Oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引
语法为：
开始监控：alter index index_name monitoring usage;
检查使用状态：select * from v$object_usage;
停止监控：alter index index_name nomonitoring usage;
当然，如果想监控整个用户下的索引，可以采用如下的脚本：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
[Q]怎么样能固定我的执行计划
[A]可以使用OUTLINE来固定SQL语句的执行计划
用如下语句可以创建一个OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
如果要删除Outline，可以采用
Drop Outline OutLn_Name;
对于已经创建了的OutLine，存放在OUTLN用户的OL$HINTS表下面
对于有些语句，你可以使用update outln.ol$hints来更新outline
如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
这样，你就把Test1 OUTLINE与Test2 OUTLINE互换了
如果想利用已经存在的OUTLINE，需要设置以下参数
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true
[Q]v$sysstat中的class分别代表什么
[A]统计类别
1 代表事例活动
2 代表Redo buffer活动
4 代表锁
8 代表数据缓冲活动
16 代表OS活动
32 代表并行活动
64 代表表访问
128 代表调试信息
[Q]怎么杀掉特定的数据库会话
[A] Alter system kill session 'sid,serial#';
或者
alter system disconnect session 'sid,serial#' immediate;
在win上，还可以采用oracle提供的orakill杀掉一个线程（其实就是一个Oracle进程）
在Linux/Unix上，可以直接利用kill杀掉数据库进程对应的OS进程
[Q]怎么快速查找锁与锁等待
[A]数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
[Q] 如何有效的删除一个大表(extent数很多的表)
[A] 一个有很多(100k)extent的表，如果只是简单地用drop table的话，会很大量消耗CPU（Oracle要对fet$、uet$数据字典进行操作），可能会用上几天的时间，较好的方法是分多次删除extent，以减轻这种消耗：
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
....
4. drop table big-table;
[Q]如何收缩临时数据文件的大小
[A]9i以下版本采用
ALTER DATABASE DATAFILE 'file name' RESIZE 100M类似的语句
9i以上版本采用
ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
注意，临时数据文件在使用时，一般不能收缩，除非关闭数据库或断开所有会话，停止对临时数据文件的使用。
[Q]怎么清理临时段
[A]可以使用如下办法
1、 使用如下语句查看一下认谁在用临时段
SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype, contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
2、 那些正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空间回缩一下
SQL>Alter tablespace TEMP coalesce;
还可以使用诊断事件
1、 确定TEMP表空间的ts#
SQL> select ts#, name FROM v$tablespace;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
……
2、 执行清理操作
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
说明：
temp表空间的TS# 为 3*, So TS#+ 1= 4
如果想清除所有表空间的临时段，则
TS# = 2147483647
[Q]怎么样dump数据库内部结构，如上面显示的控制文件的结构
[A]常见的有
1、分析数据文件块，转储数据文件n的块m
alter system dump datafile n block m
2、分析日志文件
alter system dump logfile logfilename;
3、分析控制文件的内容
alter session set events 'immediate trace name CONTROLF level 10'
4、分析所有数据文件头
alter session set events 'immediate trace name FILE_HDRS level 10'
5、分析日志文件头
alter session set events 'immediate trace name REDOHDR level 10'
6、分析系统状态，最好每10分钟一次，做三次对比
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7、分析进程状态
alter session set events 'immediate trace name PROCESSSTATE level 10'
8、分析Library Cache的详细情况
alter session set events 'immediate trace name library_cache level 10'
[Q]如何获得所有的事件代码
[A] 事件代码范围一般从10000 to 10999，以下列出了这个范围的事件代码与信息
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
在Unix系统上，事件信息放在一个文本文件里
$ORACLE_HOME/rdbms/mesg/oraus.msg
可以用如下脚本查看事件信息
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
对于已经确保的/正在跟踪的事件，可以用如下脚本获得
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line ('Event '||TO_CHAR (l_event)||
' is set at level '||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/
[Q]什么是STATSPACK，我怎么使用它？
[A]Statspack是Oracle 8i以上提供的一个非常好的性能监控与诊断工具，基本上全部包含了BSTAT/ESTAT的功能，更多的信息
可以参考附带文档$ORACLE_HOME/rdbms/admin/spdoc.txt。
安装Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- 卸载，第一次可以不需要
sqlplus "/ as sysdba" @spcreate.sql -- 需要根据提示输入表空间名
使用Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- 进行信息收集统计，每次运行都将产生一个快照号
-- 获得快照号，必须要有两个以上的快照，才能生成报表
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- 输入需要查看的开始快照号与结束快照号
其他相关脚本s:
spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计
sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号
sptrunc.sql - 清除(truncate)所有统计信息

三部分、备份与恢复
[Q]如何开启/关闭归档
[A]如果开启归档，请保证log_archive_start=true开启自动归档，否则只能手工归档，如果是关闭了归档，则设置该参数为false
注意：如果是OPS/RAC环境，需要先把parallel_server = true注释掉，然后执行如下步骤，最后用这个参数重新启动
1、开启归档
a. 关闭数据库shutdown immediate
b. startup mount
c. alter database archivelog
d. alter database opne
2、禁止归档
a. 关闭数据库shutdown immediate
b. startup mount
c. alter database noarchivelog
d. alter database open
归档信息可以通过如下语句查看
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:oracleora92databasearchive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133
[Q]怎样设置定时归档
[A]9i以上版本，保证归档的最小间隔不超过n秒
设置Archive_lag_target = n
单位：秒 范围：0~7200
[Q]不同版本怎么导出/导入
[A]导出用低版本，导入用当前版本
如果版本跨越太大，需要用到中间版本过渡
[Q]不同的字符集之前怎么导数据
[A]a.前条件是保证导出/导入符合其他字符集标准，如客户环境与数据库字符集一致。
b.修改dmp文件的2、3字节为目标数据库的字符集，注意要换成十六进制。
参考函数（以下函数中的ID是十进制的）：
nls_charset_name 根据字符集ID获得字符集名称
nls_charset_id 根据字符集名称获得字符集ID
[Q]怎么样备份控制文件
[A]再线备份为一个二进制的文件
alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse];
备份为文本文件方式
alter database backup controlfile to trace [resetlogs|noresetlogs];
[Q]控制文件损坏如何恢复
[A]1、如果是损坏单个控制文件
只需要关闭数据库，拷贝一个好的数据文件覆盖掉坏的数据文件即可
或者是修改init.ora文件的相关部分
2、如果是损失全部控制文件，则需要创建控制文件或从备份恢复
创建控制文件的脚本可以通过alter database backup controlfile to trace获取。
[Q]怎么样热备份一个表空间
[A]Alter tablespace 名称 begin backup;
host cp 这个表空间的数据文件 目的地;
Alter tablespace 名称 end backup;
如果是备份多个表空间或整个数据库，只需要一个一个表空间的操作下来就可以了。
[Q]怎么快速得到整个数据库的热备脚本
[A]可以写一段类似的脚本
SQL>set serveroutput on
begin
dbms_output.enable(10000);
for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop
dbms_output.put_line('--'||bk_ts.name);
dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;');
for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop
dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/');
end loop;
dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;');
end loop;
end;
/
[Q]丢失一个数据文件，但是没有备份，怎么样打开数据库
[A]如果没有备份只能是删除这个数据文件了，会导致相应的数据丢失。
SQL>startup mount
--ARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline;
--NOARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline drop;
SQLl>Alter database open;
注意：该数据文件不能是系统数据文件
[Q]丢失一个数据文件，没有备份但是有该数据文件创建以来的归档怎么恢复
[A]保证如下条件
a. 不能是系统数据文件
b. 不能丢失控制文件
如果满足以上条件，则
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ... reuse;
SQL>recover datafile n; -文件号
或者
SQL>recover datafile 'file name';
或者
SQL>recover database;
SQL>Alter database open;
[Q]联机日志损坏如何恢复
[A]1、如果是非当前日志而且归档，可以使用
Alter database clear logfile group n来创建一个新的日志文件
如果该日志还没有归档，则需要用
Alter database clear unarchived logfile group n
2、如果是当前日志损坏，一般不能clear，则可能意味着丢失数据
如果有备份，可以采用备份进行不完全恢复
如果没有备份，可能只能用_allow_resetlogs_corruption=true来进行强制恢复了，但是，这样的方法是不建议的，最好在有Oracle support的指导下进行。
[Q]怎么样创建RMAN恢复目录
[A]首先，创建一个数据库用户，一般都是RMAN，并给予recovery_catalog_owner角色权限
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
然后，用这个用户登录，创建恢复目录
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最后，你可以在恢复目录注册目标数据库了
rman catalog rman/rman target backdba/backdba
RMAN> register database;
[Q]怎么样在恢复的时候移动数据文件，恢复到别的地点
[A]给一个RMAN的例子
run {
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf'
to '/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf'
to '/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf'
to '/u02/oracle/prod/tmp1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}
[Q]怎么从备份片（backuppiece）中恢复(restore)控制文件与数据文件
[A]可以使用如下方法，在RMAN中恢复备份片的控制文件
restore controlfile from backuppiecefile;
如果是9i的自动备份，可以采用如下的方法
restore controlfile from autobackup;
但是，如果控制文件全部丢失，需要指定DBID，如SET DBID=?
自动备份控制文件的默认格式是%F，这个格式的形式为
c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII就是DBID
至于恢复(restore)数据文件，oracle 816开始有个包dbms_backup_restore
在 nomount 状态下就可以执行，可以读 815甚至之前的备份片，读出来的文件用于恢复
可以在SQLPLUS中运行，如下
SQL>startup nomount
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.deviceallocate('', params=>'');
6 dbms_backup_restore.restoresetdatafile;
7 dbms_backup_restore.restorecontrolfileto('E:Oracleoradatapennycontrol01.ctl');
8 dbms_backup_restore.restoreDataFileto(1,'E:Oracleoradatapennysystem01.dbf');
9 dbms_backup_restore.restoreDataFileto(2,'E:OracleoradatapennyUNDOTBS01.DBF');
10 dbms_backup_restore.restoreDataFileto(3,'E:ORACLEORADATAPENNYUSERS01.DBF');
11 dbms_backup_restore.restorebackuppiece('D:orabakBACKUP_1_4_04F4IAJT.PENNY',done=>done);
12 END;
13 /
PL/SQL 过程已成功完成。
SQL> alter database mount;
[Q]Rman的format格式中的%s类似的东西代表什么意义
[A]可以参考如下
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为日期，QQ是一个1-256的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)
[Q]执行exec dbms_logmnr_d.build('Logminer.ora','file directory')，提示下标超界，怎么办
[A]完整错误信息如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory')
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END;
*
ERROR 位于第 1 行:
ORA-06532: 下标超出限制
ORA-06512: 在"SYS.DBMS_LOGMNR_D", line 793
ORA-06512: 在line 1
解决办法为：
1.编辑位于"$ORACLE_HOME/rdbms/admin"目录下的文件"dbmslmd.sql"
改变行:
TYPE col_desc_array IS VARRAY(513) OF col_description;
为
TYPE col_desc_array IS VARRAY(700) OF col_description;
并保存文件
2. 运行改变后的脚本
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
3.重新编译该包
SQLPLUS> alter package DBMS_LOGMNR_D compile body;
[Q]执行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:无效的月份，这个是什么原因
[A]我们分析start_logmnr包
PROCEDURE start_logmnr(
startScn IN NUMBER default 0 ,
endScn IN NUMBER default 0,
startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'),
endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'),
DictFileName IN VARCHAR2 default '',
Options IN BINARY_INTEGER default 0 );
可以知道，如果TO_DATE('01-jan-1988','DD-MON-YYYY')失败，将导致以上错误
所以解决办法可以为
1、Alter session set NLS_LANGUAGE=American
2、用类似如下的方法执行
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:temp2TESTDICT.ora', starttime => TO_DATE(
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));

二部分、ORACLE构架体系
[Q]ORACLE的有那些数据类型
[A]常见的数据类型有
CHAR固定长度字符域，最大长度可达2000个字节
NCHAR多字节字符集的固定长度字符域，长度随字符集而定，最多为2000个字符或2000个字节
VARCHAR2可变长度字符域，最大长度可达4000个字符
NVARCHAR2多字节字符集的可变长度字符域，长度随字符集而定，最多为4000个字符或4000个字节
DATE用于存储全部日期的固定长度(7个字节)字符域，时间作为日期的一部分存储其中。除非
通过设置init.ora文件的NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以
DD-MON-YY格式表示，如13-APR-99表示1999.4.13
NUMBER可变长度数值列，允许值为0、正数和负数。NUMBER值通常以4个字节或更少的字节存储，最多21字节
LONG可变长度字符域，最大长度可到2GB
RAW表示二进制数据的可变长度字符域，最长为2000个字节
LONGRAW表示二进制数据的可变长度字符域，最长为2GB
MLSLABEL只用于TrustedOracle，这个数据类型每行使用2至5个字节
BLOB二进制大对象，最大长度为4GB
CLOB字符大对象，最大长度为4GB
NCLOB多字节字符集的CLOB数据类型，最大长度为4GB
BFILE外部二进制文件，大小由操作系统决定
ROWID表示RowID的二进制数据，Oracle8RowID的数值为10个字节，在Oracle7中使用的限定
RowID格式为6个字节
UROWID用于数据寻址的二进制数据，最大长度为4000个字节
[Q]Oracle有哪些常见关键字，不能被用于对象名
[A]以8i版本为例，一般保留关键字不能用做对象名
ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH
详细信息可以查看v$reserved_words视图
[Q]怎么查看数据库版本
[A]select * from v$version
包含版本信息，核心版本信息，位数信息(32位或64位)等
至于位数信息，在linux/unix平台上，可以通过file查看，如
file $ORACLE_HOME/bin/oracle
[Q]怎么查看数据库参数
[A]show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
或者select * from v$parameter
除了这部分参数，Oracle还有大量隐含参数，可以通过如下语句查看:
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME
[Q]怎么样查看数据库字符集
[A]数据库服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。
客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，
表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件
字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。
[Q]怎么样修改字符集
[A]8i以上版本可以通过alter database来修改字符集，但也只限于子集到超集，不建议修改props$表，将可能导致严重错误。
Startup nomount;
Alter database mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process=0;
Alter database open;
Alter database character set zhs16gbk;
[Q]怎样建立基于函数索引
[A]8i以上版本，确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
[Q]怎么样移动表或表分区
[A]移动表的语法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的语法
alter table tablename move (partition partname)
[update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);
[Q]怎么获得当前的SCN
[A]9i以下版本
select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;
如果是9i以上版本，还可以通过以下语句获取
select dbms_flashback.get_system_change_number from dual;
[Q]ROWID的结构与组成
[A]8以上版本的ROWID组成
OOOOOOFFFBBBBBBRRR
8以下ROWID组成（也叫受限Rowid）
BBBBBBBB.RRRR.FFFF
其中，O是对象ID，F是文件ID，B是块ID，R是行ID
如果我们查询一个表的ROWID，根据其中块的信息，可以知道该表确切占用了多少个块，进而知道占用了多少数据空间（此数据空间不等于表的分配空间）
[Q]怎么样获取对象的DDL语句
[A]第三方工具就不说了主要说一下9i以上版本的dbms_metadata
1、获得单个对象的DDL语句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLENAME','SCAME') from dual;
如果获取整个用户的脚本，可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然，如果是索引，则需要修改相关table到index
[Q]如何创建约束的索引在别的表空间上
[A]1、先创建索引，再创建约束
2、利用如下语句创建
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;
[Q]怎么知道那些表没有建立主键
[A]一般的情况下，表的主键是必要的，没有主键的表可以说是不符合设计规范的。
SELECT table_name
FROM User_tables t
WHERE NOT EXISTS
(SELECT table_name
FROM User_constraints c
WHERE constraint_type = 'P'
AND t.table_name=c.table_name)
其它相关数据字典解释
user_tables 表
user_tab_columns 表的列
user_constraints 约束
user_cons_columns 约束与列的关系
user_indexes 索引
user_ind_columns 索引与列的关系
[Q]dbms_output提示缓冲区不够，怎么增加
[A]dbms_output.enable(20000);
另外，如果dbms_output的信息不能显示，
需要设置
set serveroutput on
[Q]怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS
[Q]怎么样给sqlplus安装帮助
[A]SQLPLUS的帮助必须手工安装，shell脚本为$ORACLE_HOME/bin/helpins
在安装之前，必须先设置SYSTEM_PASS环境变量，如：
$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins
如果不设置该环境变量，将在运行脚本的时候提示输入环境变量
当然，除了shell脚本，还可以利用sql脚本安装，那就不用设置环境变量了，但是，我们必须以system登录。
$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安装之后，你就可以象如下的方法使用帮助了
SQL> help index
[Q]怎么样快速下载Oracle补丁
[A]我们先获得下载服务器地址，在http页面上有
ftp://updates.oracle.com
然后用ftp登录，用户名与密码是metalink的用户名与密码
如我们知道了补丁号3095277 (9204的补丁集)，则
ftp> cd 3095277
250 Changed directory OK.
ftp> ls
200 PORT command OK.
150 Opening data connection for file listing.
p3095277_9204_AIX64-5L.zip
p3095277_9204_AIX64.zip
……
p3095277_9204_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.
ftp>
知道了这个信息，我们用用flashget，网络蚂蚁就可以下载了。
添加如下连接
ftp://updates.oracle.com/3095277/p3...04_AIX64-5L.zip
或替换后面的部分为所需要的内容
注意，如果是flashget，网络蚂蚁请输入认证用户名及密码，就是你的metalink的用户名与密码！
[Q]如何移动数据文件
[A]1、关闭数据库，利用os拷贝
a.shutdown immediate关闭数据库
b.在os下拷贝数据文件到新的地点
c.Startup mount 启动到mount下
d.Alter database rename datafile '老文件' to '新文件';
e.Alter database open; 打开数据库
2、利用Rman联机操作
RMAN> sql "alter database datafile ''file name'' offline";
RMAN> run {
2> copy datafile 'old file location'
3> to 'new file location';
4> switch datafile ' old file location'
5> to datafilecopy ' new file location';
6> }
RMAN> sql "alter database datafile ''file name'' online";
说明：利用OS拷贝也可以联机操作，不关闭数据库，与rman的步骤一样，利用rman与利用os拷贝的原理一样，在rman中copy是拷贝数据文件，相当于OS的cp，而switch则相当于alter database rename，用来更新控制文件。
[Q]如果管理联机日志组与成员
[A]以下是常见操作，如果在OPA/RAC下注意线程号
增加一个日志文件组
Alter database add logfile [group n] '文件全名' size 10M;
在这个组上增加一个成员
Alter database add logfile member '文件全名' to group n;
在这个组上删除一个日志成员
Alter database drop logfile member '文件全名';
删除整个日志组
Alter database drop logfile group n;
[Q]怎么样计算REDO BLOCK的大小
[A]计算方法为(redo size + redo wastage) / redo blocks written + 16
具体见如下例子
SQL> select name ,value from v$sysstat where name like '%redo%';
NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 2
redo synch time 0
redo entries 76
redo size 19412
redo buffer allocation retries 0
redo wastage 5884
redo writer latching time 0
redo writes 22
redo blocks written 51
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;
Redo black(byte)
------------------
512
[Q]控制文件包含哪些基本内容
[A]控制文件主要包含如下条目，可以通过dump控制文件内容看到
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
[Q]如果发现表中有坏块，如何检索其它未坏的数据
[A]首先需要找到坏块的ID（可以运行dbverify实现），假设为，假定文件编码为。运行下面的查询查找段名：
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id =
AND between block_id and (block_id + blocks - 1)
一旦找到坏段名称，若段是一个表，则最好建立一个临时表，存放好的数据。若段是索引，则删除它，再重建。
create table good_table
as
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) = )
在这里要注意8以前的受限ROWID与现在ROWID的差别。
还可以使用诊断事件10231
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
创建一个临时表good_table的表中除坏块的数据都检索出来
SQL>CREATE TABLE good_table as select * from bad_table;
最后关闭诊断事件
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';
关于ROWID的结构，还可以参考dbms_rowid.rowid_create函数。
[Q]我创建了数据库的所有用户，我可以删除这些用户吗
[A]ORACLE数据库创建的时候，创建了一系列默认的用户和表空间，以下是他们的列表
·SYS/CHANGE_ON_INSTALL or INTERNAL
系统用户，数据字典所有者，超级权限所有者(SYSDBA)
创建脚本：?/rdbms/admin/sql.bsq and various cat*.sql
建议创建后立即修改密码
此用户不能被删除
·SYSTEM/MANAGER
数据库默认管理用户，拥有DBA角色权限
创建脚本：?/rdbms/admin/sql.bsq
建议创建后立即修改密码
此用户不能被删除
·OUTLN/OUTLN
优化计划的存储大纲用户
创建脚本：?/rdbms/admin/sql.bsq
建议创建后立即修改密码
此用户不能被删除
---------------------------------------------------
·SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
实验、测试用户，含有例表EMP与DEPT
创建脚本：?/rdbms/admin/utlsampl.sql
可以修改密码
用户可以被删除，在产品环境建议删除或锁定
·HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
实验、测试用户，含有例表EMPLOYEES与DEPARTMENTS
创建脚本：?/demo/schema/mksample.sql
可以修改密码
用户可以被删除，在产品环境建议删除或锁定
·DBSNMP/DBSNMP
Oracle Intelligent agent
创建脚本：?/rdbms/admin/catsnmp.sql, called from catalog.sql
可以改变密码--需要放置新密码到snmp_rw.ora文件
如果不需要Intelligent Agents，可以删除
---------------------------------------------------
以下用户都是可选安装用户,如果不需要，就不需要安装
·CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge)管理用户
创建脚本：?/ctx/admin/dr0csys.sql
·TRACESVR/TRACE
Oracle Trace server
创建脚本：?/rdbms/admin/otrcsvr.sql
·ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
创建脚本：?/ord/admin/ordinst.sql
·ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
创建脚本：?/ord/admin/ordinst.sql
·DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
创建脚本：?/ds/sql/dssys_init.sql
·MDSYS/MDSYS
Oracle Spatial administrator user
创建脚本：?/ord/admin/ordinst.sql
·AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
创建脚本：?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
·PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
创建脚本：?/rdbms/admin/statscre.sql

一部分、SQL&PL/SQL

[Q]怎么样查询特殊字符，如通配符%与_
[A]select * from table where name like 'A_%' escape ''
[Q]如何插入单引号到数据库表中
[A]可以用ASCII码处理，其它特殊字符如&也一样，如
insert into t values('i'||chr(39)||'m'); -- chr(39)代表字符'
或者用两个单引号表示一个
or insert into t values('I''m'); -- 两个''可以表示一个'
[Q]怎样设置事务一致性
[A]set transaction [isolation level] read committed; 默认语句级一致性
set transaction [isolation level] serializable;
read only; 事务级一致性
[Q]怎么样利用游标更新数据
[A]cursor c1 is
select * from tablename
where name is null for update [of column]
……
update tablename set column = ……
where current of c1;
[Q]怎样自定义异常
[A] pragma_exception_init(exception_name,error_number);
如果立即抛出异常
raise_application_error(error_number,error_msg,true|false);
其中number从-20000到-20999，错误信息最大2048B
异常变量
SQLCODE 错误代码
SQLERRM 错误信息
[Q]十进制与十六进制的转换
[A]8i以上版本：
to_char(100,'XX')
to_number('4D','XX')
8i以下的进制之间的转换参考如下脚本
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) p_dec OR p_dec 20;
[Q]怎么样抽取重复记录
[A]select * from table t1 where where t1.rowed !=
(select max(rowed) from table t2
where t1.id=t2.id and t1.name=t2.name)
或者
select count(*), t.col_a,t.col_b from table t
group by col_a,col_b
having count(*)>1
如果想删除重复记录，可以把第一个语句的select替换为delete
[Q]怎么样设置自治事务
[A]8i以上版本，不影响主事务
pragma autonomous_transaction;
……
commit|rollback;
[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
如：dbms_lock.sleep(5);表示暂停5秒。
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
BEGIN
start_time := dbms_utility.get_time;
SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
--transaction start
INSERT INTO t1
SELECT * FROM All_Objects;
--other dml statement
COMMIT;
end_time := dbms_utility.get_time;
SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;
[Q]怎样创建临时表
[A]8i以上版本
create global temporary tablename(column list)
on commit preserve rows; --提交保留数据 会话临时表
on commit delete rows; --提交删除数据 事务临时表
临时表是相对于会话的，别的会话看不到该会话的数据。
[Q]怎么样在PL/SQL中执行DDL语句
[A]1、8i以下版本dbms_sql包
2、8i以上版本还可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');
[Q]怎么样获取IP地址
[A]服务器(817以上)：utl_inaddr.get_host_address
客户端：sys_context('userenv','ip_address')
[Q]怎么样加密存储过程
[A]用wrap命令，如（假定你的存储过程保存为a.sql）
wrap iname=a.sql
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
[Q]怎么样在ORACLE中定时运行存储过程
[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
之后，就可以用以下语句查询已经提交的作业
select * from user_jobs;
[Q]怎么样从数据库中获得毫秒
[A]9i以上版本，有一个timestamp类型获得毫秒，如
SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,
to_char(current_timestamp) time2 from dual;
TIME1 TIME2
----------------------------- ----------------------------------------------------------------
2003-10-24 10:48:45.656000 24-OCT-03 10.48.45.656000 AM +08:00
可以看到，毫秒在to_char中对应的是FF。
8i以上版本可以创建一个如下的java函数
SQL>create or replace and compile
java source
named "MyTimestamp"
as
import java.lang.String;
import java.sql.Timestamp;
public class MyTimestamp
{
public static String getTimestamp()
{
return(new Timestamp(System.currentTimeMillis())).toString();
}
};
SQL>java created.
注：注意java的语法，注意大小写
SQL>create or replace function my_timestamp return varchar2
as language java
name 'MyTimestamp.getTimestamp() return java.lang.String';
/
SQL>function created.
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;
MY_TIMESTAMP ORACLE_TIME
------------------------ -------------------
2003-03-17 19:15:59.688 2003-03-17 19:15:59
如果只想获得1/100秒(hsecs)，还可以利用dbms_utility.get_time
[Q]如果存在就更新，不存在就插入可以用一个语句实现吗
[A]9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select …… from dual的子查询。
语法为：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
如
MERGE INTO course c
USING (SELECT course_name, period,
course_hours
FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,
c.course_hours)
VALUES (cu.course_name, cu.period,
cu.course_hours);
[Q]怎么实现左联，右联与外联
[A]在9i以前可以这么写:
左联：
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上，已经开始支持SQL99标准，所以，以上语句可以写成：
默认内部联结：
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
[Q]怎么实现一条记录根据条件多表插入
[A]9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
如果没有条件的话，则完成每个表的插入，如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
[Q]如何实现行列转换
[A]1、固定列数的行列转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
2、不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
[Q]怎么样实现分组取前N条记录
[A]8i以上版本，利用分析函数
如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn host lsntctl start
或者unix/linux平台下
SQL>!
windows平台下
SQL>$
总结：HOST 可以直接执行OS命令。
备注：cd命令无法正确执行。
[Q]怎么设置存储过程的调用者权限
[A]普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;
[Q]怎么快速获得用户下每个表或表分区的记录数
[A]可以分析该用户，然后查询user_tables字典，或者采用如下脚本即可
SET SERVEROUTPUT ON SIZE 20000
DECLARE
miCount INTEGER;
BEGIN
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'
INTO miCount;
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
END LOOP;
END IF;
END LOOP;
END;
[A]怎么在Oracle中发邮件
[Q]可以利用utl_smtp包发邮件，以下是一个发送简单邮件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
desc: ·发送邮件到指定邮箱
·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;
BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail;
[A]怎么样在Oracle中写操作系统文件，如写日志
[Q]可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数
/**************************************************************************
parameter:textContext in varchar2 日志内容
desc: ·写日志,把内容记到服务器指定目录下
·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;

根据rowid dump数据块，可以用下面的方法实现。

SQL> SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) R_FILE_NO,
  2  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO
  3  FROM T WHERE ROWNUM = 1;

 R_FILE_NO   BLOCK_NO
---------- ----------
         6        578

在很多時候我們需要通過bind var來提高整個DB的performance,在我們用第三次開發軟件做對結果集的查詢。我們如何在procedure中完成對結果集的查詢呢，從oracle7.3才被支持，在9i以后又有新的變化，在9i以前要define一個type才可以。而在9i以后oracle引入了一個新的類型為sys_refcursor，這樣就不需要我們重新定義。我們來看一個例子吧。

C:oracleora92sqlplusdemo>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 4月 2 11:09:06 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn scott/tiger@vongates
已連線.
SQL> create or replace procedure getEmpByDept(in_deptNo  in emp.deptno%type,
  2                                           out_curEmp out SYS_REFCURSOR) as
  3
  4  begin
  5    open out_curEmp for
  6      SELECT * FROM emp WHERE deptno = in_deptNo ;
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      RAISE_APPLICATION_ERROR(-20101,
 10                              'Error in getEmpByDept' || SQLCODE );
 12  end getEmpByDept;
 13  /

已建立程序.

SQL> var rset refcursor;
SQL> exec getEmpByDept(10,:rset);

PL/SQL 程序順利完成.

SQL> print rset;

     EMPNO ENAME                JOB                       MGR HIREDATE      SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7934 MILLER               CLERK                    7782 23-1月 -82      1300                    10
      7782 CLARK                MANAGER                  7839 09-1月 -81      2450                    10
      7839 KING                 PRESIDENT                     17-11月-81      5000                    10

SQL>

可以用以下几点对high-water mark进行理解。

1.指一个表中曾经被用过的最后一个块
2.如果有数据被插入表，high-water mark 就移到到被使用的最后一个块。
3.如果有数据被删除，high-water mark的位置不会变。
4.high-water mark被储存在表的段头(segment header of the table)。
5.当对表执行全表扫描时，oracle server 被所有的块直到high-water mark。

这样看来high-wate mark就是用来标识最后一个被用过的block的

另外我的理解如下：

1 如果为表分配了大量的extents , 但这些extent 还没使用可以手工收回。并且有如下两种情况
第一种 minextent < hwm可以使用
alter table tablename deallocate unused;
将hwm以上所有没使用的空间释放
第二种 minextent >hwm 则释放minextents 以上的空间。
如果要释放hwm以上的空间则使用keep 0。
2 truncate 将minextent 之上的空间释放。
3 只是将hwm移动，释放的空间不会被其他段使用

1 如何修改statspack的脚本产生自定义报表？
2 如何用statspack的报表确定热表及索引？
3 如何用statspack的报表确定keep池与default池的分配？
4 如何用crontab定期产生statspack的报表？

使用statspack有相当长的时间啦,从最初的推崇,到后来的否定,
再到现在的肯定,发现它已经是我工作中不可缺少的一部分,
每晚上读当天产生的statspack报表就成了一种习惯,
越是读得认真,越是觉得statspack妙用无穷,
现在本人就将一些心得告诉大家,希望对你有所帮助。

一 如何修改statspack的脚本产生自定义报表？
通常statspack报表可以满足大部分的需要,有时我们需要对产生报表的脚本
进行一些微小的修改,这样产生的报表就更有用途啦.
比如说某些SQL很多,但在statspack产生的报表中,每个SQL只显示5行,
结果有些比较长的SQL就只能看到一部分;
又如在top events部分,标准的报表只显示top 5,
其实我们可以显示更多的events,那如何修改呢？
用编辑工具(在linux下用vi)打开$ORACLE_HOME/rdbms/admin/sprepins.sql
define top_n_events = 5;      // top 5 events
define top_n_sql = 65;        // top sql
define top_n_segstat = 5;     // top 5 segstat
define num_rows_per_hash=5;   // 每个SQL显示5行
就看到在该脚本中已经定义了一些常数,我们只需要把它改为我们需要的值就可以啦.
define top_n_events = 10;     // top 10 events    
define top_n_sql = 65;    // top sql         
define top_n_segstat = 10;  // top 10 segstat   
define num_rows_per_hash=10; // 每个SQL显示10行  
做过修改后,然后大家就可以看到自己要的效果啦.

二 如何用statspack的报表确定热表及索引？
如果想用statspack表确定热表及索引,必须修改statspack快照的收集级别,
8i中statspack共有三种快照级别,默认值是5
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- ----------------------------------------------------------------
         0 一性性能统计:包含回退段状态、字典缓存、SGA、系统事件、后台事件、会话事件、
      系统统计、等待统计、锁统计、闩锁统计
         5 增加了收集SQL的信息、并包括0级收集的信息.
        10 增加了收集子闩锁的信息,并包括所有低级别的信息
在9i中statspack共有五种快照级别,默认值是5
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- ----------------------------------------------------------------
         0 一性性能统计:包含回退段状态、字典缓存、SGA、系统事件、后台事件、会话事件、
      系统统计、等待统计、锁统计、闩锁统计
         5 增加了收集SQL的信息、并包括0级收集的信息.
         6 增强了在SQL收集信息方面的功能(列出占用资源较高的SQL),并包所有低级别的信息
         7 增加了收集段级别的统计信息(如段的逻辑读与物理读、行锁、ITL及buffer busy waits),
      并包括所有低级别的信息
        10 增加了收集子闩锁的信息,并包括所有低级别的信息
如果你收用statspack确定热表及热索引,那就需要使用7/10的级别来收集快照。
//通过这样的设置，以后的收集级别都将是7级。
//如果你只是想本次改变收集级别，可以忽略i_modify_parameter参数。
SQL>execute statspack.snap(i_snap_level=>7,i_modify_parameter=>true);
SQL>execute statspack.snap(i_snap_level=>7);
修改完收集级别后,那大家就可以根据自己的需要设定收集的频率,
现在我们只需要注意statspack报表中的“段级别的统计信息”:
Top 5 Logical Reads per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Logical Reads Threshold:     10000
                                           Subobject  Obj.       Logical
Owner      Tablespace Object Name          Name       Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA AGENT_CARD_TYPE                 TABLE  115,220,864   18.07
CYBERCAFE  TS_CYBERCA GAME_CARD_TYPE                  TABLE   79,103,600   12.40
CYBERCAFE  TS_CYBERCA AGENT_TASK                      TABLE   57,030,304    8.94
CYBERCAFE  TS_CYBERCA AGENT_PRICE_LEVEL_OW            TABLE   46,393,968    7.28
CYBERCAFE  TS_CYBERCA IDX_ASL_RESLOG_ID               INDEX   23,261,600    3.65
-------------------------------------------------------------
Top 5 Physical Reads per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Physical Reads Threshold:    1000
                                           Subobject  Obj.      Physical
Owner      Tablespace Object Name          Name       Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA AGENT_GAME_CARD_GM13            TABLE       76,476    7.36
CYBERCAFE  TS_CYBERCA AGENT_SALE_LOG       ASL_200500 TABLE       61,270    5.89
CYBERCAFE  TS_CYBERCA RESELLER_LOG         RL_200412  TABLE       48,950    4.71
CYBERCAFE  TS_CYBERCA AGENT_GAME_CARD_GM14            TABLE       46,259    4.45
CYBERCAFE  TS_CYBERCA AGENT_CAPITAL_LOG    ACL_200500 TABLE       45,476    4.37
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Buffer Busy Waits Threshold:     100
                                           Subobject  Obj.   Buffer Busy
Owner      Tablespace Object Name          Name       Type         Waits  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA AGENT_TASK                      TABLE           22   36.07
CYBERCAFE  TS_CYBERCA AGENT_CARD_TYPE                 TABLE            9   14.75
CYBERCAFE  TS_CYBERCA IDX_RESACC_UPDTIME              INDEX            5    8.20
CYBERCAFE  TS_CYBERCA AGENT_SALE_LOG       ASL_200501 TABLE            4    6.56
CYBERCAFE  TS_CYBERCA IDX_ACL_ACPITAL_LOGI            INDEX            4    6.56
-------------------------------------------------------------
Top 5 Row Lock Waits per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Row Lock Waits Threshold:     100
                                           Subobject  Obj.      Row Lock
Owner      Tablespace Object Name          Name       Type         Waits  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA IDX_APL_GCTID2       APL_200501 INDEX           54   24.77
CYBERCAFE  TS_CYBERCA IDX_RB_RESELLER_ID              INDEX           41   18.81
CYBERCAFE  TS_CYBERCA IDX_RL_RESLOG_ID                INDEX           38   17.43
CYBERCAFE  TS_CYBERCA IDX_ACT_ACT_ID                  INDEX           17    7.80
CYBERCAFE  TS_CYBERCA IDX_SERVICE_ID                  INDEX           14    6.42
-------------------------------------------------------------
在这里可以看到逻辑读/物理读/缓存忙/行锁符合条件的一些对象,通过这些对象,
可以确定热的表及索引,然后分析如何对业务进行优化,降低对这些表的访问量等。
如果你觉得显示top 5 segment不够的话,可以按一所述修改top_n_segstat,
然后就可以显示更多的符合条件的对象,然后将这些热表放到keep池中。

三 如何用statspack的报表确定keep池与default池的分配？
如果你想使用default池与keep池,在9i中需要分配db_cache_size及
db_keep_cache_size参数,但如何确定它们的大小呢？我们可以根据2所示的一些
热表,计算热表放入keep池需要的内存,然后用将表放入相应的pool中.
alter table &table_name storage(buffer_pool &buffer_pool);
将确定的热表放入keep中之后,然后收集一段时间后再产生一个新的报表:
Buffer Pool Statistics for DB: ESAL  Instance: esal  Snaps: 2277 -2289
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
                                                           Free    Write  Buffer
     Number of Cache      Buffer    Physical   Physical  Buffer Complete    Busy
P      Buffers Hit %        Gets       Reads     Writes   Waits    Waits   Waits
--- ---------- ----- ----------- ----------- ---------- ------- --------  ------
D      128,128  99.7 482,298,597   1,557,980    265,662       0        0      88
K       32,032 100.0 372,560,023      13,951     42,405       0        0      17
-------------------------------------------------------------
确定keep池与default的需要内存时,可以根据这一部分对keep池与default池的大小进行评估,
如果K所标识的cache hit%比较小,说明keep池不足,如果D显示的cache hit%比较小,说明default池
分配怀足,如果K是的default显示是100%,那们可以将更多的热表放入到keep池中，
然后经过一段时间的调整,相信可以将default池与keep池调到一个相对比较合适的集团。

四 如何用crontab定期产生statspack的报表？
看了一段时间的statspack报表后,就懒于每天手工去产生一个报表,那如何产系统自动产生一个报表呢?
经过测试,用crontab可以方便地产生报表,然后通过sendmail直接发到相关人员的邮箱中.
[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
 from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
 from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!

BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!

mail -s "perfstat report" ddd@eee.fff < /home/oracle/sql/report/sp`date +%m%d`_ac.lst

[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >> /home/oracle/sql/backup/perf.lst 2>&1

说明:从早上8点到晚上8点之间进行快照收集,9点执行cron进程启动,产生报表的快照也限于当天收集的快照,
将当天最小的snap_id与最大的snap_id放到两个文件中,在sheel中读出,并计算出一个报表名称,
最后产生的报表通过sendmail发送到相关人员的邮箱,然后每天晚只需要收邮件就可以看到当天的报表啦。

接statspack report 分析未完待续!

Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间+解析中等待资源时间)
越高越好
% Non-Parse CPU:查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过多。100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd %

如果一个经常访问的列上的索引被删除，可能会造成buffer hit 显著的下降
如果增加了索引，但是他影响了ORACLE正确的选择表连接时的驱动顺序，那么可能会导致buffer hit 显 著增高
如果你的命中率变化幅度很大，说明你要改变SQL模式

Shared Pool相关统计数据
Memory Usage %:共享池内存使用率，应该稳定在70%-90%间，太小浪费内存，太大则内存不足。
% SQL with executions>1:执行次数大于1的sql比率，若太小可能是没有使用bind variables。
% Memory for SQL w/exec>1:也即是memory for sql with execution > 1:执行次数大于1的sql
消耗内存/所有sql消耗的内存

4、首要等待事件
常见等待事件说明:
oracle等待事件是衡量oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件,  TIMED_STATISTICS = TRUE 那么等待事件按等待的时间排序= FALSE那么事件按等待的数量排序.运行statspack期间必须session上设置TIMED_STATISTICS = TRUE.
空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，
非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。

比较影响性能常见等待事件
db file scattered read
    该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，
通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。该指数的数量过大说明缺少索引或者限制了索引的使用(也可以调整optimizer_index_cost_adj) 。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。如果经常必须进行全表扫描,而且表比较小, 把该表存人keep池.如果是大表经常进行全表扫描,那么应该是olap系统,而不是oltp的.
db file sequential read
   该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整, DB_CACHE_SIZE可以决定该事件出现的频率
buffer busy wait
    当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待.该值不应该大于1%,确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
latch free
   常跟应用没有很好的应用绑定有关. 闩锁是底层的队列机制(更加准确的名称应该是互斥机制)，用于保护系统全局区(SGA)共享内存结构闩锁用于防止对内存结构的并行访问。如果闩锁不可用，就会记录一次闩锁丢失。绝大多数得闩锁问题都与使用绑定变量失败(库缓存闩锁)、生成重作问题(重执行分配闩锁)、缓存的争用问题(缓存LRU链) 以及缓存的热数据宽块(缓存链)有关。当闩锁丢失率高于0.5%时，需要调整这个问题。
log buffer space
    日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小，增加日志缓冲区的大小，或者使用更快的磁盘来写数据。
logfile switch
    通常是因为归档速度不够快,需要增大重做日志
log file sync
    当一个用户提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进程必须等待这个填充工作完成。在每次提交时都出现，如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率, 为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件访在不同的物理磁盘上。

Enqueue   最有可能是多个用户同时修改同一个块,如果没有空闲的ITL空间,就会出现数据库块级锁.

TOP SQL
调整首要的25个缓冲区读操作和首要的25个磁盘读操作做的查询,将可对系统性能产生5%到5000%的增益.   

Instance Activity Stats for DB: CRMTEMP  Instance: crmtemp  Snaps: 3 -11            
                                                                                       
Statistic                      Total     per Second    per Trans                          
--------------------------------- ------------------ -------------- ------------     
CPU used by this session  291,318           98.1         13.0     
CPU used when call started  291,318       98.1         13.0     
CR blocks created              1,784            0.6           0.1     
Cached Commit SCN referenced 0            0.0           0.0     
Commit SCN cached                 0            0.0           0.0     
DBWR buffers scanned     985,112          331.6   44.0                                                  
DBWR checkpoint buffers written    948  0.3          0.0                                                     
DBWR checkpoints                  0            0.0          0.0                                                     
dirty buffers inspected             483        0.2            0.0    --脏缓冲的个数

free buffer inspected            8,154        2.7            0.4    --如果数量很大,说明缓冲区过小
sorts (disk)                            0            0.0            0.0    --不应当大于1-5%
sorts (memory)                  15,365       5.2          0.7                                
sorts (rows)                  1,445,018      823.0        109.2                              
summed dirty queue length  24,667     8.3          1.1  
..

 

 

最近再研究statspack,粗略地整理了一下!

等以后有时间再做详细的整理!

供需要的人参考!

最后谢谢eygle的指点!

一、statspack 输出结果中必须查看的十项内容

1、负载间档(Load profile)
2、实例效率点击率(Instance efficiency hit ratios)
3、首要的5个等待事件(Top 5 wait events)
4、等待事件(Wait events)
5、闩锁等待
6、首要的SQL(Top sql)
7、实例活动(Instance activity)
8、文件I/O(File I/O)
9、内存分配(Memory allocation)
10、缓冲区等待(Buffer waits)

二、输出结果解释

1、报表头信息
数据库实例相关信息，包括数据库名称、ID、版本号及主机等信息

2、负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分

说明:
Redo size:每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否
Logical reads:平决每秒产生的逻辑读，单位是block
block changes:每秒block变化数量，数据库事物带来改变的块数量
Physical reads:平均每秒数据库从磁盘读取的block数
Physical writes:平均每秒数据库写磁盘的block数
User calls:每秒用户call次数
Parses: 每秒解析次数，近似反应每秒语句的执行次数, 软解析每秒超过300次意味着你的"应  用程序"效率不高，没有使用soft soft parse，调整session_cursor_cache
Hard parses:每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好
Sorts:每秒产生的排序次数
Executes:每秒执行次数
Transactions:每秒产生的事务数，反映数据库任务繁重与否
Recursive Call %: 如果有很多PLSQL，那么他就会比较高

Rollback per transaction %:看回滚率是不是很高，因为回滚很耗资源

如果回滚率过高，可能说明你的数据库经历了太多的无效操作

过多的回滚可能还会带来Undo Block的竞争 该参数计算公式如下:

Round(User rollbacks / (user commits + user rollbacks) ,4)* 100%

3、实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要

说明:
Buffer Nowait %:在缓冲区中获取Buffer的未等待比率, Buffer Nowait<99%说明，有可能是有热,   块(查找x$bh的 tch和v$latch_children的cache buffers chains)  
Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率
Buffer  Hit %:数据块在数据缓冲区中得命中率，通常应在90%以上，否则，需要调整, 小于                              95%,重要的参数,小于90%可能是要加db_cache_size,但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）
In-memory Sort %:在内存中的排序率
Library Hit %:主要代表sql在共享区的命中率，通常在95%以上，否，需要要考虑加
               大共享池，绑定变量，修改cursor_sharing等参数。
Soft Parse %:近似看作sql在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，
              那么就可能sql基本没有被重用
Execute to Parse %:sql语句解析后被重复执行的次数，如果过低，可以考虑设置
                   session_cached_cursors参数, 公式为100 * (1 - Parses/Executions)       = Execute to Parse所以如果系统Parses > Executions,就可能出现该比率小于0的情况, 该值<0通常说明shared pool设置或效率存在问题造成反复解析，reparse可能较严重,或者可是同snapshot有关如果该值为负值或者极低,通常说明数据库性能存在问题

Latch Hit %: Latch Hit<99%,要确保>99%，否则存在严重的性能问题，比如绑定等会影响该参数
Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间

 

 

 

 

 

      从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别：

    create index idx_skip on emp5(job,empno);
    index created.

    select count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:03.13

    Execution Plan
    0     SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(FAST FULL SCAN) OF 'idx_skip'(NON-UNIQUE)

    Statistics

    6826 consistent gets
    6819 physical   reads

    select /*+ index(emp5 idx_skip)*/ count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:00.56

    Execution Plan
    0     SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(SKIP SCAN) OF 'idx_skip' (NON-UNIQUE)

    Statistics

    21 consistent gets
    17 physical   reads

SELECT d.VALUE || '' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d

os:

1:windows advance server 2000

2:ntfs分区.

也可以导出为多个dmp文件.

http://www.eygle.com/orabk/index-book-6.htm

下载学习中...

 查看全文

什么是绑定变量？

查询通常只是因为改变where子句中的内容而产生不同的结果。为了在这种情况下避免硬解析，需要使用绑定变量(bind variable)。它是用户放入查询中的占位符，它会告诉Oracle"我会随后为这个变量提供一个值，现在需要生成一个方案，但我实际执行语句的时候，我会为您提供应该使用的实际值"。
select * from emp where ename='KING'; //不使用绑定变量
select * from emp where ename=:bv //使用绑定变量

一般在 procedure or function 中使用，可以优化共享池的使用。

當然我們應用catalog來做rman的備份與恢復時就要求我們先要在catalog的DB中注冊我們的目標DB來完成目標DB的控制文件與catalog的同步。如果我們想取消注冊某個目標DB要如何來做呢。。我們來看一個例子。。
Cocuments and SettingsAdministrator>rmanRecovery Manager: 版本 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect catalog rman/rman@catadb

已連線至復原目錄資料庫

RMAN> connect target sys/password@ora9i

已連線到目標資料庫: ORA9I (DBID=2341661612)

RMAN> register database;

在復原目錄註冊的資料庫
啟動復原目錄的完整再同步
完整再同步完成

RMAN> list incarnation;

資料庫拷貝清單
DB 鍵 Inc 鍵 DB 名稱 DB ID CUR 重設 SCN 重設時間
------- ------- -------- ---------------- --- ---------- ----------
1 14 ORA9I 2341661612 NO 190578 12-11月-04
1 2 ORA9I 2341661612 YES 1077697 18-11月-04

RMAN>

SQL> conn sys/password@ora9i as sysdba
已連線.
SQL> select dbid from v$database;

DBID
----------
2341661612

SQL> conn rman/rman@catadb
已連線.
SQL> select db_key from db where db_id=2341661612;

DB_KEY
----------
1

SQL> exec dbms_rcvcat.unregisterdatabase(1,2341661612);

PL/SQL 程序順利完成.

SQL> host;Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

Cocuments and SettingsAdministrator>rman

Recovery Manager: 版本 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect catalog rman/rman@catadb

已連線至復原目錄資料庫

RMAN> connect target sys/password@ora9i

已連線到目標資料庫: ORA9I (DBID=2341661612)

RMAN> list incarnation;

RMAN> register database;

在復原目錄註冊的資料庫
啟動復原目錄的完整再同步
完整再同步完成

RMAN> list incarnation;

資料庫拷貝清單
DB 鍵 Inc 鍵 DB 名稱 DB ID CUR 重設 SCN 重設時間
------- ------- -------- ---------------- --- ---------- ----------
701 714 ORA9I 2341661612 NO 190578 12-11月-04
701 702 ORA9I 2341661612 YES 1077697 18-11月-04

RMAN> exit

完成 Recovery Manager.

Cocuments and SettingsAdministrator>exit

SQL> select db_key from db where db_id=2341661612;

DB_KEY
----------
701

SQL>

Oracle数据库中索引的维护

本文只讨论Oracle中最常见的索引，即是B-tree索引。本文中涉及的数据库版本是Oracle8i。

　　一. 查看系统表中的用户索引

　　在Oracle中，SYSTEM表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。

　　一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYSTEM表损坏了，只能重新生成数据库。我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。

select count(*)
from dba_indexes
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM')
/

　　二. 索引的存储情况检查

　　Oracle为数据库中的所有数据分配逻辑结构空间。数据库空间的单位是数据块（block）、范围（extent）和段（segment）。

　　Oracle数据块（block）是Oracle使用和分配的最小存储单位。它是由数据库建立时设置的DB_BLOCK_SIZE决定的。一旦数据库生成了，数据块的大小不能改变。要想改变只能重新建立数据库。（在Oracle9i中有一些不同，不过这不在本文讨论的范围内。）

　　Extent是由一组连续的block组成的。一个或多个extent组成一个segment。当一个segment中的所有空间被用完时，Oracle为它分配一个新的extent。
　
　　Segment是由一个或多个extent组成的。它包含某表空间中特定逻辑存储结构的所有数据。一个段中的extent可以是不连续的，甚至可以在不同的数据文件中。

　　一个object只能对应于一个逻辑存储的segment，我们通过查看该segment中的extent，可以看出相应object的存储情况。

　　（1）查看索引段中extent的数量：

select segment_name, count(*)
from dba_extents
where segment_type='INDEX'
and owner=UPPER('&owner')
group by segment_name
/

　　（2）查看表空间内的索引的扩展情况：

select
substr(segment_name,1,20) "SEGMENT NAME",
bytes,
count(bytes)
from dba_extents
where segment_name in
( select index_name
from dba_indexes
where tablespace_name=UPPER('&表空间'))
group by segment_name,bytes
order by segment_name
/

　　三. 索引的选择性

　　索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。

　　一个索引的选择性越接近于1，这个索引的效率就越高。

　　如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。

　　确定索引的选择性，可以有两种方法：手工测量和自动测量。

　　（1）手工测量索引的选择性

　　如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：

　　列的选择性=不同值的数目/行的总数 /* 越接近1越好 */

select count(distinct 第一列||'%'||第二列)/count(*)
from 表名
/

　　如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。

　　手工方法的优点是在创建索引前就能评估索引的选择性。

　　（2）自动测量索引的选择性

　　如果分析一个表，也会自动分析所有表的索引。

　　第一，为了确定一个表的确定性，就要分析表。

analyze table 表名
compute statistics
/

　　第二，确定索引里不同关键字的数目：

select distinct_keys
from user_indexes
where table_name='表名'
and index_name='索引名'
/

　　第三，确定表中行的总数：

select num_rows
from user_tables
where table_name='表名'
/

　　第四，索引的选择性=索引里不同关键字的数目/表中行的总数：

select i.distinct_keys/t.num_rows
from
user_indexes i,
user_tables t
where i.table_name='表名'
and i.index_name='索引名'
and i.table_name=t.table_name
/

　　第五，可以查询USER_TAB_COLUMNS以了解每个列的选择性。

　　表中所有行在该列的不同值的数目：

select
column_name,
num_distinct
from user_tab_columns
where table_name='表名'
/

　　列的选择性=NUM_DISTINCT/表中所有行的总数，查询USER_TAB_COLUMNS有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。

　　四. 确定索引的实际碎片

　　随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。

　　（1）利用验证索引命令对索引进行验证。

　　这将有价值的索引信息填入index_stats表。

validate index 用户名.索引名
/

　　（2）查询index_stats表以确定索引中删除的、未填满的叶子行的百分比。

select
name,
del_lf_rows,
lf_rows,
round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent"
from index_stats
/

　　（3）如果索引的叶子行的碎片超过10%，考虑对索引进行重建。

alter index 用户名.索引名 rebuild
tablespace 表空间名
storage(initial 初始值 next 扩展值)
nologging
/

　　（4）如果出于空间或其他考虑，不能重建索引，可以整理索引。

alter index用户名.索引名 coalesce
/

　　（5）清除分析信息

analyze index 用户名.索引名
delete statistics
/

　　五. 重建索引

　　（1）检查需要重建的索引。

　　根据以下几方面进行检查，确定需要重建的索引。

　　第一，查看SYSTEM表空间中的用户索引。

　　为了避免数据字典的碎片出现，要尽量避免在SYSTEM表空间出现用户的表和索引。

select index_name
from dba_indexes
where tablespace_name='SYSTEM'
and owner not in ('SYS','SYSTEM')
/

　　第二，确保用户的表和索引不在同一表空间内。

　　表和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。

set linesize 120
col "OWNER" format a20
col "INDEX" format a30
col "TABLE" format a30
col "TABLESPACE" format a30
select
i.owner "OWNER",
i.index_name "INDEX",
t.table_name "TABLE",
i.tablespace_name "TABLESPACE"
from
dba_indexes i,
dba_tables t
where i.owner=t.owner
and i.table_name=t.table_name
and i.tablespace_name=t.tablespace_name
and i.owner not in ('SYS','SYSTEM')
/

　　第三，查看数据表空间里有哪些索引

　　用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间。

col segment_name format a30
select
owner,
segment_name,
sum(bytes)
from dba_segments
where tablespace_name='数据表空间名'
and segment_type='INDEX'
group by owner,segment_name
/

　　第四，查看哪个索引被扩展了超过10次

　　随着表记录的增加，相应的索引也要增加。如果一个索引的next extent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。

set linesize 100
col owner format a10
col segment_name format a30
col tablespace_name format a30
select
count(*),
owner,
segment_name,
tablespace_name
from dba_extents
where segment_type='INDEX'
and owner not in ('SYS','SYSTEM')
group by owner,segment_name,tablespace_name
having count(*) >10
order by count(*) desc
/

　　（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。

set linesize 120
col "INDEX" format a30
col "TABLESPACE" format a20
select
owner "OWNER",
segment_name "INDEX",
tablespace_name "TABLESPACE",
bytes "BYTES/COUNT",
sum(bytes) "TOTAL BYTES",
round(sum(bytes)/(1024*1024),0) "TOTAL M",
count(bytes) "TOTAL COUNT"
from dba_extents
where segment_type='INDEX'
and segment_name in
(
'索引名1',
'索引名2',
......
)
group by owner,segment_name,segment_type,tablespace_name,bytes
order by owner,segment_name
/

　　（3）确定索引表空间还有足够的剩余空间。

　　确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。

select round(bytes/(1024*1024),2) free(M)
from sm$ts_free
where tablespace_name='表空间名'
/

　　（4）重建索引。

　　重建索引时要注意以下几点：

　　a.如果不指定tablespace名，索引将建在用户的默认表空间。

　　b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。

　　c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。

alter index 索引名
rebuild
tablespace 索引表空间名
storage(initial 初始值 next 扩展值)
nologging
/

　　（5）检查索引。

　　对重建好的索引进行检查。

select *
from dba_extents
where segment_name='索引名'
/

　　（6）根据索引进行查询，检查索引是否有效

　　使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。

select *
from dba_ind_columns
where index_name like '表名%'
/

　　然后，根据相应的索引项进行查询。

select *
from '表名%'
where ......
/

　　（6）找出有碎片的表空间，并收集其碎片。

　　重建索引后，原有的索引被删除，这样会造成表空间的碎片。

select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_free_space_coalesced
where percent_blocks_coalesced!=100
/

　　整理表空间的碎片。

alter tablespace 表空间名 coalesce
/

原文：　

http://www.donews.net/guoran/archive/2005/01/22/251409.aspx

rman大全

1:to back the controlfile using rman
备份控制文件
run{
allocate channel dev1 type disk format 'c:backup%U';
backup current controlfile;
}

2:to check the backup of controlfile using rman:
检查备份的控制文件
list backup of controlfile;

3:to recover using backup controlfile(startup nomount)
恢复控制文件
run {
allocate channel dev1 type disk;
restore controlfile;
alter database mount;
restore database;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS"
}

4:backup all datafile and controlfile using rman:
备份所有的数据文件和控制文件
run {
allocate channel dev1 type disk;
backup full tag = 'full backup' database include current controlfile format = 'c:backupdb_t%t_s%s_p%p';
release channel dev1;
}

5:check all backup of datafiles using rman:
检查所有的数据文件和控制文件
list backupset;

6:to restore because of missing file(first mount the database and run RMAN)
因文件丢失而修复
run {
allocate channel dev1 type disk;
restore database;
recover database;
}

7:restore until time
恢复到某个时间点
The 'SET UNTIL TIME' must match with the variable NLS_DATE_FORMAT.
Prior logging on RMAN set the NLS_DATE_FORMAT in the desired format.
For example:
If unix ===> export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
If on windows nt ===> set this vaiable in the registery.
run {
set until time 'May 1 2000 08:00:00';
allocate channel dev1 type disk;
shutdown abort;
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
sql 'alter database open resetlogs';
}

8:to purge obsolete backups:
清除旧的备份
report obsolete redundancy 3 device type disk; #REPORTS ANY BACKUP WITH MORE THAN 3 COPIES
report obsolete orphan; #USE THIS REPORT TO FILL IN THE XXXXX BELOW

run {
allocate channel for maintenance type disk;
allocate channel for delete type disk;
change backuppiece 'C:BACKUPxxxx' delete;
release channel;

run {
allocate channel for maintenance type disk;
allocate channel for delete type disk;
change datafilecopy 'C:BACKUPxxxx' delete;
release channel;

9:to backup all archive logs
备份所有的归档日志
run{
allocate channel dev1 type disk format 'c:backup%U';
backup archivelog all;
}

10:to remove all archive log files alter backup update to this line

backup archivelog all delete input;

11:skip an archive log file that can not be read or manualy deleted update to this line
backup archivelog skip inaccessible /inaccessible不可存取

12:to remove one archive log that you manualy deleted and now get an rman-6089<=8.0
allocate channel for delete type disk; or 'SBT_TAPE';
change archivelog 'path/filename' delete;
and/or
resync catalog;

13:to remove one archive log that you manualy deleted and now get an rman-6089<=8.1
allocate channel for maintenance type ....'
change archivelog <name> uncatalog

replace script backup_db_full {
#
# Backs up the whole database into backup-sets. This backup is not part
execute script alloc_all_tapes;
execute script set_maxcorrupt;
backup
full
# skip offline
# skip readonly
skip inaccessible
tag b_db_full
filesperset 6
format 'df_t%t_s%s_p%p'
database;
execute script rel_all_tapes;
execute script archive_log_current;
execute script backup_al_all;
}

replace script backup_db_level_0 {
#
# Backs up the whole database. This backup is part of the incremental
# strategy.
# It performs exactly the same backup as the script backup above, except
# the datafile backup is part of the incremental strategy (this means it
# can have incremental backups of levels > 0 applied to it - full backups
# cannot).
#
# Typically, a level 0 backup would be done at least once a week.
#
# Modified By Reason
# 961219 cd created
#
execute script alloc_all_tapes;
execute script set_maxcorrupt;
backup
incremental level 0
# skip offline
# skip readonly
skip inaccessible
tag backup_db_level_0
filesperset 6
format 'df_t%t_s%s_p%p'
database;
execute script rel_all_tapes;
execute script archive_log_current;
execute script backup_al_all;
}

replace script backup_db_level_1 {
#
# This backup will only backup blocks which have been modified since the
# last level 0 backup was performed. Otherwise it is exactly the same
# backup as the level 0 above (note, the controlfile is always backed up in
# it's entirety i.e. control file backups are never compressed).
#
# Typically, a level 1 backup would be done at least once in between level
# 0 backups.
#
# Modified By Reason
# 961219 cd created
#
execute script alloc_all_tapes;
execute script set_maxcorrupt;
backup
incremental level 1
# skip offline
# skip readonly
skip inaccessible
tag backup_db_level_1
filesperset 6
format 'df_t%t_s%s_p%p'
database;
execute script rel_all_tapes;
execute script archive_log_current;
execute script backup_al_all;
}

replace script backup_db_level_2 {
#
# This backup will only backup blocks which have been modified since the
# last level 0 or 1 backup was performed. Otherwise it is exactly the same
# backup as the level 0 or 1 backups above.
#
# Typically, a level 2 backup would be done at least once in between level
# 0 and 1 backups.
#
# Modified By Reason
# 961219 cd created
#
execute script alloc_all_tapes;
execute script set_maxcorrupt;
backup
incremental level 2
# skip offline
# skip readonly
skip inaccessible
tag backup_db_level_2
filesperset 6
format 'df_t%t_s%s_p%p'
database;
execute script rel_all_tapes;
execute script archive_log_current;
execute script backup_al_all;
}

 查看全文

insert 记录 rowid，如果回滚就根据ROWID删除记录,
delete记录整条记录的内容，如果回滚就把整条记录恢复.

有关rollback segment对update的处理：
When a transaction makes changes to a row in a table, the old image is saved in the rollback segment. If the transaction is rolled back, the value in the rollback segment is written back to the row, restoring the original value.

                                     丢失数据文件和控制文件的恢复案例.(原创)

                                                      (lijietz@126.com)

假设条件:
1、只做了对控制文件的备份
2、非归档模式
3、在对控制文件做了备份以后，创建了一个表空间，并包含一个数据文件
4、在新建的表空间下建了一张表，并存了一条数据
5、备份控制文件以后没有进行别的DML操作
6、shutdown abort以后，错误删除刚建表空间所包含的数据文件,包括控制文件.

思路:

    要想恢复数据库,必须先恢复控制文件,但控制文件是旧的,不包括删除了的表空间
和数据文件.但删除的数据文件的信息在redo里面还是存在的,由此想到了利用redo来
更新控制文件.更新控制文件后,如果open数据库,它应该会提示数据文件丢失.再增加
相应的数据文件.应用所有redo.数据恢复完成.
    可以相应的进行扩展,对archivelog模式的,如果所有存档都在,类似的恢复应该是
没有问题的.



以下是我的恢复过程.

SQL> conn sys/lovelijie@lijie as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            g:oracleoradatalijiearchive
最早的概要日志序列     1
当前日志序列           2


SQL> alter database backup controlfile to 'd:tempcontrol01.ctl';

数据库已更改。

SQL> create tablespace test datafile 'g:oracleoradatalijietest.dbf' size 5m;


表空间已创建。

SQL> create table test(n number) tablespace test;

表已创建。

SQL> insert into test values(1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> shutdown abort
ORACLE 例程已经关闭。
SQL>

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select name
  2  from v$datafile;

NAME
-----------------------------------------------------

G:ORACLEORADATALIJIESYSTEM01.DBF
G:ORACLEORADATALIJIEUNDOTBS01.DBF
G:ORACLEORADATALIJIECWMLITE01.DBF
G:ORACLEORADATALIJIEDRSYS01.DBF
G:ORACLEORADATALIJIEEXAMPLE01.DBF
G:ORACLEORADATALIJIEINDX01.DBF
G:ORACLEORADATALIJIEODM01.DBF
G:ORACLEORADATALIJIETOOLS01.DBF
G:ORACLEORADATALIJIEUSERS01.DBF
G:ORACLEORADATALIJIEXDB01.DBF
G:ORACLEORADATALIJIETEST.DBF

已选择11行。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

================================

--利用备份的控制文件,删除数据文件后尝试启动数据库.

================================

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项


SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。

=======================================

基于时间点的恢复,在alert_lijie.log中找到时间加入表空间的时间点.
主要是把控制文件恢复到最新.
换句话说就是把test.dbf的信息加入到控制文件中.

=======================================

SQL> recover database using backup controlfile until time '2005-3-28 15:25:27';
ORA-00279: 更改 406557 (在 03/28/2005 15:20:07 生成) 对于线程 1 是必需的
ORA-00289: 建议: G:ORACLEORADATALIJIEARCHIVEARC00002.001
ORA-00280: 更改 406557 对于线程 1 是按序列 # 2 进行的


指定日志: {=suggested | filename | AUTO | CANCEL}
g:oracleoradatalijieredo01.log
ORA-00283: 恢复会话因错误而取消
ORA-01244: 未命名的数据文件由介质恢复添加至控制文件
ORA-01110: 数据文件 11: 'G:ORACLEORADATALIJIETEST.DBF'


ORA-01112: 未启动介质恢复

=========================================

目的已经达到.(ORA-01244: 未命名的数据文件由介质恢复添加至控制文件.)
加入相应的数据文件.信息在(ORA-01110: 数据文件 11: 'G:ORACLEORADATALIJIETEST.DBF')

==========================================

SQL> alter database create datafile 11 as 'g:oracleoradatalijietest.dbf';

数据库已更改。

===========================================

接着和一般的恢复一样的.指定redo.

===========================================

SQL> recover database using backup controlfile;
ORA-00279: 更改 407488 (在 03/28/2005 15:25:26 生成) 对于线程 1 是必需的
ORA-00289: 建议: G:ORACLEORADATALIJIEARCHIVEARC00002.001
ORA-00280: 更改 407488 对于线程 1 是按序列 # 2 进行的


指定日志: {=suggested | filename | AUTO | CANCEL}
g:oracleoradatalijieredo01.log
ORA-00279: 更改 427753 (在 03/28/2005 15:32:57 生成) 对于线程 1 是必需的
ORA-00289: 建议: G:ORACLEORADATALIJIEARCHIVEARC00003.001
ORA-00280: 更改 427753 对于线程 1 是按序列 # 3 进行的
ORA-00278: 此恢复不再需要日志文件 'g:oracleoradatalijieredo01.log'


指定日志: {=suggested | filename | AUTO | CANCEL}
g:oracleoradatalijieredo02.dbf
ORA-00308: 无法打开存档日志 'g:oracleoradatalijieredo02.dbf'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


指定日志: {=suggested | filename | AUTO | CANCEL}
g:oracleoradatalijieredo02.log
已应用的日志。
完成介质恢复。
SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

==============================================

用resetlogs打开,使数据文件和日志文件一致.

===============================================

SQL> alter database open resetlogs;

数据库已更改。

SQL> select *
  2  from test;

         N
----------
         1

SQL>
SQL>


conclusions:

    要恢复数据库,肯定要恢复控制文件.所以要分两步走,1:恢复控制文件.2:恢复丢失的数据.
    这个case是noarchivelog模式的,如果后来的dml很多，覆盖了redo,就没法恢复了.
    如果是archivelog模式的,归档的日志都存在,应该是可以恢复的.道理一样.
   


                                                                lijietz
                                                                msn:lijietz@126.com

 查看全文

                                                                     日志文件使用小结. 
                                                                        (lijietz@126.com)
     
1:给日志组1增加重做日志文件.

SQL> alter database add logfile member 'G:ORACLEORADATALIJIEREDO1b.LOG' to group 1;

数据库已更改。

2:删除重做日志.

SQL>alter database drop logfile member 'G:ORACLEORADATALIJIEREDO1b.LOG';
备注:如果要删除点日志是当前重做日志，就无法删除.
如:
查看当前重做日志的信息.
SQL> select a.member,b.status
  2  from v$logfile a ,v$log b
  3  where a.group#=b.group#;
  

MEMBER         STATUS
----------------
G:ORACLEORADATALIJIEREDO01.LOG CURRENT

G:ORACLEORADATALIJIEREDO1B.LOG CURRENT

G:ORACLEORADATALIJIEREDO02.LOG INACTIVE

G:ORACLEORADATALIJIEREDO03.LOG INACTIVE

G:ORACLEORADATALIJIEREDO4A.LOG UNUSED

--试着删除Status列为current的的日志.

SQL> alter database drop logfile member 'G:ORACLEORADATALIJIEREDO1b.LOG';
alter database drop logfile member 'G:ORACLEORADATALIJIEREDO1b.LOG'
*
ERROR 位于第 1 行:
ORA-01609: 日志1是线程1的当前日志 - 无法删除成员
ORA-00312: 联机日志 1 线程 1: 'G:ORACLEORADATALIJIEREDO01.LOG'
ORA-00312: 联机日志 1 线程 1: 'G:ORACLEORADATALIJIEREDO1B.LOG'
--
切换日志，就可以正常删除了.
SQL> alter system switch logfile;

系统已更改。
SQL> alter database drop logfile member 'G:ORACLEORADATALIJIEREDO1b.LOG';

数据库已更改。



2:增加重做日志组.
ALTER DATABASE  ADD LOGFILE GROUP 4 
    ('G:ORACLEORADATALIJIEREDO4a.log') SIZE 10M

     

1:检查数据库当前状态.

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            g:oracleora92RDBMS
最早的概要日志序列     29
当前日志序列           31
SQL>

--把数据shutdown,并重启到mount状态后.
--并更改initSID.ora文件，把log_archive_start改为true;
--如果没有log_archive_start参数,就增加一行.如下.
*.log_archive_start=TRUE
--再增加一行,表示归档目录.
*.log_archive_dest_1 = "location=g:oracleoradatalijiearchive"

装载数据库.

SQL> alter database archivelog;

数据库已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

--查看操作系统g:oracleoradatalijiearchive有无归档日值产生.
--如果自动归档是disabled，执行了alter system switch logfile，虽然进行了日志组的切换，
--但是归档进程并不把之前的online redo log归档，需要进行手工归档：
--alter system archive log all;  把所有没有归档的日志归档


--查看日值切换的时间间隔.

select b.recid,b.first_time,a.first_time,round((a.first_time-b.first_time)*24*60,2) minates
from v$log_history a,v$log_history b
where a.recid=b.recid +1
order by a.first_time desc

1 32 2005-3-23 11:12:25 2005-3-23 11:14:46 2.35
2 31 2005-3-23 11:12:19 2005-3-23 11:12:25 0.1

3---------------------------------------------------

--如果日值切换的时间间隔小于30分钟,就应该增加日值文件大小.
--当然如果增大了日志文件,就应该增大相应的log_checkpoint_interval的值.
--log_checkpoint_interval   表示如果有多少个数据块写入的重做日值后就触发检查点.（8i)


conclusion:
1:同一个重做日值组下的所有重做日值大小必须一致.
2:修改log_archive_start=true,并让数据库工作在archivelog模式,就能产生归档日志.
3:增大重做日志的大小，可以加快一些大型的insert,update,delete操作.
4:把重做日志放在速度最快的硬盘上.
5:联机重做日志可以循环使用,而归档日志的是自动创建的,大小取决于你的重做日志.

                               数据迁移笔记(windows到linux) 
                               （lijietz@126.com）
   
   
            由于业务的需要,领导决定把一个系统分割出来,另设一个新的数据库服务器.由于上次系统down机后的
     表现还可以,领导对我很信任，让我全权负责.
    
        这种数据迁移（windows的到linux)，肯定是exp/imp的咯.一下是一些步骤.
        
        1:大概估计了一下要导出的数据的容量.以下是统计脚本.
          select sum(bytes)/1024/1024/1024 "capacity(G)"
          from dba_segments
          where upper(owner)='USER1';

         小于1G的容量,肯定不用导出为多个文件.
         以后导出的DMP文件只有198M,难道压缩比例可以这么高?!

    2:检查一下字符集.把目标服务器的字符集设置为和原服务器一致.
        因为两边都是oracle 8.1.7，不存在exp版本问题.
  
   3:查看原服务器的数据库用户,建立相同的用户和权限.如果权限不够，导入的时候会有问题.
     
    以下是两个实际执行的脚本内容.
       1：expBat.bat脚本.
     exp parfile = exp_parameter.txt
     ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
          exp_parameter.txt的内容.
          USERID=scott/tiger@lijie
          FILE=G:ORACLEORADATALIJIEEXPDAT.DMP
          FILESIZE=2G
          LOG=G:ORACLEORADATALIJIEEXPORT.LOG
          OWNER=USER1
          GRANTS=y
          INDEXES=y
          ROWS=y
          CONSTRAINTS=y
          
          用ftp把DMP文件传过来.
          ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
          2：impBat.bat脚本内容.
          ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
          imp parfile = imp_parameter.txt
          imp_parameter的内容.
          userid=lijie/lijie@lijie
          FILE=/home/LIJIE/EXPDAT.DMP
          LOG=/home/LIJIE/IMPORT.LOG
          FROMUSER=USER1
          TOUSER=USER2
          GRANTS=y
          INDEXES=y
          ROWS=y
          CONSTRAINTS=y
  
       一路执行，warning都没有,很顺利。数据库正常open,应用测试也没有问题.
  
  
       （lijietz@126.com）  2005年3月22日

     今天想下本korn shell的电子书.好东西没找到,倒是无意中发现了一个搜索下载资源的好办法.

给几个例子，打开google

1、搜索korn相关书籍，关键词.

其中inurl 表明下载地址中包含 korn, filetype表明文件类型是chm格式，你也可以指定pdf或者doc,ppt,zip,rar等

2、搜索oracle相关书籍，关键词 .

或者你也可以指定unix方面，命中率还是可以的，大都可以下载.
提供一个例子

输入: inurl:statspack   filetypedf

eygle的关于statspack文章就出来了.以下是下载地址.

http://www.eygle.com/pdf/Statspack-v3.0.pdf

lijietz@126.com

升级前:
服务器:ibm x250,4个cpu.
存储:   dell emc 2t硬盘. 2组 raid 5

操作系统:windows advace server 2000
数据库版本  oracle 8.1.6
SID:testsrv

昨天早上发现查询缓慢,重起进不了系统.重起后发现硬件故障.(硬盘坏掉一块)
等厂家过来安装系统(自己装怕损坏阵列信息,损坏阵列信息就会丢失全部数据)


装好系统再装oracle 8.1.7

1:
注意系统版本,服务器名必须和以前的一致.(盘符不能变)
   安装oracle 的路径和先前的要一致,sid一致! (把先前的oracle安装文件夹改名.)
其实也可以用oradim工具创建实例,那就不能升级了.

2:
装好oracle后,把备份的inittestsrv.ora(8.1.6)文件覆盖新装的inittestsrv.ora文件.用相应的方法覆盖tnsname.ora和listner.ora和所有控制文件.
(此时,可以正常的open数据库了,sysdba的密码都是默认的.因为没有用的是老的密码文件,但还是有一些问题,比如说select 分区表,还会报internal error)

3:
升级数据库.运行脚本%oracle_home%/rdbms/admin/u0801060.sql,catalog.sql和catapro.sql
一定要注意顺序..看了很多网页才知道要运行这几个文件.



总结:
操作系统起不来,我想数据库肯定是好的.
直接覆盖inittestsrv.sql和控制文件就能启动数据库,因为inittestsrv.sql记录的有
控制文件的路径,而数据文件的信息是记录在控制文件中的.我们安装前后做到了
盘符和路径一致,oracle就知道去哪里找相应的数据并启动.


整个过程是没有什么难度的,关键是要小心和经验.不能有任何的"可能"之类的想法.希望碰到相同问题的dx能得到一点参考.