设为首页收藏本站

Botang唐波's Oracle Station

查看: 1004|回复: 0

第40/41次活动:2017-12-12/13(星期二/三晚上7:00-9:30)

[复制链接]

745

主题

1118

帖子

8120

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8120
发表于 2017-12-12 20:27:25 | 显示全部楼层 |阅读模式
  1. select  * from dba_audit_policies;

  2. select  * from dba_audit_policy_columns;

  3. select     sys_context('userenv','os_user')||' '||user||' '||sys_context('userenv', 'current_user')||' '
  4.                 || to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '
  5.             ||sys_context('userenv','ip_address')     from dual;

  6. create table t_fga( a varchar2(2000)) ;

  7. select  * from hr.employees where department_id=20;

  8. CREATE OR REPLACE PROCEDURE proc_fga  ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
  9. VARCHAR2 ) AS
  10. begin
  11.   insert into t_fga
  12.       values (   sys_context('userenv','os_user')||' '||user||' '||sys_context('userenv', 'current_user')||' '
  13.                 || to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '
  14.             ||sys_context('userenv','ip_address')||object_schema||' '
  15.             ||object_name||' '||policy_name
  16.               );      
  17.   end;
  18.   
  19.   
  20.   
  21.   begin
  22.      dbms_fga.add_policy(object_schema => 'HR',
  23.      object_name => 'EMPLOYEES',
  24.      policy_name => 'POLICY1',
  25.      audit_condition => 'department_id=20',
  26.      audit_column => 'salary,commission_pct',
  27.      handler_schema => 'SYS',
  28.      handler_module => 'PROC_FGA',
  29.      enable => true,
  30.      statement_types => 'SELECT,UPDATE',
  31.      audit_trail =>dbms_fga.XML+dbms_fga.EXTENDED,
  32.      audit_column_opts => dbms_fga.ALL_COLUMNS);
  33.      end;
  34.      
  35.    select  * from dba_audit_policies;

  36. select  * from dba_audit_policy_columns;  

  37. select * from dba_fga_audit_trail  f order by  f.extended_timestamp desc;
  38. select * from v$xml_audit_trail  f order by  f.extended_timestamp desc;
  39. select * from dba_common_audit_trail  f
  40. where audit_type='Fine Grained XML Audit'
  41. order by  f.extended_timestamp desc;

  42. select  * from t_fga;
  43.      
  44.      
  45.   begin
  46.      dbms_fga.drop_policy('HR','EMPLOYEES','POLICY1');
  47.    end;
  48.      
  49.    
  50.    begin
  51.      dbms_fga.add_policy(object_schema => 'HR',
  52.      object_name => 'EMPLOYEES',
  53.      policy_name => 'POLICY1',
  54.      audit_condition => 'department_id=20',
  55.      audit_column => 'salary,commission_pct',
  56.      handler_schema => 'SYS',
  57.      handler_module => 'PROC_FGA',
  58.      enable => true,
  59.      statement_types => 'SELECT,UPDATE',
  60.      audit_trail =>dbms_fga.XML+dbms_fga.EXTENDED,
  61.      audit_column_opts => dbms_fga.ANY_COLUMNS);
  62.      end;
  63.      
  64.      -----
  65.      create table t_value  ( a varchar2(2000));
  66.      
  67. create or replace trigger  trg_value
  68. after update of salary on hr.employees
  69. referencing new as new old as old
  70. for each row
  71. begin
  72.   if :old.salary != :new.salary then
  73.      insert into t_value
  74.       values (  sys_context('userenv','os_user')||' '||user||' '||sysdate||' modified '||:new.salary||' '||:old.salary||' '||sys_context('userenv','ip_address')    );      
  75.   end if;
  76. end;
  77.      

  78. select  * from t_value ;
  79.   
复制代码
  1. select  bytes/1024/1024  from dba_segments s
  2. where s.owner='HR' and s.segment_name like 'T_BIG%';
  3. ----
  4. alter system flush buffer_cache ;

  5. alter system flush shared_pool;

  6. ----

  7. create table hr.t_bank_invoice (
  8.     account_number  number ,
  9.      sub_number number ,
  10.     transac_date   date ,
  11.       transac_address  varchar2(600),
  12.     transac_comment    varchar2(600),
  13.        currency    varchar2(100),
  14.       remit   varchar2(100),
  15.      in_amount  number(20,2)  ,
  16.    out_amount   number(20,2),
  17.     balance     number(20,2),
  18.    opposite_number    number  ,
  19.   opposite_username    varchar2(100),
  20.     all_comment      varchar2(2000)) ;
  21.    
  22.     select  * from hr.t_bank_invoice;
  23.    
复制代码
  1. LOAD DATA
  2. characterset ZHS16GBK
  3. INFILE 'ccb.dat'
  4. DISCARDFILE 'ccb.dis'
  5. append
  6. INTO table t_bank_invoice
  7. TRAILING NULLCOLS
  8. (
  9.    account_number "4367421823250099999",
  10.    sub_number "00000",
  11.    transac_date position(1:8)  DATE(8) "YYYYMMDD" TERMINATED BY ',',  
  12.    transac_address   position(10)   char  TERMINATED BY ',',              
  13.    out_amount decimal external  TERMINATED BY ','  nullif out_amount=blanks,
  14.    in_amount decimal external  TERMINATED BY ',' nullif in_amount=blanks  ,
  15.    balance decimal external  TERMINATED BY ',' nullif balance=blanks,
  16.    opposite_number char  TERMINATED BY ',',     
  17.    opposite_username char  TERMINATED BY ',',                                   
  18.    currency char  TERMINATED BY ',',
  19.    transac_comment  char  TERMINATED BY whitespace               
  20. )
复制代码
  1. LOAD DATA
  2. characterset ZHS16GBK
  3. INFILE 'icbc.dat'
  4. DISCARDFILE 'icbc.dis'
  5. append
  6. INTO table t_bank_invoice
  7. when (5) ='^'
  8. FIELDS TERMINATED BY '^'
  9. TRAILING NULLCOLS
  10. (
  11.    account_number "1402029101000999999",
  12.    sub_number "00000",
  13.    transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
  14.    transac_address TERMINATED BY WHITESPACE
  15.     "substr(:transac_address,2)",
  16.    transac_comment TERMINATED BY WHITESPACE
  17.     "substr(:transac_comment,2)",
  18.    currency TERMINATED BY WHITESPACE
  19.     "substr(:currency,2)",
  20.    remit TERMINATED BY WHITESPACE
  21.     "substr(:remit,2)",
  22.    in_amount TERMINATED BY WHITESPACE
  23.     "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
  24.    out_amount TERMINATED BY WHITESPACE
  25.     "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
  26.    balance TERMINATED BY WHITESPACE
  27.     "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
  28.    all_comment TERMINATED BY WHITESPACE
  29.     "substr(:all_comment,2)"
  30. )
复制代码

从图形界面获取sqlldr控制文件:
Screenshot.png

从控制文件转外部表:
  1. sqlldr hr/oracle_4U control=t_password.ctl   external_table=GENERATE_ONLY  log=t_password.sql
复制代码
控制文件里的:
trailing nullcols
等于外部表里的:
MISSING FIELD VALUES ARE NULL

  1. create table hr.t_password (
  2. l_username  varchar2(50),
  3. l_password char(1),
  4. l_uid number,
  5. l_gid number,
  6. l_comment varchar2(100),
  7. l_home varchar2(100),
  8. l_shell varchar2(50));

  9. select  * from hr.t_password;

  10. truncate table hr.t_password;

  11. create directory dir1 as '/home/oracle/dir1';

  12. grant read, write on directory dir1 to hr;

  13. drop table  hr.t_password purge;

  14. create directory dir2 as '/home/oracle/dir2';

  15. grant read, write on directory dir2 to hr;

  16. -------------
  17. select  * from hr.t_password;

  18. drop table hr.t_password;
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-12-13 17:21 , Processed in 0.186493 second(s), 28 queries .

快速回复 返回顶部 返回列表