CODEWITHSUNDEEP
sqloracle-database
Duong
Duong

Reputation: 511

About Oracle PURGE INDEX statement

I'm having a little concern about the SQL PURGE statement of Oracle database. According to Oracle doc, we can purge index , ref here.

But according to Oracle Doc in Administration, index is just associated objects with table. So index can't be purged from recycle bin alone (in fact, it'll be purged when purged table).

Can someone give me an example that I can simulate "Purge index "?

Upvotes: 1

Views: 4020

Answers (1)

William Robertson
William Robertson

Reputation: 16001

If you drop a table that has an index, both the table and its index will be moved to the recyclebin. At this point you can purge the index alone.

create table demo (id integer);

create index demo_ix on demo(id);

drop table demo;

select r.object_name, r.original_name, r.type from user_recyclebin r;

OBJECT_NAME                     ORIGINAL_NAME           TYPE
------------------------------- ----------------------- -------------------------
BIN$SC08VFzrQAGoWW5b/yBRIQ==$0  DEMO_IX                 INDEX
BIN$zIngUcYDRaqvbVqnesnUtQ==$0  DEMO                    TABLE

purge index demo_ix;

select r.object_name, r.original_name, r.type from user_recyclebin r;

OBJECT_NAME                     ORIGINAL_NAME           TYPE
------------------------------- ----------------------- -------------------------
BIN$zIngUcYDRaqvbVqnesnUtQ==$0  DEMO                    TABLE

Upvotes: 3

Related Questions