웹 프로그래밍 노트

1. SQL*Plus에서 확인방법

: 아래의 쿼리는 toad나 오렌지등 다른 툴에서는 사용할 수 없다.

SHOW PARAMETER 파라미터명;

show parameter NLS_LENGTH_SEMANTICS;

2. toad나 오렌지, SQL Developer에서는 데이터 딕셔너리인 시스템 뷰에서 조회를 할 수 있다.

SELECT name, type, value
  FROM SYS.V_$PARAMETER
 WHERE name = '파라미터명';
 
SELECT NAME, TYPE, VALUE
  FROM SYS.V_$PARAMETER
 WHERE NAME IN('TEMP_UNDO_ENABLED', 'UNDO_MANAGEMENT', 'UNDO_RETENTION', 'UNDO_TABLESPACE');

클러스터 인덱스, 넌클러스터 인덱스

클러스터 인덱스 : Oracle에서 IOT(Index Organized Table)와 동일한 개념이다.

넌클러스터 인덱스 : Oracle에서 일반 인덱스(Non-unique Index)와 동일한 개념이다.

참고로 오라클에서는 상대적으로 IOT를 잘 사용하지 않는다.

※ 클러스터 인덱스 설명 URL : https://leechun.tistory.com/41?category=983198

Oracle Index 종류

단일 인덱스(Single column Index) : 인덱스에 하나의 컬럼만 사용

결합 인덱스(Composite Index) : 인덱스에 두 개 이상의 컬럼을 사용

PK 속성은 단일 인덱스로 구성할 수 있고, 결합 인덱스로 구성될 수 있다.

Oracle Index 분류

유니크 인덱스(Unique Index) : 인덱스 구성 컬럼들 값에 중복을 허용하지 않는다.

비유니크 인덱스(Non-unique Index) : 인덱스 구성 컬럼들 값에 중복을 허용한다.

PK 제약 조건으로 컬럼을 선택한다면, 무조건 유니크 인덱스가 구성된다.

Oracle Index 물리적인 구조

B*트리 인덱스 : 트리 형태의 자료 구조를 사용함 (대부분 B*트리 사용함)

비트맵 인덱스 : 값의 종류가 많지 않은 컬럼에 사용함

Oracle 파티션 된 Index 구분

글로벌 인덱스

로컬 인덱스

Oracle 제약사항 조회

SELECT DECODE(A.CONSTRAINT_TYPE,  'P', 'Primary Key',  'R', 'Foreign Key', 
             'C', 'Table Check',  'V', 'View Check',  'U', 'Unique',  '?') AS "유형"
     , SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME
     , B.POSITION
     , SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME
  FROM DBA_CONSTRAINTS A
     , DBA_CONS_COLUMNS B
 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
-- AND A.OWNER = 'MAFCCC'
   AND A.TABLE_NAME = UPPER('테이블 이름')
 ORDER BY  1, 2, 3;

Oracle에서 성능 개선을 위해 실행계획을 사용한다.

아래와 같이 실행계획은 예상 실행계획과 실제 실행계획 방법을 살펴보자

예상 실행계획

Toad, Orange와 같은 Tool에서는 "ctrl+e" 눌러서 예상 실행계획을 편하게 확인하는 경우가 있으나 자세한 정보를 보기 위해서는 아래와 같이 따라한다.

SELECT * FROM T_ORD WHERE ORD_SEQ = 4;

위 쿼리를 예상 실행계획으로 실행하고 싶은 경우엔 아래와 같이 실행한다.

-- 실행계획 만들기
EXPLAIN PLAN FOR
SELECT * FROM T_ORD WHERE ORD_SEQ = 4;

-- 실행계획 확인하기
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

위와 같이 쿼리 2문장을 순서대로 각각 실행하면 아래와 같이 예상 실행계획 결과가 나온다.

PLAN_TABLE_OUTPUT
 
-----------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    44 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |     1 |    44 |     2   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD |     1 |       |     1   (0)|
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORD_SEQ"=4)

-- Id : 실행계획의 오퍼레이션 ID (구분자, 순서아님)
-- Operation : 해당 단계에 수행한 작업 내용
-- Name : 해당 단계에 작업을 수행한 대상 오브젝트(테이블 또는 인덱스)
-- Rows : 해당 단계 수행 시 조회될 예상 데이터 건수
-- Bytes : 해당 단계까지 사용될 예상 데이터양(누적)
-- Cost (%CPU) : 해당 단계까지 사용될 예상 비용(누적)

실제 실행계획

SELECT *
  FROM T_ORD T1
     , M_CUS T2
 WHERE T1.CUS_ID = T2.CUS_ID
   AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
   AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
   AND T2.CUS_GD = 'A';

위 쿼리를 실제 실행계획으로 실행하고 싶은 경우엔 아래와 같이 실행한다.

1번 : 실제 실행계획 만들기(hint 추가)

SELECT /*+ GATHER_PLAN_STATISTICS */
       *
  FROM T_ORD T1
     , M_CUS T2
 WHERE T1.CUS_ID = T2.CUS_ID
   AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
   AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
   AND T2.CUS_GD = 'A';

2번 : 실제 실행계획을 만든 SQL의 SQL_ID찾아내기

SELECT T1.SQL_ID ,T1.CHILD_NUMBER ,T1.SQL_TEXT
  FROM V$SQL T1
 WHERE T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
 ORDER BY T1.LAST_ACTIVE_TIME DESC;

3번 : 실제 실행계획 조회하기(각자의 SQL_ID, CHILD_NUMBER를 사용할 것)

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3ykn80uy9n32d',0,'ALLSTATS LAST'));

위와 같이 1~3번 쿼리를 순서대로 실행하면 아래와 같이 실제 실행계획 결과가 나온다.

PLAN_TABLE_OUTPUT
SQL_ID  3ykn80uy9n32d, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        *   FROM T_ORD T1      , 
M_CUS T2  WHERE T1.CUS_ID = T2.CUS_ID    AND T1.ORD_DT >= 
TO_DATE('20170101','YYYYMMDD')    AND T1.ORD_DT < 
TO_DATE('20170201','YYYYMMDD')    AND T2.CUS_GD = 'A'
 
Plan hash value: 3240201901
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    101 |00:00:00.01 |      12 |       |       |          |
|*  1 |  HASH JOIN         |       |      1 |    170 |    101 |00:00:00.01 |      12 |   779K|   779K| 1234K (0)|
|*  2 |   TABLE ACCESS FULL| M_CUS |      1 |     60 |     60 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T_ORD |      1 |    252 |    146 |00:00:00.01 |       5 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."CUS_ID"="T2"."CUS_ID")
   2 - filter("T2"."CUS_GD"='A')
   3 - filter(("T1"."ORD_DT"<TO_DATE(' 2017-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "T1"."ORD_DT">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

-- Starts : 해당 단계를 수행한 횟수
-- E-Rows : 해당 단계의 예상 데이터 건수
-- A-Rows : 해당 단계의 실제 데이터 건수
-- A-Time : 해당 단계까지 수행된 실제 시간(누적)
-- Buffers : 해당 단계까지 메모리 버퍼에서 읽은 블록 수(논리적 IO 횟수, 누적)
-- Reads : 해당 단계까지 디스크에서 읽은 블록 수(물리적 IO 횟수, 누적)
-- OMem : SQL 처리를 위해 사용한 메모리 수치
-- 1Mem : SQL 처리를 위해 사용한 메모리 수치
-- Used-Mem : SQL 처리를 위해 사용한 메모리 수치

끝.

CONNECT BY LEVEL 은 연속적인 숫자를 조회할 때 사용한다.

1~10 까지 숫자 조회

SELECT LEVEL AS NO
  FROM DUAL
CONNECT BY LEVEL <= 10;

결과 : 

2021년 1월~12월까지 조회

SELECT '2021년 ' || LPAD(LEVEL, 2, 0) || '월' AS DT
  FROM DUAL
CONNECT BY LEVEL <= 12

결과 : 

특정 날짜 구간 조회

SELECT TO_DATE('20200701', 'YYYYMMDD') + (LEVEL-1) AS DT 
  FROM DUAL 
CONNECT BY LEVEL <= (TO_DATE('20200717', 'YYYYMMDD') - TO_DATE('20200701', 'YYYYMMDD')) + 1  -- 17

결과 : 

에러 내용

ORA-12899: value too large for column

"스키마"."테이블명"."컬럼" (actual: m, maximun: n)

→ 해당 테이블 컬럼값의 타입 길이보다 실제 입력된 값이 커서 발생하는 에러이다.

해결 방법

해당 테이블의 컬럼의 타입과 길이를 확인하고, 문제가 발생하는 데이터를 찾는다.

이때 문자열 길이 함수를 사용한다.

에러 처리 방법은 아래 두가지 방법 중 하나를 선택한다.

1. 해당 문제가 되는 데이터를 찾아 값을(길이) 수정한다.

2. 해당 테이블의 컬럼 길이를 수정한다. ( 예 : VARCHAR2(30) → VARCHAR2(40) )

글자 수 구하기 LENGTH()

SELECT * FROM EMP

WHERE LENGTH(ENAME) = 5;

바이트 수 구하기 LENGTHB()

SELECT * FROM EMP

WHERE LENGTHB(ENAME) = 5;

LENGTH 함수는 입력된 문자열의 글자 수를 반환하고 LENGTHB 함수는 바이트 수로 반환한다. LENGTHB 함수는 시스템 문자셋 설정에 따라서 한글을 2byte 또는 3byte로 계산하므로 주의해야 한다.

시스템 문자셋을 확인하는 방법 (몇 byte로 계산되는지), CONVERT()

한글 문자셋

- KO16KSC5601(2byte)

- KO16MSWIN949(2byte)

- UTF8(3byte)

- AL32UTF8(3byte)

SELECT * 
 FROM nls_database_parameters 
WHERE parameter LIKE 'NLS_CHARACTERSET'

[결과]

한글을 2byte로 인식하게 하기 (3byte → 2byte)

SELECT LENGTHB(CONVERT('오라클', 'KO16MSWIN949')) AS LENGTHB
 FROM dual;

[결과]

TRUNC 함수는 주로 소수점 절사 및 날짜의 시간을 없앨 때 사용한다.

SELECT DT

        , TRUNC(DT) --시간 절사

        , NMB

        , TRUNC(NMB) --소수점 절사

FROM TEMP

SELECT DT

        , TRUNC(DT, 'DD') --시간 절사

        , TRUNC(DT, 'HH24') --분, 초 절사

        , TRUNC(DT, 'MI') --초 절사

FROM TEMP

SELECT DT

        , TRUNC(DT, 'YEAR') --월, 일 초기화

        , TRUNC(DT, 'MONTH') --일 초기화

        , TRUNC(DT, 'DAY') --요일 초기화 (일요일)

FROM TEMP

SELECT NMB

        , TRUNC(NMB, 1) --소수점 첫째 절사

        , TRUNC(NMB, 2) --소수점 둘째 절사

        , TRUNC(NMB,-1) --1단위 절사

        , TRUNC(NMB,-2) --10단위 절사

FROM TEMP

select sysdate,
         (sysdate-1/24/60) "1분 전",
         (sysdate-1/24/30) "2분 전",
         (sysdate-1/24/12) "5분 전",
         (sysdate-1/24/6) "10분 전",
         (sysdate-1/144) "10분 전",
         (sysdate-30/1440) "30분 전",
         (sysdate-1/24) "1시간 전",
         trunc(sysdate-1) "1일 전",
         trunc(sysdate-7) "7일 전",
         trunc(sysdate-30) "30일 전",
         trunc(sysdate+1)-6/24 "6AM 전",
         trunc(sysdate+1)-2/24 "2PM 전" 
from dual;

Sysdate : 02/15/2011 14:25:22
1분 전  : 02/15/2011 14:24:22
2분 전  : 02/15/2011 14:23:22
5분 전  : 02/15/2011 14:20:22
10분 전 : 02/15/2011 14:15:22
10분 전 : 02/15/2011 14:15:22
30분 전 : 02/15/2011 13:55:22
1시간 전 : 02/15/2011 13:25:22
1일 전   : 02/14/2011
7일 전   : 02/08/2011
30일 전  : 01/16/2011
6AM 전   : 02/15/2011 18:00:00
2PM 전   : 02/15/2011 22:00:00



select sysdate,
         (sysdate+1/24/60) "1분 후",
         (sysdate+1/24/30) "2분 후",
         (sysdate+1/24/12) "5분 후",
         (sysdate+1/24/6) "10분 후",
         (sysdate+1/144) "10분 후",
         (sysdate+30/1440) "30분 후",
         (sysdate+1/24) "1시간 후",
         trunc(sysdate+1) "1일 후",
         trunc(sysdate+7) "7일 후",
         trunc(sysdate+30) "30일 후",
         trunc(sysdate+1)+6/24 "6AM 후",
         trunc(sysdate+1)+2/24 "2PM 후" 
from dual;

Sysdate  : 02/15/2011 14:19:34
1분 후     : 02/15/2011 14:20:34
2분 후     : 02/15/2011 14:21:34
5분 후     : 02/15/2011 14:24:34
10분 후    : 02/15/2011 14:29:34
10분 후   : 02/15/2011 14:29:34
30분 후   : 02/15/2011 14:49:34
1시간 후 : 02/15/2011 15:19:34
1일 후    : 02/16/2011
7일 후    : 02/22/2011
30일 후  : 03/17/2011
6AM 후  : 02/16/2011 06:00:00
2PM 후  : 02/16/2011 02:00:00

Oracle 10g 부터는 데이터의 백업과 복원을 위해서 기존에 사용되는 exp/imp툴을 대신할 Oracle Data Pump(expdp/impdp) 유틸리티가 제공되어 집니다. exp/imp 툴이 없어진것은 아닙니다. Oracle 11g 부터는 기존의 Original  exp/imp툴에 대한  일반적인 사용지원이 되지 않는다고 합니다.

Oracle Data Pump 유틸리티는 더욱 유연하고, 빠른 속도의 기존의 exp/imp를 대체할 유틸리티 라고 합니다. 기존의 exp/imp와의 차이점을 간단히 알아보겠습니다.

1. exp/imp는 덤프 파일을 유틸리티를 실행하는 컴퓨터에 생성합니다. expdp/impdp 는 덤프 파일이 데이터베이스 서버의 지정된 폴더에 생성됩니다. 즉, 덤프 파일을 받고 올리기 위해서 서버에 로그인해야만 합니다.

2. exp/imp는 덤프파일이 생성된 서버의 버전이 다를 경우 호환이 되지 않습니다. 메이저 버전 뿐만 아니라, 마이너 버전 사이에도 호환이 거의 안되는것 같습니다. expdp/impdp 는 실행시 버전을 지정하여 그에 맞는 덤프 파일을 생성할 수 있습니다.

요약

1. expdp, impdp 사용한다.

2. 출발지 DB에서 DATA_PUMP_DIR 경로를 확인한다.

SELECT * FROM ALL_DIRECTORIES;

3. expdp full 방식으로 export 한다. (cmd 창에서)

expdp [db계정]/[db계정PW] full=Y directory=DATA_PUMP_DIR dumpfile=expdp_20211101.dmp logfile=expdp_20211101.log

4. DB dump 파일(.dmp)을 복사하여 목적지 DB가 있는 PC 또는 서버에 복사한다.

5. import하기 전, 출발지 DB에서 tablespace 구성을 확인한다.

 1> 목적지 db에서 sqlplus 또는 toad, orange for oracle에 system 계정으로 접속하여 tablespace 경로를 확인한다.

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스"
    ,SUBSTRB(FILE_NAME, 1, 50) AS "파일명"
    ,TO_CHAR(BLOCKS, '999,999,990') AS "블럭수"
    ,TO_CHAR(BYTES, '99,999,999') AS "크기"
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME, FILE_NAME;

 2> tablespace 편집을 누르면 옵션을 확인할 수 있다.  

6. 목적지 DB의 tablespace와 비교하여 없는 tablespace를 목적지 DB에서 다 생성할 준비를 한다.

  - 목적지에서 DATAFILE의 경로는 자유롭게 확인하여 설정한다. (위에 5-1 쿼리로 확인 가능)

CREATE TABLESPACE TS_TEMP
     DATAFILE 'D:\app\82103\oradata\orcl\TS_TEMP.DEF'
     SIZE 10M
     AUTOEXTEND ON NEXT 1M
     MAXSIZE UNLIMITED
     LOGGING
     ONLINE 
     EXTENT MANAGEMENT LOCAL AUTOALLOCATE
     BLOCKSIZE 8K
     SEGMENT SPACE MANAGEMENT MANUAL
     FLASHBACK OFF;

7. 출발지 DB에서 사용했던 DB계정과 동일하게 목적지 DB에서 생성하고, 권한을 부여한다. (PW는 달라도 됨)

8. 목적지 DB에서 impdp 실행한다. (cmd 창에서)

impdp [db계정]/[db계정 PW] full=Y directory=DATA_PUMP_DIR dumpfile=EXPDP_20211101.dmp logfile=expdp_20211101.log

결론

윈도우 서버에 있는 DB data를 노트북 로컬 DB로 옮기는 작업을 해봤다.

export까지는 문제없었으나, import 시 tablespace를 미리 생성안하면 오류가 나고 진행이 안되서 많이 당황했었다.

위에 요약에서 5, 6번 과정이 핵심인 것 같다.

아래는 방식별 상세 내용이다.

요약을 중심으로 확인하면 될 것 같다.

exp, imp 방식

자신이 사용중인 오라클DB의 데이터를 다른곳 (다른PC)로 옮기고 싶을때 데이터베이스를 덤프/임포트 하는 방법

가장먼저 DB덤프에서 임포트하는 단위는 3가지가 있습니다.

첫째 전체단위, 둘째 사용자 단위, 세번째 테이블 단위입니다.

1. 전체다위

덤프(익스포트) |

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y

임포트

C:\) imp userid=system/비밀번호 file='C:\파일명.dmp' full=y

인데 간혹 버퍼 사이즈가 맞지 않아 에러가 뜨는경우가 있다.

해결방법 : 제가 한 방법으로는 계정을 삭제하고 다시 만들어 

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y buffer = 100000000

이런식으로 하여 에러를 수정하였습니다.

2. 사용자 단위(특정 계정/사용자의 DB )

덤프(익스포트) |

C:\) exp userid=계정명/비밀번호 file='C:\파일명.dmp' 

임포트 |

C:\) imp userid=계정명/비밀번호 file='C:\파일명.dmp' 

인데 간혹 버퍼 사이즈가 맞지 않아 에러가 뜨는경우가 있다.

해결방법 : 제가 한 방법으로는 계정을 삭제하고 다시 만들어 

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y buffer = 100000000

이런식으로 하여 에러를 수정하였습니다.

3. 테이블 단위(특정 계정의 특정 테이블만을 추출)

덤프(익스포트) |

C:\) exp userid=계정명/비밀번호 file='C:\파일명.dmp' tables=테이블명

임포트 |

C:\) imp userid=계정명/비밀번호 file='C:\파일명.dmp' 

인데 간혹 버퍼 사이즈가 맞지 않아 에러가 뜨는경우가 있다.

해결방법 : 제가 한 방법으로는 계정을 삭제하고 다시 만들어 

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y buffer = 100000000

이런식으로 하여 에러를 수정하였습니다.

Oracle Data Pump(expdp/impdp) 방식

디렉토리 생성 및 권한 부여

1. 덤프 파일 생성 디렉토리 생성 (/data/dump 사용)

2. 1에서 생성한 디렉토리 oracle에 등록

CREATE OR REPLACE DIRECTORY TEST_DUMP AS '/data/dump';

3. 일반유저에게 디렉토리 읽기, 쓰기 권한 부여

GRANT READ, WRITE ON DIRECTORY TEST_DUMP TO [아이디];

• create session
• create table
• create directory
• read, write on directory

4. 권한 확인

 - 디렉토리 확인

SELECT * FROM ALL_DIRECTORIES;

 - 권한 확인

SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME IN (

SELECT DIRECTORY_NAME FROM ALL_DIRECTORIES

);

 - Oracle 11g r2 이상시 권한 확인

SELECT * FROM DATAPUMP_DIR_OBJS;

데이터 export / import

테이블 export / import (특정 테이블을 export / import 한다.)

$ expdp xeuser/xeuser@xe tables=EMP,DEPT directory=TEST_DUMP dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

$ impdp xeuser/xeuser@xe tables=EMP,DEPT directory=TEST_DUMP dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

• xeuser/xeuser@xe - 아이디/비밀번호$SID
• tables=EMP,DEPT - 테이블을 콤마(,)로 분리해서 나열
• directory=TEST_DUMP - 덤프파일이 생성될 디렉토리
• dumpfile=EMP_DEPT.dmp - 생성될 덤프 파일명
• logfile=expdpEMP_DEPT.log - 로그 파일명으로 덤프파일과 같은 위치에 만들어짐

스키마 export / import

$ expdp xeuser/xeuser@xe schemas=XEUSER directory=TEST_DUMP dumpfile=XEUSER.dmp logfile=expdpXEUSER.log

$ impdp xeuser/xeuser@xe schemas=XEUSER directory=TEST_DUMP dumpfile=XEUSER.dmp logfile=impdpXEUSER.log

• schemas=XEUSER - 주어진 사용자(schema)가 가진 전체 객체를 export / import 함

데이터베이스 전체 export / import

$ expdp system/password@xe full=Y directory=TEST_DUMP dumpfile=XE.dmp logfile=expdpXE.log

$ impdp system/password@xe full=Y directory=TEST_DUMP dumpfile=XE.dmp logfile=impdpXE.log

• 전체 작업은 권한이 있는 system 계정으로 진행
• full=Y - 전체 데이터베이스를 대상으로 함

데이터베이스 Link export / import

데이터베이스 Link를 통해서 export / import 하기 위해서는 로컬과 리모트 사용자 모두에게 EXP_FULL_DATABASE / IMP_FULL_DATEBASE 룰이 부여되어 있어야 한다.

다음 예는 로컬계정은 xeuser 이고, 리모트 서버가 REMOTE_SCOTT라는 이름으로 링크 되어 있다고 가정한다. 리모트 계정은 scott 이다.

$ expdp xeuser/xeuser@xe tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DUMP dumpfile=EMP.dmp logfile=expdpEMP.log

$ impdp xeuser/xeuser@xe tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DUMP logfile=impdpSCOTT.log remap_schema=SCOTT:XEUSER

• remap_schema-SCOTT:XEUSER - SCOTT계정의 객체들을 XEUSER 계정으로 로드

버전을 지정하여 export (버전은 9.2 이상만 지정 가능)

$ expdp xeuser/xeuser@xe schemas=XEUSER version=10.2 directory=TEST_DUMP dumpfile=XEUSER.dmp logfile=expdpXEUSER.log

그외 자세한 옵션에 대해서는 https://oracle-base.com/articles/10g/oracle-data-pump-10g 참고

오라클 공식 홈페이지를 XE 버전만 있고,

SE 버전은 찾기 힘들었습니다.

아래 URL 접속하여 database로 검색 후 필요한 부분만 체크하여 다운로드 받으면 됩니다 ^^

Oracle 11g SE 다운로드 URL (2020.12.24 기준)

edelivery.oracle.com  

프로시저를 실행했을 때 아래와 같이 오류가 나오는 경우가 있다.

오류 보고 -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

해결 방법

sysdba로 접속하여 아래와 같이 입력 ( SCOTT = user명 )

SQL>GRANT CREATE PROCEDURE TO SCOTT;

오라클 사이트에서 11g XE windows 버전으로 다운로드 후 설치

(설치파일 필요하신 분 댓글로 문의주세요.)

DB 접속 방법

1) cmd 실행

2) splplus / as sysdba

 - 관리자로 DB 접속하는 명령어

3) scott 계정 생성 및 권한 부여

4) 리스너 재시작, SQL서버 재시작

DROP TABLE emp ;
CREATE TABLE emp
 (empno    NUMBER(4) NOT NULL,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7, 2),
  comm     NUMBER(7, 2),
  deptno   NUMBER(2));
 

INSERT INTO emp VALUES (7369, 'SMITH',  'CLERK',     7902, DATE'1980-12-17',  800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN',  'SALESMAN',  7698, DATE'1981-02-20', 1600,  300, 30);
INSERT INTO emp VALUES (7521, 'WARD',   'SALESMAN',  7698, DATE'1981-02-22', 1250,  500, 30);
INSERT INTO emp VALUES (7566, 'JONES',  'MANAGER',   7839, DATE'1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN',  7698, DATE'1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE',  'MANAGER',   7839, DATE'1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK',  'MANAGER',   7839, DATE'1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT',  'ANALYST',   7566, DATE'1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING',   'PRESIDENT', NULL, DATE'1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN',  7698, DATE'1981-09-08', 1500,    0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS',  'CLERK',     7788, DATE'1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES',  'CLERK',     7698, DATE'1981-12-03',  950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD',   'ANALYST',   7566, DATE'1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK',     7782, DATE'1982-01-23', 1300, NULL, 10);

DROP TABLE dept ;
CREATE TABLE dept
 (deptno NUMBER(2),
  dname  VARCHAR2(14),
  loc    VARCHAR2(13) );
 
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO dept VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

DROP TABLE bonus ;
CREATE TABLE bonus
  (ename VARCHAR2(10),
   job   VARCHAR2(9),
   sal   NUMBER,
   comm  NUMBER);

DROP TABLE salgrade ;
CREATE TABLE salgrade 
(
  grade   NUMBER,
  losal   NUMBER,
  hisal   NUMBER
) ;

INSERT INTO salgrade VALUES (1,  700, 1200);
INSERT INTO salgrade VALUES (2, 1201, 1400);
INSERT INTO salgrade VALUES (3, 1401, 2000);
INSERT INTO salgrade VALUES (4, 2001, 3000);
INSERT INTO salgrade VALUES (5, 3001, 9999);
 
COMMIT;