다물8

# flashback table : 휴지통에서 테이블 복구

[hirasvr1:/SYSTEM/oraswno_datafile/DEVDB_DATA]$sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 29 15:25:04 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> grant dba to hr ;

SQL> conn hr/hr
Connected.
SQL> create table emp1 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp1 values ('aaa', 1) ;

1 row created.

SQL> insert into emp1 values ('bbb', 2) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> drop table emp1 ;

Table dropped.

SQL> show recyclebin <- 문제1) 무슨 명령어?
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$XymNUHIAK3vgRAAAAAAAAA==$0 TABLE 2008-12-29:15:26:16

SQL> purge recyclebin; <- 문제2) 무슨 명령어?

Recyclebin purged.

SQL>
SQL> show recyclebin <-- 휴지통에 아무것도 없으니깐 아무것도 안나옴 ^^
SQL>

SQL> create table emp1 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp1 values ('aaa', 1) ;

1 row created.

SQL> insert into emp1 values ('bbb', 2) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> drop table emp1 ;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$XymNUHIBK3vgRAAAAAAAAA==$0 TABLE 2008-12-29:15:33:13

SQL> flashback table emp1 to before drop;

Flashback complete.

SQL> select * from emp1 ;

ENAME ID
---------- ----------
aaa 1
bbb 2

SQL>
SQL> purge recyclebin;

Recyclebin purged.

SQL>
SQL> create table emp2 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp2 values ('aaa', 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> drop table emp2 purge ; <- 문제3) 무슨 명령어?

Table dropped.

SQL> show recyclebin
SQL>

SQL> create table emp3 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp3 values ('aaa', 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> create index index_emp3 on emp3(ename) ;

Index created.

SQL> select index_name from user_indexes where table_name = 'EMP3' ;

INDEX_NAME
------------------------------
INDEX_EMP3

SQL> drop table emp3 ;

Table dropped.

SQL> show recyclebin ;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP3 BIN$XymNUHIEK3vgRAAAAAAAAA==$0 TABLE 2008-12-29:15:55:10
SQL>
SQL> flashback table emp3 to before drop;

Flashback complete.

SQL> select * from emp3 ;

ENAME ID
---------- ----------
aaa 1

SQL> select index_name from user_indexes where table_name = 'EMP3' ;

INDEX_NAME
------------------------------
BIN$XymNUHIDK3vgRAAAAAAAAA==$0 <- 문제4) 이것은 무엇인가?

SQL> alter index "BIN$XymNUHIDK3vgRAAAAAAAAA==$0" rename to index_emp3 ; <- 문제5) 무슨 명령어?

Index altered.

SQL> select index_name from user_indexes where table_name = 'EMP3' ;

INDEX_NAME
------------------------------
INDEX_EMP3

SQL>

===========================================================================================
# flashback query : 과거 특정 시점의 데이터를 확인하고 그때로 돌아가자 ^^

-- 현제 시간과 DB 시간이 다를수 있으니, DB시간을 확인하고 DB시간에 맞춰 작업하세요 ^^

SQL> select to_char(sysdate, 'YYYY/MM/DD HH24:MI') from dual ;

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
29-DEC-08 04.28.16.798523 PM +09:00

SQL> create table emp4 (
2 ename varchar2(10),
3 salary number(10)) ;

Table created.

SQL> insert into emp4 values ('namwon', 100) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
29-DEC-08 04.31.08.784948 PM +09:00

SQL> select * from emp4 ;

ENAME SALARY
---------- ----------
namwon 100

SQL> update emp4
2 set salary=500
3 where ename='namwon' ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from emp4 ; <- 헉, 정남원을 급여가 100만원을 줘야 하는데 실수로 500만원을 줬다. 큰일큰일 ^^

ENAME SALARY
---------- ----------
namwon 500

SQL> select * from emp4 as of timestamp to_timestamp('2008-12-29 16:31','YYYY/MM/DD HH24:MI') ;

ENAME SALARY
---------- ----------
namwon 100

SQL> select * from emp4 as of timestamp to_timestamp('2008-12-29 16:35','YYYY/MM/DD HH24:MI') ;

ENAME SALARY
---------- ----------
namwon 100

SQL> select * from emp4 as of timestamp to_timestamp('2008-12-29 16:38','YYYY/MM/DD HH24:MI') ; <- 이 시간에 실수로 update 했구나.

ENAME SALARY
---------- ----------
namwon 500

SQL> update emp4 set salary = ( select salary from emp4
2 as of timestamp to_timestamp('2008-12-29 16:31','YYYY/MM/DD HH24:MI')
3 where ename='namwon' )
4 where ename='namwon'; <- 16:31 으로 빨리 돌아가자 ^^ 남원 급여는 100만원이야.ㅋㅋ

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from emp4 ;

ENAME SALARY
---------- ----------
namwon 100 <-- 정상적으로 100만원으로 돌아갔음 ^^

SQL>

=================================================================================================

# flashback version query : 과거 특정 기간(시간, SCN)의 이력 데이터 확인 및 그시점으로 flashback

SQL> conn hr/hr
Connected.
SQL> create table emp5 (
2 ename varchar2(10),
3 salary number(10)) ;

Table created.

SQL> insert into emp5 values ('NAMWON', 100);

1 row created.

SQL> insert into emp5 values ('KING', 1000) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 100
KING 1000

SQL> update emp5
2 set salary=200
3 where ename='NAMWON';

1 row updated.

SQL> update emp5
2 set salary=2000
3 where ename='KING' ;

1 row updated.

SQL> commit ;
Commit complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 200
KING 2000

SQL> delete from emp5 ;

2 rows deleted.

SQL> commit ;

Commit complete.

SQL> select versions_startscn, versions_endscn, versions_operation, salary, ename
2 from emp5 versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN V SALARY ENAME
----------------- --------------- - ---------- ----------
968877 D 2000 KING
968877 D 200 NAMWON
967905 968877 U 2000 KING
967905 968877 U 200 NAMWON
967846 967905 I 1000 KING
967846 967905 I 100 NAMWON

6 rows selected.

SQL> alter table emp5 enable row movement ; <- rowid를 보전하지 않기 때문에 row movement 기능 활성화 되어 있어야함.

Table altered.

SQL> flashback table emp5 to scn 967846 ; <- 돌아가고 싶은 VERSIONS_STARTSCN 을 적으면 된다

Flashback complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 100
KING 1000

SQL> flashback table emp5 to scn 967905 ;

Flashback complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 200
KING 2000

SQL> flashback table emp5 to scn 968877 ;

Flashback complete.

SQL> select * from emp5 ;

no rows selected

SQL>

# flashback transaction query : 과거의 어떤 시간이나 SCN 사이의 존재 했던 행들의 값을 질의
트랜잭션에 의해 변화한 행들의 이력을 보여줌

SQL> conn hr/hr
Connected.

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
972858

SQL> create table emp6(
2 ename varchar2(10),
3 salary number(10));

Table created.

SQL> insert into emp6 values ('NAMWON', 100) ;

1 row created.

SQL> insert into emp6 values ('KING', 1000);

1 row created.

SQL> insert into emp6 values ('ALICE', 300) ;

1 row created.

SQL> COMMIT ;

Commit complete.

SQL> update emp6
2 set salary=300
3 where ename='NAMWON' ;

1 row updated.

SQL> update emp6
2 set salary=3000
3 where ename='KING' ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> delete from emp6 ;

3 rows deleted.

SQL> commit ;

Commit complete.

SQL> select versions_startscn, versions_endscn, versions_operation, salary, ename
2 from emp6 versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN V SALARY ENAME
----------------- --------------- - ---------- ----------
973006 D 300 ALICE
973006 D 3000 KING
973006 D 300 NAMWON
972999 973006 U 3000 KING
972999 973006 U 300 NAMWON
972964 973006 I 300 ALICE
972964 972999 I 1000 KING
972964 972999 I 100 NAMWON

8 rows selected.

SQL> select undo_sql from flashback_transaction_query
2 where table_name='EMP6'
3 and commit_scn between 972999 and 973006
4 order by start_timestamp desc;

UNDO_SQL
--------------------------------------------------------------------------------
insert into "HR"."EMP6"("ENAME","SALARY") values ('ALICE','300');
insert into "HR"."EMP6"("ENAME","SALARY") values ('KING','3000');
insert into "HR"."EMP6"("ENAME","SALARY") values ('NAMWON','300');
update "HR"."EMP6" set "SALARY" = '100' where ROWID = 'AAAM4tAAEAAABFEAAA';
update "HR"."EMP6" set "SALARY" = '1000' where ROWID = 'AAAM4tAAEAAABFEAAB';

SQL>

=================================================================================================

# flashback database : 전체 데이터베이스를 과거의 특정 시점으로 돌림

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> shutdown immediate
ORA-01031: insufficient privileges

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@oracle ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 29 23:19:13 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database flashback on ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter system switch logfile ;

System altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
974236

SQL> drop user hr cascade ; <- 제가 젤로 아끼는 hr 유저 drop

User dropped.

SQL> conn hr/hr <- hr 유저가 drop 되서 접속 안됨 ㅠㅠ
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> exit
[oracle@oracle ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 29 23:23:13 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup monut
SP2-0714: invalid combination of STARTUP options
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 974236 ; <- hr 유저 drop전 SCN번호 입력 (각자 환경마다 틀림 ^^)

Flashback complete.

SQL> alter database open resetlogs ;

Database altered.

SQL> conn hr/hr <- hr 유저 복원 ^^
Connected.
SQL>

====================================================================================

해답

SQL> show recyclebin <- 문제1) 무슨 명령어?
답) 휴지통 내용보기

SQL> purge recyclebin; <- 문제2) 무슨 명령어?
답) 휴지통 비우기(휴지통 삭제)

SQL> drop table emp2 purge ; <- 문제3) 무슨 명령어?
답) 테이블 drop 하면서 휴지통에도 동시에 삭제 (flashback 으로 복구 불가 ^^)

INDEX_NAME
------------------------------
BIN$XymNUHIDK3vgRAAAAAAAAA==$0 <- 문제4) 이것은 무엇인가?
답) 테이블이 삭제된후 휴지통으로 들어가면 인덱스명이 바뀐다. 그래서 원래 이름으로 rename 해야 한다 ^^

SQL> alter index "BIN$XymNUHIDK3vgRAAAAAAAAA==$0" rename to index_emp3 ; <- 문제5) 무슨 명령어?
답) 인덱스 이름 바꾸는 명령어

-- 20130311

-- 오라클 데이터 딕션어리 뷰

select *
from dict ;

dba_     : (dba권한) DB 모든 정보 조회 가능
-> grant select_catalog_role to DB계정명 (딕션어리정보만 볼수 있는 권한)
all_       : DB 내가 만든 것 + DB 내가 권한 받은 것
user_    : DB 내가 만든 것
x$        : DB 성능 및 통계정보
v$        : DB 성능 및 통계정보

EX)
select *
from dba_tables ;     all_table ;        user_tables ;

--PK
1. not  null + 중복제거
2. pk컬럼은 자동으로 인덱스 생성
3. 1개 테이블에 pk는 존재 o (pk 1개만 생성), 존재 x
4. 1개 테이블에 1개 또는 여러개의 컬럼으로 pk는 1개 만들 수 있다.

--FK
1. 데이터는 부모없는 자식은 없다.
2. 데이터는 자식없는 부모는 있다.
3. 데이터의 변경 및 삭제는 자식부터 한다.

-- 테이블 생성 스크립트
CREATE TABLE 테이블명  (
   FILE_ID              VARCHAR2(25)                     NOT NULL,
   FILE_ID_FORM         VARCHAR2(6),
   RECP_DT              VARCHAR2(8),
   WRK_TYPE_SEQ         VARCHAR2(6),
   RECV_DT              VARCHAR2(8),
   CONSTRAINT PK_테이블명 PRIMARY KEY (FILE_ID)
   using index
   tablespace 테이블스페이스명
   storage( initial 1M next 1M maxextents unlimited pctincrease 0
freelists 10 freelist groups 2 )
)
tablespace 테이블스페이스명
storage( initial 1M next 1M maxextents unlimited pctincrease 0
freelists 10 freelist groups 2 )
/

-- NOT NULL을 NULL로 바꾸기
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 NULL ) ;

-- NULL을 NOT NULL로 바꾸기
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 NOT NULL ) ;

-- 컬럼 변경
ALTER TABLE 테이블명 MODIFY (컬럼명 VARCHAR2( 20 ) ) ;

-- 컬럼 추가
alter table 테이블명
add        (컬럼명  데이터타입);

-- 컬럼 삭제
alter table 테이블명
drop        column 컬럼명  ;

-- 테이블명 변경
alter table 변경전 테이블명 rename to 변경 후 테이블명 ;

-- 컬럼명 변경
alter table 테이블명 rename column 기존컬럼명 to 새로사용할컬럼명 ;

-- 테이블 삭제
drop table 테이블명 ;

-- 스퀸스 삭제
drop sequence 유저명.시퀀스명 ;

-- 스퀸스 생성
create sequence 유저명.시퀀스명
       increment by 1   -- 증가값
       start with 701     -- 시작값
       MINVALUE 701   -- 최소값
       MAXVALUE 799  -- 최대값
       cache 20           -- 메모리
       cycle ;
             
-- 스퀸스 MAX값 변경      
ALTER SEQUENCE 유저명.시퀀스명 MAXVALUE 99999;    

-- FK 생성시 enable validate 로 변경할때
alter table 테이블명 enable validate constraint  FK명 ;

--FK 생성
ALTER TABLE 자식테이블명 ADD (
  CONSTRAINT  FK명  FOREIGN KEY (컬럼명)
    REFERENCES 부모테이블명 (컬럼명));

--FK 삭제
alter table 자식테이블명 DROP CONSTRAINTS  FK명 ;

--인덱스 추가
CREATE INDEX 인덱스명 ON 테이블명
 (컬럼명)
tablespace 테이블스페이스명
storage(initial 1M next 1M pctincrease 0 maxextents unlimited freelists 10 freelist groups 2);

-- 인덱스 삭제
drop index 인덱스명 ;

-- 인덱스명 변경
alter index 변경전인덱스명 rename TO 변경후인덱스명 ;

-- pk 리빌드
alter index  PK명  rebuild ;

-- PK 변경
select * from dba_segments where segment_name like '%PK명%';

alter table 테이블명 drop primary key;
drop index PK명 ;

alter table 테이블명  add
   CONSTRAINT PK명  PRIMARY KEY (컬럼명)
   using index
   tablespace 테이블스페이스명
   storage( initial 1M next 1M maxextents unlimited pctincrease 0 freelists 10 freelist groups 2 );

-- PK 생성
ALTER TABLE 테이블명  add
   CONSTRAINT  PK명  PRIMARY KEY (컬럼명)
      using index
   tablespace 테이블스페이스명
   storage( initial 1M next 1M maxextents unlimited pctincrease 0 freelists 10 freelist groups 2 )
;
-- 주석 보기
-- 테이블 주석
dba_tab_comments
-- 컬럼 주석
dba_col_comments