Flashback a Table, Index and Trigger Script

This script performs a flashback of tables, indexes and triggers. If the item already exists, it will be renamed with the base_object number.


1) After displaying a list of tables that are in the Recycle Bin, the script will ask for a table to be flashbacked.

2) If a table with that name already exists, a message is displayed stating that the base number will be added to the name.

3) A report is then displayed showing indexes and triggers that will also be reestablished.

4) The report labeled 4 shows the items recreated.

5) Lets you know what else might have to be done.

Click here for a downloadable attachment.

Define OnOff = Off
rem +--------------------------------------------------------------------------+
rem | Script ID:     FlashTbl.sql
rem |
rem | Purpose:       This performs a flashback of tables, indexes and triggers.
rem |                If the table already exists, it will be renamed with the
rem |                base_object number.
rem |
rem | Developer:     Lynn Tobias
rem | Script Date:   August 08, 2008
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual, User_Recyclebin
rem |                
rem | Called by:     n/a
rem | Calls:         FlashObj.Sql (created in this script)
rem |
rem | Variables:     OnOff          - Used to debug
rem |                Dropped_Table  - Name of table to flashback
rem |                Ctr            - if table found in user_table then '1'
rem |                Renamed_Phrase - the Renamed_Phrase phrase of flashback 
rem |                                 if a table already exists
rem |                New_Name       - the name of the table with the base number
rem |                                 if Ctr=1 otherwise,original name
rem | Output:        n/a
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |                
rem +--------------------------------------------------------------------------+

--Clear Screen

--+- 1 -------------------------------------------------------------------------
--| Show the dropped tables available for selection
--+-----------------------------------------------------------------------------

Set Heading On Newpage 2 Linesize 200 Pagesize 60 Term On
Define xSubHead = '    DROPPED TABLES IN USER_RECYCLEBIN'

Ttitle -
Left 'User: '  _user         col 68 'F L A S H B A C K' col 120 'Date:  ' _DATE  skip 1 -
Left 'Script: ' FlashTbl.Sql col 55 xSubHead            col 120 'Page: ' sql.pno skip 2

Column Object_Name    Heading Object|Name          Format A15 
Column Original_Name  Heading Original|Name        Format A30
Column Operation      Heading Oper-|ation          Format A5
Column Type           Heading Type                 Format A7
Column Ts_Name        Heading T(bl)|S(pc)|Name     Format A5
Column Createtime     Heading Create|Time          Format A10
Column Droptime       Heading Drop|Time            Format A10
Column Dropscn        Heading Drop|Scn             Format 9,999,999
Column Partition_Name Heading Part-|ition|Name     Format A5
Column Can_Undrop     Heading Can|Undrop           Format A6
Column Can_Purge      Heading Can|Purge            Format A5
Column Related        Heading Related              Format 99,999
Column Base_Object    Heading Base|Object          Format 99,999
Column Purge_Object   Heading Purge|Object         Format 99,999
Column Space          Heading Space|(Blocks)       Format 999

Select Object_Name,  Original_Name, Operation,   Type,           Ts_Name,    
       Createtime,   Droptime,      Dropscn,     Partition_Name, Can_Undrop, 
       Can_Purge,    Related,       Base_Object, Purge_Object,   Space
  From User_Recyclebin
 Where Type = 'TABLE'
 Order By Original_Name;

Ttitle Off

--+- 2 -------------------------------------------------------------------------
--| Ask for a table to flashback
--+-----------------------------------------------------------------------------

Accept Dropped_Table Prompt 'Name of table to flashback: '

--+- 3 -------------------------------------------------------------------------
--| If that tablename already exists, CTR is set to '1'. Post a message.
--+-----------------------------------------------------------------------------

Set Heading Off NewPage 1
Column Ctr New_Value Ctr NoPrint 

Select Case When Count(*) = 1
            Then 'Note: A table with that name exists. This will be renamed with base#.'
            Else ' '
        End ,
      --------------------------------------------------------------------------
      Count(*) As Ctr
      --------------------------------------------------------------------------
 From User_Tables
Where Table_Name = Upper('&Dropped_Table')
/
Prompt
--+- 4 ------------------------------------------------------------------------
--| Pull everything that needs to be fixed (table, indexes, triggers).
--| Also, determine the new name if a liked-name table already exists.
--+-----------------------------------------------------------------------------

Create Global Temporary Table GT_Flash
  On Commit Preserve Rows
  As
--------------------------------------------------
Select urb.*,  
       --------------------------------------------- 
               Case When &Ctr = 1 
                    Then Substr(
                                Original_Name||'_'||Base_Object
                               ,1,30
                                )
                    Else Original_Name
                End 
                As 
       New_Name
  From User_Recyclebin urb
 Where Base_Object = 
                    --+- 4.1 ------------------------------------------------
                    --| Get the latest base# where it's the table requested
                    --| This is necessary as there may be more than one table
                    --| with the name selected.
                    --+------------------------------------------------------
                   (
                    Select Max(Base_Object)
                      From User_Recyclebin
                     Where Original_Name  = Upper('&Dropped_Table')
                   );

--+- 5 -------------------------------------------------------------------------
--| If Ctr = 1, build the 'rename' portion of the flashback statement. 
--+-----------------------------------------------------------------------------

Set Term &OnOff
Column Renamed_Phrase New_Value Renamed_Phrase

Select          Case When &Ctr = 1 
                     Then 'Rename To '||New_Name
                     Else ' '
                  End 
                  As 
       Renamed_Phrase
  From GT_Flash
 Where Original_Name  = Upper('&Dropped_Table');

Set Term On

--+- 6 ------------------------------------------------------------------------
--| Display the other objects (Triggers, Indexes)
--+-----------------------------------------------------------------------------

Set Newpage 2
Ttitle On
Define xSubHead = 'ITEMS THAT WILL BE REESTABLISHED AFTER FLASHBACK'
Set Heading On

Select *
  From GT_Flash
 Where Original_Name != Upper('&Dropped_Table')  -- everything but the table
;

--+- 7 -------------------------------------------------------------------------
--| Build the statements to rebuild indexes and triggers. Get the Base_Object 
--| since there can be more than one with that name. (Base_Object (and RELATED) 
--| will match across tables, indexes and triggers.)
--|
--| Examples:
--| ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
--| ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;
--+-----------------------------------------------------------------------------

Spool FlashObj.Sql

Ttitle Off
Set Heading &OnOff Feedback &OnOff Verify &OnOff Term &OnOff

Select 'Alter ' || Type || ' "' || Object_Name || '" Rename To ' ||New_Name ||' ;'
  From GT_Flash
 Where Original_Name != Upper('&Dropped_Table') -- everything but the table
;

Spool Off

--+- 8 -------------------------------------------------------------------------
--| Flashback the table
--+-----------------------------------------------------------------------------

Flashback Table &Dropped_Table To Before Drop &Renamed_Phrase
/
--+- 9 -------------------------------------------------------------------------
--| Bring back indexes and triggers
--+-----------------------------------------------------------------------------

@FlashObj

--+- 9 -------------------------------------------------------------------------
--| Show them anything in User_Objects that is also in our global table
--+-----------------------------------------------------------------------------

Set Term On Heading On

Ttitle On
Column Object_Name Format A30

Define xSubHead = 'ITEM(S) RECOVERED AND NOW FOUND IN USER_OBJECTS';

Select Object_Name, Object_Type, Created, Status
  From User_Objects
 Where Object_Name In 
                     --+- 9.1 --------------------------------------------------
                     --| Find out if there is an active table with that name
                     --+--------------------------------------------------------
                     (
                     Select New_Name
                       From GT_Flash
                     )
;

--+- 10 ------------------------------------------------------------------------
--| Now bring the bad news...
--+-----------------------------------------------------------------------------

Prompt
Prompt Follow-up:
Prompt =========
Prompt Views and procedures defined on the table are not recompiled and remain in the invalid state. -
These old names must be retrieved manually and applied.
Prompt
Prompt Dropped bitmap indexes are not placed in the recycle bin. Constraint names are also not retrievable.
Prompt

--+- 10 ------------------------------------------------------------------------
--| Clean up
--+-----------------------------------------------------------------------------

Truncate Table GT_Flash;
   Drop Table GT_Flash;

Undefine Dropped_Table Ctr Renamed_Phrase New_Name    
Ttitle Off