[오라클] 명령어 모음

안녕하세요. yeTi입니다.

오늘은 오라클에서 사용할만한 명령어들을 알아보겠습니다.

오라클 버젼 : 11g

1. 사용자 암호를 잃어버렸을 경우

- C:\>sqlplus "/as sysdba"
- show user
- alter user [계정] identified by [암호]
   

2. 사용자 계정들 조회

- SYSDBA로 접속

- SELECT USERNAME

  FROM DBA_USERS
       

3. 사용자 추가

- CREATE USER [계정] IDENTIFIED BY [비밀번호] DEFAULT TABLESPACE [테이블스페이스명]

4. 사용자에 권한 추가

- GRANT CONNECT TO [계정]

5. 사용자의 권한 확인

- SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '[계정_대문자]'

6. 권한 롤 확인

- SELECT * FROM DBA_ROLES

- SELECT *

  FROM ROLE_SYS_PRIVS

        WHERE ROLE='[롤 명칭]'

7. 접속한 계정의 테이블 목록 보기

- SELECT *

  FROM TABS

8. Database link 생성

- CREATE DATABASE LINK [database link 명]
CONNECT TO [접속 계정]
IDENTIFIED BY [접속 계정 비밀번호]
USING '[tnsnames.ora]의 정보'

9. Database link를 통한 테이블 조회

- SELECT * FROM TEST@[database link 명]

10. Database 명 확인

- SELECT NAME, DB_UNIQUE_NAME FROM v$database

11. SID 확인

- SELECT instance FROM v$thread

12. Dump Export (해당 소유자의 데이터만 내보내기)

- NLS_LANG 변경

- 리눅스 : export NLS_LANG=KOREAN_KOREA.KO16MSWIN949

- 윈도우 : set NLS_LANG=KOREAN_KOREA.KO16MSWIN949

- exp [계정]/[패스워드]@[SID] file=[덤프 파일] owner=[소유자] statistics=none log=[로그 파일 명]

- expdp [계정]/[패스워드] dumpfile=[Dump파일 명] directory=[디렉토리 명] SCHEMAS=[Export할 계정 명]

13. Dump Import

- imp [계정]/[패스워드]@[SID] file=[덤프 파일] full=Y log=[로그 파일 명]

- imp [시스템 계정]/[패스워드]@[SID] file=[덤프 파일] log=[로그 파일 명] fromuser=[익스포트한 계정] touser=[임포트할 계정]

- impdp [계정]/[패스워드] DIRECTORY=[디렉토리 명] DUMPFILE=[Dump파일 명] 

REMAP_SCHEMA=[Export한 계정 명]:[Import할 계정 명]

REMAP_TABLESPACE=[Export한 Tablespace 명]:[Import할 Tablespace 명]

REMAP_TABLE=[Export한 계정 명].[Export한 테이블 명]:[Import할 테이블 명], ...

TABLES=[Export한 계정 명].[Export한 테이블 명],[Export한 계정 명].[Export한 테이블 명],...

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

INCLUDE=SEQUENCE:\"= '[시퀀스 명]'\"

INCLUDE=SEQUENCE:\"LIKE '%[단어]'\"

14. 다른 테이블에서 조회해서 데이터 삽입

INSERT INTO [테이블 명]( [컬럼명], [컬럼명], ... )
SELECT [컬럼명], [컬럼명], ...
FROM [테이블 명]

15. 디렉토리 조회

SELECT * FROM DBA_DIRECTORIES

16. 디렉토리 생성

CREATE DIRECTORY [디렉토리 명] AS '[디렉토리 경로]'

17. 휴지통 확인

SHOW RECYCLEBIN

18. 휴지통 비우기

- 계정에 속하는 테이블스페이스내의 Object들 비우기

PURGE TABLESPACE [테이블 스페이스 명] USER [계정]

- 특정 테이블 비우기

PURGE TABLE [테이블 명]

19. 현재 사용자의 Object 확인

- SELECT TABLESPACE_NAME, SEGMENT_NAME, SEGMENT_TYPE

FROM USER_SEGMENTS

20. 현재 사용자의 Index 확인

- SELECT INDEX_NAME

FROM USER_INDEXES

21. 대기중인 쿼리 조회

- select /*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */
           s.sid SID, s.username, s.program, p.spid "OS-Pid",w.seconds_in_wait as "W_time(Sec)",
          decode(w.wait_time,0,'Wai-ting', 'Waited') Status, w.ename event,
--              p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','||
--              p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",
           q.SQL_FULLTEXT
from ( select a.*, decode(a.event,'latch free', 'latch free (' ||b.name||')',
                                         'row cache lock', 'row cache lock (' || c.parameter || ')',
                                         'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||
                                                                             chr(bitand(p1,16711680)/65535)||':'||
                                                decode(bitand(p1,65535), 1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',
                              a.event ) ename
           from v$session_wait a, v$latchname b, v$rowcache c
         where a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = 'PARENT'
           and a.event not in ('rdbms ipc message','smon timer','pmon timer','slave wait','pipe get','null event',
                                     'SQL*Net message from client', 'SQL*Net message to client','PX Idle Wait',
                                      'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
                                         'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */
                                         'lock manager wait for remote message', 'single-task message')
        ) w, v$session s, v$process p, v$sql q
where w.sid = s.sid and s.paddr = p.addr
  AND S.USERNAME = '[접속 아이디]'
and s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+)
order by w.ename

22. 수행중인 쿼리 조회

- SELECT
  a.sid,       -- SID
  a.serial#,   -- 시리얼번호
  a.status,    -- 상태정보
  a.process,   -- 프로세스정보
  a.username,  -- 유저
  a.osuser,    -- 접속자의 OS 사용자 정보
  b.sql_text,  -- sql
  c.program    -- 접속 프로그램
FROM
  v$session a,
  v$sqlarea b,
  v$process c
WHERE
  a.sql_hash_value=b.hash_value
  AND a.sql_address=b.address
  AND a.paddr=c.addr
  AND a.status='ACTIVE'