oracle删除表未进回收站

2021/02/05 09:12
阅读数 33

开发说删除了一张表想找回,到回收站查,发现没有,确认没有加purge。还有一种情况,启用了fga的表在drop掉是不会进回收站的,惊喜不?



--create table
SQL> create table scott.test as select * from dba_objects;

--create policy
begin
dbms_fga.add_policy (
object_schema=>'SCOTT',
object_name=>'test',
policy_name=>'test_audit',
audit_column => 'OBJECT_ID',
audit_condition => 'OBJECT_ID < 30',
statement_types => 'SELECT,INSERT,UPDATE',
audit_column_opts => DBMS_FGA.ANY_COLUMNS,
enable => true );
end;
/

--policy enable
col policy_text for a30
select object_schema,object_name,policy_name,policy_text,enabled from dba_audit_policies;

--test sqls
conn scott/admin
select count(*) from test where OBJECT_ID < 20;
select count(*) from test where OBJECT_ID = 20;
select count(*) from test where OBJECT_ID >= 50;

--check
conn / as sysdba
select DB_USER,statement_type,SQL_TEXT from dba_fga_audit_trail;
DB_USER           SQL_TEXT
----------------- ----------------------------------------------------------------------------------------------------
SCOTT             select count(*) from test where OBJECT_ID < 20;
SCOTT             select count(*) from test where OBJECT_ID = 20;

--
create table scott.aa as select * from dba_objects; 

-- drop table
conn scott/admin
drop table aa;
drop table test;

见证奇迹的时刻:
SYS@ORCL> select owner,original_name,object_name from dba_recyclebin;
owner   original_name   object_name
------  --------------- -------------
APP    AA        BIN$ulScLZIsCmXgU49oqMBg8Q==$0    <<只有aa表

Doc ID 435998.1

  • Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.

  • Partitioned index-organized tables are not protected by the recycle bin.


展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部