drop索引是否进回收站的测试-白红宇

drop索引是否进回收站的测试

阅读量：2064 次

发布时间：2019-04-29

本文共 2587 字，大约阅读时间需要 8 分钟。

背景：一次项目中发现单独drop一张表的索引，此索引不进回收站。

结论：经过测试发现，单独drop索引，确实不会进回收站；但若drop建有索引的表，索引和表会一起进回收站。

0、前提：回收站机制打开

SQL> show parameter recyclebin

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string on

1、单独删除表的索引，索引将不会进入回收站

SQL> CREATE TABLE TEST.TEST_RECYCLEBIN_1C(ID NUMBER);

TABLE CREATED.

SQL> CREATE INDEX TEST.IDX_ID_1C ON TEST.TEST_RECYCLEBIN_1C(ID);

INDEX CREATED.

SQL> INSERT INTO TEST.TEST_RECYCLEBIN_1C VALUES(1);

1 ROW CREATED.

SQL> COMMIT;

COMMIT COMPLETE.

SQL> DROP INDEX TEST.IDX_ID_1C;

INDEX DROPPED.

SQL> SELECT COUNT(*) FROM DBA_RECYCLEBIN;

COUNT(*)

----------

2、若直接删含有索引的表，且没purge表，则表和索引都会进入回收站

SQL> CREATE TABLE TEST.TEST_RECYCLEBIN (ID NUMBER PRIMARY KEY);

TABLE CREATED.

SQL> INSERT INTO TEST.TEST_RECYCLEBIN VALUES(3);

1 ROW CREATED.

SQL> COMMIT;

COMMIT COMPLETE.

SQL> DROP TABLE TEST.TEST_RECYCLEBIN;

TABLE DROPPED.

SQL> SELECT COUNT(*) FROM DBA_RECYCLEBIN;

COUNT(*)

----------

SQL> SELECT ORIGINAL_NAME FROM DBA_RECYCLEBIN;

ORIGINAL_NAME

--------------------------------

TEST_RECYCLEBIN

SYS_C0011092

若flashback table TEST.TEST_RECYCLEBIN，恢复的index_name还是回收站的object_name，需要rename索引SQL> select index_name from dba_indexes where table_name='TEST_RECYCLEBIN';

INDEX_NAME

------------------------------

BIN$2HqeHGo3TtLgMx41LQpWaw==$0

alter index test."BIN$2HqeHGo3TtLgMx41LQpWaw==$0" rename to IDX_ID;

3、purge table或者purge index可以彻底删除索引

4、回收站的定义

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_RECYCLEBIN" ("OWNER", "OBJECT_NAME", "ORIGINAL_NAME", "OPERATION", "TYPE", "TS_NAME", "CREATETIME", "DROPTIME",

"DROPSCN", "PARTITION_NAME", "CAN_UNDROP", "CAN_PURGE", "RELATED", "BASE_OBJECT", "PURGE_OBJECT", "SPACE") AS

select u.name, o.name, r.original_name,

decode(r.operation, 0, 'DROP', 1, 'TRUNCATE', 'UNDEFINED'),

decode(r.type#, 1, 'TABLE', 2, 'INDEX', 3, 'INDEX',

4, 'NESTED TABLE', 5, 'LOB', 6, 'LOB INDEX',

7, 'DOMAIN INDEX', 8, 'IOT TOP INDEX',

9, 'IOT OVERFLOW SEGMENT', 10, 'IOT MAPPING TABLE',

11, 'TRIGGER', 12, 'CONSTRAINT', 13, 'Table Partition',

14, 'Table Composite Partition', 15, 'Index Partition',

16, 'Index Composite Partition', 17, 'LOB Partition',

18, 'LOB Composite Partition',

'UNDEFINED'),