Simple is the BEST

A DB에서 B라는 DB를 접속 하여 작업 하고자 하는 경우


1. B서버의 값이 A서버의 tnaname.ora에 셋팅 되어 있어야 한다.

2. DB Link 생성 권한 확인 (현재 접속된 ID의 권한을 볼수 있다)

    SELECT DISTINCT privilege AS "Database Link Privileges"
    FROM role_sys_privs
    WHERE privilege IN ('CREATE SESSION', 'CREATE DATABASE LINK');


3. 현재 DB Link 확인

    SELECT * FROM all_db_links;


4. DB Link 생성 (사용하고자 하는 유저로 접속)

    CREATE DATABASE LINK [DB링크이름] CONNECT TO [B Server 로그인ID]
    IDENTIFIED BY [B Server 로그인PW] USING ['B Server DB Service Name']


5. 생성된 링크 사용법

    SELECT * FROM [원격 DB 테이블명]@[링크이름]


6. DB Link 삭제

    DROP DATABASE LINK [DB링크이름]

1. 현재 control file 위치 확인
    SQL> select value from v$spparameter where name='control_files';


2. 위치 변경
    SQL> alter system set control_files='path1','path2','path3' scope=spfile;

    위의 path는 full path 를 입력해야 함 (예: D:\xxx\yyy\control01.ctl)


3. 변경된 위치 확인 (3개의 row가 나와야 함)


4. shutdown


5. 변경된 위치로 control file 이동


6. startup

export/import를 이용하여 데이터를 옮기거나 space를 정리할 때 table과 index를 각각 다른 테이블스페이스에 분리하여 저장하기도 한다. 이러한 방법은 Oracle의 속도를 향상시키는 데 많은 도움을 준다.

import할 때 table과 index를 생성시켰던 script를 가지고 있으면 편리하게 이 작업을 할 수 있다. 먼저 import할 때 indexes=N 옵션을 사용하여 Index를 제외한 나머지 object들을 import하고, index는 script에서 테이블스페이스를 지정한 후 SQL file을 실행시켜서 생성하면 된다.

index 생성 script를 가지고 있지 않을 때는 import 옵션중에서 indexfile 옵션을 사용하여 index script를 만들어 낼 수가 있다. 이 방법은 indexfile만 만들기 때문에 속도가 빠르고, Index 뿐만이 아니라 table script도 만들어 지기 때문에 결과적으로 table script도 활용할 수 있다. 다음과 같이 만들 수 있다.

다음 작업은 scott user의 모든 데이터를 test user로 옮기기 위해서 export/import를 이용하는 작업이다. import시에 table과 index는 테이블스페이스를 다르게 하여 저장한다.

1. 다음과 같이 scott user를 export 받는다.

    $ exp  scott/tiger  file=scott.dmp

2. export받은 file에서 index를 제외한 나머지만 import한다.

    $ imp  system/manager  file=scott.dmp  fromuser=scott  touser=test  indexes=n  commit=y

    위와 같이 실행하면 test user에는 index를 제외한 나머지 object만 import된다.

3. 그리고 다음과 같이 indexfile 옵션을 이용하여 index script를 만든다.

    $ imp  system/manager  file=scott.dmp  indexfile=index.sql

위와 같이 명령을 실행하면 실제로 데이터는 import되지 않고 index.sql이라는 파일만 만들어진다. 그리고 파일을 vi 등으로 열어서 확인해보면 create table 문장과 create index 문장이 있고 create table 문장은 REM으로 막혀 있어서 결과적으로 create index문만 실행할 수 있도록 되어있다.

이 화일에서 create index문의 테이블스페이스만 바꾸어서 다음과 같이 SQL*Plus에서 실행시킨다.

$  sqlplus test/test

SQL>  @index

이렇게 하여 table과 index를 다른 테이블스페이스로 분리하여 저장할 수 있다.

이 indexfile 옵션은 index만이 아니라 table 생성문장이 필요할 때도 유용하게 사용하여 활용할 수 있다.

■ INDEX Reorg.

1. 인덱스 삭제 후 새로 생성하는 방법

2. rebuild 하는 방법

    alter index [index name] rebuild;
    alter index [index name] rebuild tablespace [tablespace name];    (인덱스 테이블스페이스를 옮기고자 할 경우)

    index rebuild 작업 중에는 테이블에 대한 DML 작업이 불가능합니다. (insert, update, delete 등)
    만일 Enterprise 버전을 사용한다면 online 옵션으로 index rebuild 작업 중 DML 작업이 가능 (대신 rebuild 시간이 오래 걸림)

    alter index [index name] rebuild online;



■ TABLE Reorg.

1. export / import 하는 방법

2. insert into ... select 쿼리를 이용하여 새로운 테이블로 옮기는 방법

3. alter table

    alter table [table name] move tablespace [tablespace name];    (동일 테이블스페이스로 이동하는 것도 가능)

    index가 존재하는 테이블에 대해 alter table ... move 옵션을 사용하여 작업을 수행할 경우,
    해당 index들이 모두 invalid 상태가 되므로 관련 index들은 반드시 rebuild 해주어야 합니다.

    LOB 컬럼을 가지고 있는 테이블들은 alter table 구문으로 reorg. 되지 않습니다. (위의 1번, 2번 방법 중 하나를 사용)

[개요]

프로시져와 같은 오브젝트를 재컴파일시에 Lock으로 인해 Waiting 현상이 발생할 수 있다.
이러한 경우 ORA-04021 오류 메세지로 나타난다.

04021, 00000, "timeout occurred while waiting to lock object %s%s%s%s%s"
 *Cause:  While waiting to lock a library object, a timeout is occurred.
 *Action: Retry the operation later.


[조치방법] 

1. select SID  from v$access where object='TEST_PROCEDURE';

    → SID = 60

2. select sid, serial#, username, status from v$session where sid=60;

3. alter system kill session 'sid,serial#';

4. 3번에서 해결되지 않으면 OS process를 kill 해야 함

5. select a.spid 
    from v$process a, v$session b
    where a.addr=b.paddr and  b.sid=60;

    → process id = 86662 

6. 서버에 로그인 후 ps -ef|grep 86662

7. kill -9 86662

쿼리 튜닝 시 가장 좋은 방법은 실제 실행된 쿼리문에 대해서 Trace를 떠보는 것입니다.


■ 전제조건

    1. plustrace 롤이 DB에 있어야 함
        → 없으면 sys로 로그인 후 $ORACLE_HOME/sqlplus/admin/plustrace.sql 실행

    2. 쿼리를 실행하는 user가 plustrace 롤을 가지고 있어야 함
        → 없으면 sys로 로그인 후 grant plustrace to [user];

    3. plan_table이 DB에 있어야 함
        → 없으면 user로 로그인 후 $ORACLE_HOME/rdbms/admin/utlxplan.sql 실행


■ Trace 실행

    1. 우선 sys로 로그인하여 아래의 파라미터 값을 확인합니다.
        SQL> show parameter timed
        SQL> show parameter sql_trace

    2. 이제 쿼리를 실행할 user로 로그인합니다.

    3. 위의 파라미터 값들이 둘 다 true라면 다음 단계로 진행, 만일 false라면 아래의 명령 실행 (현재 세션에만 적용)
        SQL> alter session set timed_statistics=true;
        SQL> alter session set sql_trace=true;

    4. 다음 명령 실행
        SQL> set autotrace on
        SQL> execute dbms_session.set_sql_trace(true);

    5. Trace를 뜰 쿼리문을 실행시킵니다.


■ 결과 확인

    1. 결과는 user dump destination 에 저장됩니다.
       → show parameter user로 확인, 보통 $ORACLE_HOME/admin/SID/udump에 위치

    2. 가장 최근의 파일을 찾습니다.
        # ls -altr

    3. 보기 편한 모양으로 만들기 위해 tkprof 명령을 사용합니다.
        # tkprof  [가장최근의덤프파일]  [원하는 파일명]

    4. 만들어진 text 파일을 확인합니다.