DROP TABLE 명령으로 삭제된 테이블 복구 예제 (1)

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.

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.

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.

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.

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.

SQL> DROP TABLE EDU.T100; Table 'EDU.T100' dropped.

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 ^

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.

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.