|
- select * from dba_audit_policies;
- select * from dba_audit_policy_columns;
- select sys_context('userenv','os_user')||' '||user||' '||sys_context('userenv', 'current_user')||' '
- || to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '
- ||sys_context('userenv','ip_address') from dual;
- create table t_fga( a varchar2(2000)) ;
- select * from hr.employees where department_id=20;
- CREATE OR REPLACE PROCEDURE proc_fga ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
- VARCHAR2 ) AS
- begin
- insert into t_fga
- values ( sys_context('userenv','os_user')||' '||user||' '||sys_context('userenv', 'current_user')||' '
- || to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '
- ||sys_context('userenv','ip_address')||object_schema||' '
- ||object_name||' '||policy_name
- );
- end;
-
-
-
- begin
- dbms_fga.add_policy(object_schema => 'HR',
- object_name => 'EMPLOYEES',
- policy_name => 'POLICY1',
- audit_condition => 'department_id=20',
- audit_column => 'salary,commission_pct',
- handler_schema => 'SYS',
- handler_module => 'PROC_FGA',
- enable => true,
- statement_types => 'SELECT,UPDATE',
- audit_trail =>dbms_fga.XML+dbms_fga.EXTENDED,
- audit_column_opts => dbms_fga.ALL_COLUMNS);
- end;
-
- select * from dba_audit_policies;
- select * from dba_audit_policy_columns;
- select * from dba_fga_audit_trail f order by f.extended_timestamp desc;
- select * from v$xml_audit_trail f order by f.extended_timestamp desc;
- select * from dba_common_audit_trail f
- where audit_type='Fine Grained XML Audit'
- order by f.extended_timestamp desc;
- select * from t_fga;
-
-
- begin
- dbms_fga.drop_policy('HR','EMPLOYEES','POLICY1');
- end;
-
-
- begin
- dbms_fga.add_policy(object_schema => 'HR',
- object_name => 'EMPLOYEES',
- policy_name => 'POLICY1',
- audit_condition => 'department_id=20',
- audit_column => 'salary,commission_pct',
- handler_schema => 'SYS',
- handler_module => 'PROC_FGA',
- enable => true,
- statement_types => 'SELECT,UPDATE',
- audit_trail =>dbms_fga.XML+dbms_fga.EXTENDED,
- audit_column_opts => dbms_fga.ANY_COLUMNS);
- end;
-
- -----
- create table t_value ( a varchar2(2000));
-
- create or replace trigger trg_value
- after update of salary on hr.employees
- referencing new as new old as old
- for each row
- begin
- if :old.salary != :new.salary then
- insert into t_value
- values ( sys_context('userenv','os_user')||' '||user||' '||sysdate||' modified '||:new.salary||' '||:old.salary||' '||sys_context('userenv','ip_address') );
- end if;
- end;
-
- select * from t_value ;
-
复制代码- select bytes/1024/1024 from dba_segments s
- where s.owner='HR' and s.segment_name like 'T_BIG%';
- ----
- alter system flush buffer_cache ;
-
- alter system flush shared_pool;
-
- ----
-
- create table hr.t_bank_invoice (
- account_number number ,
- sub_number number ,
- transac_date date ,
- transac_address varchar2(600),
- transac_comment varchar2(600),
- currency varchar2(100),
- remit varchar2(100),
- in_amount number(20,2) ,
- out_amount number(20,2),
- balance number(20,2),
- opposite_number number ,
- opposite_username varchar2(100),
- all_comment varchar2(2000)) ;
-
- select * from hr.t_bank_invoice;
-
-
复制代码- LOAD DATA
- characterset ZHS16GBK
- INFILE 'ccb.dat'
- DISCARDFILE 'ccb.dis'
- append
- INTO table t_bank_invoice
- TRAILING NULLCOLS
- (
- account_number "4367421823250099999",
- sub_number "00000",
- transac_date position(1:8) DATE(8) "YYYYMMDD" TERMINATED BY ',',
- transac_address position(10) char TERMINATED BY ',',
- out_amount decimal external TERMINATED BY ',' nullif out_amount=blanks,
- in_amount decimal external TERMINATED BY ',' nullif in_amount=blanks ,
- balance decimal external TERMINATED BY ',' nullif balance=blanks,
- opposite_number char TERMINATED BY ',',
- opposite_username char TERMINATED BY ',',
- currency char TERMINATED BY ',',
- transac_comment char TERMINATED BY whitespace
- )
复制代码- LOAD DATA
- characterset ZHS16GBK
- INFILE 'icbc.dat'
- DISCARDFILE 'icbc.dis'
- append
- INTO table t_bank_invoice
- when (5) ='^'
- FIELDS TERMINATED BY '^'
- TRAILING NULLCOLS
- (
- account_number "1402029101000999999",
- sub_number "00000",
- transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
- transac_address TERMINATED BY WHITESPACE
- "substr(:transac_address,2)",
- transac_comment TERMINATED BY WHITESPACE
- "substr(:transac_comment,2)",
- currency TERMINATED BY WHITESPACE
- "substr(:currency,2)",
- remit TERMINATED BY WHITESPACE
- "substr(:remit,2)",
- in_amount TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
- out_amount TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
- balance TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
- all_comment TERMINATED BY WHITESPACE
- "substr(:all_comment,2)"
- )
复制代码
从图形界面获取sqlldr控制文件:
从控制文件转外部表:
- sqlldr hr/oracle_4U control=t_password.ctl external_table=GENERATE_ONLY log=t_password.sql
复制代码 控制文件里的:
trailing nullcols
等于外部表里的:
MISSING FIELD VALUES ARE NULL
- create table hr.t_password (
- l_username varchar2(50),
- l_password char(1),
- l_uid number,
- l_gid number,
- l_comment varchar2(100),
- l_home varchar2(100),
- l_shell varchar2(50));
- select * from hr.t_password;
- truncate table hr.t_password;
- create directory dir1 as '/home/oracle/dir1';
- grant read, write on directory dir1 to hr;
- drop table hr.t_password purge;
- create directory dir2 as '/home/oracle/dir2';
- grant read, write on directory dir2 to hr;
- -------------
- select * from hr.t_password;
- drop table hr.t_password;
复制代码
|
|