dbi Blog

By William Sescu

If you think that Flashback Drop feature just brings back your table, then this is only half of the story. It does much more than that. Besides undropping the table, it also brings back your constraints, your indexes, your trigger, your grants and the statistics as well.

The ugly part is, that the flashback drop brings back some strange object names e.g. your indexes and constraints with names like “BIN$…” or alike. Maybe something you don’t want. So why not combining the Flashback Drop with a Flashback Query on the Dictionary to get the old constraint and index names.

Let’s setup a few objects in the SCOTT schema. But before we do that, we need to grant the user SCOTT some extra privileges.

SQL> grant execute on dbms_flashback to scott;

Grant succeeded.

SQL> grant flashback on user_indexes to scott;

Grant succeeded.

SQL> grant flashback on user_constraints to scott;

Grant succeeded.

SQL> grant flashback on user_triggers to scott;

Grant succeeded.

Now we can setup our objects for this test. I will create 2 tables, and few grants, a trigger and statistics. The goal is to have after the flashback to before drop, exactly the same object names afterwards for the table the index, the constraints and the trigger.

SQL> connect scott/tiger
Connected.

SQL> create table dbi_t
  2  ( x int, constraint t_pk primary key(x),
  3   y int, constraint check_x check(x>0)
  4  );

Table created.

SQL> insert into dbi_t values (1,1);

1 row created.

SQL> insert into dbi_t values (2,2);

1 row created.

SQL> insert into dbi_t values (3,3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> create table dbi_audit
  2  (x int, x_before int, y int, y_before int, z varchar2(10));

Table created.


SQL> CREATE OR REPLACE TRIGGER dbi_after_update
  2  AFTER INSERT OR UPDATE
  3     ON DBI_T
  4     FOR EACH ROW
  5  DECLARE
  6     v_z varchar2(10);
  7  BEGIN
  8     SELECT user INTO v_z FROM dual;
  9     -- Insert record into audit table
 10     INSERT INTO dbi_audit
 11     ( x,
 12       x_before,
 13       y,
 14       y_before,
 15       z)
 16     VALUES
 17     ( :new.x,
 18       :old.x,
 19       :new.y,
 20       :old.y,
 21       v_z );
 22* END;
 /

Trigger created.


SQL> insert into dbi_t values (4,4);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into dbi_t values (5,5);

1 row created.

SQL> commit;

Commit complete.

SQL> update dbi_t set x=6 where y=5;

1 row updated.

SQL> commit;

Commit complete.


SQL> select * from dbi_t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3
         4          4
         6          5

SQL> select * from dbi_audit;

         X   X_BEFORE          Y   Y_BEFORE Z
---------- ---------- ---------- ---------- ----------
         4                     4            SCOTT
         5                     5            SCOTT
         6          5          5          5 SCOTT

		 


SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => '"SCOTT"',
  4  tabname => '"DBI_T"',
  5  estimate_percent => 100
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => '"SCOTT"',
  4  tabname => '"DBI_AUDIT"',
  5  estimate_percent => 100
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.


SQL> grant select on dbi_t to hr;

Grant succeeded.

SQL> grant select on dbi_audit to hr;

Grant succeeded.

Ok. So let’s take a look how is the current situation is right now.

SQL> select TABLE_NAME, LAST_ANALYZED
  2  from user_tables
  3  where TABLE_NAME in ('DBI_T','DBI_AUDIT');

TABLE_NAME   LAST_ANALYZED
------------ --------------------
DBI_AUDIT    17-FEB-17
DBI_T        17-FEB-17

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'DBI_T';

CONSTRAINT_NAME                      C
------------------------------------ -
CHECK_X                              C
T_PK                                 P

SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
T_PK

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from user_tab_privs
  2  where table_name in ('DBI_T','DBI_AUDIT');

GRANTEE        OWNER          TABLE_NAME           GRANTOR        PRIVILEGE
-------------- -------------- -------------------- -------------- --------------------
HR             SCOTT          DBI_AUDIT            SCOTT          SELECT
HR             SCOTT          DBI_T                SCOTT          SELECT

SQL> select TRIGGER_NAME, TABLE_NAME, STATUS from user_triggers;

TRIGGER_NAME             TABLE_NA STATUS
------------------------ -------- --------
DBI_AFTER_UPDATE         DBI_T    ENABLED

Everything looks good. Up to date statistics, trigger is enabled and no objects with “BIN$xx” or something. The next step is a quite important one for this demo. I am just saving the SCN number before the “drop table” into a variable. In the real world, you need to find the SCN number yourself, e.g. with the TIMESTAMP_TO_SCN function.

SQL> column SCN new_val S
SQL> select dbms_flashback.get_system_change_number SCN from dual;

       SCN
----------
   1056212

After we got the SCN, we can drop the table and undrop it afterwards.

SQL> drop table dbi_t;

Table dropped.

SQL> flashback table dbi_t to before drop;

Flashback complete.

Let’s take a look how our constraints and index names look right now. Exactly like expected. They have this ugly “BIN$xxx” names, but we want the old names back.

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'DBI_T';

CONSTRAINT_NAME                      C
------------------------------------ -
BIN$SLt7vMNFZNbgU8k4qMDm6g==$0       C
BIN$SLt7vMNGZNbgU8k4qMDm6g==$0       P

SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
BIN$SLt7vMNHZNbgU8k4qMDm6g==$0

The trick is now to invoke a Flashback Query on the dictionary. Flashback query on the dictionary is not 100% supported, but it works. I just save the current index name into the variable “I” and the old name into variable “OI”.

SQL> column index_name new_val I
SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
BIN$SLt7vMNHZNbgU8k4qMDm6g==$0

SQL> column index_name new_val OI
SQL> select index_name from user_indexes as of scn &S
  2  where table_name = 'DBI_T';
old   1: select index_name from user_indexes as of scn &S
new   1: select index_name from user_indexes as of scn    1056212

INDEX_NAME
------------------------------------
T_PK

After I have the current and the old name in place, I can do an alter index and get my old name back.

SQL> alter index "&I" rename to "&OI";
old   1: alter index "&I" rename to "&OI"
new   1: alter index "BIN$SLt7vMNHZNbgU8k4qMDm6g==$0" rename to "T_PK"

Index altered.

SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
T_PK

I will do now exactly the same for the constraints and the trigger.

SQL> column constraint_name new_val CC
SQL> select constraint_name from user_constraints where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C';

CONSTRAINT_NAME
------------------------------------
BIN$SLt7vMNFZNbgU8k4qMDm6g==$0

SQL> column constraint_name new_val OCC
SQL> select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C';
old   1: select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C'
new   1: select constraint_name from user_constraints as of scn    1056212 where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C'

CONSTRAINT_NAME
------------------------------------
CHECK_X


SQL> alter table DBI_T RENAME CONSTRAINT "&CC" TO "&OCC";
old   1: alter table DBI_T RENAME CONSTRAINT "&CC" TO "&OCC"
new   1: alter table DBI_T RENAME CONSTRAINT "BIN$SLt7vMNFZNbgU8k4qMDm6g==$0" TO "CHECK_X"

Table altered.

SQL> column constraint_name new_val PC
SQL> select constraint_name from user_constraints where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P';

CONSTRAINT_NAME
------------------------------------
BIN$SLt7vMNGZNbgU8k4qMDm6g==$0

SQL> column constraint_name new_val OPC
SQL> select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P';
old   1: select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P'
new   1: select constraint_name from user_constraints as of scn    1056212 where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P'

CONSTRAINT_NAME
------------------------------------
T_PK


SQL> alter table DBI_T RENAME CONSTRAINT "&PC" TO "&OPC";
old   1: alter table DBI_T RENAME CONSTRAINT "&PC" TO "&OPC"
new   1: alter table DBI_T RENAME CONSTRAINT "BIN$SLt7vMNGZNbgU8k4qMDm6g==$0" TO "T_PK"

Table altered.

SQL> col CONSTRAINT_NAME format a36
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'DBI_T';

CONSTRAINT_NAME                      C
------------------------------------ -
CHECK_X                              C
T_PK                                 P

SQL> col INDEX_NAME format a36
SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
T_PK


SQL> select TRIGGER_NAME, TABLE_NAME,STATUS from user_triggers;

TRIGGER_NAME                     TABLE_NAME                       STATUS
-------------------------------- -------------------------------- --------
BIN$SLt7vMNIZNbgU8k4qMDm6g==$0   DBI_T                            ENABLED

SQL> column trigger_name new_val T
SQL> select trigger_name from user_triggers where table_name = 'DBI_T';

TRIGGER_NAME
--------------------------------
BIN$SLt7vMNIZNbgU8k4qMDm6g==$0

SQL> column trigger_name new_val OT
SQL> select trigger_name from user_triggers as of scn &S where table_name = 'DBI_T';
old   1: select trigger_name from user_triggers as of scn &S where table_name = 'DBI_T'
new   1: select trigger_name from user_triggers as of scn    1056212 where table_name = 'DBI_T'

TRIGGER_NAME
--------------------------------
DBI_AFTER_UPDATE

SQL> alter trigger "&T" RENAME TO "&OT";
old   1: alter trigger "&T" RENAME TO "&OT"
new   1: alter trigger "BIN$SLt7vMNIZNbgU8k4qMDm6g==$0" RENAME TO "DBI_AFTER_UPDATE"

Trigger altered.


SQL> select TRIGGER_NAME, TABLE_NAME, STATUS from user_triggers;

TRIGGER_NAME             TABLE_NAME             STATUS
------------------------ ---------------------- --------
DBI_AFTER_UPDATE         DBI_T                  ENABLED

The stats and the grants do come back automatically.

SQL> select TABLE_NAME, LAST_ANALYZED
  2  from user_tables
  3  where TABLE_NAME in ('DBI_T','DBI_AUDIT');

TABLE_NAME   LAST_ANALYZED
------------ --------------------
DBI_AUDIT    17-FEB-17
DBI_T        17-FEB-17


SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from user_tab_privs
  2  where table_name in ('DBI_T','DBI_AUDIT');

GRANTEE        OWNER          TABLE_NAME           GRANTOR        PRIVILEGE
-------------- -------------- -------------------- -------------- --------------------
HR             SCOTT          DBI_AUDIT            SCOTT          SELECT
HR             SCOTT          DBI_T                SCOTT          SELECT

Conclusion

The Flashback Drop feature does not just bring back your table. It does much more, it brings back your grants, the trigger, the statistics, the indexes and the constraints as well. If you are lucky, you can even combine it with the Flashback Query to retrieve your old names for the indexes, constraints and triggers.

Post Views: 358

Oracle 12c – Combining Flashback Drop and Flashback Query

By William Sescu

Conclusion

Oracle Team

Leave a Reply:

Related blog articles

By William Sescu

Conclusion

Oracle Team

Leave a Reply:

Related blog articles

A first look at openSUSE Leap Micro 6.0

Another file system for Linux: bcachefs (3) – Mirroring/Replicas

Another file system for Linux: bcachefs (2) – multi device file systems

Another file system for Linux: bcachefs (1) – basics