Drop table and purge in Oracle Database

Actual Example:

Create or replace procedure pro_droptable IS

Cursor cur is

Select table_name from user_tables where table_name like 'temptable % ';

Drop_ SQL varchar2 (1000 );

Table_number number;

BEGIN

Select count (*) INTO table_number from USER_TABLES WHERE TABLE_NAME LIKE 'tem % ';

For tbname in cur loop

Begin

If table_number <50 then

Exit;

End if;

Drop_ SQL: = 'drop table' | tbname. table_name | 'purge ';

Execute immediate drop_ SQL;

Table_number: = table_number-1;

End;

End loop;

END pro_droptable;

Explanation:

The drop table is placed in the recycle bin (user_recyclebin) instead of deleted directly. In this way, the table information in the recycle bin can be restored or completely cleared.
1. query the recycle bin user_recyclebin to obtain the information of the table to be deleted, and then use the statement
Flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to <new_table_name>];
Restore the table in the recycle bin to the original name or specify a new name. data in the table is not lost.
To completely delete a table, use the following statement: drop table <table_name> purge;
2. clear information in the recycle bin.
Clear the specified table: purge table <table_name>;
Clear the recycle bin of the current user: purge recyclebin;
Clear the recycle bin of all users: purge dba_recyclebin;

Example 2:

Create or replace procedure pro_clean_recycle IS

-- TmpVar NUMBER;

Clean_recycle_ SQL varchar2 (1000 );

BEGIN

-- Purge recyclebin;

Clean_recycle_ SQL: = 'purge recyclebin ';

Execute immediate clean_recycle_ SQL;

END pro_clean_recycle;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12