Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

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

Document Type | Technical Information

Category | Backup/Recovery

Document Number | TBATI031

 

Overview

Practice the settings and deletion process step-by-step until the table is dropped.
 

Execution Order

  1. Create test tablespace and user
  2. Create test tables
  3. Query objects
  4. Enable Recyclebin
  5. Query Recyclebin
  6. Delete table (DROP TABLE)
  7. Error occurrence due to table deletion
  8. Query objects
  9. Query Recyclebin
 
 

Method

1. Create test tablespace and user
SQL> CONNECT SYS/TIBERO
Connected to Tibero.

SQL> DROP TABLESPACE TBS_EDU INCLUDING CONTENTS AND DATAFILES;

Tablespace 'TBS_EDU' dropped. SQL> DROP USER EDU CASCADE;

User 'EDU' dropped.

SQL> CREATE TABLESPACE TBS_EDU DATAFILE 'TBS_EDU01.DTF' SIZE 10M;

Tablespace 'TBS_EDU' created.

SQL> CREATE USER EDU IDENTIFIED BY EDU;

User 'EDU' created.

SQL> GRANT CONNECT, RESOURCE TO EDU;

Granted.

2. Create test tables
SQL> CONN EDU/EDU
Connected to Tibero.

SQL> CREATE TABLE T10 (C1 VARCHAR(10)) TABLESPACE TBS_EDU;

Table 'T10' created.

SQL> CREATE UNIQUE INDEX PK_T10 ON T10(C1) TABLESPACE TBS_EDU;

Index 'PK_T10' created.

SQL> ALTER TABLE T10 ADD CONSTRAINT PK_T10 PRIMARY KEY(C1);

Table 'T10' altered.

SQL> CREATE TABLE T100(C1 NUMBER NOT NULL, C2 NUMBER(10), C3 VARCHAR(10)) TABLESPACE TBS_EDU;

Table 'T100' created.

SQL> CREATE UNIQUE INDEX PK_T100 ON T100(C1) TABLESPACE TBS_EDU;

Index 'PK_T100' created.

SQL> ALTER TABLE T100 ADD CONSTRAINT PK_T100 PRIMARY KEY(C1);

Table 'T100' altered.

SQL> CREATE INDEX IDX_T100_C2 ON T100(C2) TABLESPACE TBS_EDU;

Index 'IDX_T100_C2' created.

SQL> ALTER TABLE T100 ADD CONSTRAINT FK_T100 FOREIGN KEY(C3) REFERENCES T10(C1);

Table 'T100' altered.

SQL> INSERT INTO T10 VALUES ('TIBERO');

1 row inserted.

SQL> INSERT INTO T100 SELECT LEVEL, LEVEL, 'TIBERO' FROM DUAL CONNECT BY LEVEL <= 10;

10 rows inserted.

SQL> COMMIT;

Commit completed.

SQL> SELECT * FROM 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

TIBERO 10 rows 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           IDX_T100_C2      VALID
T100           PK_T100          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
--------------  ------------  ----------  ------
IDX_T100_C2     INDEX         2858        VALID
PK_T10          INDEX         2855        VALID
PK_T100         INDEX         2857        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_T10              PRIMARY KEY   ENABLED   T10          PK_T10
PK_T100             PRIMARY KEY   ENABLED   T100         PK_T100
FK_T100             REFERENTIAL   ENABLED   T100         
EDU_CON45300321     NOT NULL      ENABLED   T100  

4 rows selected.

4. Enable Recyclebin
SQL> ALTER SYSTEM SET USE_RECYCLEBIN=Y;

System altered.

SQL> SELECT VALUE FROM V$PARAMETERS WHERE NAME='USE_RECYCLEBIN';

VALUE
----------
YES

1 row selected.

SQL> PURGE RECYCLEBIN;
Purged.
 
5. 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.

6. Delete table (DROP TABLE)
SQL> DROP TABLE EDU.T100;

Table 'EDU.T100' dropped.

7. Error occurrence due to table deletion
SQL> SELECT /*+EDUTEST*/ c1, c2, c3, sysdate FROM EDU.T100 WHERE C1=1;

TBR-8033: Specified schema object was not found.

at line 1, column 47 of null:
SELECT /*+EDUTEST*/ c1, c2, c3, sysdate FROM EDU.T100 WHERE C1=1
                                               ^

8. 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

1	row 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

1 row 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        INVALID
EDU_IDX285800     INDEX         2858        INVALID
EDU_TBL285600     TABLE         2856        INVALID
PK_T10            INDEX         2855        VALID
T10               TABLE         2854        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
------------------  ------------  --------  ---------------  ----------------
EDU_CON45400856     PRIMARY KEY   ENABLED   EDU_TBL285600    EDU_IDX285700
PK_T10              PRIMARY KEY   ENABLED   T10              PK_T10
EDU_CON45300010     NOT NULL      ENABLED   EDU_TBL285600    

3 rows selected.

9. 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';

OWNER   OBJECT_NAME      TYPE    TS_NAME  DROPTIME
------- ---------------- ------- -------- ----------------------
EDU     EDU_TBL285600    TABLE   TBS_EDU  2020-09-08:05:28:14

1 row selected.

 

 

 

Related to