第40/41次活动：2017-12-12/13（星期二/三晚上7:00-9:30）
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_trailf order byf.extended_timestamp desc;
select * from v$xml_audit_trailf order byf.extended_timestamp desc;
select * from dba_common_audit_trailf
where audit_type='Fine Grained XML Audit'
order byf.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 triggertrg_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 ;
selectbytes/1024/1024from 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_numbernumber ,
sub_number number ,
transac_date date ,
transac_addressvarchar2(600),
transac_comment varchar2(600),
currency varchar2(100),
remit varchar2(100),
in_amountnumber(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) charTERMINATED BY ',',
out_amount decimal externalTERMINATED BY ','nullif out_amount=blanks,
in_amount decimal externalTERMINATED BY ',' nullif in_amount=blanks,
balance decimal externalTERMINATED BY ',' nullif balance=blanks,
opposite_number charTERMINATED BY ',',
opposite_username charTERMINATED BY ',',
currency charTERMINATED BY ',',
transac_commentcharTERMINATED 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_ONLYlog=t_password.sql
控制文件里的：
trailing nullcols
等于外部表里的：
MISSING FIELD VALUES ARE NULL
create table hr.t_password (
l_usernamevarchar2(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 tablehr.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;
页:
[1]