作者：YongJing(敬勇)

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的语句

创建一个物化视图用来提高查询数据速度，要创建的视图是sj_jf_zgyb_dw,和sj_jf_zgyb_dw表,这里使用的是将视图sj_jf_zgyb_dw查询出来的数据存储在相应的sj_jf_zgyb_dw表中来减少以后查询相同的数据不用再从视图中获取而提高速度.
— Create table

create table SJ_JF_ZGYB_DW
(
  XZDM       VARCHAR2(64),
  XZQHDM     VARCHAR2(6),
  TCQDM      VARCHAR2(6),
  DWBH       VARCHAR2(64),
  FKSSQ      VARCHAR2(6),
  FKSSQ_DY   VARCHAR2(6),
  DZRQ_DW    VARCHAR2(8),
  ZJFS       VARCHAR2(2),
  JFLX       VARCHAR2(1),
  JFJE_DW_YJ NUMBER(38,6),
  JFJE_DW_SJ NUMBER(38,6),
  JFJE_DWHGR NUMBER(38,6),
  JFJS_DW    NUMBER(38,6),
  JFBL_DW    NUMBER(38,6),
  JFRS_YJ    NUMBER(38,6),
  JFRS_SJ    NUMBER(38,6),
  DZBZ_DW    VARCHAR2(1)
)
tablespace HYGEIA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column SJ_JF_ZGYB_DW.XZDM
  is '险种名称        该项填“城镇职工基本医疗保险”                                                                                                  ';
comment on column SJ_JF_ZGYB_DW.XZQHDM
  is '行政区划代码      指社保机构所在地的行政区划代码，参照GB/T2260-2007中华人民共和国行政区划代码填列。                                                                  ';
comment on column SJ_JF_ZGYB_DW.TCQDM
  is '统筹区代码       指此险种的统筹区，填列所属统筹区的行政区划代码。                                                                                         ';
comment on column SJ_JF_ZGYB_DW.DWBH
  is '单位编号        指系统内单位的唯一标识                                                                                                      ';
comment on column SJ_JF_ZGYB_DW.FKSSQ
  is '费款所属期       指核定用人单位或个人社会保险费时的年月，表示方式为YYYYMM                                                                                  ';
comment on column SJ_JF_ZGYB_DW.FKSSQ_DY
  is '对应费款所属期     指核定的社会保险费应收取年月或社会保险待遇应付出年月，表示方式为YYYYMM当对应费款所属期小于费款所属期时，为补收或补发；当对应费款所属期等于费款所属期时，为正常缴费/待遇；当对应费款所属期大于费款所属期时，为趸缴或预支付 ';
comment on column SJ_JF_ZGYB_DW.DZRQ_DW
  is '单位到帐日期      指社保机构实际收到社会保险费时的日期，通常采用自然日期，表示方式为YYYYMMDD。当原始数据仅能精确到月份，而无法确定到“日”时，统一设为当月第一天。                                     ';
comment on column SJ_JF_ZGYB_DW.ZJFS
  is '征缴方式        指社保机构收取社会保险费的方式，按下列代码填列：1-银行托收，2-税务代征/税务全责征收，3-经办机构自收，4-学校代收，5-社区代收 ，99-其他                                       ';
comment on column SJ_JF_ZGYB_DW.JFLX
  is '缴费类型        按下列代码填列：1-正常，2-补缴，3-退费，4-预交，5-缓缴                                                                                 ';
comment on column SJ_JF_ZGYB_DW.JFJE_DW_YJ
  is '单位应缴金额      指用人单位应缴纳的社会保险费金额。不可为空，可填0                                                                                        ';
comment on column SJ_JF_ZGYB_DW.JFJE_DW_SJ
  is '单位实缴金额      指用人单位实际缴纳的社会保险费金额。不可为空，可填0                                                                                       ';
comment on column SJ_JF_ZGYB_DW.JFJE_DWHGR
  is '单位金额划个人账户金额 指从用人单位实际缴纳的社会保险费中划转记入个人账户的金额。不可为空，可填0                                                                            ';
comment on column SJ_JF_ZGYB_DW.JFJS_DW
  is '单位缴费基数      指单位缴纳社会保险费的基数。不可为空，可填0                                                                                           ';
comment on column SJ_JF_ZGYB_DW.JFBL_DW
  is '单位缴费比例      指用人单位缴纳保险费的比例。比例以小数方式表示，如“0.20”                                                                                  ';
comment on column SJ_JF_ZGYB_DW.JFRS_YJ
  is '应缴人数        指用人单位当月应缴纳社会保险费人数。不可为空，可填0                                                                                       ';
comment on column SJ_JF_ZGYB_DW.JFRS_SJ
  is '实缴人数        指用人单位当月实际缴纳社会保险费人数。不可为空，可填0                                                                                      ';
comment on column SJ_JF_ZGYB_DW.DZBZ_DW
  is '单位到账标志      按下列代码填列：1-已到账，0-未到账                                                                                              ';



create materialized view SJ_JF_ZGYB_DW
on prebuilt table
WITH REDUCED PRECISION--当与视图相应的表中的字段长度不匹配时减少精度
refresh force on demand
enable query rewrite
as
select '城镇职工基本医疗保险' as XZDM,
       lt.center_id as XZQHDM,
       substr(lt.center_id,0,4) as TCQDM,
       to_char(lt.corp_id) as DWBH,
       lt.calc_prd as FKSSQ,
       to_char(lt.to_pay_date, 'yyyymm') as FKSSQ_DY,
       to_char(lt.fac_pay_date, 'yyyymmdd') as DZRQ_DW,
       '1' as ZJFS,
       to_char(lt.topay_type) as JFLX,
       sum(decode(pm.indi_flag, 1, lc.pay_money, 0)) as JFJE_DW_YJ,
       sum(decode(lt.payed_flag,
                  1,
                  decode(pm.indi_flag, 1, lc.pay_money, 0),
                  0)) as JFJE_DW_SJ,
       Sum(decode(pm.money_id, 5, lc.pay_money, 0)) as JFJE_DWHGR,
       sum(decode(lc.money_id, 4, abs(sign(lc.pay_money) * lc.calc_base), 0)) as JFJS_DW,
       0.07 as JFBL_DW,
       sum(decode(lc.money_id, 4, lc.calc_man_sum, 0)) as JFRS_YJ,
       sum(decode(lc.money_id, 4, lc.calc_man_sum, 0)) as JFRS_SJ,
       decode(lt.payed_flag, 1, '1', '0') as DZBZ_DW
  From lv_insr_topay  lt,
       lv_cropfundpar lc,
       pfs_money_info pm
 Where lt.pay_info_no = lc.pay_info_no
   And pm.money_id = lc.money_id
   And lt.insr_detail_code = 2
   And lt.src_type = 1
/*   And lt.center_id=430701
   And lt.calc_prd between '201201' and '201202'*/
   group by  lt.center_id,
   lt.corp_id,
   lt.calc_prd,
   to_char(lt.to_pay_date, 'yyyymm'),
   to_char(lt.fac_pay_date, 'yyyymmdd'),
   to_char(lt.topay_type),
   decode(lt.payed_flag, 1, '1', '0');

这里是手工刷新数据，因为有上十亿的数，查询的是十几年的医保缴费数据

call  dbms_mview.refresh('SJ_JF_ZGYB_DW');

由于数据库服务器崩溃，造成了无法进入系统进行数据库备份，只能把oracle相关文件拷贝出来。对于拷贝出来的文件在测试机上进行一次不完全恢复，具体流程如下所示：

1、安装oracle 10g服务端并创建一个与要进行恢复的数据库相同名称的实例(db_name,sid,字符集一样,因为在创建控制文件时,会判断你要恢复的dbf文件中文件头信息中的数据库名是否与所在的实例名是否一样)
2、以sysdba身份进行备份控件文件到udmp目录的trace文件（语句：

alter database backup controlfile to trace

）
3、Shutdown immediate停止数据库，备份当前~\oracle\product\10.2.0\oradata目录中的文件，
接着删除这些文件并把需恢复的数据库的所有.dbf文件拷到此目录下。其中 system01.dbf,sysaux01.dbf和存储真实数据的.dbf文件是必要的
（只要DBF就可以了，日志和控制文件不要）。
4、以sysdba进入并执行startup nomount。把数据库启动到nomount状态。
5、从第2步备份出来trace文件中拷贝CREATE CONTROLFILE部分语句来重建控制文件。此处应根据实际情况增删表空间文件记录，如以下黑体部分则为新增记录。

CREATE CONTROLFILE   set Database ocp  Resetlogs
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\oracle\product\10.2.0\oradata\ocp\RED001.LOG'SIZE 50M,
GROUP 2 'C:\oracle\product\10.2.0\oradata\ocp\RED002.LOG'SIZE 50M,
GROUP 3 'C:\oracle\product\10.2.0\oradata\ocpRED003.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

6

alter database open  resetlogs;

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

第二种情况(其实要恢复数据库只要system01.dbf,sysaux01.dbf和存储真实数据的.dbf文件)
1、安装oracle 10g服务端并创建一个与要进行恢复的数据库相同名称的实例(db_name,sid,字符集一样,因为在创建控制文件时,会判断你要恢复的dbf文件中文件头信息中的数据库名是否与所在的实例名是否一样)
2、以sysdba身份进行备份控件文件到udmp目录的trace文件（语句：alter database backup controlfile to trace）
3、Shutdown immediate停止数据库，备份当前~\oracle\product\10.2.0\oradata目录中的文件，接着删除这些文件并把需恢复的数据库的所有.dbf文件拷到此目录下。其中 system01.dbf,sysaux01.dbf和存储真实数据的.dbf文件是必要的
（只要DBF就可以了，日志和控制文件不要）。
4、以sysdba进入并执行startup nomount。把数据库启动到nomount状态。
5、从第2步备份出来trace文件中拷贝CREATE CONTROLFILE部分语句来重建控制文件。此处应根据实际情况增删表空间文件记录，如以下黑体部分则为新增记录。

CREATE CONTROLFILE   set Database ocp  resetlogs
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\oracle\product\10.2.0\oradata\ocp\RED001.LOG'SIZE 50M,
GROUP 2 'D:\oracle\product\10.2.0\oradata\ocp\RED002.LOG'SIZE 50M,
GROUP 3 'D:\oracle\product\10.2.0\oradata\ocp\RED003.LOG'SIZE 50M
DATAFILE
'D:\oracle\product\10.2.0\oradata\ocp\SYSTEM01.DBF',--损坏的数据库的system表空间的数据文件
'D:\oracle\product\10.2.0\oradata\ocp\SYSAUX01.DBF',--损坏的数据库的sysaux表空间的数据文件
'D:\oracle\product\10.2.0\oradata\ocp\HYGEIA01.DBF',--要恢复的数据所在的dbf文件
'D:\oracle\product\10.2.0\oradata\ocp\HYGEIA02.DBF', --要恢复的数据所在的dbf文件
'D:\oracle\product\10.2.0\oradata\ocp\HYGEIA03.DBF'  --要恢复的数据所在的dbf文件
CHARACTER SET ZHS16GBK

6、

Shutdown immediate

7、在admin\pfile目录下的init.ora里加入隐含参数：

_allow_resetlogs_corruption=true
_allow_terminal_recovery_corruption=true
undo_tablespace='SYSTEM'
undo_management='MANUAL'

8、执行

startup pfile='D:\oracle\product\10.2.0\admin\orcl\pfile\init.ora(文件名和目录按照实际情况而定)' mount force;

9、

Recover database\ recover database until cancel

进行介质恢复。
10、

Alter database open resetlogs

打开数据库可能不成功：此处出现了ORA-03113 ” ORA-03113: 通信通道的文件结尾”的错误提示。
11、 重新启动数据库(startup) ，结果出现以下错误提示：
ora-01113:文件1需要介质恢复
ora-01110:数据文件1：’D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF’
12、 从新恢复表空间文件

recover datafile D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

(其它表空间文件可能也需要恢复)
13、

alter database open;

数据库已更新
14.重新创建UNDOTBS1表空间

create undo tablespace undotbs1 DATAFILE
'D:\oracle\product\10.2.0\oradata\orcl\undotbs01.dbf' SIZE 200M

15、

Shutdown immediate

16、在admin\pfile目录下的init.ora里删除隐含参数：
删除_allow_resetlogs_corruption=true
删除_allow_terminal_recovery_corruption=true
undo_tablespace=’UNDOTBS1′
undo_management=’AUTO’
17.

startup pfile='D:\oracle\product\10.2.0\admin\orcl\pfile\init.ora

18、 full export备份数据库。
19 重新创建临时表空间
20 查看恢复后结果

一、关闭归档

　　1、启动SQL*PLUS以管理身份登录Oracle数据库：

　　SQL> connect / as sysdba

　　2、关闭数据库实例

　　SQL> shutdown immediate

　　3、备份数据库：在对数据库做出任何重要的改变之前，建议备份数据库以免出现任何问题。
　　4、启动一个新的实例并装载数据库，但不打开数据库：

　　SQL> startup mount

　　5、禁止自动存档

　　SQL> alter system archive log stop;

　　6、禁止存档联机重做日志：转换数据库的存档模式。

　　SQL> alter database noarchivelog ;

　　7、打开数据库：

　　SQL> alter database open ;

　　8、察看已连接实例的存档信息：

　　SQL> archive log list ;

　　数据库日志模式 非存档模式
　　自动存档 禁用
　　存档终点 E:oraclearc
　　最早的联机日志序列 50
　　当前日志序列 52
　　二、启用闪回恢复区归档(Oracle 10g新特性)-Oracle数据库安装完成后首次创建自动归档日志
　　1、启动SQL*PLUS以管理身份登录Oracle数据库：

　　SQL> connect / as sysdba

　　2、关闭数据库实例

　　SQL> shutdown immediate

　　3、备份数据库：在对数据库做出任何重要的改变之前，建议备份数据库以免出现任何问题。
　　4、启动一个新的实例并装载数据库，但不打开数据库：

　　SQL> startup mount

　　5、转换数据库的存档模式为归档方式：

　　SQL> alter database archivelog ;

　　6、打开数据库：

　　SQL> alter database open ;

　　7、在数据库实例启动后允许自动存档方式：

　　SQL> alter system archive log start ;

　　8、通过资源管理器察看flash_recovery_area的日志文件结构快照如下：
　　9、启用回闪：

　　SQL>alter database flashback on;

　　10、闪回区默认的存储空间为2G，修改FLASH_RECOVERY_AREA空间为20GB和快速恢复区的目录

    SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
    SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20g ;
    

使用logminer来分析对表所做的修改
1.设置初始化参数UTL_FILE_DIR 以指定一个允许 PL/SQL 文件 I/O 的目录

utl_file_dir='D:\oracle\log';

2.执行 BMS_LOGMNR_D.BUILD 过程以创建字典文件

execute dbms_logmnr_d.build('ocp.ora','D:\oracle\log');

指定要分析的日志文件
3.设置 V$LOGMNR_CONTENTS 视图:
初始化新列表并指定第一个日志文件

SQL> execute dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\REDO01.LOG',dbms_logmnr.new);

PL/SQL 过程已成功完成。

向列表中添加其它日志文件

SQL> execute dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCP\REDO02.LOG',dbms_logmnr.addfile);

PL/SQL 过程已成功完成。

设置 LogMiner 会话
一旦创建了字典文件您就可以开始分析重做日志第一步是使用
DBMS_LOGMNR.ADD_LOGFILE 过程指定要分析的日志文件
使用下列常量
? DBMS_LOGMNR.NEW 创建一个新列表并指定第一个日志文件
? DBMS_LOGMNR.ADDFILE 向列表中添加其它日志文件
? DBMS_LOGMNR.REMOVEFILE 从列表中删除重做日志
LogMiner 可以分析联机和归档日志文件
开始分析重做日志文件
初始化 LogMiner 会话:

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>'D:\oracle\log\ocp.ora');

4.创建一个中间表用来存储对lv_insr_topay表的dml操作记录

create table jy_logmnr
(sql_redo varchar2(4000),
sql_undo varchar2(4000)
);

insert into jy_logmnr
SELECT sql_redo,SQL_UNDO FROM v$logmnr_contents WHERE seg_name = 'LV_INSR_TOPAY';

select * from jy_logmnr;
SQL> select * from jy_logmnr ;

SQL_REDO                                                                         SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
insert into "INSUR_CHANGDE"."LV_INSR_TOPAY"("PAY_INFO_NO","CALC_PRD","PAY_MONEY" delete from "INSUR_CHANGDE"."LV_INSR_TOPAY" where "PAY_INFO_NO" = '17524467' and

也可以运行

SELECT timestamp, username, sql_redo,SQL_UNDO
FROM v$logmnr_contents
WHERE seg_name = 'LV_INSR_TOPAY';

SQL> SELECT timestamp, username, sql_redo,SQL_UNDO
  2  FROM v$logmnr_contents
  3  WHERE seg_name = 'LV_INSR_TOPAY';


TIMESTAMP   USERNAME                       SQL_REDO                                                                         SQL_UNDO
----------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2012-1-1 13                                insert into "INSUR_CHANGDE"."LV_INSR_TOPAY"("PAY_INFO_NO","CALC_PRD","PAY_MONEY" delete from "INSUR_CHANGDE"."LV_INSR_TOPAY" where "PAY_INFO_NO" = '17524467' and

5.完成 LogMiner 会话:

EXECUTE DBMS_LOGMNR.END_LOGMNR;