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

botang · 发表于 2017-12-12 20:27:25

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;

复制代码