SQLD 테이블 되살리기 스택구조
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 9 11:57:39 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@orcl>startup
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 557846096 bytes
Database Buffers 285212672 bytes
Redo Buffers 5132288 bytes
Database mounted.
Database opened.
SYS@orcl>conn hr/hr
Connected.
테이블 만들었다 지우기 5번 한 후 recyclebin 에서 꺼내는 것을 실습할 것임 ㅇㅇ
1.
HR@orcl>create table a as select * from jobs;
Table created.
HR@orcl>drop table a;
Table dropped.
2.
HR@orcl>create table a as select * from employees;
Table created.
HR@orcl>drop table a
2 ;
Table dropped.
3
HR@orcl>create table a as select * from departments;
Table created.
HR@orcl>drop table a;
Table dropped.
4
HR@orcl>create table a as select * from job_history;
Table created.
HR@orcl>drop table a;
Table dropped.
제일 나중에 만든 테이블을 플래시백 해보기
HR@orcl>flashback table a to before drop rename to b; // 방금 지운 4의 a 테이블의 이름을 b로 지정해서 되살리기
Flashback complete.
HR@orcl>select * from b; // 살아났쥬?
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
100 17-JUN-87 06-APR-18 AD_PRES 90
107 07-FEB-99 06-APR-18 IT_PROG 60
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90
12 rows selected.
계속 순차적으로 저 방법을 쓰긴 어려우니까 쉬운 방법 알려드림 ㅇㅇ
HR@orcl>set linesize 80 // 일단 라인사이즈 조정하시구여
HR@orcl>show recyclebin // recyclebin을 봅시다. a 테이블이 3개나 더 버려져 있군여. 그새 새로운 이름도 받았네여 BIN$!~
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
A BIN$aWGVr4PeoZ3gUAB/AQAR+w==$0 TABLE 2018-04-09:11:59:19
A BIN$aWGVr4PcoZ3gUAB/AQAR+w==$0 TABLE 2018-04-09:11:59:04
A BIN$aWGVr4PXoZ3gUAB/AQAR+w==$0 TABLE 2018-04-09:11:58:47
HR@orcl>desc "BIN$aWGVr4PXoZ3gUAB/AQAR+w==$0" // 어떤 걸 살릴 지 보려면 테이블 구조를 살펴보아야 하니 DESC !
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
HR@orcl>desc "BIN$aWGVr4PcoZ3gUAB/AQAR+w==$0"
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@orcl>flashback table"BIN$aWGVr4PcoZ3gUAB/AQAR+w==$0" to before drop rename to c; // 내가 원하던 그 테이블을 찾았다면 플래시백을 이용하고 꼭!!!!!!!! RENAME해주세요 ! 저는 C로 합니다.
Flashback complete.
HR@orcl>select * from c; // 쨘! 복구 됐쥬?
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
208 Jand Harris
Jaharris 03-APR-18 SA_REP 1100
.05 107 80
210 bloe blarris
yauaoo 03-APR-18 SA_REP 1100
.05 107 80
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
211 David Smith
DASMITH 04-APR-18 SA_REP 1100
.05 107 80
200 Jennifer Whalen
JWHALEN 515.123.4444 17-SEP-87 AD_ASST 5280
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
101 10
201 Michael Hartstein
MHARTSTE 515.123.5555 17-FEB-96 MK_MAN 15600
100 20
202 Pat Fay
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
PFAY 603.123.6666 17-AUG-97 MK_REP 7200
201 20
205 Shelley Higgins
SHIGGINS 515.123.8080 07-JUN-94 AC_MGR 14400
101 110
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
206 William Gietz
WGIETZ 515.123.8181 07-JUN-94 AC_ACCOUNT 9960
205 110
100 Steven Smith
SKING 515.123.4567 17-JUN-87 SA_REP 7700
0 90
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
101 Neena Kochhar
NKOCHHAR 515.123.4568 21-SEP-89 AD_VP 24633
100 90
102 Lex De Haan
LDEHAAN 515.123.4569 13-JAN-93 AD_VP 26979
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 90
103 Alexander Hunold
AHUNOLD 590.423.4567 03-JAN-90 IT_PROG 10800
102 60
104 Bruce Ernst
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
BERNST 590.423.4568 21-MAY-91 IT_PROG 7200
103 60
107 Diana Lorentz
DLORENTZ 590.423.5567 07-FEB-99 SA_REP 6098.4
.05 103 60
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
124 Kevin Mourgos
KMOURGOS 650.123.5234 16-NOV-99 ST_MAN 6960
100 50
141 Trenna Rajs
TRAJS 650.121.8009 17-OCT-95 ST_CLERK 4200
124 50
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
142 Curtis Davies
CDAVIES 650.121.2994 29-JAN-97 ST_CLERK 3720
124 50
143 Randall Matos
RMATOS 650.121.2874 15-MAR-98 ST_CLERK 3120
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
124 50
144 Peter Vargas
PVARGAS 650.121.2004 09-JUL-98 ST_CLERK 3000
124 50
149 Eleni Zlotkey
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
EZLOTKEY 011.44.1344.429018 29-JAN-00 SA_MAN 12600
.2 100 80
174 Ellen Abel
EABEL 011.44.1644.429267 11-MAY-96 SA_REP 14520
.35 149 80
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
176 Jonathon Taylor
JTAYLOR 011.44.1644.429265 24-MAR-98 SA_REP 13735.92
.25 149 80
178 Kimberely Grant
KGRANT 011.44.1644.429263 24-MAY-99 SA_REP 9240
.2 149
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
450 gwon hyejin
koongdori 88570735 06-APR-18 IT_PROG 5000
24 rows selected.
HR@orcl>select * from tab; // 이렇게 해도 C 테이블이 되살아난 걸 확인할 수 있어요!
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AUDIT_EMP TABLE
B TABLE
BIGEMP TABLE
BIG_TABLE TABLE
BIN$aWGVr4PXoZ3gUAB/AQAR+w==$0 TABLE
BIN$aWGVr4PeoZ3gUAB/AQAR+w==$0 TABLE
C TABLE
COPY_EMP TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
DEPARTMENTS2 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
DP_EMP TABLE
EMP TABLE
EMP1 TABLE
EMP2 TABLE
EMPLOYEES TABLE
EMPXT TABLE
EMPXT2 TABLE
JOBS TABLE
JOB_GRADES TABLE
JOB_HISTORY TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LOCATIONS TABLE
LOG_TABLE TABLE
MESSAGES TABLE
MIN_SAL_BY_JOB TABLE
REGIONS TABLE
SYS_FBA_DDL_COLMAP_17841 TABLE
SYS_FBA_HIST_17841 TABLE
SYS_FBA_TCRV_17841 TABLE
SYS_TEMP_FBT TABLE
TEST TABLE
TEST_EMP TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TOP_DOGS TABLE
34 rows selected.