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
- Create test tablespace and user
- Create test tables
- Query objects
- Enable Recyclebin
- Query Recyclebin
- Delete table (DROP TABLE)
- Error occurrence due to table deletion
- Query objects
- 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.