审核

• 强制审核
• 标准数据库审核
• 基于值的审核
• 细粒度审核(FGA)

    desc dbms_fga;---STATEMENT_TYPES审核语句类型
    execute dbms_fga.add_policy('SCOTT','EMP','FGA_DEMO',STATEMENT_TYPES=>'SELECT,INSERT,DELETE');
    ---执行select,insert,delete操作
    desc dba_fga_audit_trail
    alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    ---中间进行scott用户下的select,insert和delete操作
    select TIMESTAMP,DB_USER,SQL_TEXT from dba_fga_audit_trail

• 审核DBA

    show parameter audit

触发器(头部,触发条件,被触发代码)

• DML触发器

  添加for each row进行逐行操作显示

  添加referencing old as o new as n for each row when(n.sal>5000)进行条件

    create table demo1(a varchar(20));
    create or replace trigger scott.tr1
    after update on scott.emp for each row
    begin
    	insert into scott.demo1 values('changed!');
    end;
    update emp set sal=2000 where empno=7369;
    select * from demo1;
    create or replace trigger scott.tr1
    after update on scott.emp referencing old as o new as n for each row when(n.sal>5000)
    begin
    	insert into scott.demo1 values('changed!');
    end;

创建一个工资表, 用触发器实现更改工资之后, 显示旧的工资和新的工资的变化🆗

    create table sal_change(empno number(4),ename varchar2(10), old_sal number(7,2), new_sal number(7,2));
    create or replace trigger scott.tr1
    after update on scott.emp referencing old as o new as n for each row
    begin
      insert into scott.sal_change values(:o.empno, :o.ename,:o.sal,:n.sal);
    end;
    update emp set sal=4000 where empno=7902;
    select * from sal_change;

• 系统触发器

  如用来记录用户的登陆情况

  login_rec +++(username,login_time)
  create or replace trigger scott.tr1
  after logon on database
  begin
  	insert into login_rec values ();
  end;---scott权限不足得换用户
  alter session set nls_data_format='yyyy-mm-dd hh24:mi:ss';
  desc dbms_shared_pool;---共享池
  desc v$db_object_cache;---内存中的东西
  select owner,name,type from v$db_object_cache
  where owner<>'SYS' and kept='YES';---查询内存中的内存
  desc user_procedures;
  select object_name,object_type from user_procedures
  where object_type ='PROCEDURE';---查询存储过程
  execute dbms_shared_pool.keep('scott.p20');---保持p20这个存储过程
  select object_name,object_type from user_procedures
  where object_type ='procedure';
  

  创建一个startup的触发器

  create or replace procedure scott.p20
  as
  begin
  null;
  end;
  /
  select owner,name,type from v$db_object_cache
  where owner<>'SYS' and kept='YES';---查询一下内存中是否存在 N
  execute dbms_shared_pool.keep('scott.p20');
  select owner,name,type from v$db_object_cache
  where owner<>'SYS' and kept='YES';---查询一下内存中是否存在 Y
  ---经过shutdown和startup之后,p20在内存中不会存在了
  ---如果遇见shutdown和startup之后,操作报错就再操作一遍
  shutdown immediate
  startup
  ---如果是scott.tr2就是在scott下进行keep操作就需要grant权限
  create or replace trigger tr2
  after startup on database
  begin
  	sys.dbms_shared_pool.keep('scott.p20');
  end;
  shutdown immediate
  startup
  select owner,name,type from v$db_object_cache
  where owner<>'SYS' and kept='YES';---有了
  

移动数据

• 数据文件—>数据库

  • cmd下sqlldr

  • cmd下exp/imp (export和import)

      查询exp和imp的参数
      CMD>exp help=y
    
      导出工资在2500以上的emp表中的数据---cmd中
      CMD>exp scott/tiger file=d:\demo1.dmp tables=emp query='where sal>2500'
      报错 LRM-00111: 值 'where sal' 缺少右引号
      是因为>是管道命令,输出的意思. 因此会在当前cmd目录下产生一个名为"2500'"的文件
      因此要屏蔽大于号
      CMD>exp scott/tiger file=d:\demo1.dmp tables=emp query='where "sal>2500"'
      或者在>SQL中加上$跑
      SQL>$exp scott/tiger file=d:\demo1.dmp tables=emp query='where "sal>2500"'
    
      字符集问题 16进制->354对应字符集id->852,查询为ZHS16GBK
      select nls_charset_name(852) from dual;
      更改字符集为WE8ISO8859P1,查询该id得到为31,转为16进制->1F
      select nls_charset_id('WE8ISO8859P1') from dual;
    

  

  

  • expdp/impdp 数据泵, 并行(大任务, 足够多的空闲资源)

      CMD>expdp scott/tiger file=d:\demo2.dmp tables=emp
      报错:
      ORA-39001: 参数值无效
      ORA-39000: 转储文件说明错误
      ORA-39088: 文件名不能包含路径说明
      SQL>create directory sha as 'd:\dxp' 只是在数据库中声明,实际目录需要在文件中创建好
      SQL>grant read,write on directory sha to scott;
      CMD>expdp scott/tiger directory=sha file=demo2.dmp tables=emp
      SQL>conn scott/tiger
      SQL>drop table emp;
      CMD>impdp scott/tiger directory=sha file=demo2.dmp tables=emp
    

  

  

• 数据库<—>数据库

• 数据库—>数据文件