ORACLE GRANT CONNECT, RESOURCE 그리고 FLASHBACK

1. ORACLE GRANT CONNECT, RESOURCE

http://kohseok21.blog.me/150004459576

DB에 접속하려면 최소한 CREATE SESSION 권한이 있어야 함.

CONNECT : ALTER SESSION, CREATE SESSION, CREATE DATABASE LINK, CREATE SEQUENCE,
CREATE SESSION, CREATE SYNONUM, CREATE VIEW

- 11g에는 connect에 create view 권한은 없습니다

RESOURCE : CREATE CLUSTER/INDEXTYPE/OPERATOR/PROCEDURE/SEQUENCE/TABLE/TRIGGER/TYPE

GRANT ALL : DROP/CREATE/ALTER

2. FLASHBACK 사용법

SQL> DROP TABLE T1;
(만약, SQL> DROP TABLE T1 PURGE; 했다면 FLASHBACK을 사용해서 복구 불능)

SQL> SELECT * from tab;

BIN$.....

SQL> show recyclebin
SQL> select * from "BIN$각자이름이 상이";

SQL> flashback table t1 to before drop;

SQL> select * from tab;
SQL> show recyclebin

SQL> DESC USER_TABLES;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TABLE_NAME                                                                                                        NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                                                                                            VARCHAR2(30)
 CLUSTER_NAME                                                                                                               VARCHAR2(30)
 IOT_NAME                                                                                                                   VARCHAR2(30)
 STATUS                                                                                                                     VARCHAR2(8)
 PCT_FREE                                                                                                                   NUMBER
 PCT_USED                                                                                                                   NUMBER
 INI_TRANS                                                                                                                  NUMBER
 MAX_TRANS                                                                                                                  NUMBER
 INITIAL_EXTENT                                                                                                             NUMBER
 NEXT_EXTENT                                                                                                                NUMBER
 MIN_EXTENTS                                                                                                                NUMBER
 MAX_EXTENTS                                                                                                                NUMBER
 PCT_INCREASE                                                                                                               NUMBER
 FREELISTS                                                                                                                  NUMBER
 FREELIST_GROUPS                                                                                                            NUMBER
 LOGGING                                                                                                                    VARCHAR2(3)
 BACKED_UP                                                                                                                  VARCHAR2(1)
 NUM_ROWS                                                                                                                   NUMBER
 BLOCKS                                                                                                                     NUMBER
 EMPTY_BLOCKS                                                                                                               NUMBER
 AVG_SPACE                                                                                                                  NUMBER
 CHAIN_CNT                                                                                                                  NUMBER
 AVG_ROW_LEN                                                                                                                NUMBER
 AVG_SPACE_FREELIST_BLOCKS                                                                                                  NUMBER
 NUM_FREELIST_BLOCKS                                                                                                        NUMBER
 DEGREE                                                                                                                     VARCHAR2(10)
 INSTANCES                                                                                                                  VARCHAR2(10)
 CACHE                                                                                                                      VARCHAR2(5)
 TABLE_LOCK                                                                                                                 VARCHAR2(8)
 SAMPLE_SIZE                                                                                                                NUMBER
 LAST_ANALYZED                                                                                                              DATE
 PARTITIONED                                                                                                                VARCHAR2(3)
 IOT_TYPE                                                                                                                   VARCHAR2(12)
 TEMPORARY                                                                                                                  VARCHAR2(1)
 SECONDARY                                                                                                                  VARCHAR2(1)
 NESTED                                                                                                                     VARCHAR2(3)
 BUFFER_POOL                                                                                                                VARCHAR2(7)
 ROW_MOVEMENT                                                                                                               VARCHAR2(8)
 GLOBAL_STATS                                                                                                               VARCHAR2(3)
 USER_STATS                                                                                                                 VARCHAR2(3)
 DURATION                                                                                                                   VARCHAR2(15)
 SKIP_CORRUPT                                                                                                               VARCHAR2(8)
 MONITORING                                                                                                                 VARCHAR2(3)
 CLUSTER_OWNER                                                                                                              VARCHAR2(30)
 DEPENDENCIES                                                                                                               VARCHAR2(8)
 COMPRESSION                                                                                                                VARCHAR2(8)
 DROPPED                                                                                                                    VARCHAR2(3)

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME                                                                                                                                                                                             
------------------------------                                                                                                                                                                         
T_MASTER                                                                                                                                                                                               
T_TX                                                                                                                                                                                                   
T2                                                                                                                                                                                                     
BONUS                                                                                                                                                                                                  
DEPARTMENTS                                                                                                                                                                                            
DEPT                                                                                                                                                                                                   
DUMMY                                                                                                                                                                                                  
EMPLOYEES                                                                                                                                                                                              
EMP                                                                                                                                                                                                    
JOBS                                                                                                                                                                                                   
JOB_GRADES                                                                                                                                                                                             
JOB_HISTORY                                                                                                                                                                                            
LOCATIONS                                                                                                                                                                                              
REGIONS                                                                                                                                                                                                
SALGRADE                                                                                                                                                                                               
EMP2                                                                                                                                                                                                   
T1                                                                                                                                                                                                     
T_SIMIN                                                                                                                                                                                                
T_BOOK                                                                                                                                                                                                 
T3                                                                                                                                                                                                     
T_NEWS                                                                                                                                                                                                 
T4                                                                                                                                                                                                     
T5                                                                                                                                                                                                     
DEPT_TEST                                                                                                                                                                                              
EMP_TEST                                                                                                                                                                                               
EMPLOYEES2                                                                                                                                                                                             
EMP_TEST2                                                                                                                                                                                              
MY_EMPLOYEE                                                                                                                                                                                            
COUNTRIES                                                                                                                                                                                              

29 rows selected.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                                                                 
---------------- ------------------------------ ------------ -------------------                                                                                                                       
DEPT_TEST        BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE        2012-11-13:13:52:58                                                                                                                       
EMP_TEST2        BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE        2012-11-13:15:05:50                                                                                                                       
SQL> DROP TABLE T1;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                                                                 
---------------- ------------------------------ ------------ -------------------                                                                                                                       
DEPT_TEST        BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE        2012-11-13:13:52:58                                                                                                                       
EMP_TEST2        BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE        2012-11-13:15:05:50                                                                                                                       
T1               BIN$M+dwv6RYRrCyaU89JNpK4A==$0 TABLE        2012-11-13:19:01:06                                                                                                                       
SQL> FLASHBACK TABLE T1 TO BEFORE DROP;

Flashback complete.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                                                                 
---------------- ------------------------------ ------------ -------------------                                                                                                                       
DEPT_TEST        BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE        2012-11-13:13:52:58                                                                                                                       
EMP_TEST2        BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE        2012-11-13:15:05:50                                                                                                                       
SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID                                                                                                                                                      
------------------------------ ------- ----------                                                                                                                                                      
BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE                                                                                                                                                                   
BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE                                                                                                                                                                   
BONUS                          TABLE                                                                                                                                                                   
COUNTRIES                      TABLE                                                                                                                                                                   
DEPARTMENTS                    TABLE                                                                                                                                                                   
DEPT                           TABLE                                                                                                                                                                   
DEPT50                         VIEW                                                                                                                                                                    
DEPT_TEST                      TABLE                                                                                                                                                                   
DUMMY                          TABLE                                                                                                                                                                   
EMP                            TABLE                                                                                                                                                                   
EMP01                          SYNONYM                                                                                                                                                                 
EMP10                          SYNONYM                                                                                                                                                                 
EMP2                           TABLE                                                                                                                                                                   
EMPLOYEES                      TABLE                                                                                                                                                                   
EMPLOYEES2                     TABLE                                                                                                                                                                   
EMPLOYEES_VU                   VIEW                                                                                                                                                                    
EMP_DETAILS_VIEW               VIEW                                                                                                                                                                    
EMP_TEST                       TABLE                                                                                                                                                                   
EMP_TEST2                      TABLE                                                                                                                                                                   
JOBS                           TABLE                                                                                                                                                                   
JOB_GRADES                     TABLE                                                                                                                                                                   
JOB_HISTORY                    TABLE                                                                                                                                                                   
LOCATIONS                      TABLE                                                                                                                                                                   
MY_EMPLOYEE                    TABLE                                                                                                                                                                   
REGIONS                        TABLE                                                                                                                                                                   
SALGRADE                       TABLE                                                                                                                                                                   
SAWON                          SYNONYM                                                                                                                                                                 
SAWON_IR                       VIEW                                                                                                                                                                    
SAWON_R                        VIEW                                                                                                                                                                    
SA_R                           VIEW                                                                                                                                                                    
T1                             TABLE                                                                                                                                                                   
T2                             TABLE                                                                                                                                                                   
T3                             TABLE                                                                                                                                                                   
T4                             TABLE                                                                                                                                                                   
T5                             TABLE                                                                                                                                                                   
T_BOOK                         TABLE                                                                                                                                                                   
T_MASTER                       TABLE                                                                                                                                                                   

TNAME                          TABTYPE  CLUSTERID                                                                                                                                                      
------------------------------ ------- ----------                                                                                                                                                      
T_NEWS                         TABLE                                                                                                                                                                   
T_SIMIN                        TABLE                                                                                                                                                                   
T_TX                           TABLE                                                                                                                                                                   
V1                             VIEW                                                                                                                                                                    

41 rows selected.

SQL> SPOOL OFF