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
Posts
I know the dbms_random to generate random number but I have one requirement to create 4 digit random number without using dbms_random package. Is it possible to generate using without dbms_random package? if possible please share me the sample code .
ReplyDeleteNiranjan, I apologize, I'm just seeing your question about a random number generator. Do you still need this function? Lynn
Delete