经常忘记，记录这里，以便备查：

PGA的内存命中：

SELECT name profile,cnt,decode( total, 0, 0, round( cnt * 100 / total)) percentage
FROM ( SELECT name, value cnt, ( SUM( value ) OVER()) total
FROM v$sysstat
WHERE name LIKE 'workarea exec%');

-- 数据缓冲区高速缓存
SELECT physical_reads, db_block_gets, consistent_gets, NAME,
100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio"
FROM v$buffer_pool_statistics;

-- 重做日至缓冲区
SELECT a.VALUE redo_entries, b.VALUE redo_buffer_allocation_retries,
ROUND ((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'redo entries' AND b.NAME = 'redo buffer allocation retries';

-- 数据字典高速缓存
SELECT SUM (pinhits) / SUM (pins) * 100 "hit radio"
FROM v$librarycache;

-- 库高速缓存
SELECT TO_CHAR (ROUND ((1 - SUM (getmisses) / SUM (gets)) * 100, 1)) || '%' "Dictionary Cache Hit Ratio"
FROM v$rowcache;

-- 排序
SELECT a.VALUE disk_sort, b.VALUE memory_sort, ROUND ((1 - a.VALUE / (a.VALUE + b.VALUE)) * 100, 4) sort_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'sorts (disk)' AND b.NAME = 'sorts (memory)';

--找出相关的sql根据系统pid
select se.username,se.machine,sq.cpu_time,sq.sql_text from
v$process p,v$session se,v$sqlarea sq
where p.addr=se.paddr and se.sql_hash_value=sq.hash_value and p.addr='&pid';

列出cpu_time top 10

select cpu_time,sql_text
from (select sql_text,cpu_time,
rank() over (order by cpu_time desc) exec_rank
from v$sql
)
where exec_rank <=10;

执行次数最多的top 10
select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=10;

 查看全文

今天看到个帖子，关于索引中是否包含ROWID，做个DUMP跟踪下看，以下为详细的跟踪记录：

SQL> create table test as select rownum a ,'c' c from dual connect by level<101;

Table created

SQL> create index u_test on test(a);

Index created

SQL> select extent_id,file_id,block_id from dba_extents where segment_name='U_TEST';

EXTENT_ID FILE_ID BLOCK_ID

---------- ---------- ----------

0 6 121

SQL> alter system dump datafile 6 block 121; --assm 自动段管理位图占3个块，跳3个块

System altered

SQL> alter system dump datafile 6 block 124;

System altered

以下部分跟踪记录：

row#0[8024] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 02 ----------------索引键值

col 1; len 6; (6): 01 80 00 74 00 00 -----------------------rowid

row#1[8012] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 80 00 74 00 01

row#2[8000] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 04

col 1; len 6; (6): 01 80 00 74 00 02

SQL> create unique index u_test on test(a);

Index created

SQL> select extent_id,file_id,block_id from dba_extents where segment_name='U_TEST';

EXTENT_ID FILE_ID BLOCK_ID

---------- ---------- ----------

0 6 121

SQL> alter system dump datafile 6 block 124;

以下部分跟踪记录：

row#0[8025] flag: ------, lock: 0, len=11, data:(6): 01 80 00 74 00 00

col 0; len 2; (2): c1 02

row#1[8014] flag: ------, lock: 0, len=11, data:(6): 01 80 00 74 00 01

col 0; len 2; (2): c1 03

row#2[8003] flag: ------, lock: 0, len=11, data:(6): 01 80 00 74 00 02

col 0; len 2; (2): c1 04

row#3[7992] flag: ------, lock: 0, len=11, data:(6): 01 80 00 74 00 03

如上所示：唯一索引不包含ROWID，普通索引包含rowid.

转自：http://ningoo.itpub.net/post/2149/287794

Oracle常用dump命令，记录一下备查。

一.Memory Dumps

1).Global Area

ALTER SESSION SET EVENTS 'immediate trace name global_area level n';

1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory

2).Library Cache

ALTER SESSION SET EVENTS 'immediate trace name library_cache level n';

1 library cache统计信息
2 包含hash table histogram
3 包含object handle
4 包含object结构（Heap 0）

3).Row Cache

ALTER SESSION SET EVENTS 'immediate trace name row_cache level n';

1 row cache统计信息
2 包含hash table histogram
8 包含object结构

4).Buffers

ALTER SESSION SET EVENTS 'immediate trace name buffers level n';

1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters

5).Buffer

ALTER SESSION SET EVENTS 'immediate trace name buffer level n';

n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。

6).Heap

ALTER SESSION SET EVENTS 'immediate trace name heapdump level level';

1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA内容
2050 SGA内容
4100 UGA内容
8200 Current call内容
16400 User call内容
32800 Large call内容

7).Sub Heap

Oracle 9.0.1版本之前

ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level n';

若n为subheap的地址，转储的是subheap的摘要信息
若n为subheap的地址+1，转储的则是subheap的内容

Oracle 9.2.0版本之后

ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level n, addr m';

其中m为subheap的地址

n为1转储subheap的摘要，n为2转储subheap的内容

8).Process State

ALTER SESSION SET EVENTS 'immediate trace name processstate level n';

9).System State

ALTER SESSION SET EVENTS 'immediate trace name systemstate level n';

10).Error State

ALTER SESSION SET EVENTS 'immediate trace name errorstack level n';

0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area

11).Hang Analysis

ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level n';

12).Work Area

ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level n';

1 SGA信息
2 Workarea Table摘要信息
3 Workarea Table详细信息

13).Latches

ALTER SESSION SET EVENTS 'immediate trace name latches level n';

1 latch信息
2 统计信息

14).Events

ALTER SESSION SET EVENTS 'immediate trace name events level n';

1 session
2 process
3 system

15).Locks

ALTER SESSION SET EVENTS 'immediate trace name locks level n';

16).Shared Server Process

ALTER SESSION SET EVENTS 'immediate trace name shared_server_state level n';

n取值为1~14

17).Background Messages

ALTER SESSION SET EVENTS 'immediate trace name bg_messages level n';

n为pid+1

二.File Dumps

1).Block

Oracle 7之前

ALTER SESSION SET EVENTS 'immediate trace name blockdump level n';

n为block的rdba

Oracle8以后

ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;

ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;

2).Tree Dump

ALTER SESSION SET EVENTS 'immediate trace name treedump level n';

n为object_id

3).Undo Segment Header

ALTER SYSTEM DUMP UNDO_HEADER 'segment_name';

4).Undo for a Transaction

ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn;

5).File Header

ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level n';

1 控制文件中的文件头信息
2 level 1 + 文件头信息
3 level 2 + 数据文件头信息
10 level 3

6).Control file

ALTER SESSION SET EVENTS 'immediate trace name controlf level n';

1 文件头信息
2 level 1 + 数据库信息 + 检查点信息
3 level 2 + 可重用节信息
10 level 3

7).Redo log Header

ALTER SESSION SET EVENTS 'immediate trace name redohdr level n';

1 控制文件中的redo log信息
2 level 1 + 文件头信息
3 level 2 + 日志文件头信息
10 level 3

8).Redo log

ALTER SYSTEM DUMP LOGFILE 'FileName';

ALTER SYSTEM DUMP LOGFILE 'FileName'
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;

其中time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;

9).Loghist

ALTER SESSION SET EVENTS 'immediate trace name loghist level n';

1 dump控制文件中最早和最迟的日志历史项
>1 dump 2^n个日志历史项

由于需要，需缩小一数据库的ＳＧＡ，并重新分配:

alter system set sga_max_size=500m scope=spfile;

alter system set sga_target=500m scope=spfile;

shutdown immediate;

startup;

alter system system set shared_poll_size=300m scope=both;

记录下．

　　昨天在给应用系统做升级的时候，脚本老是执行到一半，就出现，已断开数据库连接，跟踪后发现，执行到中间回出现，ora-03113错误(通信通道的文件结束),接着就是ora-03114(失去oracle连接）．

１．怀疑脚本问题，语句有问题，仔细研究下，没问题．

２．网络问题．换台机器还是一样．

３．换了个库试下，还是一样．

４．查了下　失效对象，有一些失效对象，compile之，结果得以解决．

总结：　一些失效的对象也会引起ora-03113 ora-3114错误

 查看全文

关于ORACLE自动统计CBO统计信息

ORACLE10G以后，在建库后默认就创建了个GATHER_STATS_JOB的定时任务。默认情况下在工作日晚上10：00-6：00和周末全天开启。它调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC的程序收集统计信息。它检测统计信息缺失的对象和陈旧的对象。然后确定优先级，再开始进行统计信息。

说明：当做完统计信息后，如果对对象的行数修改达到10%，DBMS_STATS就认为是统计信息过旧。

可以查询这个JOB的运行情况：

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

也可以关闭自动统计信息收集功能：

DBMS_SCHERDULER.DISABLE(‘GATHER_STATS_JOB’);

对与易变对象的变化，可以人工收集统计信息(DBMS_STATS)外 主要两种处理方式：

.

一种就是删除统计信息。使它的统计信息为空，对于任何统计信息缺失的表,oracle会用动态取样特性自动产生统计信息。如果使用久的统计信息 就可能产生错误的执行计划。需要设置optimizer_dynamic_sampling为2（ORACLE10G默认值）或以上都可以启动此特性。

optimizer_dynamic_sampling ,提供在SQL分析的时候，自动根据不同的Level（0-10）以不同的准确度分析SQL中未被analyze过的表，意在为CBO提供更多的统计信 息。在Oracle9iR2中引入，默认为Level 1，10g默认为2 。

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1135.htm#REFRN10140

Level 0: Do not dynamically sample the table(s)

Level 1：Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects

Level 2:Sample all unanalyzed tables referenced in the query using default sampling amounts(small sample)

Level 3 -- Level 10 ........更详细的sample而已。

例如：dbms_stats.delete_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)

另一种就是设置为表进行锁定,这样就可以不更新统计信息（注意LOCK表以后，表就不能修改了）。

dbms_stats.lock_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)；

关于块损坏

块损坏或物理损坏是物理磁盘上的快边的不可读或数据不一致到不可用的状态。块损坏一般原因是人为错误。缺陷，补丁或硬件异常也会导致块损坏。查看块损坏通过阅读操作系统，应用程序，数据库层次的日志文件。在WINDOW可以查看系统日志。在大多数UNIX环境下文件位于/var/adm/syslog。数据库日志和跟踪文件参考与alter.log关联的跟踪文件位于UDUMP或BDUMP目录，alter.log通常概要的说明存在一个问题，跟踪文件详尽的给出损坏的细节。

检测坏块的方法有四种方法：

1． ANALYZE TABLE TABLE_NAME VALIDATE STRUCTURE

分析对象的完整性，返回错误，可能需要重建对象。

2． ORACLE DBVERIFY 工具用于离线数据文件。

DBVERIFY 用来检查一个特定数据文件是否有坏块。

DBV HELP=Y看参数设置

3． Init.ora 参数DB_BLOCK_CHECKING ,在创建或修改数据和索引块时，对其进行检查。

4． DBMS_REPAIR程序包，用语表，索引 和分区

DBMS_REPAIR是一组程序，可以检测和修复坏块。

可以通过恢复进行数据恢复，恢复数据文件或数据块

在RMAN里可以进行数据块恢复

rman> connect target

创建分区表

范围分区：

Create table t {range_key_column date,

Data varchar2(20)

}

Partition by ranger(range_key_column)

(

partiton part1 values less then (….),

partiton part2 values less then (….),

)

HASH分区可以是数据分散从而更好的避免热块,建议N是2的一个幂,可以得到最佳的分布。

Create table t(hash_key_word date,

data varchar2(20)

)

Partition by HASH(hash_key_column)

(

partition part1 tablespace p1,

partition part2 tablespace p2

)

列表分区

Create table t (state_cd varchar2(2),

Data varchar2(20))

Partition by list(stat_cd)

(

partition part1 values(‘1’,’2’),

partiton part2 values(‘3’,’4’)

)

组合分区 外层总是范围分区

实现空表 和 表分区交换的 表和分区表的结构要一致。

alter table partitioned exchange partition part1

with table partiton including indexes without validation

增加一分区

alter table t add partition part3 value less then(….)

删除一分区

alter table t drop partition part3;

合并分区

alter table t

merge partitions part1,part2 into partition part;

分区改名称

alter table t rename partition part1 to part4;

分区更改表空间

alter table t move partiton part1 tablespaces tb_test1 nologging;

分区表的导出

USERID = USER/PWD@ORCL

TABLE = T:PART1,T:PART2

FILE=D: est.dmp

Flashback drop 提供虚拟回收站，允许删除对象重建。

Flashback versions query, Flashback transation Query 用语识别或确定要恢复到当前状态的数据行。

Flashback Table 用于恢复单独的表，比如错误的更新了表。

10g后drop 数据库对象 实际是把对象放入回收站,可以查看回收站:

show recyclebin;

select object_name as recycle_name,original_name from recyclebin;

在回收站的命名格式是bin$globalUID$Version 24位的全局UID和数据库分配的版本号

对于recyclebin里表可以象正常表一样访问数据.

Select * from “bin$globalUID$version”;

实现恢复:

flashback table “bin$globalUID$version” to before drop( rename to t2) ;

purge table “bin$globalUID$version” 可以从回收站彻底删除对象并且释放空间

purge tablespace tablespacename 清除所有特定表空间的所有丢弃对象.

Purge tablespace tablespacename USER user的特定用户表空间内容

Purge recuclebin 清空用户的回收站

Purge DBA_RECYCLEBIN 清空所有用户回收站.(具有sysdba权限).

对于flashback drop 的一些局限

回收站功能只能用于一些非系统,本地管理的表空间.

在回收站的时间很难界定,是由系统空间 和系统活动 决定的.

对回收站里的表不能进行dml,ddl

回收站恢复 依存的对象也就被恢复.

分区表 不受回收站保护..

使用flashback version query

闪回查询时间是由UNDO_RETENTION的时间决定.闪回查询要具有select 和 flashback 权限.

Select * from t1 versions between scn minvalue and maxvalue where…

Select * from t1 versions between timestamp

To_timestamp(‘2004-10-26 11:37:00’,’YYYY-MM-DD HH:MI:SS’) and

To_timestamp(‘2004-10-26 11:43:00’,’YYYY-MM-DDHH:MI:SS’) where …

函数 SCN_TO_TIMESTAMP 和 TIMESTAMP_TO_SCN能可以让scn和时间相互转换.

关于新增的一些虚列:

VERSIONS_STARTSCN 创建行的起始SCN,如为NULL,在在BETWEEN 前创建.

VERSIONS_STARTTIME 创建行的起始TIMESTAMP,如为NULL,在在BETWEEN 前创建.

VERSIONS_ENDSCN 行版本终止时的SCN,如为NULL,此版本为当前版本,或对应一个delete 操作.

VERSIONS_ENDTIME 行版本终止时的TIMESTAMP,如为NULL,此版本为当前版本,或对应一个delete 操作.

VERSIONS_XID 创建行版本的事务标识符.

VERSIONS_OPERATION 改变数据的事物执行的操作.(I,D,U)

Select versions_starttime,version_endtime,versions_xid,version_operation from t1 versions between scn minvalue and maxvalue where ………..

使用闪回事务查询

使用闪回事务查询可以识别表和表操作,从而进行分析:

select table_name,operation,undo_sql from flashback_transaction_Query where xid=’…’

使用这个的功能权限 需要flashback any table

使用闪回表

同样受UNDO_RETENTION的影响.

闪回表需要权限 flashback any table 或 flashback table 或 执行闪回select,insert,delete,alter权限.

闪回表必须启动row movement;

alter table t1 enable row movement ;

flashback table to scn/timestamp enable triggers;

Flashback 最早出现在oracle 9i flashback query,oracle 10g 增强了flashback的功能：

1． Flashback database

2． Flashback drop

3． Flashback versions query

4． Flashback Transaction Query

5． Flashback Table

不过除了 Flashback database 是基于Flashback log， 其他都是基于UNDO DATA.

Flashback drop 提供虚拟回收站，允许删除对象重建。

Flashback versions query, Flashback transation Query 用语识别或确定要恢复到当前状态的数据行。

Flashback Table 用于恢复单独的表，比如错误的更新了表。

Flashback database 能使整个数据库闪会至特定的时间点，闪会数据库不能对删除数据文件，缩小数据文件恢复，闪回数据库比传统恢复速度更快。

1. flashback 不能解决媒介故障。

2. 数据文件截断。

3. 不能删除表空间并并resetlogs恢复

4. 不能超出回的界限.(SCN时间点)

Flashback area 通过数据库初始文件建立,文件保留长度由RMAN的保留策略决定.

RMAN CONFIGURE RETENTION POLICY 决定.

Alter system set db_recovery_file_dest_size=10M scope=Both;

Alter system set db_recovery_file_dest=’C:oraceflash_recovery_Areaora_t’;

Alter system set db_recovery_file_dest_size=’25M’;--改变flashback area大小.

Alter system set db_recovery_file_dest=’’ 停用flashback area

配置闪回数据库,数据库必须为归档模式:

connect / as sysdba ;

startup mount ;

alter database set db_flashback_retention_target=4320;(分钟为单位,也就是3天)

alter database flashback on;

alter database open;

在RMAN中用flashback database 就很简单:

select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log可以得到时间或SCN,然后数据库重启动到MOUNT状态:

flashback database to time/scn (to sequence thread number)

完成数据库的恢复。必须有两个必要的步骤,重建(restore)和恢复(recovery).

一.服务器管理的恢复

数据库完全恢复的例子：

1. 为了完成一个 完全恢复数据库恢复，目标数据库必须在mount状态。

2. 执行restore database 和recover database 命令

run

{

allocate channel c1 type disk;

restore database;--重建数据文件

recover database;--是否要应用REDOLOG

alter database open;

}

二 用户管理恢复 用户直接管理和恢复要求的数据文件,需要一些用户的交互操作.

1. 恢复控制文件

a) 使用控制文件自动备份来恢复控制文件.

先进行配置备份控制文件:

rman> connect target

configure controlfile autobackup on;

run

{

backup database;

backup(archivelog all)
}

在NOMOUNT下启动数据库 :

connect / as sydba

startup nomount;

连接目标数据库和RMAN 库;

SQL> connect rman/rman@rmanLink;

RMAN> connect target /

因为控制文件损坏,需设置DBID (select DBID from V$databse)

set dbid 1212131;

restore contrilfile from auteoback;

alter database mount;

recover database;

later databse open resetlogs;

b) 重建控制文件

connect / as dydba;

alter database backup controlfile to trace; (跟踪文件UDUMP目录里)

connect / as sysdba

@backup_controlfile_noreset.txt (即产生的创建控制文件脚本)

数据库的不完全恢复:

RMAN不完全恢复有两种,一种是RECOVER 之前 set until time /sequence/scn

或用 RECOVER DATABASE UNTIL TIME/SEQUENCE/SCN;

用户管理用RECOVER DATABASE UNTIL TIME/CHANGE/CANCEL

例 基于时间和序列号的恢复:

1. 时间点; startup mount ;

set nls_date_format=DD-MM-YYYY HH24:MI:SS

rman> run
{

set until time ’06-sep-2004 11:25:00’;

restore database;

recover database;
}

alter database open resetlogs;

2. sequence 可以从V$LOG_HISTORY中获取列和线程信息

startup mount ;

rman> run
{

set until SEQUENCE 3 thread 1;

restore database;

recover database;
}

alter database open resetlogs;

用户管理的不完全恢复:

set nls_date_format=DD-MM-YYYY HH24:MI:SS ;

startup mount;

recover database until time ‘06-sep-2004 11:25:00’ ;

alter databse open resetlogs;(用后以前的备份失效,需重新进行冷备)

resetlogs 选择项会重置REDOLOG SEQUENCE ,重置ONLINE redo 内容,scn不会重置,是数据库一个生命周期的开始和数据库一个生命周期的结束

Oracle 非关键损失恢复

1． 非关键损失的恢复，不会对数据库的运行产生致命的影响。

a. 恢复临时表空间：数据库mount状态下，删除临时表空间,创建新的临时表空间.

新建临时表空间， 切换默认临时表空间。

1.Startup mount;

2.Drop tablespace temp including contents;

3.Create temporary tablespace temp tempfile ‘C:oracleoradate emp01.dbf ’

Size 100M extent management local uniform size 128k;

切换临时表空间：

alter database default temporary tablespace temp2;

注意： 运行时切换临时表空间，所有使用TEMP表空间的查询都会CANCEL。

可以查看V$sort_usage,v$sql_text.

使用到临时表空间的操作主要有sort,group by ,hash josh , analyze 等

b. 恢复REDOLOG：这里的恢复是指REDOLOG有镜像的REDOLOG只丢失一个REDOLOG组成员的恢复。

Alter database drop logfile member ‘C:oracleoradata edo01.log’;

Alter database add logfile membe ‘c:oracleoradata edo01.log’ to group 1;

注意：在重建过程中 保持当日志在非活动状态。可以在限制模式下操作。

c. 恢复索引表空间 ： 注意要先知道原来的索引情况， MOUNT状态下，先删除表空间，再重建，再重建索引。

DROP TABLESPACE INDEXES INCLUDING CONTENTS；

Create tablespace indexe datafile ‘C:oracleoradateindex01.dbf’;

执行重建索引的脚本。

d. 恢复只读表空间: 当表空间被设置为只读表空间后 有它的备份，只需在数据库关闭情况下物理COPY回原地址即可。

e. 重建密码文件：

shutdown immediate;

startup;

orapwd file=orapwora password=sys entries =20;

Undo_retention 是oracle 9i 以后开始出现的参数，控制UNDO保留事物信息的时间。9i默认900s,10g 默认时间为10800S，9I以后的UNDO管理使ora-01555 snap too old 的 错误大大降低，但当无可重用的空间后（无可用空间 或 UNDO不可扩展），系统还是会重用UNDO，这在很多情况下是不允许的。

通过设置：

ALTER TABLESPACE UNDO_TS RETENTION GUARANTEE;

ALTER TABLESPACE UNDO_TS2 RETENTION NOGUARANTEE;

关于动态语句

在PL/SQL中，DML和事务控制语句可以直接使用SQL，DDL和系统控制语句不能直接在PL/SQL使用，必须使用动态语句执行。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

动态语句可以通过本地动态SQL执行，也可以通过DBMS_SQL来执行。

1. 本地动态SQL 使用 EXECUTE IMMEDIATE 来执行：

create or replace procedure proc_test
(
table_name in varchar2, --表名
field1 in varchar2, --字段名
datatype1 in varchar2, --字段类型
field2 in varchar2, --字段名
datatype2 in varchar2 --字段类型
) as
str_sql varchar2(500);
begin
str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;
execute immediate str_sql; --动态执行DDL语句
exception
when others then
null;
end ;

动态执行DML语句：

create or replace procedure proc_insert
(
id in number, --输入序号
name in varchar2 --输入姓名
) as
str_sql varchar2(500);
begin
str_sql:=’insert into dinya_test values(:1,:2)’;
execute immediate str_sql using id,name; --动态执行插入操作
exception
when others then
null;
end ;

2． 使用DML_SQL执行

使用DBMS_SQL包实现动态SQL的步骤如下：

A、 先将要执行的SQL语句或一个语句块放到一个字符串变量中。

B、 使用DBMS_SQL包的parse过程来分析该字符串。

C、 使用DBMS_SQL包的bind_variable过程来绑定变量。

D、 使用DBMS_SQL包的execute函数来执行语句。

create or replace procedure proc_dbms_sql
(
table_name in varchar2, --表名
field_name1 in varchar2, --字段名
datatype1 in varchar2, --字段类型
field_name2 in varchar2, --字段名
datatype2 in varchar2 --字段类型
)as
v_cursor number; --定义光标
v_string varchar2(200); --定义字符串变量
v_row number; --行数
begin
v_cursor:=dbms_sql.open_cursor; --为处理打开光标
v_string:=’create table ’||table_name||’(’||field_name1||’ ’||datatype1||’,’||field_name2||’ ’||datatype2||’)’;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析语句
v_row:=dbms_sql.execute(v_cursor); --执行语句
dbms_sql.close_cursor(v_cursor); --关闭光标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭光标
raise;
end;

动态语句执行DML

create or replace procedure proc_dbms_sql_update
(
id number,
name varchar2
)as
v_cursor number; --定义光标
v_string varchar2(200); --字符串变量
v_row number; --行数
begin
v_cursor:=dbms_sql.open_cursor; --为处理打开光标
v_string:=’update dinya_test2 a set a.name=:p_name where a.id=:p_id’;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析语句
dbms_sql.bind_variable(v_cursor,’:p_name’,name); --绑定变量
dbms_sql.bind_variable(v_cursor,’:p_id’,id); --绑定变量
v_row:=dbms_sql.execute(v_cursor);　　　　　　　　　　　--执行动态ＳＱＬ
dbms_sql.close_cursor(v_cursor); --关闭光标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭光标
raise;
end;

使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句是查询语句，则要使用DBMS_SQL.define_column定义输出变量，然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。

在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同，在DDL中使用绑定变量是非法的（bind_variable(v_cursor,’:p_name’,name)），分析后不需要执行DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARSE时执行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。

关于PL/SQL中的异常

1．异常的分类

PL/SQL的异常 分为 预定义异常和自定义异常，预定义异常已在standard包中定义，可以直接使用.自定义异常使用时需要定义 例如: example exception.预定义异常只定义了部分ORACLE错误,可以自定义异常与oracle错误相关连。

例如：定义个 E_TOOLONG 和 oracle-01401相关连

declare

E_TOOLONG exception;

Pragma exception_init(E_TOOLONG,’-01401’);

通过exception_init, 自定义异常,一个只能与一个oracle 错误相关联，并且异常处理中sqlcode,sqlerrm 返回oracle错误代码和oralce 错误信息，而不是自定义信息。

2．关于异常的产生

与 oracle 异常 相关联的异常 在响应错误出现时候 自动出现，而其他自定义异常则可以用RAISE 或 直接RAISE_APPLICATION_ERROR(-20001,’错误代码’)

3．异常处理

一条异常处理语句可以处理多个异常。只要在when子句中加由or分隔的多个异常名即可。如果块中的异常没有被处理，则该块会带着未处理的异常返回调用它的程序，这会导致调用它的程序出错。如果在存储过程中出现异常，则存储过程的OUT参数将得不到返回值。
为了避免未处理异常带来的问题，我们最好在块的最外层使用others子句处理块中所有未处理的异常。这样就能确保所有的错误都能被发现和处理。

(1)处理可执行部分产生的异常
(2)处理定义部分产生的异常 如果是定义部分的一个赋值语句产生了异常，即使在当前块的异常处理部分有处理该异常的处理语句时，也不去执行，而是立刻被传递到外部块中。由外部块处理。
(3)处理异常处理部分产生的异常 在异常处理语句中也可以产生异常，这个异常可以通过raise语句产生，或是由于出现一个运行错误而产生。这两种情况下产生的异常都被立刻传递到块外。
(4)sqlcode和sqlerrm函数 由于others子句处理when子句没有处理的异常，所以在others子句中处理的异常是未知的。我们可以用sqlcode和sqlerrm函数来确定异常对应的错误代码和信息。

异常种类 sqlcode sqlerrm
oracle错误对应的异常 负数 oracle错误
no_data_found +100 no data found 用户自定义异常 +1 user-defined exception 没有产生异常 0 ora-0000:normal,successful completion 注意，如果使用exception_init预编译指令声明与oracle错误相连的自定义异常，则sqlcode和sqlerrm返回对应的oracle错误代码和相应的错误信息，则不是返回"+1"和"user-defined exception"。

下面是一个完整的例子：

declare
e_toosmallsalary exception;
v_currentsalary number(8,2);
v_smallsalary number(8,2) default 100;
v_errorcode number;--获得错误消息代码的变量。 v_errortext varchar2(200);--获得错误消息文本的变量。 begin select salary into v_currentsalary from auths where author_code='A00002'; if v_currentsalary<v_smallsalary then
raise e_toosmallsalary; end if;
exception when e_toosmallsalary then delete auths where author_code='A00002'; when others then
v_errorcode:=sqlcode;
v_errortext:=substr(sqlerrm,1,200);
dbms_output.put_line(v_errorcode);
dbms_output.put_line(v_errortext); end;

注意，如果要在SQL语句中使用sqlcode和sqlerrm，则一定要先把它们的值赋给局部变量，然后再将这些局部变量用在SQL语句中，因为这些函数都是过程性的，不能直接用在SQL语句中。

关于ORACLE的审计

1. Standard Database auditing

必须先设置 静态参数 AUDIT_TRAIL 有3个参数:

NONE: 不收集审计信息

DB: 审计信息存储在数据库里

OS: 审计信息存储在操作系统里

可以审计四类信息:
1).登录 2)系统权限的使用 3)对象权限的使用 4) SQL的使用

eg: Audit table ;

Audit delete on hr.testtable whenever successful

可以从Dba_audit_trail,dba_audit_exists,dba_audit_object 里查看审计信息.

2. Value-based auditing

使用触发器对系统收集审计信息,可能影响系统性能,最好standard database audit 收集信息不足够时使用.

3. Fine-gained auditing(DBMS_FGA)

可以有一些更精细的审计,可以审计到列和实际的数据处理

例子:

dbms_fga.add_policy (

object_schema => 'hr',

object_name => 'employees',

policy_name => 'audit_emps_salary',

audit_condition=> 'dept_id=10',

audit_column => 'salary',

handler_schema => 'secure',

handler_module => 'log_emps_salary',

enable => TRUE,

statement_types=> 'select' );

可以从DBA_FGA_AUDIT_TRAIL查询审计信息.

ALL_AUDIT_POLICIES,DBA_AUDIT_POLICIES,USER_AUDIT_POLICIES等等

审计SYSDBA,SYSOPER操作,oracle 会自动审计登录信息,必须要设置初始参数audit_sys_operations=TRUE 可以设置 audit_file_dest放审计信息

对于WINDOWS 会默认存在window event log ,对于unix,linux 审计信息默认存储在$ORACLE_HOME/rdbms/audit.

在WINDOWS配置LISTENER 或进行其他的一些设置常需要进行HOST 验证，但常常输入了正确的密码和用户名仍会 显示：ERROR: Wrong password for user，这个是可能是由于WINDWOS的安全设置问题,需设置:

进如 : 控制面板->管理工具->本地安全策略->本地策略->用户权利指派->作为批处理作业登录

添加 ora_dba 用户.

好象清楚，但一下老是说不全， 还是总结一下：

1.truncate delete 都是删除数据，不删除表结构，DROP删除数据和表结构。

2.delete 是DML语句，会产生undo数据，需要提交事物后生效，可以回滚，并且触发TRIGGER；而 truncate ,drop是DDL 立即生效，不可以回滚，不触发TRIGGER。

3.delete 不影响所占用的EXTENT,HWM保持不变。drop 会释放全部空间。truncate 会释放到默认MINEXTEN个extent,除非用resuse stroage,并且会恢复HWM.

4.10g后DROP在允许时间内 undo_retention 时间内 可以用FLASHBACK恢复。

一般时间DROP<TRUNCATE<DELETE

5、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限

匿名块没有名称，执行后不可重用；

函数 和 过程 有名称， 用过后可以重用；

函数有返回值 ，过程没有；

函数一般是有针对性的目的，过程一般都比较复杂；

函数一般在语句中调用，过程一般都单独调用

在10G 以前收缩表空间是用：

　　1.exp/imp
　　2.alter table xxx move　　

10G后用shrink表空间

表空间中支持自动段空间管理 (ASSM), 否则会报 ORA-10635: Invalid segment or tablespace type

适用表，索引，大对象，IOT，物化视图

alter table tbname enable row movement shrink 必须开启行迁移功能。
　　保持HWM
　　alter table tbname shrink space compact;
　　回缩表与HWM
　　alter table tbname shrink space;
　　回缩表与相关索引
　　alter table tbname shrink space cascade;
　　回缩索引
　　alter index idxname shrink space;
　　
　　相关限制
　　cluster中的表
　　有long类型的表
　　有on_commit物化视图的表
　　有基于rowid物化视图的表
　　大对象(LOB)索引

触发器的限制

触发器主要用于增强约束，审记 触发其他处理程序

触发器使用应该注意的问题：

1． 触发器不应该使用事物控制语句（commit,rollback,savepoint）,因为它本身和触发语句在同一事务中。

2． 触发器中调用的过程和函数不能使用控制性语句。

3． 触发器主体不能声明任何LONG 或 LONG RAW 变量，并且:NEW,:OLD不能指向定义触发体表中的 LONG 或 LONG raw

4． 触发器访问的表有所限制。

变化表：DML语句正在修改的表。

限制表：可需要对参考完整性限制进行读操作的表。

触发器主体不允许

a) 读取或修改触发语句的任何变化表，也包括触发表本身。

一条sql导致数据库整体性能下降的诊断和解决的全过程

这里有个关于控制JOB时间的 让它在 8，9，10，15，16，17整点运行

trunc(sysdate) + decode(to_char(sysdate,'hh24'),8,9,9,10,10,15,15,16,16,17,17,23,32)/24

一般创建 主键约束，唯一约束，非空约束时 一般会用以下语句

alter table table_name add constraint constraint_name primary key [unique] (field_name);

alter table table_name modify (field_name) not null;

如果在业务繁忙的情况下，严重的情况下，将堵塞select操作，引发严重的性能问题。

这是因为shaerd pool语句解析的时候，不能获得对象的句柄，所以将发生大量的library cache pin的等待事件，语句处于等待解析，所以阻塞了读。

但是，数据的唯一性校验是不阻塞读的，如创建唯一索引，validate等等。

只有约束改变是阻塞读

alter table ... add constraint;

alter table ... modify constraint enable validate;

这样创建正确的约束就2种方法：

1． 创建唯一索引。

Create unique index index_name on table table_name(field_name) online ;

Alter table table_name add constraint constraint_name primary key(field name)

Using index index_name;

2． 创建普通索引,采用不校验以前的数据

Create index index_name on table table_name(field_name) online;

Alter table table_name add constraint constraint_name primary key(field_name)

Using index index_name novalidate;

以前写过一篇：怎么样在业务繁忙时期正确的创建表约束，除了约束，这里也有必要介绍一下怎么在业务繁忙时期创建或者是重组索引。重新创建（create）索引的主要原因是因为新的业务的发展的需要，而重组索引往往是因为索引的偏移膨胀或者是数据删除引起的稀疏状态，也就是有些人说的“碎片”，这个情况下，我们就可以在线重组索引（rebuild online）。

当然，如果需要创建的索引或者需要重组的索引很小，创建与重组过程在几秒之内，这些都可以直接做而不需要讨论。实际情况是，业务很繁重以及表与索引都很大。这些情况下我们需要注意些什么呢？

1、创建新索引

首先，评估该索引的需要程度，如果不是特别紧急的大索引，最好在维护时间操作，还要评估该索引是否会对现有的语句造成负面影响，如导致以前的语句错误的走到这个新索引上（在日期打头的索引上，很容易出现这样的问题）。

然后，根据索引大小以及需要在上面创建的表业务是否繁忙，如果业务繁忙，尽量选择业务不繁忙的时间，系统负载不高的情况下做，避免额外的消耗，如凌晨2点-6点进行操作。创建索引之前评估索引的大小以及索引所在表空间剩余空间的大小，除此之外，还要评估临时表空间大小是否足够，用于创建索引时的排序操作。

最后，确定要创建以后，可以采用online模式创建，并且在创建的时候马上分析。

创建完成以后，还要马上检查系统应用，如果发现有错误走到该索引的语句，并且有严重影响的，可能需要立即删除该索引或者约束。

在一些情况下，需要改造一个索引，如添加一个字段到索引或者从索引中删除一个字段，这个时候也需要重新创建索引，但是需要严格按照如下顺序来操作

a、创建新的替代索引（如加字段或者减字段后的索引）

b、测试新的索引没有任何问题

c、删除原来的索引

2、重组索引

如果索引因为更新太频繁或者是删除数据过多，可能引起索引的数据稀疏分布，造成大量的空间浪费，并且严重影响索引的扫描速度。这样的情况下，我们需要对该索引进行空间重组。

重组之前，因为重组索引的时候，先并不删除以前的索引，同样需要确认重组以后的索引所在的表空间是否有足够的空间，以及是否有足够的临时表空间用于排序。同样，如果索引很大，而且使用比较频繁，请确认在业务不繁忙的时候操作。

3、快速创建/重组

有的时候，索引实在太大，如几十个G的索引，创建一次或者重组一次需要耗费很长的时间，如果硬件条件许可，我们可以采用一些特殊的方法来提高速度，如采用大的排序区，并行操作等等。

然后，特别需要注意的是，在并行创建或者重组完成以后，一定要取消索引的并行度，否则，在OLTP环境中，可能会因为意外的使用并行而出现严重性能问题。

Linux在具有高稳定性、可靠性的同时，具有很好的可伸缩性和扩展性，能够针对不同的应用和硬件环境调整，优化出满足当前应用需要的最佳性能。因此企业在维护Linux系统、进行系统调优时，了解系统性能分析工具是至关重要的。
　　在Linux下有很多系统性能分析工具，比较常见的有top、free、ps、time、timex、uptime等。下文将介绍几个较为重要的性能分析工具vmstat、iostat和sar及其使用。

　　用vmstat监视内存使用情况

　　vmstat是Virtual Meomory Statistics（虚拟内存统计）的缩写，可对操作系统的虚拟内存、进程、CPU活动进行监视。它是对系统的整体情况进行统计，不足之处是无法对某个进程进行深入分析。

　　vmstat的语法如下：

　　vmstat [-V] [-n] [delay [count]]

iostat [ -c | -d ] [ -k ] [ -t ] [ -V ] [ -x [ device ] ] [ interval  [ count ] ]

Linux 2.4.18-18smp (builder.linux.com)  2003年03月07日

avg-cpu:  %user   %nice    %sys   %idle
           4.81    0.01    1.03   94.15

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev3-0           30.31      1117.68       846.52   16104536   12197374
dev3-1            7.06       229.61        40.40    3308486     582080

sar [-option] [-o file] t [n]

sar [-option] [-s time] [-e time] [-i sec] [-f file]

sar  -q -u 5 1

Linux 2.4.18-18smp (builder.linux.com) 	2003年03月07日

09时46分16?      CPU     %user     %nice   %system     %idle
09时46分21?      all      0.20      0.00      0.00     99.80

09时46分16?  runq-sz  plist-sz   ldavg-1   ldavg-5
09时46分21?        0        91      0.00      0.00

Average:          CPU     %user     %nice   %system     %idle
Average:          all      0.20      0.00      0.00     99.80

Average:      runq-sz  plist-sz   ldavg-1   ldavg-5
Average:            0        91      0.00      0.00

转自：http://blog.chinaunix.net/u/11498/showart.php?id=99330

数据库 和 数据库实例 ：

数据库 是 有数据文件，控制文件，日志文件组成

数据库实例 是由 MEMERY 和系统进程组成

一个完整的数据库可以有几个数据库实例

exists / in :

exists主要用于片面的,有满足一个条件的即可,
in 主要用于具体的集合操作, 有多少满足条件.

EXISTS检查是否有结果，判断是否有记录，返回的是一个布尔型（TRUE/FALSE）。
IN是对结果值进行比较，判断一个字段是否存在于几个值的范围中，所以 EXISTS 比 IN 快。

in 是对外表和内表 进行HASH JOIN,而exists 是对外表LOOP 然后再进行查询

因此 IN 适合 外表 内表都比较大的情况，exist 适合外表结果集 很小的情况

原问题贴：http://www.itpub.net/288140,1.html

最后结果贴：http://www.itpub.net/288140,6.html

1.知识点：not in/not exists+null

准备：

关于where条件：
X AND Y: 只要X或者Y有一个是FALSE或者null的话，X AND Y返回FALSE。

关于null:
一个NULL值意味着未知，因此，对一个NULL值的任何比较或操作也都是无效的，而任何返回NULL的测试也都被忽视了。
所以：
select 'true' from dual where 1 = null;
select 'true' from dual where 1 != null;
select 'true' from dual where 1 is null;
以上比较不返回任何值。

select 'true' from dual where null is null;
null is null不是比较，而是说null 是个null，所以返回一行。

~~~~~~~~~~~~~~~~
IN：
This comparison operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored.
Also, expressions of the form value NOT IN set yield FALSE if the set contains a null.

in后里面的列表可以包括null，但是因为相当于用=依次比较，然后去or，true or null =true，所以他们是被忽视的(不理睬)。
SQL> select count(*) from usergrade where username in (select username from usertable);

COUNT(*)
----------
1

not in实际上是用!=依次比较列表，然后去and，TRUE AND NULL = NULL。只要列表包括null值，就会返回FALSE(此where条件为FALSE)。
所以，当not in后面是子查询时， 只有保证select后面的字段有not null约束或暗示的时候才能使用。
SQL> select count(*) from usergrade where username not in (select username from usertable);

COUNT(*)
----------
0
为了防止这种情况发生，可以在列表中阻止返回null：
not in (select a from t where a is not null);
not in (select nvl(a,0) from t);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXISTS：
EXISTS(n) returns TRUE if the nth element in a collection exists.
也就是说exists考虑的是(n)返回的行数，而不是值。
SQL> select count(*) from usergrade g where not exists
2 (select null from usertable t where t.userid=g.userid and t.username=g.username);

COUNT(*)
----------
3


2.知识点：in/exists+rownum

Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username and rownum <=1);

rownum <=1在这里作用不大，只要不是rownum<=0即可(子查询返回0行)。后者使得exists(n)一定返回FALSE。


3.知识点：封锁与并发
问题一：只关心session2的变化，因为session1还没有提交，不用理睬；
问题二：查询emp的结果。这个表在T1和T2分别由两个session修改了不同的行，都已经提交，所以看最后结果就好了。
问题三：T4的时候，发生了dept表上的行锁等待。
T5 session2提交，释放锁。使得session1的事务继续。session1 的update操作根据本事务的可视数据（不理睬session2的修改，事务的一致性）修改数据，并提交。
所以，dept表的数据由后来提交的session1决定。



4.知识点：关联更新
答案返回null。
应该改成：
Update student_grade s set s.grade =
(select t.grade from student_makeup t where s.id=t.id)
Where Exists ( Select 1 From student_makeup t Where s.id=t.id)

或者：
Update student_grade s set s.grade =
(select t.grade from student_makeup t where s.id=t.id)
where s.grade<60


5.知识点：DDL与封锁
返回800

DDL可以看成3个步骤：
先commit;
然后ddl语句；
然后commit;

这个实验可以这样理解：
T3的操作因为修改了与T2修改的相同的行，会产生行锁等待。
T4可以理解成上面说的ddl的3个步骤：
先commit，提交T2的delete 100rows，释放锁；
T3操作得到锁，进行delete，并逐个commit；
这个过程，同时也进行truncate，但是由于锁的存在，报错：
ERROR 位于第 1 行:
ORA-00054: 资源正忙，要求指定 NOWAIT

此时，session2的操作也完成了删除100rows，并且都提交。所以一共删除了200行。

代码:

declare
cursor c is select tname from tab ;
l_tname_array dbms_sql.varchar2_table;
begin


open c ;
fetch c bulk collect into l_tname_array ;


for i in 1 .. l_tname_array.count loop


    dbms_output.put_line(l_tname_array(i) );
end loop;
close c;
end;


/






..


..

Bulk Collect的 显式游标 + limit

代码:

declare
cursor c is select tname from tab ;
l_tname_array dbms_sql.varchar2_table;
begin


open c ;
loop


fetch c bulk collect into  l_tname_array limit 10 ;


exit when c%notfound ;


        for i in 1 .. l_tname_array.count loop


                dbms_output.put_line(l_tname_array(i) );


        end loop;
end loop;
close c;
end;


/

1NF： 数据库表中的所有字段都是单一属性，不可再分的。

2NF：数据库表中的非主关键字段对任一主关键字段不存在部分依赖关系。

3NF：在2NF基础上,数据库表中不存在的非关键字段对任一候选关键字段的传递依赖关系

这是必须要把表空间置于备份状态，当前表空间数据是只读，全备的脚本：

set feedback off pagesize 0 heading off verify off linesize 100 trimspool on
define dir ='D:bakOpen'
define fil ='D:bakOpenopen_back_up_commands.sql'
define spo = '&diropen_backup_output.lst'
prompt *** Spooling to &fil
set serveroutput on
spool &fil
prompt spool &spo ;;
prompt archive log list;;
prompt alter system switch logfile;;
declare
cursor cur_tablespace is
select tablespace_name from dba_tablespaces
where status<>'READ ONLY' and contents<>'TEMPORARY';
cursor cur_datafile(tn varchar) is
select file_name from dba_data_files
where tablespace_name = tn;
begin
for ct in cur_tablespace loop
dbms_output.put_line('alter tablespace '||ct.tablespace_name||' begin backup ;');

for cd in cur_datafile (ct.tablespace_name) loop
dbms_output.put_line(' host copy '||cd.file_name||' &dir');
end loop;
dbms_output.put_line('alter tablespace '||ct.tablespace_name||' end backup;');
end loop ;
end;
/
prompt alter system switch logfile;;
prompt alter database backup controlfile to '&dirbackup.ctl' reuse;;
prompt archive log list;;
prompt spool off;;
spool off;
@&fil;

 查看全文

这个是生成冷备份脚本的一个脚本，这里记录下

remark set sql*plus variables to manipulate output
set feedback off heading off verify off trimspool off
set pagesize 0 linesize 200
remark set sql*plus user variables used in this scripe
define dir='D:bak'
define fil ='D:bakclosed_back_up_commands.sql'
prompt *** spooling to &fil
remark cteate a command file with file backup commands
spool &fil
select 'host copy '||name||' &dir' from v$datafile order by 1;
select 'host copy '||member||' &dir' from v$logfile order by 1;
select 'host copy '||name||' &dir' from v$controlfile order by 1;
select 'host copy '||name||' &dir' from v$tempfile order by 1;
spool off;
remark shutdown the database cleanly
shutdown immediate;
remark run the copy file commands from the operating system
@ &fil
remark start the database again
startup ;

1. 存储过程不能使用 truncate,

TRUNCATE 属于DDL 语句。存储过程中不支持DDL 语句

2.触发器不能用commit

在触发器中，不能使用commit / rollback
因为ddl语句具有隐式的commit，所以也不允许使用

3.有外健约素的 不能truncate

先DISENABLE 外键 再TRUNCATE

4.cursor 4个属性 %ISOPEN，%FOUND，%NOTFOUND，%ROWCOUNT

5。主键和唯一索引的区别

a.主键一定是唯一性索引，唯一性索引并不一定就是主键；
b.一个表中可以有多个唯一性索引，但只能有一个主键；
c.主键列不允许空值，而唯一性索引列允许空值。

SQL整体优化和调整学习笔记

1．下图是SQL整体处理过程,SQL的响应时间，主要和系统的db block gets/consistent gets/physical read/sort(memory)/sort(disk)相关。

关于OPTIMIZER_MODE(initSID.ora) 优化模式：

Value Means

CHOOSE

A. 这是OPTIMIZER_MODE参数的缺省值,表示优化器会在CBO和RBO间选择.若(SQL所访问

对象的)统计信息有效,则选择CBO,否则将选RBO.

B. 只要在SQL 所访问的表(一个或多个)的数据字典中,存在一个(或多个)统计,则优化器将会选择

CBO方法,并且以最佳吞吐为目标.

C. 若数据字典中只含有部分统计信息,则仍然用CBO,但优化器需在对象(表、索引等)无统计的情

况下去猜测其统计数据,这会导致次优的执行计划(非最优).

D. 若数据字典中未含SQL所访问表的任何统计,则优化器用RBO.

ALL_ROWS

Optimizer 将使用CBO(不管统计信息是否存在),并且将优化目标定为最佳吞吐(即使用最少的资

源执行完成整个语句).

FIRST_ROWS_n

Optimizer 将使用CBO(不管统计信息是否存在),并且将优化目标定为最快时间(best response time)返回结果的前N(N可以为1,10,100,1000)行.

FIRST_ROWS

Optimizer将综合使用成本和启发(heuristics)来找出最快返回结果第一行的执行计划.

注意: 在使用启发(heuristics)时,有可能会导致CBO 生成成本比不使用启发(heuristics)高得多

的执行计划.FIRST_ROWS这个参数主要是为了用于向后兼容和保持原计划的稳定性的.

RULE

Optimizer将使用RBO而不管统计信息是否存在.

optimizer_mode 和 optimizer_goal

可以使用语句：

alter session set optimizer_mode = FIRST_ROW_10来改变当前SESSION的优化模式。

另外设置 optimizer_goal 也和以上有相同效果，都设置optimizer_goal 将覆盖optimizer_mode参数设置。

也可以在语句中加HINTS来实现语句级别的优化模式改变：
/*+All_ROWS(N)*/ ALL_ROWS/CHOOSE/RULE/FIRST_ROWS

CBO的统计信息

可以用DBMS_STATS或ANALYZE（8I以前）对TABLE/INDEX进行完全或部分采样统计物理存储和数据统计属性。

对8I以后建议用DBMS_STATS进行分析，相比而言，DBMS_STAT可以并行进行统计，并可以对分区信息进行统计

对于以后ANALYZE 主要是进行VALIDATE校验对象结构有效性，使用LIST CHAINED ROWS来分析行链接，收集FREELIST BLOCK信息。

当SQL对象有以下特性时，整个SQL会用到CBO，即使optimizer_mode = rule :

Partitioned tables and indexes

Index-organized tables(IOT)

Reverse key indexes

Function-based indexes

SAMPLE clause in a SELECT statement

Parallel query and parallel DML

Star transformations and star joins

Extensible optimizer

Query rewrite with materialized views

Enterprise Manager progress meter

Hash joins

Bitmap indexes and bitmap join indexes

Index skip scans

理解CBO

CBO 在生成SQL 的计划时,会考虑对象的可访问路径和对象的统计信息,才会决定哪个执行计划是最优的.CBO 还会考虑hints(用户置于语句中的优化建议).

以下是CBO执行的步骤:

A. 基于可用的访问路径和hints, Optimizer为SQL语句生成一个有效的计划集合.

B. Optimizer基于SQL语句所访问对象存在数据字典中的统计(即对象的数据分布和存储特性),来评估每个计划的成本.

所谓成本(Cost)就是执行(某个SQL 的)某种计划估计需要使用到多少资源.Optimizer 评估执行所需用到的计算机资源(包括I/O,CPU和内存),根据结果来计算access paths和join orders的成本.成本高的计划比成本低的执行计划需要更长的执行时间.但在使用一个并行计划时,资源的使用并不能直接反映到执行所需要的时间上去.

CBO结构

CBO包含三个主要组件: Query Transformer / Estimator / Plan Generator

CBO的结果如图：

Query transformer

是将已parse好的查询，这个已经parse好的查询包含子查询块，这些块相互嵌套，相互关联。

Query transformer的目的就是改变查询语句的格式降低执行成本，从而生成更优的执行计划。

View Merging /Predicate Pushing/Subquery Unnesting/Query Rewrite with Materialized Views主要为这四种Query transformer转换技术。

View Merging 对于查询中引用到的视图都被parse成独立的Query block,这些本质对应着对立的视图的定义,产生一个视图,而对于OPTIMIZER来说,最简单的办法是对分析视图的Query block，产生SUBPLAN,在整个EXPLAN PLAN中使用SUBPLAN.但这种方法常产生非最优的计划。

Query transformer将VIEW产生的Query block合并到整个SQL中去，绝大部分视图被合并，对应的视图Query block被合并到整个SQL中.

Predicate Pushing 对于那些未合并的视图,Query transformer会加入一些相关query block 从而使这些未合并的视图的执行计划使用索引或有更高的过滤性。

Subquery Unnesting 将多数子查询转化成JOIN从而解除嵌套，已变生成更优的执行计划。对于未解除嵌套的子查询，将单独生成SUBPLAN。并且为提高所有查询计划的执行速度，subplan将被格式化为更有效的方式。

Query Rewrite with Materialized Views

物化视图就象一个查询连同查询结果一起存在于一个表中.当Query Transformer 发现用户查询语句与物化视图的查询相兼容时,用户的查询SQL就会被用物化视图相关的查询来重写. 用这种技术可以提高用户查询的效率,因为物化视图中已存放了很多的查询结果.Query Transformer 会查找与用户查询兼容的物化视图,若找到的话,就用这些物化视图来重构用户查询. 用物化视图来重写用户查询是基于成本的.也就是说假如基于物化视图重写出来的计划成本高于不用物化视图的成本,则Query Transformer将不会用物化视图.

Estimator

Estimator将生成三种不同的衡量值 Selectivity/Cardinality/Cost 这些是相关的

Selectivity：与查询条件相关，过滤出的行数占总记录数的比率，取值0-1之间。假如统计不可用，则会取默认的值。

Cardinality：相关ROWSET的总记录数。

COST ：使用的相关工作单元和资源。包括磁盘I/O，CPU使用量，内存工作量

Plan Generator 的主要功能就是根据查询生成多种可行的不同计划,并找出其中成本最低的计划。

包括 join order,join method

理解CBO的Access Paths

Access Paths 就是何种方式从数据库中检索出数据：

FULL TABLE SCAN,INDEX TABLE SCAN,ROWID

主要有以下几种：

Full table scans / Sample table scans / rowid scans/ index scans / cluster scans/ hash scans

Full table scans : 将会扫描所有HWM之下的块,并将行数据与where条件相比较。进行全表扫描时，是按顺序读取数据块的，因次一次I/O读取更多的数据块能提高性能，初始参数DB_FILE_MULTIBLOCK_READ_COUNT就可设置一次I/O读多少块.

Optimizer在下面情况下使用全表扫描：

LACK OF ACCESS PATHS 没有任何可以使用的索引。

LARGE AMOUNT OF DATA :Optimizer会访问多数数据块。

SMALL TABLE ：HWM下的块数小于DB_FILE_MULTIBLOCK_READ_COUNT的块数，一次I/O比index range scan 更低。

Old statistics: 表从来没分析过，并且HWM下的块数小于DB_FILE_MULTIBLOCK_READ_COUNT的块数，就认为是小表，走全表扫描，可查看all_tables里的last_anayled 和BLOCKS。

High Degree of Parallelism：当表有很高的并行度时。可查询all_tables里的DEGREE。

Full table scans hint: 全表扫描 /*+Full(tablename)*/

Sample Table Scans : 当查询语句中有sample（占总记录数百分比）或sample block（占总块数百分比）时,将用到 Sample Table Scans .当用到JOIN或远程表时，将不会用Sample Table Scans。

可以先CREATE TABLE AS 然后在引用，也用到CBO。

Rowid Scans ：ROWID对应数据的物理地址，从where字句获的或索引获取是最高效的路径

Index Scans： 从属于索引的一个或几个列值检索出对应行的ROWID或其他索引列的值

Index Unique Scans/ Index Range Scans/ Index Range Scans Descending/ Index Skip Scans/ Full Scans/Fast Full Index Scans/Index Joins/Bitmap Joins

Index Unique Scans: 当sql中的where 条件和表上的Unique约束 或 主键约束 相匹配。/*+Index()*/

Index Range Scans : 当查询条件中含一个或多个索引引导列是。

Index Range Scans Descending：和上相同，但是是按降序排列（缺省是按升续排列）/*+Index_Des()*/

Index skip scan ：当复合索引的前缀列并未用条件上，并且当第一列只有很少重复值，效率才比较高

Index Full Scan ：引用了索引中的某列，可能使用。所需查询列都包含在索引中，索引列至少有一列有NOT NULL 约束,所的结果是按索引排序。只用于CBO。

Index Fast Full Scan : 查询所需列都在索引列中，并不是按索引排序，并行对整个索引做MUTIBLOOCK read, 设置初始参数 OPTIMIZER_FEATURES_ENABLE 或 INDEX_FFS hint ,index fast full Scan 不能用于位图索引。

Index join : 是指将多个索引中的列 通过hash连接在一起。所需的列 全在索引中 无须访问表。Index join 只能用于CBO。 可以设置参数Index join。设置参数OPTIMIZER_FEATURE_ENABLE 或 INDEX_JOIN 这种访问模式。

Bitmap Joins：用于CBO .9I Enterprise Edition可以用。Standard Edition 中无此功能。

Cluster Scans : 用于indexed cluster中，

Hash Scans：用于hash cluster中

Understanding Joins

Nested loop join

Sort merge join

Hash join(这种join模式在RBO中不能使用)

Cluster join

Nested Loop Joins : 当小的结果集需连接且连接条件可高效地访问第二个表时,nested loop join是很有效的./*+ USE_NL(a b)*/

Nested Loop Outer Joins:

Hash Join: Optimizer会在两个表用非等价连接,且满足以下条件之一时使用hash join,需要连接的数据量巨大；或表的大多数需要连接。/*+ USE_HASH(a b)*/

Hash Outer Joins:

Sort Merge Joins：Sort merge joins可用于将两个不相关的sources连接到一起./*+USE_MERGE(a b)*/

Sort Merge Outer Joins:

Cartesian Joins：当多个表间无任何连接条件作连接时,使用的就是Cartesian(笛卡尔) join./*+order+*/

Full Outer Joins：

，

hints 這個東使用的好的話很好用。在进行sql执行过程中，由于有时候系统自动优化的方式并不是最优的。需要我们手工添加hint来提高查询效率。在SQL语句优化过程中，我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法：
1. /*+ALL_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';



3. /*+CHOOSE*/表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
4. /*+RULE*/表明对语句块选择基于规则的优化方法.例如:
SELECT /*+ RULE / EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
5. /FULL(TABLE)*/表明对表选择全局扫描的方法.例如:
SELECT /*FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
6. /*+ROWID(TABLE)*/提示明确表明对指定表根据ROWID进行访问.例如:
SELECT /*+ROWID(BSEMPMS)*/ FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';
7. /CLUSTER(TABLE)*/ 提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如:
SELECT /*CLUSTER / BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /INDEX(TABLE INDEX_NAME)*/表明对表选择索引的扫描方法.例如:
SELECT /*INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS / FROM BSEMPMS WHERE SEX='M';
9. /INDEX_ASC(TABLE INDEX_NAME)*/表明对表选择索引升序的扫描方法.例如:
SELECT /*INDEX_ASC(BSEMPMS PK_BSEMPMS) / FROM BSEMPMS WHERE DPT_NO='SCOTT';
10. /INDEX_COMBINE*/为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:
SELECT /*INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE11. /INDEX_JOIN(TABLE INDEX_NAME)*/提示明确命令优化器使用索引作为访问路径.例如:
SELECT /*INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/表明对表选择索引降序的扫描方法.例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) / FROM BSEMPMS WHERE DPT_NO='SCOTT';
13. /INDEX_FFS(TABLE INDEX_NAME)*/对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:
SELECT /*INDEX_FFS(BSEMPMS IN_EMPNAM)*/ FROM BSEMPMS WHERE DPT_NO='TEC305';
14. /ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/提示明确进行执行规划的选择,将几个单列索引的扫描合起来.例如:
SELECT /*INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';
15. /USE_CONCAT*/对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.例如:
SELECT /*USE_CONCAT*/ FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
16. /NO_EXPAND*/对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.例如:
SELECT /*NO_EXPAND*/ FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
17. /NOWRITE*/禁止对查询块的查询重写操作.
18. /*REWRITE*/可以将视图作为参数.
19. /*+MERGE(TABLE)*/能够对视图的各个查询进行相应的合并.例如:
SELECT /*+MERGE(V) / A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
20. /NO_MERGE(TABLE)*/对于有可合并的视图不再合并.例如:
SELECT /*NO_MERGE(V) / A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /ORDERED*/根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.例如:
SELECT /*ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/将指定的表与其他行源通过合并排序连接方式连接起来.例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /USE_HASH(TABLE)*/将指定的表与其他行源通过哈希连接方式连接起来.例如:
SELECT /*USE_HASH(BSEMPMS,BSDPTMS)*/ FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /DRIVING_SITE(TABLE)*/强制与ORACLE所选择的位置不同的表进行查询执行.例如:
SELECT /*DRIVING_SITE(DEPT)*/ FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /LEADING(TABLE)*/将指定的表作为连接次序中的首表.
27. /*CACHE(TABLE)*/当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) / EMP_NAM FROM BSEMPMS;
28. /NOCACHE(TABLE)*/当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:
SELECT /*FULL(BSEMPMS) NOCAHE(BSEMPMS) / EMP_NAM FROM BSEMPMS;
29. /APPEND*/直接插入到表的最后,可以提高速度.
insert /*append*/ into test1 select from test4 ;
30. /NOAPPEND*/通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*noappend*/ into test1 select * from test4 ;

1.java 存储过程

2 dbms_scheduler 里可以运行操作系统命令

3.dbms_pipe

CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
RETURN INTEGER IS
status NUMBER;
errormsg VARCHAR2(80);
pipe_name VARCHAR2(30);
BEGIN
pipe_name := 'HOST_PIPE';
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message(pipe_name);
RETURN status;
END;
/

BUFFER CACHE 是SGA的重要组成部分，数据的访问和数据修改都是靠它完成。ORACLE 是通过几个链表进行内存管理。通常数据库初始化时，所有的BUF都hash到

LRU list管理，当需要读取数据时，先确定LRU上是否存在（扫描过程会将已修改的记录移动到CHECKPOINT Queue），不存在则在LRU list链表上找到FREE buffer读取,读取BLOCK如果不满足一致性条件，ORACLE ，当BUFF没有足够空间的时候，DBWn将被触发。还有当扫描LRU超过40%时或CHECKPOINT QUEUE超过25%时都会触发DBWn.

当通知DBWn时，进程处于FREE BUSY WAIT。ORACLE8I以后 多了个LRU list 的辅助 list(AUXILIARY RPL_LST) 用于提高管理效率，初始化时BUFF首先放到辅助的list. 使用后放LRU list 上,这样搜索 free buffer 时，就可以从LRU_AUX开始 ，搜索DIRTY BUFFER时可以从LRU list 开始：

可以通过如下命令转储 buffer cache,从而清晰的看到以上描述的数据结构：

alter session set events ‘immediate trace name buffers level 4’;

不同level 转储的内容详细程度不同，详细级别如下：

LEVEL 1: 仅包含Buffer Headers 信息 。

LEVEL 2: 包含Buffer Headers 和 Buffer 概要信息转储

LEVEL 3: 包含 Buffer Headers 和 完整Buffer 内容转储

LEVEL 4：LEVEL 1+ Latch 转储+ LRU 队列。

LEVEL 5：LEVEL 4+BUFFER 概要信息转储

LEVEL 6 和 LEVEL 7: LEVEL 4 + 完整的BUFFER 内容转储

LEVEL 8：LEVEL 4 + 显示users/waiter 信息.

LEVEL 9: LEVEL 5 + 显示users/waiter 信息

LEVEL10：LEVEL6 +显示users/waiter 信息

关于DBMS_SCHEDULER，要执行DBMS_SCHEDULER 至少需要有CREATE JOB权限，新建用户须要GRANT CREATE JOB TO TEST。

这个和DBMS_JOB不同，不用设置INIT.ORA文件进行初始化参数，

DBMS_SCHEDULER PROGRAM支持操作系统命令。

一个调度任务，核心3部分，JOB（作业），SCHEDULER（调度表），PROGRAM（程序）

创建程序块（program）

DBMS_SCHEDULER.CREATE_PROGRAM (

       program_name             IN VARCHAR2,

       program_type             IN VARCHAR2,

       program_action           IN VARCHAR2,

       number_of_arguments      IN PLS_INTEGER DEFAULT 0,

       enabled                  IN BOOLEAN DEFAULT FALSE,

       comments                 IN VARCHAR2 DEFAULT NULL);

例：

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(

PROGRAM_NAME=> ‘TEST.TEST’, --运行程序块名称,系统内必须唯一

PROGRAM_ACTION=>’ d:TEST.SQL’ ,–要运行的程序块，可以是PL/SQL块，也支持OS命令

PROGRAM_TYPE=>’PLSQL_BLOCK’,--运行程序块类型，有3个值

-- PLSQL_BLOCK，当为这个类型时,程序不支持参数 NUMBER_OF_ARGUMENTS 必须为0.

-- stored_procedure 可以为PL/SQL，C，JAVA存储过程，也不支持参数。

-- executable 支持操作系统命

NUMBER_OF_ARGUMENTS=>0,--程序获去的参数，默认是0（0-255）

COMMENTS=>’测试运行’,--说明注释

ENABLED=>TRUE); --这个说明程序是否被创建，默认是FALSE

END;

创建调度表（scheduler）

DBMS_SCHEDULER.CREATE_SCHEDULE (

       schedule_name   IN VARCHAR2,

       start_date        IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,

       repeat_interval   IN VARCHAR2,

       end_date        IN TIMESTAMP WITH TIMEZONE DEFAULT NULL, --调度计划不运行时间

       comments       IN VARCHAR2 DEFAULT NULL);

begin

DBMS_SCHEDULER.CREATE_SCHEDULER(

schedule_name=>’TEST.SCH’, --唯一标识调度表名

start_date => to_timestamp_tz('2004-03-22 US/Eastern', 'YYYY-MM-DD TZR'),

           --表示调度表什么时候被激活，以后的运行时间参考repeat_interval。

Repeate_inteval=>

’FREQ=DAILY;INTERVAL=2;BYHOUR=18;BYMINUTE=0;BYSECOND=0’,

--这里需要说明的是频度，间隔，和时间 这个还可以内置时间间隔可参考后面详细。 

               commets=>’test’ ,--注释

end;

创建调度计划（JOB）：

1．没有现有的调度计划和程序

DBMS_SCHEDULER.CREATE_JOB (

      job_name             IN VARCHAR2,

      job_type              IN VARCHAR2,

      job_action            IN VARCHAR2,

      number_of_arguments  IN PLS_INTEGER              DEFAULT 0,

      start_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,

      repeat_interval        IN VARCHAR2                 DEFAULT NULL,

      end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,

      job_class             IN VARCHAR2      DEFAULT  'DEFAULT_JOB_CLASS',

      enabled              IN BOOLEAN                  DEFAULT FALSE,

      auto_drop            IN BOOLEAN                  DEFAULT TRUE,

      comments            IN VARCHAR2                 DEFAULT NULL);

1． 使用现有的调度计划和程序

DBMS_SCHEDULER.CREATE_JOB (

      job_name           IN VARCHAR2,

      program_name       IN VARCHAR2,

      schedule_name       IN VARCHAR2,

      job_class            IN VARCHAR2  DEFAULT  'DEFAULT_JOB_CLASS',

      enabled                 IN BOOLEAN               DEFAULT FALSE,

      auto_drop               IN BOOLEAN               DEFAULT TRUE,

comments                IN VARCHAR2              DEFAULT NULL);

可以设置DBMS_SCHEDULER的默认限制时间。过了这个限制时间，任务不在运行。

dbms_scheduler.set_attribute（name=>’test.sql’,arrtribute=>’schedule_limited’,’10’）

如果

参考：

http://www.oracle.com/global/cn/oramag/oracle/04-jul/o44tech_dba.html

http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10802/d_sched.htm#1010014

http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10802/d_sched.htm#1010185

DBMS_JOB是制定定时计划任务，而DBMS_SCHEDULE是调度计划，是ORACLE10G新增加的，就当是DBMS的增强版，这里做下总结：

DBMS_JOB包是目录$oraclehome/rdbms/admin/下DBMSJOB.SQL和PRVTJOB.PLB两个脚本创建.

这两个脚本被CATPROC.SQL脚本调用,而CATPROC.SQL在数据库创建后立即执行的DBMS_JOB创建了个公用同义词，并给该包授与了公共的可执行权限，所有的ORACLE用户都可以使用运行这个包。

查询是可查询DBA_JOBS,USER_JOBS,DBA_JOBS_RUNNING.

再者就是 系统参数

JOB_QUEUE_PROCESSES = N (N IN (0…36)之间的参数

JOB_QUEUE_INTERVAL 后台队列（SNP）决定两次检查目录表之间的时间间隔。默认60S

JOB_QUEUE_KEEP_CONNECTIONS （SNP）过程在两次检查间隔时是否保持ORACLE连接。

DBMS_JOB 制定定时计划任务。

a. DBMS_JOB.SUBMIT(JOB OUT BINARY_INTEGER,--系统指定的任务号，唯一的标识任务号

WHAT IN VARCHAR2,-- 执行的pl/sql代码块

NEXT_DATE IN DATE ,--何时运行JOB

INTERVAL IN VARCHAR2,--下次执行的时间

NO_PARSE VARCHAR2,--是JOB提交时是否对代码块进行语法分析，

--true 表示第一次执行是进行语法分析,

-- false每次执行立刻进行语法分析

）

b. DBMS_JOB.ISUBMIT 唯一不同用户指定系统的唯一的标识任务号JOB

c. DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER,--唯一的标识JOB

WHAT IN VARCHAR2,

NEXT_DATE IN DATE,

INTERVAL IN VARCHAR2)

改变指定任务的设置。

d. DBMS_JOB,INTERVAL(JOB IN BINARY_INTEGER,INTERVAL IN VARCHAR2)

显式改变JOB任务的执行时间间隔

e. DBMS_JOB.NEXT_DAY(JOB IN BINARY_INTEGER,NEXT_DATE IN DATE)

显式修改JOB的执行时

f. DBMS_JOB.WHAT(JOB IN BINARY_INTEGER,WHAT IN OUT VARCHAR)

显示修改JOB要执行的PL/SQL块。

g. DBMS_JOB.RUN(JOB IN BINARY_INTEGER) JOB工作执行

h. DBMS_JOB.REMOVE(JOB IN BINARY_INTEGER) 删除一个已计划执行的JOB

i. DBMS_JOB. BROKEN( JOB IN BINARY_INTEGER,

BROKEN IN BOOLEAN, --true任务是否被挂起

NEXT_DATE IN DATE :SYSDATE) –什么时候任务再次执行

j. DBMS_JOB.USER_EXPORT(JOB IN BINARY_INTEGER,MY_CALL IN OUT VARCHAR2)

创建文字字符串，用于重新创建一个任务。

参考：http://www.ideagrace.com/html/doc/2006/11/12/08529.html

折腾了好几天 总算装好了，庆祝下。

这个详细的安装 按这个安装就好，连接如下：

http://oracle.chinaitlab.com/install/534865.html

这里需要说明的是安装完后数据库和监听并不会自动随系统启动，需要自己编写脚本

更改 内核参数后 可以用 /sbin/sysctl -p 刷新参数

还可以查讯 内核参数 值 /sbin/sysctl -a | grep shmmax

更改 环境变量 可以用 source .bash_profile刷新变量

这里附上一些内核参数的意义：

shmmax - 共享内存段，建议设大点， 达到最大SGA
shmmin - 最小的共享内存段.
shmmni - 共享内存标志符的数量.
shmseg - 一个进程可分配的最大内存段数.
shmall - 最大可允许的内存数，比SGA还要大.
semmns - 信号量，跟ORACLE的PROCESS数有关.
semmsl - 一个信号量中最大的信号量数.

可以用EXPLAIN PLAN FOR 语句获取 执行计划，

完了后用 select * from table(DBMS_XPLAN.DISPLAY) 获取执行

计划的详细内容。这里做下记录，老忘记.....

当然查看执行计划还可以用SET AUTOTRACE ON。。

这个要做一定的设置。

如果比较具体的还是 看STATPACK要好点

9i,10gR1以前 for udpate 的锁都是行共享锁(rs)2，10gR2以后变成了行排他锁(rx)3.

详细验证见：http://www.itpub.net/730852.html 有例子

可以在oracle中建立目录对象：

1.create or replace directory tmp as 'D:test';

2.create table all_objects_unload
organization external
( type oracle_datapump
default directory TMP
location( 'allobjects.dat' )
)
as
select
*
from all_objects;

目录下就有个数据泵格式的文件allobjects.dat,

再在 原库上获取all_objects_unload的定义：

select dbms_metadata.get_ddl('TABLE','all_objects_unload') from dual;

获取 表的定义。

就可以在目标库里建立个directort对象,并拷贝文件，在上面建立外部表，从而LOAD进数据：

CREATE TABLE "SYS"."ALL_OBJECTS_UNLOAD"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "TMP"

LOCATION
( 'allobjects.dat'
)
)

AUTOTRACE 可以显示所查询的执行计划。

1.进入[oracle_home]/rdbms/admin;

2.system 登陆系统

3.运行@UTLXPLAN.SQL(建立了一个PLAN_TABLE表)

4.CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE

5.GRANT ALL ON PLAN_TABLE TO PUBLIC

这里注意也可以 把PUBLIC换成某个指定的用户。

同时还要设定其他 一些视图的访问权限。

1.cd [oracle_home]sql_plusadmin

2.connect / as sysdba

3.运行@plustrce;（建立PLUSTRACE角色，对v_$sesstat，v_$statname，v_$mystat 的SELECT权限

grant select on v_$sesstat to plustrace... ）

4.GRANT PLUSTRACE TO PUBLIC;

这里同样可以把PUBLIC换成某个特定用户。