Skip to main content

Search

Welcome to Tibero GTS!

Example of Recovering a Table Deleted by the DROP TABLE Command (2)

Document TypeㅣTechnical Information

CategoryㅣBackup/Recovery

Document NumberㅣTBATI032

 

Overview

Recover the deleted table using the RECYCLEBIN and check its status.
 

Execution Order

  1. Table Recovery
  2. Query RECYCLEBIN
  3. Query Objects
  4. Query Recovered Table
 
 

Method

1. Table Recovery
SQL> FLASHBACK TABLE EDU.T100 TO BEFORE DROP;
Flashbacked.

2. Query RECYCLEBIN
SQL> COL OWNER FOR A10
SQL> COL TS_NAME FOR A10
SQL> SELECT OWNER, OBJECT_NAME, TYPE, TS_NAME, DROPTIME FROM DBA_RECYCLEBIN WHERE ORIGINAL_NAME='T100';

0 row selected.

3. Query Objects
SQL> CONN SYS/TIBERO
Connected to Tibero.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME;

TABLE_NAME
------------------------
T10
T100

2 rows selected.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT TABLE_NAME, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME,INDEX_NAME;

TABLE_NAME  INDEX_NAME      STATUS
----------  --------------  -------
T10         PK_T10          VALID
T100        EDU_IDX285700   VALID
T100        EDU_IDX285800   VALID

3 rows selected.

SQL>
SQL> COL OBJECT_NAME FOR A20 SQL> COL OBJECT_TYPE FOR A12
SQL> SELECT O.OBJECT_NAME,O.OBJECT_TYPE, O.OBJECT_ID, O.STATUS
       2 FROM DBA_OBJECTS O
       3 WHERE O.OWNER='EDU'
       4 ORDER BY O.STATUS, O.OBJECT_TYPE, OBJECT_NAME;

OBJECT_NAME      OBJECT_TYPE   OBJECT_ID   STATUS
---------------  ------------  ----------  -------
EDU_IDX285700    INDEX         2857        VALID
EDU_IDX285800    INDEX         2858        VALID
PK_T10           INDEX         2855        VALID
T10              TABLE         2854        VALID
T100             TABLE         2856        VALID

5 rows selected.

SQL>
SQL> SET LINESIZE 120
SQL> COL CONSTRAINT_NAME FOR A22
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT CONSTRAINT_NAME, CON_TYPE, STATUS, TABLE_NAME, INDEX_NAME FROM DBA_CONSTRAINTS WHERE OWNER='EDU';

CONSTRAINT_NAME     CON_TYPE      STATUS    TABLE_NAME   INDEX_NAME
------------------  ------------  --------  -----------  ----------------
PK_T1               PRIMARY KEY   ENABLED   T10          PK_T10
EDU_CON45400856     PRIMARY KEY   ENABLED   T100         EDU_IDX285700
EDU_CON45300010     NOT NULL      ENABLED   T100         

3 rows selected.

4. Query Recovered Table
SQL> SELECT * FROM EDU.T100 ORDER BY C1;

   C1     C2  C3
-----  -----  --------
    1      1  TIBERO
    2      2  TIBERO
    3      3  TIBERO
    4      4  TIBERO
    5      5  TIBERO
    6      6  TIBERO
    7      7  TIBERO
    8      8  TIBERO
    9      9  TIBERO
   10     10  TIBERO
   
10 rows selected.

 

 
 

Related to