웹 개발 정보

SQL> SELECT * FROM all_users;

USERNAME    USER_ID    CREATED
------------------------------------------------
USER4           91            22-JAN-19
USER3           90            22-JAN-19
USER2           89            22-JAN-19
USER1           88            22-JAN-19

ROLE

ROLE 설명

CONNECT

RESOURCE

DBA

EXP_FULL_DATABASE

IMP_FULL_DATABASE

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER4' ; -- 시스템 관리자 접속일 때 확인

SQL> SELECT * FROM USER_ROLE_PRIVS; -- ROLE을 확인하고자 하는 계정에 접속일 때 확인

USERNAME      GRANTED_ROLE      ADM      DEF 
------------------------------------------------------------------------------------
USER4             CONNECT              NO         YES
USER4             RESOURCE             NO         YES 

SQL> GRANT DBA TO USER4;

 SELECT ANY TABLE

모든 유저의 테이블 조회 권한 

 CREATE ANY TABLE

모든 유저의 테이블 생성 권한 

 CREATE USER

유저 생성 권한 

 CREATE SESSION

접속 권한 

 CREATE TABLE

테이블 생성 권한 

 CREATE VIEW

뷰 생성 권한 

 CREATE PROCED USER

프로시저 생성 권한 

 CREATE SEQUENCE

시퀀스 생성 권한 

 SYSDBA

DBA 권한 부여 

 SYSOPER

DB 관리 권한 

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER4'; -- 시스템 관리자 접속일 때 확인

SQL> SELECT * FROM USER_SYS_PRIVS; -- 시스템권한을 확인하고자 하는 계정에 접속일 때 확인

GRANTEE      PRIVILEGE                            ADM
----------------------------------------------------------
USER4          CREATE VIEW                       NO
USER4          UNLIMITED TABLESPACE        NO

 SQL> GRANT SYSOPER TO USER4;

SQL> create tablespace SPACE_NAME4 datafile '/home/server/oracle/oradata/db_sid/spaceNameFile4.dbf‘ size 100m;

SQL>  create user USER4 identified by USER4PW default tablespace SPACE_NAME4;

SQL> SELECT USERNAME, DEFAULT_TABLESPACE FROM dba_users;

USERNAME       DEFAULT_TABLESPACE
----------------------------------------------
USER1             SPACE_NAME1
USER2             SPACE_NAME2
USER3             SPACE_NAME3
USER4             SPACE_NAME4

SQL> SELECT TABLESPACE_NAME FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SPACE_NAME1
SPACE_NAME2
SPACE_NAME3

 SQL> SELECT FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;

FILE_ID     TABLESPACE_NAME   FILE_NAME
-----------------------------------------------------------------------------------------------------------------
28             SPACE_NAME1           /home/server/oracle/oradata/db_sid/spaceNameFile1.dbf
29             SPACE_NAME2           /home/server/oracle/oradata/db_sid/spaceNameFile2.dbf
30             SPACE_NAME3           /home/server/oracle/oradata/db_sid/spaceNameFile3.dbf

SQL> SELECT name FROM v$database;

NAME
----------------
db_service_name

 SQL> SELECT instance FROM v$thread;

INSTANCE
-------------
db_sid

 ps -ef | grep oracle

oracle    3934     1  0  2018 ?        00:00:00 oracle (LOCAL=NO)
oracle    3936     1  0  2018 ?        00:00:00 oracle (LOCAL=NO)
oracle    3938     1  0  2018 ?        00:00:02 oracle (LOCAL=NO)
oracle   15325     1  0  2018 ?        00:00:00 ora_reco
oracle   15333     1  0  2018 ?        00:00:00 ora_s000
oracle   15341     1  0  2018 ?        00:04:06 ora_cjq0
oracle   15353     1  0  2018 ?        00:00:16 /home/server/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

 su - oracle

 sqlplus '/as sysdba'

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

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

SQL>

 SELECT * FROM  TABLE AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) where 1=1 -- 10분 이전 데이터 조회

 SELECT * FROM  TABLE AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' HOUR) where 1=1 -- 1시간 이전 데이터 조회 

 SELECT * FROM  TABLE AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' DAY) where 1=1 -- 하루전 데이터 조회