Document TypeㅣTechnical Information
CategoryㅣBackup/Recovery
Document NumberㅣTBATI032
Overview
Recover the deleted table using the RECYCLEBIN and check its status.
Execution Order
- Table Recovery
- Query RECYCLEBIN
- Query Objects
- 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.