本文深入探讨了Oracle FGAC（Fine-Grained Access Control）与VPD（View-Point Dependency）机制的原理与实现，通过实例展示了如何在Oracle数据库中实施精细审计、设置FGA策略、监控特定查询语句，并通过函数实现基于用户的上下文信息对查询条件进行动态调整。同时，阐述了FGAC作为基于行的访问控制策略，以及如何利用VPD实现基于行的数据过滤与行级安全性增强。
精细审计
精细审计FGA（Fined-Grained Aiditing）能实现比标准审计粒度更细化的审计功能，如当用户对满足指定条件的数据行或列进行了操作才进行审计。
FGA的实现通过dbms_fga包进行。审计结果放在fga_log$中，可通过系统视图dba_fga_audit_trail进行查询。
SQL> begin
2 dbms_fga.add_policy
3 (object_schema=>'scott',
4 object_name=>'emp',
5 policy_name=>'fga_rows_audit',
6 audit_condition=>'deptno=10',
7 enable=>true,
8 statement_types=>'select,update');
9 end;
10 /
PL/SQL procedure successfully completed.
【设置FGA审计策略，当scott用户emp表上部门号为10的数据行被查询或修改时即记录审计信息】
[oracle@desktop241 ~]$ sqlplus / as sysdba
SQL> select * from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
SCOTT EMP
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
FGA_ROWS_AUDIT
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
deptno=10
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------ ------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
YES YES NO YES
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
NO DB+EXTENDED ANY_COLUMNS
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
SQL> grant select any table to john;
Grant succeeded.
SQL> grant update any table to john;
Grant succeeded.
SQL> select timestamp,db_user,os_user,object_schema,object_name,sql_textfrom dba_fga_audit_trail;
no rows selected【查询FGA审计结果】
[oracle@desktop241 ~]$ sqlplus john/john123
SQL> select * from scott.emp wheredeptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
18-OCT-12 JOHN
oracle
SCOTT
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select * from scott.emp where deptno=10
【再查FGA审计结果】
SQL> select * from scott.emp wheredeptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
18-OCT-12 JOHN
oracle
SCOTT
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select * from scott.emp where deptno=10
SQL>update scott.emp set sal=sal*1.1 where deptno=10;
3 rows updated.
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
update scott.emp set sal=sal*1.1 wheredeptno=10
SQL> execdbms_fga.drop_policy('scott','emp','fga_rows_audit');
PL/SQL procedure successfully completed.
SQL> truncate table fga_log$;
Table truncated.
SQL> begin
2 dbms_fga.add_policy
3 (object_schema=>'scott',
4 object_name=>'emp',
5 policy_name=>'fga_columns_audit',
6 audit_column=>'sal',
7 enable=>true,
8 statement_types=>'select');
9 end;
10 /
PL/SQL procedure successfully completed.
【再设FGA审计策略，当scott用户emp表上sal列被查询时即记录审计信息】
测试：
SQL> select sal from scott.emp wheredeptno=10;
SAL
----------
2695
5500
1430
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
EMP
select sal from scott.emp where deptno=10
SQL> select ename from scott.emp wheredeptno=10;
ENAME
----------
CLARK
KING
MILLER
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
select sal from scott.emp where deptno=10
SQL> select ename from scott.emp wheredeptno=10 and sal>3000;
ENAME
----------
KING
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
--------------------------------------------------------------------------------
EMP
select ename from scott.emp where deptno=10and sal>3000
SQL> execdbms_fga.drop_policy('scott','emp','fga_columns_audit');
PL/SQL procedure successfully completed.
SQL> truncate table fga_log$;
Table truncated.
SQL> get fga.add
1 begin
2 dbms_fga.add_policy
3 (object_schema=>'scott',
4 object_name=>'emp',
5 policy_name=>'fga_audit',
6 audit_condition=>'deptno=10',
7 audit_column=>'sal',
8 enable=>true,
9 statement_types=>'select');
10*end;
11 /
PL/SQL procedure successfully completed.
【再设FGA审计策略，当scott用户emp表上deptno为10的数据库行的sal列被查询时即记录审计信息。即当audit_condition与audit_column指定的条件都满足时将进行FGA审计记录】
测试：
SQL> select empno from scott.emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
SQL> show user;
USER is "JOHN"
SQL> select timestamp,db_user,os_user,object_schema,object_name,sql_textfrom dba_fga_audit_trail;
no rows selected
SQL> select empno,sal from scott.emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2695
7788 3000
7839 5500
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1430
14 rows selected.
SQL> select timestamp,db_user,os_user,object_schema,object_name,sql_textfrom dba_fga_audit_trail;
no rows selected
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select empno,sal from scott.emp
SQL> select empno,sal from scott.emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
SQL> show user;
USER is "SCOTT"
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select empno,sal from scott.emp
SQL> execdbms_fga.drop_policy('scott','emp','fga_audit');
PL/SQL procedure successfully completed.
SQL> truncate table fga_log$;
Table truncated.【查自己的表记录，sys用户查不记录】
强制审计
【记录以sysdba身份登录数据库的动作，默认生效，不依赖于参数设置，在数据库关闭时也同样生效】
SQL> show parameter audit_file_dest;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ora10/ad
ump
[oracle@desktop241 ~]$ sqlplus / as sysdba
[oracle@desktop241 ~]$ ps -ef | grepV$SPID;
oracle 30705 30589 0 22:56 pts/4 00:00:00 grep V
[oracle@desktop241 adump]$ vimora10_ora_30582_1.aud
Thu Oct 18 22:53:41 2012
LENGTH : '158'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '775040288'
VPD虚拟专用数据库（virtual private database）
虚拟专用数据库（VPD）提供了角色和视图无法提供的行级访问控制，如实现每个销售用户登录后只能访问销售表中自己的销售中自己的销售记录。具体实现过程为：
1. 先设置应用程序上下文A（应用程序上下文是一个数据库对象，在整个session周期内保存session的各项属性如登录用户名，类似于环境变量）用来保存用户登录时的登录名；
2. 再在用户登录时由触发器C执行预设定好的程序单元B 获取用户名并保存在应用程序上下文A中；
3. 预设定好的审计规则E在监控到销售用户执行销售记录的查询语句时，由预设定好的程序单元D在用户执行的查询语句后隐式添加指定条件，如当销售用户执行select * from sales时系统自动转换为select * from sales where seller=’s001’;
建立测试环境
SQL> create table scott.sales(product_id varchar(4),price number,qtys number,seller varchar(4));
Table created.
SQL> insert into scott.sales values('0001',1000,1,'s001');
1 row created.
SQL> insert into scott.sales values('0002',2000,2,'s002');
1 row created.
SQL> create user s001 identified bys001;
User created.
SQL> create user s002 identified bys002;
User created.
SQL> create user mgr identified by mgr;
User created.
SQL> grant connect,resource,select anytable to s001;
Grant succeeded.
SQL> grant connect,resource,select anytable to s002;
Grant succeeded.
SQL> grant connect,resource,select anytable to mgr;
Grant succeeded.
[oracle@desktop241 ~]$ sqlplus s001/s001
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 1000 1 s001
0002 2000 2 s002
SQL> connect s002/s002
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 1000 1 s001
0002 2000 2 s002
SQL> connect mgr/mgr
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 1000 1 s001
0002 2000 2 s002
【测试3个用户均能查到sales销售表中所有数据，VPD需要实现Mgr用户能查所有数据，而s001和s002只能查到自己的销售数据】
A. B建立应用程序上下文A（保存登录用户名）和存储过程B（获取登录用户名）
SQL> connect / as sysdba
Connected.
SQL> create or replacecontext vpd_context using scott.vpd_get_username;
【建立应用程序上下文vpd_context,其中所保存的属性由存储过程scott.vpd_get_username(即存储过程B)赋予】
Context created.
SQL> create or replaceprocedure scott.vpd_get_username is begin
2 dbms_session.set_context('vpd_context','seller',user);
3 end;
4 /
【建立存储过程，为vpd_context建立属性名seller，用来保存属性值user】
Procedure created.
SQL> grant execute on scott.vpd_get_username to public;
【将存储过程的执行权限赋给所有用户】
Grant succeeded.
C．建立触发器C（自动获取登录用户名）
SQL>create or replace trigger get_username_on_logon
2 after logon on database
3 begin
4 scott.vpd_get_username;
5 end;
6 /
【建立触发器，用于用户登录后自动执行存储过程vpd_get_username获取用户名存入应用程序上下文vpd_context中】
Trigger created.
SQL> connect s001/s001
Connected.
SQL> selectsys_context('vpd_context','seller')from dual;
SYS_CONTEXT('VPD_CONTEXT','SELLER')
--------------------------------------------------------------------------------
S001
SQL> connect s002/s002
Connected.
SQL> selectsys_context('vpd_context','seller')from dual;
SYS_CONTEXT('VPD_CONTEXT','SELLER')
--------------------------------------------------------------------------------
S002
SQL> connect mgr/mgr
Connected.
SQL> selectsys_context('vpd_context','seller')from dual;
SYS_CONTEXT('VPD_CONTEXT','SELLER')
--------------------------------------------------------------------------------
MGR
【测试应用程序上下文vpd_context是否生效，其属性seller中是否包含用户名属性值】
D．建立函数D（在用户执行的语句后隐式添加指定条件）
SQL> create or replace function scott.vpd_add_condition
2 (p_schema_name varchar2,p_tab_name varchar2)
3 return varchar2 is
4 v_namevarchar2(100):=upper(sys_context('vpd_context','seller'));
5 v_condition varchar2(2000);
6 begin
7 if v_name like 's%' thenv_condition:='seller='||''''||v_name||'''';
8 else v_condition:=null;
9 end if;
10 return v_condition;
11 end;
12 /
Function created.
【建立函数D，在用户执行的查询语句后隐式添加指定条件。如当销售用户执行语句select * fromsales时系统自动转换为select *from sales where seller=’s001’;而当非销售用户mgr执行语句时，则不添加指定条件（Null）,其中p_schema_name和p_tab_name两变量值为FGAC规则函数必须的两个传入参数，分别用来表示对哪个schema下的哪个table 添加FGAC规则】
FGAC即FINE_CRAINED ACCESS CONTROL，即对数据行进行过滤的VPD，即提供行级安全性的VPD,即基于行的VPD。
E．建立审计规则E（监控销售表上执行的查询语句）
SQL>begin dbms_rls.add_policy
2 (object_schema=>'scott',
3 object_name=>'sales',
4 policy_name=>'fgac_vpd',
5 function_schema=>'scott',
6 policy_function=>'vpd_add_condition',
7 statement_types=>'select',
8 enable=>true);
9 end;
10 /
PL/SQL procedure successfully completed.
【建立审计规则E，监控到sales销售表上执行查询语句时，就调用vpd_add_condition函数进行处理】
测试结果：
SQL> conn S001/S001
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 100 1 S001
SQL> conn scott/tiger
Connected.
SQL> select * from scott.sales;
no rows selected
SQL> conn S002/S002
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0002 200 2 S002
SQL> conn mgr/mgr
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 100 1 S001
0002 200 2 S002
SQL> conn system/song
Connected.
SQL> select * from scott.sales;
no rows selected
SQL> conn sys/song as sysdba
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 100 1 S001
0002 200 2 S002
基于列的VPD
仅有具有权限的用户才能访问敏感列，敏感列，否则列数据被屏蔽
SQL> create or replace functionscott.vpd_col_condition
2 (p_owner varchar2,p_objvarchar2)
3 return varchar2 is
4 v_condition varchar2(2000);
5 begin
6 if (p_owner=user)thenv_condition:=null;
7 elsev_condition:='1=2';
8 end if;
9 return v_condition;
10 end;
11 /
【建立规则函数，判断执行查询语句的用户是不是表的属主，是则不做限制，不是则添加条件1=2】
Function created.
SQL> begin dbms_rls.add_policy
2 (object_schema=>'scott',
3 object_name=>'emp',
4 policy_name=>'fgac_col_vpd',
5 function_schema=>'scott',
6 policy_function=>'vpd_col_condition',
7 statement_types=>'select',
8 sec_relevant_cols=>'sal',
9 sec_relevant_cols_opt=>dbms_rls.all_rows);
10 end;
11 /
PL/SQL procedure successfully completed.
【建立审计规则，监控到emp表上执行查询语句时，就调用vpd_col_condition函数判断执行查询语句的用户是不是表的属主，是则不做限制，不是则对所有记录（由参数sec_relevant_cols_opt=>dbms_rls.all_rows指定）的sal列（sec_relevant_cols=>’sal’指定）单独添加条件1=2（即隐藏该列数据）】
SQL> conn scott/tiger
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
SQL> conn mgr/mgr
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80
20
7499 ALLEN SALESMAN 7698 20-FEB-81 300
30
7521 WARD SALESMAN 7698 22-FEB-81 500
30
SQL> conn / as sysdba
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 3
TDE透明数据加密（transparent database encryption）
使用VPD可对敏感信息进行保护，但无法阻止用户直接从数据文件dump出重要信息，实际工程中常在应用程序代码中调用特定加密函数对数据加密，但要求熟悉应用程序。
Oracle的TDE在数据写入磁盘前调用钱包wallet文件中的密钥自动加密数据（支持3DES168，AES128，AES192，AES256等加密算法），查询时则调用钱包wallet文件中的密钥自动解密，简化了成本和复杂性，但其只将数据在存储级别进行了加密，不能阻止用户通过查询语句查询敏感数据，故应将VPD,TDE等方法结合起来保护数据。
Sys用户的表不能被加密。
SQL> host mkdir/u01/app/oracle/admin/ora10/wallet
【指定钱包默认存储路径】
SQL> alter system set encryption keyidentified by song;
【创建钱包文件并设置钱包访问口令】
System altered.
SQL> host ls -l$ORACLE_BASE/admin/ora10/wallet/
总计 4【查看生成的钱包文件】
-rw------- 1 oracle oinstall 1309 10-1916:19 ewallet.p12
SQL> create table scott.tde(idnumber,name char(10) encrypt using 'AES128' no salt);
【利用钱包使用AES128算法对新建表 指定列进行加密，加密时不使用随机字符串增强密码强度（ salt，使得相同明文加密后密文不同，）以使能在加密列上建立索引】
Table created.
测试：
SQL> insert into scott.tde values (0001,'aaa');
1 row created.
SQL> insert into scott.tde values(0002,'bbb');
1 row created.
SQL> insert into scott.tde values(0002,'ccc');
1 row created.
SQL> select * from scott.tde;
IDNAME
---------- ----------
1 aaa
2 bbb
2 ccc
SQL> alter system set encryption walletclose;
System altered.
SQL> select * from scott.tde;
select * from scott.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> select id from scott.tde;
ID
----------
1
2
2
SQL> alter system set encryption walletopen authenticated by song;
System altered.
SQL> select * from scott.tde;
IDNAME
---------- ----------
1 aaa
2 bbb
2 ccc
SQL> alter table scott.tde modify namechar(10) decrypt;
【取消列加密】
Table altered.
SQL> alter system set encryption walletclose;
System altered.
SQL> select * from scott.tde;
IDNAME
---------- ----------
1 aaa
2 bbb
2 ccc
加密备份数据
SQL> alter system set encryption walletopen authenticated by song;
System altered.
RMAN> connect target sys/song
connected to target database: ORA10(DBID=775040288)
using target database control file insteadof recovery catalog
RMAN> configure encryption for databaseon;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters aresuccessfully stored
RMAN> backup tablespace users;
Starting backup at 19-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00004name=/u01/app/oracle/oradata/ora10/users01.dbf
channel ORA_DISK_1: starting piece 1 at19-OCT-12
channel ORA_DISK_1: finished piece 1 at19-OCT-12
piece handle=/backup/backup_0pno5hvm_1_1 tag=TAG20121019T164022comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:03
Finished backup at 19-OCT-12
Starting Control File and SPFILE Autobackupat 19-OCT-12
piecehandle=/u01/app/oracle/flash_recovery_area/ORA10/autobackup/2012_10_19/o1_mf_s_797100026_8824hysg_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 19-OCT-12
【打开钱包，配置RMAN为透明加密模式，则备份数据时利用钱包进行加密，恢复时也需打开钱包才能进行，适合备份和恢复都在本地进行的备份模式】
RMAN> set encryption identified by song1only;
executing command: SET encryption
RMAN> backup tablespace users;
Starting backup at 19-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00004name=/u01/app/oracle/oradata/ora10/users01.dbf
channel ORA_DISK_1: starting piece 1 at19-OCT-12
channel ORA_DISK_1: finished piece 1 at19-OCT-12
piece handle=/backup/backup_0rno5i71_1_1tag=TAG20121019T164417 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:02
Finished backup at 19-OCT-12
Starting Control File and SPFILE Autobackupat 19-OCT-12
piecehandle=/u01/app/oracle/flash_recovery_area/ORA10/autobackup/2012_10_19/o1_mf_s_797100259_8824q3st_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 19-OCT-12
【不使用钱包，配置RMAN为密码加密模式，则恢复时必须提供备份时所指定的密码（set decryption）才能进行，适合备份在本地，恢复在异地的备份模式】
RMAN> set decryption identified bysong1;
executing command: SET decryption
RMAN> set encryption identified bysong2;
executing command: SET encryption
RMAN> backup tablespace users;
Starting backup at 19-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00004name=/u01/app/oracle/oradata/ora10/users01.dbf
channel ORA_DISK_1: starting piece 1 at19-OCT-12
channel ORA_DISK_1: finished piece 1 at19-OCT-12
piece handle=/backup/backup_0tno5iii_1_1tag=TAG20121019T165026 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 19-OCT-12
Starting Control File and SPFILE Autobackupat 19-OCT-12
piecehandle=/u01/app/oracle/flash_recovery_area/ORA10/autobackup/2012_10_19/o1_mf_s_797100627_88252n6k_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 19-OCT-12
RMAN> set decryption identified bysong2;
executing command: SET decryption
【使用钱包和密码，配置RMAN为混合加密模式，则恢复时打开钱包，或者提供备份时所指定的密码均可进行，适合恢复即可能在本地，也可能在异地进行的备份模式】
LOCK & LATCH
Lock
独占锁（X锁）与共享锁（S锁）
SQL> update t1 set id=2 where id=1;
1 row updated.
SQL> update t1 set id=3 where id=1;
Waiting….
【为保证数据的一致性，会话1在修改记录时在记录上加了独占锁（exclusive,简称x锁），又称排他锁，以防止事务在提交或回退前记录被其他事务同时修改。
一旦用户对某个资源添加了x锁，则其他用户都不能再对该资源添加任何类型的锁，直到该用户释放了资源上的x锁为止】
SQL> update t1 set id=2 where id=1;
1 row updated.
SQL> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00054: resource busy and acquire withNOWAIT specified
【为保证数据的一致性，会话1在修改记录时同时还在表头上加了共享锁（shared ,简称s锁），以便其他删表事务执行前不用检查表中每条记录是否加了独占锁（那就不能删表），而只要检查表头是否存在共享锁就可以了
若删表事务（要对全表加独占锁）进行前，还有另一会话还进行了删除记录操作（且未提交或回退，即事务未结束），那么另一会话在被删记录上加独占锁，同时在表上再加一个共享锁，则删表事务要等到表头上所有共享锁全部取消后（即所有独占事务全部完成后），才能进行（才能对整表施加独占锁）。
一旦用户对某个资源添加了S锁，则其他用户都不能在该资源上添加X锁，只能添加S锁，直到该用户释放了资源上的s 锁为止】
DML事务锁
根据被保护的对象种类的不同，锁还可以分成多种类型，如因DDL引起的锁，DML事务引起的锁，分布式事务中涉及的锁等，DML事务锁应主要关注。
DML事务锁能保证当某用户正在更新表中某行数据时，其他用户不能同时更新相同的数据行，而且也不能删除或修改被更新的表，包括行锁TX和表锁TM。
前列修改记录时，在行记录上所施加的就是行（独占）锁，对于ORACLE来说，行锁只可能是独占锁；而同时在表头上所施加的就是表（共享）锁，对于ORACLE来说，表锁可能是共享锁（如删表中记录时在表头上所施加的共享锁），也可能是独占锁（如删表操作时需要在表头上所施加的独占锁）。
TM表锁的种类
TM表锁即可能是独占锁，也可能是共享锁，可细分为如下几类：
编号 表锁 含义
0/1 无
2 RS row share
3 RX row exclusive
4 S share
5 SRX share+row exclusive
6 x exclusive
A. select * from table_name不会产生锁；
B. lock table table_name in rowshare mode 会在表上加RS锁，使用完毕后rollback即解锁。
【当执行相应语句时，系统自动在所要操作的表上申请表级RS锁（意向锁，表示某事务有意向进行锁表），当表上加RS锁后，不许其他事务对该表添加x排他锁（如drop table），但允许其他事务对该表再加除x锁之外的其他锁，如其他事务再对该表加RS锁（再执行lock table in row share mode）,或其他事务再对该表加RX锁（再执行插删改DML操作），或其他事务再对该表加S锁（再执行建索引操作）等】
SQL> create table t4 (id number);
Table created.
SQL> insert into t4 values (1);
1 row created.
SQL> insert into t4 values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> lock table t4 in row share mode;
Table(s) Locked.
SQL> select sid from v$mystat;
SID
----------
157
SQL> select * from v$lock where sid=157;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- -------------------- ---------- ----------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 157 TM 55335 0 2 0
116 0
SQL> drop table t4;
drop table t4
*
ERROR at line 1:
ORA-00054: resource busy and acquire withNOWAIT specified
C/D/E/F.insert/update/ delete DML语句及select * from table_name for update句会在表上加RX锁（其中select for update用于在读数据过程中禁止其他事务对所读数据进行DML操作），操作完成后commit或roback解锁。
当执行相应语句时，系统自动在所要操作的表上申请表级RX锁（意向锁，表示某事务有意向要进行锁表），当表级锁获得后，系统再自动申请TX行锁（实体锁，表示该事务的具体锁动作），并将相应数据行加x锁，当表上加RX锁后，不许其他事务对该表再加X排他锁（如drop table），不允许其他事务对该表再加S锁（如建索引）；但允许其他事务对该表再加RS锁（如执行lock table inrow share mode）或RX锁（如插删改记录，注意，其他事务在同一表上再加RX表锁没问题，但再加行锁时，不能再加在表内已加X锁的数据行上，而只许加在表内其他数据行上。
对表施加RX锁也可以通过手工执行Lock table table_name in rowexclusive mode进行显示加锁，执行后即对表施加了RX锁（但未对数据行申请加TX行锁），直到rollback释放。
RS与RX区别：
1. 产生条件不同。
2. RX锁后，其他事务读不到更改后的结果。而RS对其他事务而言读是没有问题的，故称RS，虽都是指在表的层面上防止其他事务锁表，但RX是 forexclusive reading or writing，而RS是for exclusive write access.这也是RS的S（share）表现的地方。
SQL> insert into t4 values (8);
1 row created.
SQL> commit;
Commit complete.
SQL> update t4 set id=9 where id<=3;
4 rows updated.
SQL> select * from v$mystat;
SID STATISTIC# VALUE
---------- ---------- ----------
145 0 1
145 1 1
SQL> select * from v$lock where sid=145;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 3 0
131 0
3FB85DF8 3FB85E1C 145TX 458775 597 6 0
SQL> rollback;
SQL> select * from v$lock where sid=145;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 3 0
172 0
3FB85DF8 3FB85E1C 145 TX 458775 597 6 0
172 0
SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
XIDUSN XIDSLOT XIDSQN STATUS
---------- ---------- ---------- ----------------
7 23 597 ACTIVE
SQL> select sid,type,id1,id2,lmode,request,block from v$lockwhere sid=145;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
145 TM 55336 0 3 0 0
145 TX 458775 597 6 0 0
SQL> select object_name from dba_objects where object_id='55336';
OBJECT_NAME
--------------------------------------------------------------------------------
T4
SQL> select trunc(458775/power(2,16)) from dual;
TRUNC(458775/POWER(2,16))
-------------------------
7
SQL> select bitand (458775,to_number('ffff','xxxx'))+0 from dual;
BITAND(458775,TO_NUMBER('FFFF','XXXX'))+0
-----------------------------------------
23
SQL> show parameter transactions
NAME TYPE VALUE
------------------------------------ -----------------------------------------
transactions integer 187
transactions_per_rollback_segment integer 5
SQL> show parameter dml_locks;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
dml_locks integer 748
【TX数量和事务个数相同，并受transactions参数（默认187）限制，TM锁数量和被更新的表的个数相同，并受dml_locks参数（默认748）限制，即默认情况下可同时启动187个事务，更新748个表，平均允许每个事务同时更新4个表】
SQL> select resource_name,initial_allocation,current_utilization,max_utilizationfrom v$resource_limit where resource_name in ('transactions','dml_locks');
RESOURCE_NAME INITIAL_ALLOCATION CURRENT_UTILIZATION
------------------------------ ---------------------------------------
MAX_UTILIZATION
---------------
dml_locks 748 0
44
transactions 187 0
9
【查看当前TX/TM锁的使用情况，包括能够分配的最大个数，当前分配的个数，及曾经分配的最大个数】
SQL> update t4 set id=10 where id<10;
9 rows updated.
SQL> update t4 set id=10 where id<=10;
SQL> select sid,type,id1,id2,lmode,request,block from v$lockwhere sid in (145,158) order by sid desc;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
158 TM 55336 0 3 0 0
158 TX 196619 637 0 6 0
145 TX 196619 637 6 0 1
145 TM 55336 0 3 0 0
Create index on table语句会在表上加S锁，操作完成后即解锁。
创建索引时，需要读表中所有数据，且在读数据过程中数据不能由其他事务进行更新，此时需要对表加S锁，S锁用于保证在操作期间整个表上不能再由其他事务加RX锁（进行插删改操作）及X锁（不能删表），因为若在创建索引时，允许有未提交的其他插删改事务发生，而新的数据是不会被索引的，则将造成数据和索引间的不一致。因为S和RX锁是互斥的，所以当某事务在表上有未提交插删改事务时（以加RX锁），另一事务不能创建索引（无法再加S锁）。或者反之，当创建索引动作还没结束时（S锁未释放），其他事务即不可以在表上开始插删改事务（无法获得RX锁）。
当表上加S锁后，不许其他事务对该表再加X排他锁（如drop table），不允许其他事务对该表再加RX锁（如插删改记录）；但允许其他事务对该表再加RS锁（如执行LOCK table inrow share mode）.
对表施加S锁也可以通过手工执行lock table table_name in share mode 进行显式加锁，执行后即对表施加了S 锁，其他事务对该表即不能进行数据插删改及删表操作，直到rollback释放。
SQL> update t4 set id=10 where id=10;
0 rows updated.
SQL> select * from v$lock where sid=158;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A608 3FB4A620 158 TM 55336 0 3 0
21 0
SQL> create index i4 on t4 (id);
create index i4 on t4 (id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> select * from v$lock where sid=158;
no rows selected
SQL> create index i4 on t4 (id);
create index i4 on t4 (id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> lock table t4 in share mode;
SQL> select * from v$lock where sid in (158,145);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 3 0
2185 0
3FB86364 3FB86388 145 TX 196619 637 6 0
2185 0
SQL> select * from v$lock where sid in (145,158,148);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 5 0
179 1
3FB4A6C4 3FB4A6DC 148TM 55336 0 0 3
60 0
3FB4A780 3FB4A798 158 TM 55336 0 0 3
33 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB86364 3FB86388 145 TX 458790 575 6 0
179 0
如果对一个数据库对象加SRX锁，表示对它加S锁，再加RX锁，即SRX=S+RX.例如事务对某个表加SRX锁，则表示该事务需要读表中所有数据，且在读数据过程中数据不能由其他事务进行更新（所以要对该表加S锁），同时本事务会更新个别行（所以要对该表加RX锁），注意，表上加S锁后再由本事务加RX锁是允许的，而表上加S锁后再由其他事务加RX锁是不允许的。
对表施加SRX锁也可以通过手工执行lock table table_name in share row exclusive mode 进行显式加锁，执行后即对表施加了SRX锁，其他事务对该表即不能进行数据插删改及删表操作，直到rollback释放。
执行Alter table,drop table ,drop index,truncatetable操作时，需要对表加X锁，X锁是限制级别最高的锁。对表加X锁后，不能进行其他任何锁操作。
对表施加X锁也可以通过手工执行lock table table_name in exclusive mode 进行显式加锁，执行后即对表施加了x锁，其他事务对该表即不能进行任何其他加锁操作，直到rollback释放。
死锁deadlock
精细审计
精细审计FGA（Fined-Grained Aiditing）能实现比标准审计粒度更细化的审计功能，如当用户对满足指定条件的数据行或列进行了操作才进行审计。
FGA的实现通过dbms_fga包进行。审计结果放在fga_log$中，可通过系统视图dba_fga_audit_trail进行查询。
SQL> begin
2 dbms_fga.add_policy
3 (object_schema=>'scott',
4 object_name=>'emp',
5 policy_name=>'fga_rows_audit',
6 audit_condition=>'deptno=10',
7 enable=>true,
8 statement_types=>'select,update');
9 end;
10 /
PL/SQL procedure successfully completed.
【设置FGA审计策略，当scott用户emp表上部门号为10的数据行被查询或修改时即记录审计信息】
[oracle@desktop241 ~]$ sqlplus / as sysdba
SQL> select * from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
SCOTT EMP
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
FGA_ROWS_AUDIT
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
deptno=10
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------ ------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
YES YES NO YES
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
NO DB+EXTENDED ANY_COLUMNS
OBJECT_SCHEMA OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE PF_FUNCTION ENA SEL INS UPD
------------------------------------------------------------ --- --- --- ---
DEL AUDIT_TRAIL POLICY_COLU
--- ------------ -----------
SQL> grant select any table to john;
Grant succeeded.
SQL> grant update any table to john;
Grant succeeded.
SQL> select timestamp,db_user,os_user,object_schema,object_name,sql_textfrom dba_fga_audit_trail;
no rows selected【查询FGA审计结果】
[oracle@desktop241 ~]$ sqlplus john/john123
SQL> select * from scott.emp wheredeptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
18-OCT-12 JOHN
oracle
SCOTT
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select * from scott.emp where deptno=10
【再查FGA审计结果】
SQL> select * from scott.emp wheredeptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
18-OCT-12 JOHN
oracle
SCOTT
TIMESTAMP DB_USER
------------ ------------------------------
OS_USER
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select * from scott.emp where deptno=10
SQL>update scott.emp set sal=sal*1.1 where deptno=10;
3 rows updated.
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
update scott.emp set sal=sal*1.1 wheredeptno=10
SQL> execdbms_fga.drop_policy('scott','emp','fga_rows_audit');
PL/SQL procedure successfully completed.
SQL> truncate table fga_log$;
Table truncated.
SQL> begin
2 dbms_fga.add_policy
3 (object_schema=>'scott',
4 object_name=>'emp',
5 policy_name=>'fga_columns_audit',
6 audit_column=>'sal',
7 enable=>true,
8 statement_types=>'select');
9 end;
10 /
PL/SQL procedure successfully completed.
【再设FGA审计策略，当scott用户emp表上sal列被查询时即记录审计信息】
测试：
SQL> select sal from scott.emp wheredeptno=10;
SAL
----------
2695
5500
1430
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
EMP
select sal from scott.emp where deptno=10
SQL> select ename from scott.emp wheredeptno=10;
ENAME
----------
CLARK
KING
MILLER
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
select sal from scott.emp where deptno=10
SQL> select ename from scott.emp wheredeptno=10 and sal>3000;
ENAME
----------
KING
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
--------------------------------------------------------------------------------
EMP
select ename from scott.emp where deptno=10and sal>3000
SQL> execdbms_fga.drop_policy('scott','emp','fga_columns_audit');
PL/SQL procedure successfully completed.
SQL> truncate table fga_log$;
Table truncated.
SQL> get fga.add
1 begin
2 dbms_fga.add_policy
3 (object_schema=>'scott',
4 object_name=>'emp',
5 policy_name=>'fga_audit',
6 audit_condition=>'deptno=10',
7 audit_column=>'sal',
8 enable=>true,
9 statement_types=>'select');
10*end;
11 /
PL/SQL procedure successfully completed.
【再设FGA审计策略，当scott用户emp表上deptno为10的数据库行的sal列被查询时即记录审计信息。即当audit_condition与audit_column指定的条件都满足时将进行FGA审计记录】
测试：
SQL> select empno from scott.emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
SQL> show user;
USER is "JOHN"
SQL> select timestamp,db_user,os_user,object_schema,object_name,sql_textfrom dba_fga_audit_trail;
no rows selected
SQL> select empno,sal from scott.emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2695
7788 3000
7839 5500
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1430
14 rows selected.
SQL> select timestamp,db_user,os_user,object_schema,object_name,sql_textfrom dba_fga_audit_trail;
no rows selected
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select empno,sal from scott.emp
SQL> select empno,sal from scott.emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> selecttimestamp,db_user,os_user,object_schema,object_name,sql_text fromdba_fga_audit_trail;
SQL> show user;
USER is "SCOTT"
SQL_TEXT
--------------------------------------------------------------------------------
EMP
select empno,sal from scott.emp
SQL> execdbms_fga.drop_policy('scott','emp','fga_audit');
PL/SQL procedure successfully completed.
SQL> truncate table fga_log$;
Table truncated.【查自己的表记录，sys用户查不记录】
强制审计
【记录以sysdba身份登录数据库的动作，默认生效，不依赖于参数设置，在数据库关闭时也同样生效】
SQL> show parameter audit_file_dest;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ora10/ad
ump
[oracle@desktop241 ~]$ sqlplus / as sysdba
[oracle@desktop241 ~]$ ps -ef | grepV$SPID;
oracle 30705 30589 0 22:56 pts/4 00:00:00 grep V
[oracle@desktop241 adump]$ vimora10_ora_30582_1.aud
Thu Oct 18 22:53:41 2012
LENGTH : '158'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '775040288'
VPD虚拟专用数据库（virtual private database）
虚拟专用数据库（VPD）提供了角色和视图无法提供的行级访问控制，如实现每个销售用户登录后只能访问销售表中自己的销售中自己的销售记录。具体实现过程为：
1. 先设置应用程序上下文A（应用程序上下文是一个数据库对象，在整个session周期内保存session的各项属性如登录用户名，类似于环境变量）用来保存用户登录时的登录名；
2. 再在用户登录时由触发器C执行预设定好的程序单元B 获取用户名并保存在应用程序上下文A中；
3. 预设定好的审计规则E在监控到销售用户执行销售记录的查询语句时，由预设定好的程序单元D在用户执行的查询语句后隐式添加指定条件，如当销售用户执行select * from sales时系统自动转换为select * from sales where seller=’s001’;
建立测试环境
SQL> create table scott.sales(product_id varchar(4),price number,qtys number,seller varchar(4));
Table created.
SQL> insert into scott.sales values('0001',1000,1,'s001');
1 row created.
SQL> insert into scott.sales values('0002',2000,2,'s002');
1 row created.
SQL> create user s001 identified bys001;
User created.
SQL> create user s002 identified bys002;
User created.
SQL> create user mgr identified by mgr;
User created.
SQL> grant connect,resource,select anytable to s001;
Grant succeeded.
SQL> grant connect,resource,select anytable to s002;
Grant succeeded.
SQL> grant connect,resource,select anytable to mgr;
Grant succeeded.
[oracle@desktop241 ~]$ sqlplus s001/s001
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 1000 1 s001
0002 2000 2 s002
SQL> connect s002/s002
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 1000 1 s001
0002 2000 2 s002
SQL> connect mgr/mgr
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 1000 1 s001
0002 2000 2 s002
【测试3个用户均能查到sales销售表中所有数据，VPD需要实现Mgr用户能查所有数据，而s001和s002只能查到自己的销售数据】
A. B建立应用程序上下文A（保存登录用户名）和存储过程B（获取登录用户名）
SQL> connect / as sysdba
Connected.
SQL> create or replacecontext vpd_context using scott.vpd_get_username;
【建立应用程序上下文vpd_context,其中所保存的属性由存储过程scott.vpd_get_username(即存储过程B)赋予】
Context created.
SQL> create or replaceprocedure scott.vpd_get_username is begin
2 dbms_session.set_context('vpd_context','seller',user);
3 end;
4 /
【建立存储过程，为vpd_context建立属性名seller，用来保存属性值user】
Procedure created.
SQL> grant execute on scott.vpd_get_username to public;
【将存储过程的执行权限赋给所有用户】
Grant succeeded.
C．建立触发器C（自动获取登录用户名）
SQL>create or replace trigger get_username_on_logon
2 after logon on database
3 begin
4 scott.vpd_get_username;
5 end;
6 /
【建立触发器，用于用户登录后自动执行存储过程vpd_get_username获取用户名存入应用程序上下文vpd_context中】
Trigger created.
SQL> connect s001/s001
Connected.
SQL> selectsys_context('vpd_context','seller')from dual;
SYS_CONTEXT('VPD_CONTEXT','SELLER')
--------------------------------------------------------------------------------
S001
SQL> connect s002/s002
Connected.
SQL> selectsys_context('vpd_context','seller')from dual;
SYS_CONTEXT('VPD_CONTEXT','SELLER')
--------------------------------------------------------------------------------
S002
SQL> connect mgr/mgr
Connected.
SQL> selectsys_context('vpd_context','seller')from dual;
SYS_CONTEXT('VPD_CONTEXT','SELLER')
--------------------------------------------------------------------------------
MGR
【测试应用程序上下文vpd_context是否生效，其属性seller中是否包含用户名属性值】
D．建立函数D（在用户执行的语句后隐式添加指定条件）
SQL> create or replace function scott.vpd_add_condition
2 (p_schema_name varchar2,p_tab_name varchar2)
3 return varchar2 is
4 v_namevarchar2(100):=upper(sys_context('vpd_context','seller'));
5 v_condition varchar2(2000);
6 begin
7 if v_name like 's%' thenv_condition:='seller='||''''||v_name||'''';
8 else v_condition:=null;
9 end if;
10 return v_condition;
11 end;
12 /
Function created.
【建立函数D，在用户执行的查询语句后隐式添加指定条件。如当销售用户执行语句select * fromsales时系统自动转换为select *from sales where seller=’s001’;而当非销售用户mgr执行语句时，则不添加指定条件（Null）,其中p_schema_name和p_tab_name两变量值为FGAC规则函数必须的两个传入参数，分别用来表示对哪个schema下的哪个table 添加FGAC规则】
FGAC即FINE_CRAINED ACCESS CONTROL，即对数据行进行过滤的VPD，即提供行级安全性的VPD,即基于行的VPD。
E．建立审计规则E（监控销售表上执行的查询语句）
SQL>begin dbms_rls.add_policy
2 (object_schema=>'scott',
3 object_name=>'sales',
4 policy_name=>'fgac_vpd',
5 function_schema=>'scott',
6 policy_function=>'vpd_add_condition',
7 statement_types=>'select',
8 enable=>true);
9 end;
10 /
PL/SQL procedure successfully completed.
【建立审计规则E，监控到sales销售表上执行查询语句时，就调用vpd_add_condition函数进行处理】
测试结果：
SQL> conn S001/S001
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 100 1 S001
SQL> conn scott/tiger
Connected.
SQL> select * from scott.sales;
no rows selected
SQL> conn S002/S002
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0002 200 2 S002
SQL> conn mgr/mgr
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 100 1 S001
0002 200 2 S002
SQL> conn system/song
Connected.
SQL> select * from scott.sales;
no rows selected
SQL> conn sys/song as sysdba
Connected.
SQL> select * from scott.sales;
PROD PRICE QTYS SELL
---- ---------- ---------- ----
0001 100 1 S001
0002 200 2 S002
基于列的VPD
仅有具有权限的用户才能访问敏感列，敏感列，否则列数据被屏蔽
SQL> create or replace functionscott.vpd_col_condition
2 (p_owner varchar2,p_objvarchar2)
3 return varchar2 is
4 v_condition varchar2(2000);
5 begin
6 if (p_owner=user)thenv_condition:=null;
7 elsev_condition:='1=2';
8 end if;
9 return v_condition;
10 end;
11 /
【建立规则函数，判断执行查询语句的用户是不是表的属主，是则不做限制，不是则添加条件1=2】
Function created.
SQL> begin dbms_rls.add_policy
2 (object_schema=>'scott',
3 object_name=>'emp',
4 policy_name=>'fgac_col_vpd',
5 function_schema=>'scott',
6 policy_function=>'vpd_col_condition',
7 statement_types=>'select',
8 sec_relevant_cols=>'sal',
9 sec_relevant_cols_opt=>dbms_rls.all_rows);
10 end;
11 /
PL/SQL procedure successfully completed.
【建立审计规则，监控到emp表上执行查询语句时，就调用vpd_col_condition函数判断执行查询语句的用户是不是表的属主，是则不做限制，不是则对所有记录（由参数sec_relevant_cols_opt=>dbms_rls.all_rows指定）的sal列（sec_relevant_cols=>’sal’指定）单独添加条件1=2（即隐藏该列数据）】
SQL> conn scott/tiger
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
SQL> conn mgr/mgr
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80
20
7499 ALLEN SALESMAN 7698 20-FEB-81 300
30
7521 WARD SALESMAN 7698 22-FEB-81 500
30
SQL> conn / as sysdba
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 3
TDE透明数据加密（transparent database encryption）
使用VPD可对敏感信息进行保护，但无法阻止用户直接从数据文件dump出重要信息，实际工程中常在应用程序代码中调用特定加密函数对数据加密，但要求熟悉应用程序。
Oracle的TDE在数据写入磁盘前调用钱包wallet文件中的密钥自动加密数据（支持3DES168，AES128，AES192，AES256等加密算法），查询时则调用钱包wallet文件中的密钥自动解密，简化了成本和复杂性，但其只将数据在存储级别进行了加密，不能阻止用户通过查询语句查询敏感数据，故应将VPD,TDE等方法结合起来保护数据。
Sys用户的表不能被加密。
SQL> host mkdir/u01/app/oracle/admin/ora10/wallet
【指定钱包默认存储路径】
SQL> alter system set encryption keyidentified by song;
【创建钱包文件并设置钱包访问口令】
System altered.
SQL> host ls -l$ORACLE_BASE/admin/ora10/wallet/
总计 4【查看生成的钱包文件】
-rw------- 1 oracle oinstall 1309 10-1916:19 ewallet.p12
SQL> create table scott.tde(idnumber,name char(10) encrypt using 'AES128' no salt);
【利用钱包使用AES128算法对新建表 指定列进行加密，加密时不使用随机字符串增强密码强度（ salt，使得相同明文加密后密文不同，）以使能在加密列上建立索引】
Table created.
测试：
SQL> insert into scott.tde values (0001,'aaa');
1 row created.
SQL> insert into scott.tde values(0002,'bbb');
1 row created.
SQL> insert into scott.tde values(0002,'ccc');
1 row created.
SQL> select * from scott.tde;
IDNAME
---------- ----------
1 aaa
2 bbb
2 ccc
SQL> alter system set encryption walletclose;
System altered.
SQL> select * from scott.tde;
select * from scott.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> select id from scott.tde;
ID
----------
1
2
2
SQL> alter system set encryption walletopen authenticated by song;
System altered.
SQL> select * from scott.tde;
IDNAME
---------- ----------
1 aaa
2 bbb
2 ccc
SQL> alter table scott.tde modify namechar(10) decrypt;
【取消列加密】
Table altered.
SQL> alter system set encryption walletclose;
System altered.
SQL> select * from scott.tde;
IDNAME
---------- ----------
1 aaa
2 bbb
2 ccc
加密备份数据
SQL> alter system set encryption walletopen authenticated by song;
System altered.
RMAN> connect target sys/song
connected to target database: ORA10(DBID=775040288)
using target database control file insteadof recovery catalog
RMAN> configure encryption for databaseon;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters aresuccessfully stored
RMAN> backup tablespace users;
Starting backup at 19-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00004name=/u01/app/oracle/oradata/ora10/users01.dbf
channel ORA_DISK_1: starting piece 1 at19-OCT-12
channel ORA_DISK_1: finished piece 1 at19-OCT-12
piece handle=/backup/backup_0pno5hvm_1_1 tag=TAG20121019T164022comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:03
Finished backup at 19-OCT-12
Starting Control File and SPFILE Autobackupat 19-OCT-12
piecehandle=/u01/app/oracle/flash_recovery_area/ORA10/autobackup/2012_10_19/o1_mf_s_797100026_8824hysg_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 19-OCT-12
【打开钱包，配置RMAN为透明加密模式，则备份数据时利用钱包进行加密，恢复时也需打开钱包才能进行，适合备份和恢复都在本地进行的备份模式】
RMAN> set encryption identified by song1only;
executing command: SET encryption
RMAN> backup tablespace users;
Starting backup at 19-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00004name=/u01/app/oracle/oradata/ora10/users01.dbf
channel ORA_DISK_1: starting piece 1 at19-OCT-12
channel ORA_DISK_1: finished piece 1 at19-OCT-12
piece handle=/backup/backup_0rno5i71_1_1tag=TAG20121019T164417 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:02
Finished backup at 19-OCT-12
Starting Control File and SPFILE Autobackupat 19-OCT-12
piecehandle=/u01/app/oracle/flash_recovery_area/ORA10/autobackup/2012_10_19/o1_mf_s_797100259_8824q3st_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 19-OCT-12
【不使用钱包，配置RMAN为密码加密模式，则恢复时必须提供备份时所指定的密码（set decryption）才能进行，适合备份在本地，恢复在异地的备份模式】
RMAN> set decryption identified bysong1;
executing command: SET decryption
RMAN> set encryption identified bysong2;
executing command: SET encryption
RMAN> backup tablespace users;
Starting backup at 19-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00004name=/u01/app/oracle/oradata/ora10/users01.dbf
channel ORA_DISK_1: starting piece 1 at19-OCT-12
channel ORA_DISK_1: finished piece 1 at19-OCT-12
piece handle=/backup/backup_0tno5iii_1_1tag=TAG20121019T165026 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 19-OCT-12
Starting Control File and SPFILE Autobackupat 19-OCT-12
piecehandle=/u01/app/oracle/flash_recovery_area/ORA10/autobackup/2012_10_19/o1_mf_s_797100627_88252n6k_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 19-OCT-12
RMAN> set decryption identified bysong2;
executing command: SET decryption
【使用钱包和密码，配置RMAN为混合加密模式，则恢复时打开钱包，或者提供备份时所指定的密码均可进行，适合恢复即可能在本地，也可能在异地进行的备份模式】
LOCK & LATCH
Lock
独占锁（X锁）与共享锁（S锁）
SQL> update t1 set id=2 where id=1;
1 row updated.
SQL> update t1 set id=3 where id=1;
Waiting….
【为保证数据的一致性，会话1在修改记录时在记录上加了独占锁（exclusive,简称x锁），又称排他锁，以防止事务在提交或回退前记录被其他事务同时修改。
一旦用户对某个资源添加了x锁，则其他用户都不能再对该资源添加任何类型的锁，直到该用户释放了资源上的x锁为止】
SQL> update t1 set id=2 where id=1;
1 row updated.
SQL> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00054: resource busy and acquire withNOWAIT specified
【为保证数据的一致性，会话1在修改记录时同时还在表头上加了共享锁（shared ,简称s锁），以便其他删表事务执行前不用检查表中每条记录是否加了独占锁（那就不能删表），而只要检查表头是否存在共享锁就可以了
若删表事务（要对全表加独占锁）进行前，还有另一会话还进行了删除记录操作（且未提交或回退，即事务未结束），那么另一会话在被删记录上加独占锁，同时在表上再加一个共享锁，则删表事务要等到表头上所有共享锁全部取消后（即所有独占事务全部完成后），才能进行（才能对整表施加独占锁）。
一旦用户对某个资源添加了S锁，则其他用户都不能在该资源上添加X锁，只能添加S锁，直到该用户释放了资源上的s 锁为止】
DML事务锁
根据被保护的对象种类的不同，锁还可以分成多种类型，如因DDL引起的锁，DML事务引起的锁，分布式事务中涉及的锁等，DML事务锁应主要关注。
DML事务锁能保证当某用户正在更新表中某行数据时，其他用户不能同时更新相同的数据行，而且也不能删除或修改被更新的表，包括行锁TX和表锁TM。
前列修改记录时，在行记录上所施加的就是行（独占）锁，对于ORACLE来说，行锁只可能是独占锁；而同时在表头上所施加的就是表（共享）锁，对于ORACLE来说，表锁可能是共享锁（如删表中记录时在表头上所施加的共享锁），也可能是独占锁（如删表操作时需要在表头上所施加的独占锁）。
TM表锁的种类
TM表锁即可能是独占锁，也可能是共享锁，可细分为如下几类：
编号 表锁 含义
0/1 无
2 RS row share
3 RX row exclusive
4 S share
5 SRX share+row exclusive
6 x exclusive
A. select * from table_name不会产生锁；
B. lock table table_name in rowshare mode 会在表上加RS锁，使用完毕后rollback即解锁。
【当执行相应语句时，系统自动在所要操作的表上申请表级RS锁（意向锁，表示某事务有意向进行锁表），当表上加RS锁后，不许其他事务对该表添加x排他锁（如drop table），但允许其他事务对该表再加除x锁之外的其他锁，如其他事务再对该表加RS锁（再执行lock table in row share mode）,或其他事务再对该表加RX锁（再执行插删改DML操作），或其他事务再对该表加S锁（再执行建索引操作）等】
SQL> create table t4 (id number);
Table created.
SQL> insert into t4 values (1);
1 row created.
SQL> insert into t4 values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> lock table t4 in row share mode;
Table(s) Locked.
SQL> select sid from v$mystat;
SID
----------
157
SQL> select * from v$lock where sid=157;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- -------------------- ---------- ----------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 157 TM 55335 0 2 0
116 0
SQL> drop table t4;
drop table t4
*
ERROR at line 1:
ORA-00054: resource busy and acquire withNOWAIT specified
C/D/E/F.insert/update/ delete DML语句及select * from table_name for update句会在表上加RX锁（其中select for update用于在读数据过程中禁止其他事务对所读数据进行DML操作），操作完成后commit或roback解锁。
当执行相应语句时，系统自动在所要操作的表上申请表级RX锁（意向锁，表示某事务有意向要进行锁表），当表级锁获得后，系统再自动申请TX行锁（实体锁，表示该事务的具体锁动作），并将相应数据行加x锁，当表上加RX锁后，不许其他事务对该表再加X排他锁（如drop table），不允许其他事务对该表再加S锁（如建索引）；但允许其他事务对该表再加RS锁（如执行lock table inrow share mode）或RX锁（如插删改记录，注意，其他事务在同一表上再加RX表锁没问题，但再加行锁时，不能再加在表内已加X锁的数据行上，而只许加在表内其他数据行上。
对表施加RX锁也可以通过手工执行Lock table table_name in rowexclusive mode进行显示加锁，执行后即对表施加了RX锁（但未对数据行申请加TX行锁），直到rollback释放。
RS与RX区别：
1. 产生条件不同。
2. RX锁后，其他事务读不到更改后的结果。而RS对其他事务而言读是没有问题的，故称RS，虽都是指在表的层面上防止其他事务锁表，但RX是 forexclusive reading or writing，而RS是for exclusive write access.这也是RS的S（share）表现的地方。
SQL> insert into t4 values (8);
1 row created.
SQL> commit;
Commit complete.
SQL> update t4 set id=9 where id<=3;
4 rows updated.
SQL> select * from v$mystat;
SID STATISTIC# VALUE
---------- ---------- ----------
145 0 1
145 1 1
SQL> select * from v$lock where sid=145;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 3 0
131 0
3FB85DF8 3FB85E1C 145TX 458775 597 6 0
SQL> rollback;
SQL> select * from v$lock where sid=145;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 3 0
172 0
3FB85DF8 3FB85E1C 145 TX 458775 597 6 0
172 0
SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
XIDUSN XIDSLOT XIDSQN STATUS
---------- ---------- ---------- ----------------
7 23 597 ACTIVE
SQL> select sid,type,id1,id2,lmode,request,block from v$lockwhere sid=145;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
145 TM 55336 0 3 0 0
145 TX 458775 597 6 0 0
SQL> select object_name from dba_objects where object_id='55336';
OBJECT_NAME
--------------------------------------------------------------------------------
T4
SQL> select trunc(458775/power(2,16)) from dual;
TRUNC(458775/POWER(2,16))
-------------------------
7
SQL> select bitand (458775,to_number('ffff','xxxx'))+0 from dual;
BITAND(458775,TO_NUMBER('FFFF','XXXX'))+0
-----------------------------------------
23
SQL> show parameter transactions
NAME TYPE VALUE
------------------------------------ -----------------------------------------
transactions integer 187
transactions_per_rollback_segment integer 5
SQL> show parameter dml_locks;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
dml_locks integer 748
【TX数量和事务个数相同，并受transactions参数（默认187）限制，TM锁数量和被更新的表的个数相同，并受dml_locks参数（默认748）限制，即默认情况下可同时启动187个事务，更新748个表，平均允许每个事务同时更新4个表】
SQL> select resource_name,initial_allocation,current_utilization,max_utilizationfrom v$resource_limit where resource_name in ('transactions','dml_locks');
RESOURCE_NAME INITIAL_ALLOCATION CURRENT_UTILIZATION
------------------------------ ---------------------------------------
MAX_UTILIZATION
---------------
dml_locks 748 0
44
transactions 187 0
9
【查看当前TX/TM锁的使用情况，包括能够分配的最大个数，当前分配的个数，及曾经分配的最大个数】
SQL> update t4 set id=10 where id<10;
9 rows updated.
SQL> update t4 set id=10 where id<=10;
SQL> select sid,type,id1,id2,lmode,request,block from v$lockwhere sid in (145,158) order by sid desc;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
158 TM 55336 0 3 0 0
158 TX 196619 637 0 6 0
145 TX 196619 637 6 0 1
145 TM 55336 0 3 0 0
Create index on table语句会在表上加S锁，操作完成后即解锁。
创建索引时，需要读表中所有数据，且在读数据过程中数据不能由其他事务进行更新，此时需要对表加S锁，S锁用于保证在操作期间整个表上不能再由其他事务加RX锁（进行插删改操作）及X锁（不能删表），因为若在创建索引时，允许有未提交的其他插删改事务发生，而新的数据是不会被索引的，则将造成数据和索引间的不一致。因为S和RX锁是互斥的，所以当某事务在表上有未提交插删改事务时（以加RX锁），另一事务不能创建索引（无法再加S锁）。或者反之，当创建索引动作还没结束时（S锁未释放），其他事务即不可以在表上开始插删改事务（无法获得RX锁）。
当表上加S锁后，不许其他事务对该表再加X排他锁（如drop table），不允许其他事务对该表再加RX锁（如插删改记录）；但允许其他事务对该表再加RS锁（如执行LOCK table inrow share mode）.
对表施加S锁也可以通过手工执行lock table table_name in share mode 进行显式加锁，执行后即对表施加了S 锁，其他事务对该表即不能进行数据插删改及删表操作，直到rollback释放。
SQL> update t4 set id=10 where id=10;
0 rows updated.
SQL> select * from v$lock where sid=158;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A608 3FB4A620 158 TM 55336 0 3 0
21 0
SQL> create index i4 on t4 (id);
create index i4 on t4 (id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> select * from v$lock where sid=158;
no rows selected
SQL> create index i4 on t4 (id);
create index i4 on t4 (id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> lock table t4 in share mode;
SQL> select * from v$lock where sid in (158,145);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 3 0
2185 0
3FB86364 3FB86388 145 TX 196619 637 6 0
2185 0
SQL> select * from v$lock where sid in (145,158,148);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB4A54C 3FB4A564 145 TM 55336 0 5 0
179 1
3FB4A6C4 3FB4A6DC 148TM 55336 0 0 3
60 0
3FB4A780 3FB4A798 158 TM 55336 0 0 3
33 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- --------------------
CTIME BLOCK
---------- ----------
3FB86364 3FB86388 145 TX 458790 575 6 0
179 0
如果对一个数据库对象加SRX锁，表示对它加S锁，再加RX锁，即SRX=S+RX.例如事务对某个表加SRX锁，则表示该事务需要读表中所有数据，且在读数据过程中数据不能由其他事务进行更新（所以要对该表加S锁），同时本事务会更新个别行（所以要对该表加RX锁），注意，表上加S锁后再由本事务加RX锁是允许的，而表上加S锁后再由其他事务加RX锁是不允许的。
对表施加SRX锁也可以通过手工执行lock table table_name in share row exclusive mode 进行显式加锁，执行后即对表施加了SRX锁，其他事务对该表即不能进行数据插删改及删表操作，直到rollback释放。
执行Alter table,drop table ,drop index,truncatetable操作时，需要对表加X锁，X锁是限制级别最高的锁。对表加X锁后，不能进行其他任何锁操作。
对表施加X锁也可以通过手工执行lock table table_name in exclusive mode 进行显式加锁，执行后即对表施加了x锁，其他事务对该表即不能进行任何其他加锁操作，直到rollback释放。
扫一扫
1851
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
