月份：2013年6月

数据库启用了回闪时恢复删除了用户

rman>shutdown
rman>startup mount;
rman>alter database flashback on;
rman>alter database open;

于 2012-02-24 15:25:08删除用户test

drop user test cascade;

使用回闪恢复数据库

rman>shutdown
rman>startup mount;

使用回闪语句

RMAN> flashback database to time "to_date('2012-02-24 15:24:00','yyyy-mm-dd hh24
:mi:ss')";
RMAN> alter database open resetlogs;

数据库已打开

在归档模式下recover database是将数据库恢复到它可以恢复到出现介质故障的时间点,并会应用到这一时间的所有重做日志文件.
--------------
有完全备份和归档重做日志时恢复删除了用户

RMAN> backup database;

启动 backup 于 2012-02-24 16:36:19
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
输入数据文件 fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA01.DBF
输入数据文件 fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA02.DBF
输入数据文件 fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSTEM01.DBF
输入数据文件 fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSAUX01.DBF
输入数据文件 fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\UNDOTBS01.DBF
通道 ORA_DISK_1: 正在启动段 1 于 2012-02-24 16:36:20
通道 ORA_DISK_1: 已完成段 1 于 2012-02-24 16:51:05
段句柄=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP\BACKUPSET\2012_02_24\O1_
MF_NNNDF_TAG20120224T163620_7NGM04K2_.BKP 标记=TAG20120224T163620 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:14:45
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
备份集中包括当前控制文件
在备份集中包含当前的 SPFILE
通道 ORA_DISK_1: 正在启动段 1 于 2012-02-24 16:51:07
通道 ORA_DISK_1: 已完成段 1 于 2012-02-24 16:51:08
段句柄=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP\BACKUPSET\2012_02_24\O1_
MF_NCSNF_TAG20120224T163620_7NGMVVNJ_.BKP 标记=TAG20120224T163620 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03
完成 backup 于 2012-02-24 16:51:08

于 2012-02-24 16:54:08删除用户test

drop user test cascade;

使用不完全恢复恢复数据库

rman>shutdown
rman>startup nomount;
rman>restore controlfile to 'D:\oracle\product\10.2.0\oradata\ocp\control01.ctl';
rman>startup mount;
rman>restore database;
rman>recover database until time '2012-02-24 16:53:00';
rman>alter database open resetlogs;

另一种写法是

RMAN> run
2> {
3> set until time '2012-02-24 16:52:30';
4> restore database;
5> recover database;
6> }

正在执行命令: SET until clause

启动 restore 于 2012-02-24 17:20:36
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始恢复数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集恢复的数据文件
正将数据文件00001恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSTEM01.DBF
正将数据文件00002恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\UNDOTBS01.DBF
正将数据文件00003恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSAUX01.DBF
正将数据文件00004恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA01.DBF
正将数据文件00005恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA02.DBF
通道 ORA_DISK_1: 正在读取备份段 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP
\BACKUPSET\2012_02_24\O1_MF_NNNDF_TAG20120224T163620_7NGM04K2_.BKP
通道 ORA_DISK_1: 已恢复备份段 1
段句柄 = D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP\BACKUPSET\2012_02_24\O
1_MF_NNNDF_TAG20120224T163620_7NGM04K2_.BKP 标记 = TAG20120224T163620
通道 ORA_DISK_1: 恢复完成, 用时: 00:11:57
完成 restore 于 2012-02-24 17:32:38

启动 recover 于 2012-02-24 17:32:49
使用通道 ORA_DISK_1

正在开始介质的恢复
介质恢复完成, 用时: 00:00:15

完成 recover 于 2012-02-24 17:34:42

RMAN> alter database open resetlogs;

数据库已打开

在非归档模式下不完全恢复

RMAN> backup database;

启动 backup 于 2012-02-25 10:11:09
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
输入数据文件 fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA01.DBF
输入数据文件 fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA02.DBF
输入数据文件 fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSTEM01.DBF
输入数据文件 fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\UNDOTBS01.DBF
输入数据文件 fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSAUX01.DBF
通道 ORA_DISK_1: 正在启动段 1 于 2012-02-25 10:11:11
通道 ORA_DISK_1: 已完成段 1 于 2012-02-25 10:26:26
段句柄=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP\BACKUPSET\2012_02_25\O1_
MF_NNNDF_TAG20120225T101110_7NJJSZL2_.BKP 标记=TAG20120225T101110 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:15:15
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
备份集中包括当前控制文件
在备份集中包含当前的 SPFILE
通道 ORA_DISK_1: 正在启动段 1 于 2012-02-25 10:26:28
通道 ORA_DISK_1: 已完成段 1 于 2012-02-25 10:26:29
段句柄=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP\BACKUPSET\2012_02_25\O1_
MF_NCSNF_TAG20120225T101110_7NJKPNL2_.BKP 标记=TAG20120225T101110 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03
完成 backup 于 2012-02-25 10:26:29

RMAN> alter database open;

数据库已打开

于 2012-02-25 11:01:29 删除test用户

rman>shutdown;
rman>startup mount;
RMAN> restore database;

启动 restore 于 2012-02-25 11:11:20
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始恢复数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集恢复的数据文件
正将数据文件00001恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSTEM01.DBF
正将数据文件00002恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\UNDOTBS01.DBF
正将数据文件00003恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\SYSAUX01.DBF
正将数据文件00004恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA01.DBF
正将数据文件00005恢复到D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\HYGEIA02.DBF
通道 ORA_DISK_1: 正在读取备份段 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP
\BACKUPSET\2012_02_25\O1_MF_NNNDF_TAG20120225T101110_7NJJSZL2_.BKP
通道 ORA_DISK_1: 已恢复备份段 1
段句柄 = D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\OCP\BACKUPSET\2012_02_25\O
1_MF_NNNDF_TAG20120225T101110_7NJJSZL2_.BKP 标记 = TAG20120225T101110
通道 ORA_DISK_1: 恢复完成, 用时: 00:12:15
完成 restore 于 2012-02-25 11:23:36

RMAN> recover database until time '2012-02-25 10:30:00';

启动 recover 于 2012-02-25 11:25:16
使用通道 ORA_DISK_1

正在开始介质的恢复
介质恢复完成, 用时: 00:00:03

完成 recover 于 2012-02-25 11:25:31

RMAN> alter database open resetlogs;

数据库已打开

在执行alter database open resetlogs如果报
ORA-01190: 控制文件或数据文件 4 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 4: ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF’
可以设置初始化参数_allow_resetlogs_corruption=true这个参数是允许控制文件与数据文件的scn不一至情况下启动数据库 _allow_terminal_recovery_corruption=ture 允许恢复错误
再执行alter database open resetlogs如果还是报错

CREATE CONTROLFILE   set Database op  Resetlogs
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\oracle\product\10.2.0\oradata\op\RED001.LOG'SIZE 50M,
GROUP 2 'C:\oracle\product\10.2.0\oradata\op\RED002.LOG'SIZE 50M,
GROUP 3 'C:\oracle\product\10.2.0\oradata\opRED003.LOG'SIZE 50M
DATAFILE
'D:\oradata\SYSTEM01.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\UNDOTBS01.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\SYSAUX01.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\USERS01.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\EXAMPLE01.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\HYGEIA01.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\HYGEIA02.DBF',--要恢复的数据所在的dbf文件
'D:\oradata\HYGEIA03.DBF'--要恢复的数据所在的dbf文件
CHARACTER SET ZHS16GBK

再执行

alter database open  resetlogs;

重新创建临时表空间
查看恢复后结果

先执行以下查询语句

select a.program, b.spid, c.sql_text,c.SQL_ID
  from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
   and a.sql_hash_value = c.hash_value
   and a.username is not null;
PROGRAM                         SPID    SQL_TEXT                  SQL_ID
---------------------------------------------------------------- -------------
racgimon@cdcj1 (TNS V1-V3)      12      DECLARE     reason_id    65vuzhm491wk9
oracleRLZY@rx6600-2 (TNS V1-V3) 25867   SELECT "AKB020" FROM "KB 09rp36jmgpj5a
oracleRLZY@rx6600-2 (TNS V1-V3) 25869   SELECT "AAC001","BTC102" 00931052n13uq
oracleRLZY@rx6600-2 (TNS V1-V3) 3018    SELECT "AAC001","BTC102" 00931052n13uq
plsqldev.exe                    26531   select a.program, b.spid 5qq47bz3tzfws
JDBC Thin Client                21074   select * from ( select r 686nqabc8sgs2

如上图中的program列中的jdbc thin client代表的是通过jdbc连接执行的sql语句,由于sql_text列没有显示完整
的sql语句.所以找到sql_id:686nqabc8sgs2再查询v$sql

select a.* from v$sql a where a.SQL_ID='686nqabc8sgs2'

可以查看完整的sql文本内容

监控用户执行过的SQL语句，经常会使用查询V$SQL等视图，但这样查不出来多次执行相同的SQL语句，使用审计可以解决这个问题，这里采用精细审计某个用户的所有表。

用sys用户登记
创建下面的的过程

create or replace procedure auditUser(auditUser in varchar2,oper in int)
is
       policyName varchar2(2000);
       policynum varchar2(20);
       cursor tables is select object_name from dba_objects where wner=auditUser and              object_type='TABLE';
begin

       for tableName in tables loop
           begin
           policynum := 'AUDIT1_';
           policyName := concat(policynum,tableName.Object_Name);
           if per=1 then
           dbms_fga.add_policy(object_schema=>auditUser,object_name=>  tableName.Object_Name,policy_name => policyName,
           statement_types=>'select,insert,update,delete',enable=>true);
           end if;
           if per=0 then
           dbms_fga.drop_policy(object_schema=>auditUser,object_name=>tableName.Object_Name,policy_name => policyName);
           end if;
           exception
              when others then
               NULL;
           end;
       end loop;
       commit;
end;

查询建立的审计策略：

select * from DBA_AUDIT_POLICIES

入参auditUser 表示要审计的用户，oper=1表示审计用户的所有表，oper=0表示删除审计策略
审计完成之后最好删除建立的审计策略。
查询执行过的SQL语句：

select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail order by timestamp desc

系统允许一段时间之后，数据量增加，查询会很慢，可删除表数据

truncate table fga_log$

bulk collect可以将查询结果一次性地加载到collections中，而不用一条一条地处理。
在select into,fetch into,returning into语句使用使用bulk collect时，所有的into变量都必须是collections。

create table jy
(
object_id number(12),
object_name varchar2(20),
object_type varchar2(20)
)

在select into语句中使用bulk collect

declare
type object_list is table of jy.object_name%type;
objs object_list;
begin
select object_name bulk collect
into objs
from jy;
for r in objs.first .. objs.last loop
dbms_output.put_line(''|| objs(r));
end loop;
end;
/

在fetch into中使用bulk collect

declare
type objecttab is table of jy%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from jy;
begin
open cob;
fetch cob bulk collect
into objs;
close cob; -- 把结果集一次fetch到collect中，我们还可以通过limit参数，来分批fetch数据
for r in objs.first .. objs.last loop
dbms_output.put_line(' ' || objs(r).object_name);
end loop;
end;



declare
type objecttab is table of jy%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from jy;
begin
open cob;
loop
fetch cob bulk collect
into objs limit 100;--每次取一百条数据这是可以根据你的数据库性能来决定的
exit when cob%notfound;
dbms_output.put_line('count:' || objs.count || ' first:' || objs.first ||
' last:' || objs.last);
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end loop;
close cob;
end;

在returning into中使用bulk collect

declare
type id_list is table of jy.object_id%type;
ids id_list;
type name_list is table of jy.object_name%type;
names name_list;
begin
delete from jy  returning object_id, object_name bulk collect into ids,
names;
dbms_output.put_line('deleted ' || sql%rowcount || ' rows:');
for i in ids.first .. ids.last loop
dbms_output.put_line('object #' || ids(i) || ': ' || names(i));
end loop;
end;

ORACLE批量绑定FORALL与BULK COLLECT
FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。

create table test_forall ( user_id number(10), user_name varchar2(20));

select into 中使用bulk collect

DECLARE
  TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
  v_table table_forall;
BEGIN
    SELECT mub.user_id,mub.user_name
         BULK COLLECT INTO v_table
    FROM mag_user_basic mub
         WHERE mub.user_id BETWEEN 10000 AND 10100;
    FORALL idx IN 1..v_table.COUNT
           INSERT INTO test_forall VALUES v_table(idx);
           --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
           --在PL/SQL中，BULK In-BIND与RECORD，%ROWTYPE是不能在一块使用的，
           --也就是说，BULK In-BIND只能与简单类型的数组一块使用
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

END;

fetch into 中使用bulk collect

DECLARE
  TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
  v_table table_forall;

  CURSOR c1 IS
    SELECT mub.user_id,mub.user_name
         FROM mag_user_basic mub
           WHERE mub.user_id BETWEEN 10000 AND 10100;
BEGIN
   OPEN c1;
   --在fetch into中使用bulk collect
   FETCH c1 BULK COLLECT INTO v_table;

   FORALL idx IN 1..v_table.COUNT
         INSERT INTO test_forall VALUES v_table(idx);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;

在returning into中使用bulk collect

CREATE TABLE test_forall2 AS SELECT * FROM test_forall;

—-在returning into中使用bulk collect

DECLARE
   TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;
   enums IdList;
   TYPE NameList IS TABLE OF test_forall.user_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM test_forall2 WHERE user_id = 10100
        RETURNING user_id, user_name BULK COLLECT INTO enums, names;
   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
     dbms_output.put_line('User #' || enums(i) || ': ' || names(i));
   END LOOP;
   COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

END;

–批量更新中，将for改成forall

DECLARE
    TYPE NumList IS VARRAY(20) OF NUMBER;
    depts NumList := NumList(10, 30, 70, ...);
 -- department numbers
     BEGIN
    
       /*FOR i IN depts.FIRST..depts.LAST
       LOOP
       ...
       --UPDATE statement is sent to the SQL engine
       -- with each iteration of the FOR loop!
         UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
       END LOOP:
      */
       FORALL i IN depts.FIRST..depts.LAST
        UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
       commit;
    END;

oracle更改默认端口号，监听器加密，限制ip访问

oracle数据库的安全包含两部分：
1.一部分是os的安全
2.网络的安全
2.oracle软件本身的安全
os的安全依赖相应的操作系统及管理员的技术水平，我们这里只谈谈oracle的安全
我了解的oracle软件的安全包含：
1.更改oracle的默认监听端口号
2.给监听器加密码
3.oracle控制ip的连接
下面将针对这三个方面测试学习

———oracle默认监听端口更改————–

1. 更改oracle的默认监听端口号
修改端口号的整体步骤
1.1 。 查看当前监听的状态
1.2 。 停止监听
1.3 。 修改监听文件的端口号
1.4 。 修改初始化参数local_listener
1.5 . 重启监听器
1.6 。 修改完毕，使用新端口登录测试
实践步骤：
1.1 。 查看当前监听的状态

C:\Documents and Settings\skate_db>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-7月 -2008 12:1
1:19
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 19-7月 -2008 11:47:59
正常运行时间 0 天 0 小时 23 分 19 秒
跟踪级别 off
安全性 ON: Password or Local OS Authentication
SNMP OFF
监听程序参数文件 E:\oracle\product\10.2.0\db_3\network\admin\listener.o
ra
监听程序日志文件 E:\oracle\product\10.2.0\db_3\network\log\listener.log
监听端点概要...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=skate)(PORT=1522)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
 例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "orcl" 包含 1 个例程。
 例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orclXDB" 包含 1 个例程。
 例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orcl_XPT" 包含 1 个例程。
 例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

1.2 。 停止监听

C:\Documents and Settings\skate_db>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-7月 -2008 13:1
6:15
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
命令执行成功

1.3 。 修改监听文件的端口号，用于网络连接
把端口号修改为1523

# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_3\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = E:\oracle\product\10.2.0\db_3)
 (PROGRAM = extproc)
 )
 )
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
 (ADDRESS = (PROTOCOL = TCP)(HOST = skate)(PORT = 1523))
 )
 )
#----ADDED BY TNSLSNR 12-7月 -2008 13:26:50---
PASSWORDS_LISTENER = 3650F1EB3C37ABD9
#---------------------------------------------

1.4 。 修改初始化参数local_listener ，用于本地连接

C:\Documents and Settings\skate_db>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 19 13:24:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=s
 kate)(port=1522))
SQL> alter system set local_listener="(address=(protocol=tcp)(host=skate)(port=1
523))";
系统已更改。

1.5 . 重启监听器

C:\Documents and Settings\skate_db>lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-7月 -2008 13:2
7:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
启动tnslsnr: 请稍候...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
系统参数文件为E:\oracle\product\10.2.0\db_3\network\admin\listener.ora
写入E:\oracle\product\10.2.0\db_3\network\log\listener.log的日志信息
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=skate)(PORT=1523)))
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 19-7月 -2008 13:27:47
正常运行时间 0 天 0 小时 0 分 3 秒
跟踪级别 off
安全性 ON: Password or Local OS Authentication
SNMP OFF
监听程序参数文件 E:\oracle\product\10.2.0\db_3\network\admin\listener.o
ra
监听程序日志文件 E:\oracle\product\10.2.0\db_3\network\log\listener.log
监听端点概要...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=skate)(PORT=1523)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
 例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功

1.6 。 修改完毕，使用新端口登录测试
1.6.1。 检查监听端口是否改变

C:\Documents and Settings\skate_db>netstat -an | find "1523"
 TCP 0.0.0.0:1523 0.0.0.0:0 LISTENING
 TCP 192.168.0.103:1523 192.168.0.103:2389 ESTABLISHED
 TCP 192.168.0.103:2389 192.168.0.103:1523 ESTABLISHED
 

1.6.2。 查看监听器的状态

C:\Documents and Settings\skate_db>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-7月 -2008 13:2
8:56
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 19-7月 -2008 13:27:47
正常运行时间 0 天 0 小时 1 分 10 秒
跟踪级别 off
安全性 ON: Password or Local OS Authentication
SNMP OFF
监听程序参数文件 E:\oracle\product\10.2.0\db_3\network\admin\listener.o
ra
监听程序日志文件 E:\oracle\product\10.2.0\db_3\network\log\listener.log
监听端点概要...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=skate)(PORT=1523)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
 例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "orcl" 包含 1 个例程。
 例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orclXDB" 包含 1 个例程。
 例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orcl_XPT" 包含 1 个例程。
 例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功
1.6.3 。使用新端口号（1523）登录测试一下

C:\Documents and Settings\skate_db>sqlplus
SQL*Plus: Release 10.2.0.1.0 – Production on 星期六 7月 19 13:32:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
请输入用户名:sys/oracle@skate:1523/orclas sysdba
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL> select open_mode from v$database;
OPEN_MODE
———-
READ WRITE
SQL>

注意：当修改了oracle默认监听端口后，用tnsping会出错的，因为这个时候，oracle会
读tnsnames。ora这个文件，而这个文件的端口号没有更改，只要更改下这里对应的
端口号就ok。

C:\Documents and Settings\skate_db>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-7月 -
2008 17:11:05
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
E:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = skate)(P
ORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
TNS-12541: TNS: 无监听程序

在这里可以看到，oracle仍然用端口1521，我们更改下tnsnames.ora的端口号，就ok了。

# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_3\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = skate)(PORT = 1523))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = orcl)
 )
 )
EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
 )
 (CONNECT_DATA =
 (SID = PLSExtProc)
 (PRESENTATION = RO)
 )
 )

重启监听器，在tnsping

C:\Documents and Settings\skate_db>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-7月 -
2008 17:22:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
E:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = skate)(P
ORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (50 毫秒)

———oracle默认监听端口更改————–

××××××××××××××××××××××××××oracle控制ip的连接××××××××××××××××××××××××
2 .oracle控制ip的连接（我们也可以用数据库触发器记录用户的登录情况，但是不能记录dba权限的用户）
单纯的设定允许的IP 和 禁止的IP，在oracle9i以前有文档说增加或修改protocol.ora文件，
在9i及以后版本中真正起作用的是sqlnet.ora文件，我们修改sqlnet.ora其实是最好最快的方法

sqlnet.ora文件的功能：
1. Specify the client domain to append to unqualified names
2. Prioritize naming methods
3. Enable logging and tracing features
4. Route connections through specific processes
5. Configure parameters for external naming
6. Configure Oracle Advanced Security
7. Use protocol-specific parameters to restrict access to the database
我在这里用的就是第7个功能

在sqlnet.ora中增加如下部分
—————————–

tcp.validnode_checking=yes

#允许访问的IP
tcp.invited_nodes=(ip1,ip2……)

#禁止访问的IP
tcp.excluded_nodes=(ip1,ip2……)

之后重新启动监听器即可

# sqlnet.ora Network Configuration File: E:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#skate modify
tcp.validnode_checking=yes
tcp.invited_nodes=(192.168.0.103)
tcp.excluded_nodes=(192.168.0.10)

需要注意的问题：
1、 需要设置参数为YES,这样才能激活。
2、 建议设置允许访问的IP，因为IP地址有可能被随意修改，就不能起到自己的目的。
3、 TCP当参数TCP.INVITED_NODES和TCP.EXCLUDED_NODES设置的地址相同的时候将覆盖TCP.EXCLUDED_NODES设置。
4、 需要重启监听器才能生效。
5、 这个方式只是适合TCP协议。
6、 这个配置适用于9i以上版本。在9i之前的版本使用文件protocol.ora。
7、 在服务器上直接连接数据库不受影响。
8、 这种限制方式事通过监听器来限制的。
9、 这个限制只是针对IP检测，对于用户名检测事不支持的。

××××××××××××××××××××××××××oracle控制ip的连接××××××××××××××××××××××××

3. 给监听器加密码
Oracle的监听器一直以来都存在一个严重的安全问题，那就是：
如果不设置安全措施，那么能够访问的用户就可以远程关闭监听器

1.设置监听器密码
2.更改监听器密码

1.设置监听器密码

[oracle@jumper log]$ lsnrctl

C:/Documents and Settings/skate_db>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-10月-2008 19:18
:06

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

欢迎来到LSNRCTL，请键入"help"以获得信息。

LSNRCTL> set current_listener listener
Current Listener is listener
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.11)(PORT=1521)))
Password changed for listener
The command completed successfully
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.11)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Old Parameter File /opt/oracle/product/9.2.0/network/admin/listener.bak
The command completed successfully

—到此监听器已经设置好了密码，下面就测试下

LSNRCTL> service
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-01169: 监听器尚未识别口令

LSNRCTL> set password
Password:
命令执行成功

LSNRCTL> service
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
 例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
 处理程序:
 "DEDICATED" 已建立:0 已被拒绝:0
 LOCAL SERVER
服务 "orcl9i" 包含 2 个例程。
 例程 "orcl9i", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
 处理程序:
 "DEDICATED" 已建立:0 已被拒绝:0
 LOCAL SERVER
 例程 "orcl9i", 状态 READY, 包含此服务的 1 个处理程序...
 处理程序:
 "DEDICATED" 已建立:0 已拒绝:0 状态:ready
 LOCAL SERVER
服务 "orcl9iXDB" 包含 1 个例程。
 例程 "orcl9i", 状态 READY, 包含此服务的 1 个处理程序...
 处理程序:
 "D000" 已建立:0 已被拒绝:0 当前: 0 最大: 1002 状态: ready
 DISPATCHER 
 (ADDRESS=(PROTOCOL=tcp)(HOST=skate)(PORT=3939))
命令执行成功

2.更改监听器密码

C:/Documents and Settings/skate_db>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-10月-2008 19:18
:06

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

欢迎来到LSNRCTL，请键入”help”以获得信息。

A。

要更改监听器密码，先输入原密码以便更改成功

LSNRCTL> set password
Password:
命令执行成功

B。

更改新密码

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
LISTENER的口令已更改
命令执行成功
LSNRCTL>

C.
新密码起作用，输入新密码后才能运行save_config起作用

LSNRCTL> set password
Password:
命令执行成功

D.
保存更改

LSNRCTL> save_config
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
保存的LISTENER配置参数。
监听器参数文件 E:/oracle9i/product/9.2.0.1/db_4/network/admin/listener.
ora
旧的参数文件E:/oracle9i/product/9.2.0.1/db_4/network/admin/listener.bak
命令执行成功
LSNRCTL>

(1)查看用户的连接状况

select username,sid,serial# from v$session
------------------------------------------
如下结果：
username sid serial#
----------------------------------------
NETBNEW 513 22974
NETBNEW 514 18183
NETBNEW 516 21573
NETBNEW 531 9
test 532 4562

找到要删除用户的sid,和serial，并删除
——————————————-
你要删除用户’test’,可以这样做：

alter system kill session'532,4562'

(3)删除用户
——————————————–

drop user username cascade

如果在drop 后还提示ORA-01940:无法删除当前已链接的用户，说明还有连接的session，可以通过查看session的状态来确定该session是否被kill 了，用如下语句查看：
————————————-

select saddr,sid,serial#,paddr,username,status from v$session where username is not null

saddr sid serial# paddr username status
--------------------------------------------------------------------------------------------------------

564B8184 532 4562 56A1075C test KILLED

status 为要删除用户的session状态，如果还为inactive，说明没有被kill掉，如果状态为killed，说明已kill。
继续使用

alter system kill session'532,4562'

来杀掉这个会话

回收hygeia用户的表空间权限

 revoke unlimited tablespace from hygeia;
 

不让hygeia用户获得user表空间的配额

alter user hygeia  quota 0 on users;

授予hygeia用户你所想要存储数据的表空间的使用权限

 alter user  hygeia  quota unlimited on cdcj;

索引如何指定表空间．在IMP时候使用INDEXFILE参数据例如C:\>imp insur_test/test@ybcs file=F:\cj.dmp indexfile=f:\cjindex.sql fromuser=simis_city touser=insur_test
这样会生成一个cjindex.sql文件中里有创建表结构和索引的语句.而不会将数据导入数据库中.在cjindex.sql将创建表的语句删除修改创建索引的语句将你索引的表空间指定为你想要的表空间
再执行导入数据使用indexes=n不导入索引
如:

C:\>imp insur_test/test@ybcs file=F:\cj.dmp indexes=n fromuser=simis_city touser=insur_test

当导入完后
再执行cjindex.sql文件创建索引的语句

C:>@F:\cjindex.sql;

写动态sql，下面是删表的例子
一:

spool droptable.sql
select 'drop table '||table_name||';' from user_tables;
spool off
start droptable.sql

二:

create or replace procedure pro_droptable is
cursor cur is select table_name from user_tables;
drop_sql varchar2(1000);
begin
  for tbname in cur loop
    begin
      drop_sql:='drop table '||tbname.table_name;
      execute immediate drop_sql;
    end;
  end loop;
end pro_droptable;

手动创建数据库（windows)

1.先创建口令文件

C:\Documents and Settings\Administrator>orapwd file=d:/oracle/product/10.2.0/db_
1/database/orapwdtest.ora password=admin entries=10

2.准备参数文件
从另一个数据库复制一个参数文件进行修改

3.启动实例

startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\inittest.ora'

4.创建数据库

CREATE DATABASE test
LOGFILE
GROUP 1 ('D:\oracle\product\10.2.0\oradata\test\redo01.log') SIZE 100M,
GROUP 2 ('D:\oracle\product\10.2.0\oradata\test\redo02.log') SIZE 100M,
GROUP 3 ('D:\oracle\product\10.2.0\oradata\test\redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE 'D:\oracle\product\10.2.0\oradata\test\system01.dbf' SIZE 325M
extent management local
sysaux datafile 'D:\oracle\product\10.2.0\oradata\test\sysaux01.dbf' SIZE 325M
undo tablespace undotbs1 DATAFILE
'D:\oracle\product\10.2.0\oradata\test\undotbs01.dbf' SIZE 200M
default temporary tablespace temp  tempfile
'D:\oracle\product\10.2.0\oradata\test\temp01.dbf' SIZE 325M
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16

5.运行脚本。
– 创建数据库后必须运行两个脚本：catalog.sql 和catproc.sql。这两个脚
本都必须以具有SYSDBA 权限的用户SYS 身份运行。执行脚本前，数据库必须
处于OPEN 状态。
– catalog.sql：在基表和动态性能视图上创建视图及其同义词。它还启动其它
脚本，为以下各项创建对象：
PL/SQL 基本环境，包括PL/SQL 数据类型的声明、预定义异常、内置过程
和函数、SQL 操作等
审计
导入/导出
SQL*Loader
已安装选项

catproc.sql：创建使用PL/SQL 所需的程序包和过程。此外，此脚本还创建
用于扩展RDBMS 功能的若干PL/SQL 程序包，以及用于预警、管道、logminer、
大对象、对象、排队、复制和其它内置选项的程序包视图。
– pupbld.sql：创建名为“产品用户配置文件” (Product User Profile) 的
表以及相关的过程。运行此脚本将在用户每次连接到SQL*Plus 时防止生成警告
消息。
注：必须以用户SYSTEM 的身份运行此脚本。运行脚本。
– 创建数据库后必须运行两个脚本：catalog.sql 和catproc.sql。这两个脚
本都必须以具有SYSDBA 权限的用户SYS 身份运行。执行脚本前，数据库必须
处于OPEN 状态。

使用sql trace工具和tkprof

1.给跟踪文件设置初始化参数
2.对你所有跟踪的会话和程序启用sql trace功能.这个步骤会将这个程序使用的sql语句的统计输出到一个跟踪文件中.
3.运行tkprof来翻译第二步输出的跟踪文件成为一个可读的文件袋.这一步也可以创建一个sql脚本在数据库中存储统计
4.解释说明第三步生成的输出文件.
5.可以在第三步运行一个sql脚本将统计存储在数据库

给跟踪文件设置初始化参数
当对会话启用sql trace功能后,oracle会对这个会话跟踪的sql语句生成一个跟踪文件.跟踪文件包括统计信息.当对整个实例启用sql trace时,oracle会对每一个进程生成一个跟踪文件.在启用sql trace功能前需要做以下检查.
1.检查timed_statistics,max_dump_file_size和user_dump_dest初始化参数
timed_statistics:这个参数是用来是否启用或禁用收集时间统计,象cpu和运行时间,通过sql trace功能也能收集动态性能表中的各 种统计.这个参数缺省值是false禁用收集时间统计.当为true时启用时间统计收集,启用时间统计收集会对低级别的操作造成额外的调 用时间,这是一个动态参数.也是一个会话级别的参数.

max_dump_file_size:当在实例级别启用sql trace功能时每一个调用都会生成一个操作系统文本格式的文本文件.这些文件的大小是 由这个参数的参数值决定的.这个参数缺省值是500,如果你发现一个跟踪输出被截段了,那么在生成另一个跟踪文件之前可以增加这个 参数值.这是一个动态参数，也是一会话级别的参数.

user_dump_dest:这个参数是给跟踪文件指定一个输出目录.这个参数的缺省值是系统目录.这个参数可以使用alter system set user_dump_dest=newdir语句来修改，这是一个动态参数,也是一个会话级别的参数.

设计一种方法来识别输出的跟踪文件
要确定你知道怎样通过名字来识别跟踪文件.oracle通过user_dump_dest参数来写这些跟踪文件到你指定的这个目录下.然而这个目录 下会很快就会使用生成的名字产生几百个跟踪文件,使用这些跟踪文件很难找到他建军这些跟踪文件和会话或过程.在你的程序中可以 使用象select ‘program_name’ from dual的语句加入到跟踪文件中.那么就能跟踪这些文件是由哪个过程创建的.
你也能设置tracefile_identifier初始化参数来指定一个客户标识符作业跟踪文件名的一部分.例如,可以简单加一个my_trace_id到 这个跟踪文件名进行简单的识别.

alter session set tracefile_identifier='my_trace_id';

如果操作系统保留的多个版本的文件,那么要确保版本限制高足以容纳你所期望sql trace所生成的跟踪文件数据量.

生成的跟踪文件除了你自己还可能由一个操作系统用户进行操作.在你使用tkprof来格式化它前之前这个操作系统用户必须确保你能 使用这些跟踪文件.

启用sql trace功能
对会话启用sql trace可以使用下面的任何一种方式来执行
1.dbms_session.set_sql_trace
2.alter session set sql_trace=true;
注意:因为运行sql trace功能会增加系统开销，仅仅只有当调整sql语句时才启用sql trace,当调整完以后禁用sql trace建议使用 dbms_session或dbms_monitor包来对会话或实例启用sql跟踪来代替alter 语句.
为了对会话禁用sql trace可以执行以下语句

alter session set sql_trace=false;

当应用程序从oracle断开后对这个会话的sql trace也会自动禁用

也能通过设置sql_trace初始化参数来对整个实例来启用sql trace功能.
sql_trace=true
在更新这个参数后实例要重新启动.当实例启用sql trace会收集所有会话的统计.可以设置sql_trace=false来禁用sql trace

使用tkprof来格式化跟踪文件
tkprof能访问通过sql trace功能产生的跟踪文件生成一个格式的输出文件.tkprof也能用来生成执行计划.
在sql trace功能已经生成了一些跟踪文件夹后你可以执行以下操作
1.在每个单独的跟踪文件上执行tkprof,为每一个会话的生成一个格式输出文件.
2.能联合所有的跟踪文件，运行tkprof对整个实例生成一个格式化输出文件.
3.运行trcsess命令行工具来联合多个跟踪文件的信息然后运行tkprof生成格式化的结果.
在跟踪文件中tkprof不支持commit和rollback命令.

tkprof格式化输出的例子如下

select c.*,
       (select d.corp_join_no
          from bs_corp_insure d
         where d.corp_id = b.corp_id
           and d.insr_detail_code = c.insr_detail_code) corp_join_no_dw
  from bs_corp_pres b, bs_pres_insur c
 where b.indi_id = c.indi_id
   and b.corp_id = c.corp_id and b.corp_id=:"SYS_B_0"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         17          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0         17          0           2

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID BS_CORP_INSURE (cr=6 pr=0 pw=0 time=40 us)
      2   INDEX RANGE SCAN IDX_BS_CORP_INSURE_1 (cr=4 pr=0 pw=0 time=26 us)(object id 230781)
      2  TABLE ACCESS BY INDEX ROWID BS_PRES_INSUR (cr=11 pr=0 pw=0 time=143 us)
      4   NESTED LOOPS  (cr=9 pr=0 pw=0 time=307 us)
      1    INDEX RANGE SCAN PK_BS_CORP_PRES (cr=3 pr=0 pw=0 time=44 us)(object id 230787)
      2    BITMAP CONVERSION TO ROWIDS (cr=6 pr=0 pw=0 time=66 us)
      1     BITMAP AND  (cr=6 pr=0 pw=0 time=63 us)
      1      BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=28 us)
      2       INDEX RANGE SCAN INDX_BS_PRES_INSUR_INDI_ID (cr=3 pr=0 pw=0 time=13 us)(object id 231131)
      1      BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=21 us)
      2       INDEX RANGE SCAN INDX_BS_PRES_INSUR_CORP_ID (cr=3 pr=0 pw=0 time=16 us)(object id 231129)

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           3
Fetch        2      0.00       0.00          0         17          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.00          0         17          0           6

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    5  user  SQL statements in session.
    0  internal SQL statements in session.
    5  SQL statements in session.
********************************************************************************
Trace file: /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.trc
Trace file compatibility: 10.01.00
Sort options: default

       0  session in tracefile.
       5  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       5  SQL statements in trace file.
       3  unique SQL statements in trace file.
      57  lines in trace file.
      39  elapsed seconds in trace file.

对于这个语句,tkprof的输出包括以下信息
1.sql语句的文本
2.表格形式的sql跟踪统计
3.解析和执行这个语句库缓存丢失的情况
4.调用解析该语句的用户
5.通过explain plan生成的执行计划.

tkprof还提供了用户级别语句和递归语句调用的摘要信息.

使用tkprof的语法
tkprof是在操作系统命令提示下执行的,它的语法如下
tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

在上面的语法中只有filename1(输入文件名)和filename2输出文件名是必须指定的.
tkprof参数如下
filename1:指定输入文件(由sql trace功能生成的包括了统计的文件),这个文件可以是一个跟踪文件,也可以是多个会话的跟踪文件 合并成的一个文件.
filename2:指定tkprof格式化的输出文件名
waits:指示在这个跟踪文件中是否有任何等待事件的记录摘要，这参数的值有yes或no 缺省的是yes.
sorts:在将格式化的内容写入输出文件之前可以指定排序选项将跟踪的语句按降序排列.如果排序选项指定了多个值，那么就会按这 些排序项的合计值来进行降序排序.如果你没有指定这个参数，那么tkprof会按输出文件中的列出的参数顺序来排序,下面是排序选项 的列表.
[
1:prscnt 解析时间
2:prscpu 解析使用的cpu时间
3:prsela 解析运行时间
4:prsdsk 在解析时从磁盘执行的物理读取数
5:prsqry 在解析时一至读取数据块数
6:prscu 在解析时当前模式下数据块读取数
7:prsmis 在解析时库缓存丢失数
8:execnt 执行次数
9:exeela 执行时间
10:exedsk 执行时从磁盘执行物理读取数
11:exeqry 执行时一至读取数据块数
12:execu 执行时当前模式下读取数据块数
13:exerow 执行时处理的行数
14:exemis 执行时库缓存丢失数
15:fchcnt 获取数据数
16:fchcpu 获取数据花费的cpu时间
17:fchela 获取数据花费的运行时间
18:fchdsk 获取数据时从磁盘读取数据数
19:fchqry 获取数据时一至性读取数据块数
20:fchcu 获取数据时当前模式下读取数据块数
21:fchrow 获取数据的行数
22:userid 解析用户游标的用户ID
]
print:只从输出文件中显示排序后的第一个sql语句,如果没有指定这个参数,那么tkprof将会显示出所有的sql语句.这个参数不会影 响可选的sql脚本.这个脚本总是会生成所有跟踪的sql语句的数据.
aggregate:如果指定aggregate=no时，那么tkprof不会对相同的sql文本的多个用户进行聚合操作.
insert:在数据库中创建一个脚本来存储这个跟踪文件的统计.tkprof使用name filename3来创建这个脚本.这个脚本将会创建一个表 并向这个表中给每一个跟踪的sql语句插入一条统计记录.
sys:通过用户sys来启用或禁用是否将sql语句或递归sql语句写入到输出文件中.这个缺省值是yes，那么tkprof会将所有的sql语句写 入到输出文件中.如果为no时那么tkprof将会忽略它们.这个参数不会影响可选的脚本,这个sql脚本总是会插入所有跟踪sql语句的统 计包括递归sql语句.
table:指定表的方案和表名那么tkprof在将它们写入到输出文件中之前会将这个执行计划临时存储在这个表中.如果指定的表已经存 在了,那么tkprof删除表中的所有记录.使用它来explain plan 解析语句.然后删除这些行.如果这个表不存在,那么tkprof创建表,然 后删除表.

指定的用户必须能够对这个表使用insert,select和delete语句.如果这个表不存在,那么用户必须能使用create table 和drop table 语句.
这个选项允许在explain时多个个同时使用相同的用户运行tkprof,这些个人能指定不同的表名在临时存储计划表时避免各个用户相互 影响.
如果使用explain参数时没有指定table参数,那么tkprof使用prof$plan_table.如果使用table参数没有使用explain参数时，那么 tkprof会忽略这个table参数
如果没有执行计划表存在,tkprof会创建一个prof$plan_table在最后删除它.

explain:在跟踪文件中判断每一个sql语句的执行计划并将这些执行计划写到输出文件中.tkprof判断每一个sql语句的执行计划是通 过指定用户名和密码参数连接到oracle数据库后使用explain plan语句来生成的.指定的用户必须有create session系统权限.tkprof 如果使用explain选项时能长时间的处理一个很大的跟踪文件.

record:使用指定的filename4这个参数创建一个sql脚本文件将所有的不是递归的sql语句写入到跟踪文件中.从跟踪文件中回放用户事件.
width:一个整数值用来控制某些tkprof输出的宽度象执行计划.这个参数是用来后期处理tkprof的输出的.

例子
一.先设置timed_statistics,max_dump_file_size和user_dump_dest初始化参数
timed_statistics=true
max_dump_file_size=UNLIMITED
user_dump_dest=/oracle/admin/RLZY/udump
二.启用会话跟踪:

alter session set sql_trace=true

三.给生成的跟踪文件加入标识符用于与区分其它的文件:

alter session set tracefile_identifier='my_trace_id';

四.在这个会话中执行查询语句

select c.*,
       (select d.corp_join_no
          from bs_corp_insure d
         where d.corp_id = b.corp_id
           and d.insr_detail_code = c.insr_detail_code) corp_join_no_dw
  from bs_corp_pres b, bs_pres_insur c
 where b.indi_id = c.indi_id
   and b.corp_id = c.corp_id and b.corp_id=349;

 INDI_JOIN_NO INSR_DETAIL_CODE       CORP_ID  CORP_JOIN_NO       INDI_ID BEGIN_DATE  END_DATE    ALI_PAY_MONS TO_PAY_MONS FAC_PAY_MONS IF_INDI_ACCO HANDLE_MAN           HANDLE_DATE INDI_JOIN_STA LOST_PAY_SUM PAY_MODE_CODE TRANALI_PAY_MONS INDI_CHARGE_STA LOST_ENJOY_SUM DEAL_BEGIN_DATE FREEZE_STA FREEZE_REASON                                                                    TOPAY_BEGIN_PRD LACK_MONTHS MOD_TIMESTAMP                                                                    CORP_JOIN_NO_DW
------------- ---------------- ------------- ------------- ------------- ----------- ----------- ------------ ----------- ------------ ------------ -------------------- ----------- ------------- ------------ ------------- ---------------- --------------- -------------- --------------- ---------- -------------------------------------------------------------------------------- --------------- ----------- -------------------------------------------------------------------------------- ---------------
       121255                3           349          2484         12338 2009-11-1                                                                1 转数据               2011-4-8 10             1                                                           1                                         0                                                                                                                                                                                                          2484
        12323                2           349           593         12338 2009-11-1       and b.corp_id = c.corp_id and b.corp_id=349;

 INDI_JOIN_NO INSR_DETAIL_CODE       CORP_ID  CORP_JOIN_NO       INDI_ID BEGIN_DATE  END_DATE    ALI_PAY_MONS TO_PAY_MONS FAC_PAY_MONS IF_INDI_ACCO HANDLE_MAN           HANDLE_DATE INDI_JOIN_STA LOST_PAY_SUM PAY_MODE_CODE TRANALI_PAY_MONS INDI_CHARGE_STA LOST_ENJOY_SUM DEAL_BEGIN_DATE FREEZE_STA FREEZE_REASON                                                                    TOPAY_BEGIN_PRD LACK_MONTHS MOD_TIMESTAMP                                                                    CORP_JOIN_NO_DW
------------- ---------------- ------------- ------------- ------------- ----------- ----------- ------------ ----------- ------------ ------------ -------------------- ----------- ------------- ------------ ------------- ---------------- --------------- -------------- --------------- ---------- -------------------------------------------------------------------------------- --------------- ----------- -------------------------------------------------------------------------------- ---------------
       121255                3           349          2484         12338 2009-11-1                                                                1 转数据               2011-4-8 10             1                                                           1                                         0                                                                                                                                                                                                          2484
        12323                2           349           593         12338 2009-11-1

五.检查生成的跟踪文件,在/oracle/admin/RLZY/udump目录下生成了一个叫rlzy_ora_16831_my_trace_id.trc的文件
六.使用tkprof来格式化跟文件rlzy_ora_16831_my_trace_id.trc

$ tkprof /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.trc
output = /oracle/admin/RLZY/udump/jy_02_07.trc
insert= /oracle/admin/RLZY/udump/jy_02_07.sql

七.检查输出文件jy_02_07.trc的内容如下

TKPROF: Release 10.2.0.1.0 - Production on Tue Feb 7 16:42:51 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

begin :id := sys.dbms_transaction.local_transaction_id; end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           3

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 57
********************************************************************************

select :"SYS_B_0"
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)

********************************************************************************

select c.*,
       (select d.corp_join_no
          from bs_corp_insure d
         where d.corp_id = b.corp_id
           and d.insr_detail_code = c.insr_detail_code) corp_join_no_dw
  from bs_corp_pres b, bs_pres_insur c
 where b.indi_id = c.indi_id
   and b.corp_id = c.corp_id and b.corp_id=:"SYS_B_0"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         17          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0         17          0           2

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID BS_CORP_INSURE (cr=6 pr=0 pw=0 time=40 us)
      2   INDEX RANGE SCAN IDX_BS_CORP_INSURE_1 (cr=4 pr=0 pw=0 time=26 us)(object id 230781)
      2  TABLE ACCESS BY INDEX ROWID BS_PRES_INSUR (cr=11 pr=0 pw=0 time=143 us)
      4   NESTED LOOPS  (cr=9 pr=0 pw=0 time=307 us)
      1    INDEX RANGE SCAN PK_BS_CORP_PRES (cr=3 pr=0 pw=0 time=44 us)(object id 230787)
      2    BITMAP CONVERSION TO ROWIDS (cr=6 pr=0 pw=0 time=66 us)
      1     BITMAP AND  (cr=6 pr=0 pw=0 time=63 us)
      1      BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=28 us)
      2       INDEX RANGE SCAN INDX_BS_PRES_INSUR_INDI_ID (cr=3 pr=0 pw=0 time=13 us)(object id 231131)
      1      BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=21 us)
      2       INDEX RANGE SCAN INDX_BS_PRES_INSUR_CORP_ID (cr=3 pr=0 pw=0 time=16 us)(object id 231129)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           3
Fetch        2      0.00       0.00          0         17          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.00          0         17          0           6

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    5  user  SQL statements in session.
    0  internal SQL statements in session.
    5  SQL statements in session.
********************************************************************************
Trace file: /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.trc
Trace file compatibility: 10.01.00
Sort options: default

       0  session in tracefile.
       5  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       5  SQL statements in trace file.
       3  unique SQL statements in trace file.
      57  lines in trace file.
      39  elapsed seconds in trace file.

八.检查输出文件jy_02_07.sql脚本文件的内容如下

REM  Edit and/or remove the following  CREATE TABLE
REM  statement as your needs dictate.
CREATE TABLE  tkprof_table
(
 date_of_insert                       DATE
,cursor_num                           NUMBER
,depth                                NUMBER
,user_id                              NUMBER
,parse_cnt                            NUMBER
,parse_cpu                            NUMBER
,parse_elap                           NUMBER
,parse_disk                           NUMBER
,parse_query                          NUMBER
,parse_current                        NUMBER
,parse_miss                           NUMBER
,exe_count                            NUMBER
,exe_cpu                              NUMBER
,exe_elap                             NUMBER
,exe_disk                             NUMBER
,exe_query                            NUMBER
,exe_current                          NUMBER
,exe_miss                             NUMBER
,exe_rows                             NUMBER
,fetch_count                          NUMBER
,fetch_cpu                            NUMBER
,fetch_elap                           NUMBER
,fetch_disk                           NUMBER
,fetch_query                          NUMBER
,fetch_current                        NUMBER
,fetch_rows                           NUMBER
,ticks                                NUMBER
,sql_statement                        LONG
);
set sqlterminator off
set sqlterminator on

创建一个tkprof_table的语句