4-6 Database Management - Data Pump export and import jobs for data transfer

난이도: ★★★☆☆

문제 1:  ryu유저에 테이블을 2개 생성하고 export한 다음 그 중 한 테이블을 다른 데이터베이스에 같은 유저에 import하여라. (8분)

 

$ sqlplus / as sysdba

CREATE USER ryu IDENTIFIED BY asdf0930k;
GRANT dba, unlimited tablespace TO ryu;

CONN ryu/asdf0930k;
CREATE TABLE tbl_one AS SELECT * FROM dba_objects WHERE rownum <= 1000;
CREATE TABLE tbl_two AS SELECT * FROM dba_objects WHERE rownum <= 100;


$ expdp ryu/asdf0930k directory=mydir dumpfile=export_01.dmp logfile=export_01.log tables=tbl_one,tbl_two


$ export ORACLE_SID=orcl3
$ sqlplus / as sysdba

CREATE USER ryu IDENTIFIED BY asdf0930k;
GRANT dba, unlimited tablespace TO ryu;

$ impdp ryu/asdf0930k directory=mydir dumpfile=export_01.dmp logfile=export_01.log tables=tbl_one,tbl_two

$ sqlplus / as sysdba

SELECT count(*) FROM tbl_one;

 

 

 

 

 

 

 

난이도: ★★★☆☆

문제 2:  유저 ryu에서 dba_objects 뷰를 활용한 4개의 파티션을 가진 range-partitioned table을 생성하라. 그리고 export한 후, 다른 데이터베이스에 접속하여 두 개의 파티션만 import하여라. (10분)

 

CREATE TABLE tbl_one
PARTITION BY RANGE(object_id)
(PARTITION one VALUES LESS THAN (100),
 PARTITION two VALUES LESS THAN (500),
 PARTITION three VALUES LESS THAN (2000),
 PARTITION four VALUES LESS THAN (MAXVAULE))
AS
SELECT	*
FROM	dba_objects;

$ expdp ryu/asdf0930k directory=mydir dumpfile=export_02.dmp logfile=export_02.log tables=tbl_one:one,tbl_one:two


$ export ORACLE_SID=orcl3

$ impdp ryu/asdf0930k directory=mydir dumpfile=export_02.dmp logfile=export_02.log tables=tbl_one:one,tbl_one:two

 

 

 

 

 

난이도: ★★☆☆☆

문제 3: oracul 데이터베이스를 full export하고, 새 데이터베이스를 만들고 import를 진행하여라. (14분)

 

/* import 시에는 impdp 후 똑같은 코드를 작성하면 된다. */
/* CDB 사용 환경에서는, full=yes를 지정하면 해당 컨테이너가 export된다. CDB를 통째로 export하는 경우는 없다. */
$ expdp system/asdf0930k directory=mydir dumpfile=fullexport.dmp logfile=fullexport.log full=yes

 

 

 

 

 

난이도: ★★★☆☆

문제 4: 테이블을 생성하고, ryu 유저에 프로시저와 트리거를 생성하여라. 그리고 이 유저의 테이블과 프로시저만을 export하도록 한다. (10분)

$ expdp system/asdf0930k directory=mydir dumpfile=export_01.dmp include=table include=view include=procedure

 

 

 

 

 

 

난이도: ★★★☆☆

문제 5: ryu 유저에 뷰를 3개 생성한다. 그리고 INCLUDE 파라미터를 사용하여, 뷰의 이름을 지정하여 export를 실행한다. (8분)

 

$ expdp system/asdf0930k directory=mydir dumpfile=export_02.dmp schemas=ryu include=view:"LIKE 'VIEW%'"

 

 

 

 

 

 

난이도: ★★★☆☆

문제 6: 유저 ryu에 대해서 compressed 혹은 uncompressed 덤프파일을 생성하여라. compress 덤프파일과 uncompress 덤프파일의 사이즈를 비교하여라. (8분)

 

/* no compression */
$ expdp system/asdf0930k directory=mydir dumpfile=compress_01.dmp schemas=ryu

/* compress only the data */
$ expdp system/asdf0930k directory=mydir dumpfile=compress_02.dmp schemas=ryu compression=data_only

/* compress only the metadata */
$ expdp system/asdf0930k directory=mydir dumpfile=compress_03.dmp schemas=ryu compression=metadata_only

/* both data and metadata */
$ expdp system/asdf0930k directory=mydir dumpfile=compress_04.dmp schemas=ryu compression=all



$ ls -lrth /tmp/compress*

 

 

 

 

 

 

난이도: ★★★☆☆

문제 7: 유저 ryu의 메타데이터 덤프를 export하여라. (6분)

 

/* export metadata only */
$ expdp system/asdf0930k directory=mydir dumpfile=content schemas=ryu content=metadata_only

/* export data only */
$ expdp system/asdf0930k directory=mydir dumpfile=content schemas=ryu content=data_only

/* both data and metadata, default option */
$ expdp system/asdf0930k directory=mydir dumpfile=content schemas=ryu content=all

 

 

 

 

 

난이도: ★★★★☆

문제 8: 유저 ryu의 덤프파일을 AES256 알고리즘으로 encryption하여 export하여라. (6분)

 

ENCRYPTION 파라미터:

ALL - is used to encrypt both data and metadata

DATA_ONLY - is used to encrypt only data

METADATA_ONLY - is used to encrypt the metadata of exported objects

NONE - is the default which is used to specify that no data will be encrypted.

 

ENCRYPTION_MODE 파라미터:

PASSWORD - is the default value which is used to specify that the user must provide a password to decrypt the dump file.

TRANSPARENT - requires a wallet to be open and doesn't require a password.

DUAL - creates a dump file that accepts either password or a wallet when the file is imported.

 

$ expdp system/asdf0930k directory=mydir dumpfile=exp_encrypted.dmp schemas=ryu encryption=data_only encryption_algorithm=AES256 encryption_mode=password ENCRYPTION_PASSWORD=asdf0930k


/* impdp시 오류발생 */
$ impdp system/asdf0930k directory=mydir dumpfile=exp_encrypted.dmp schemas=ryu
/* impdp 오류 발생 해결 */
$ impdp system/asdf0930k directory=mydir dumpfile=exp_encrypted.dmp schemas=ryu ENCRYPTION_PASSWORD=asdf0930k

 

 

 

 

 

 

난이도: ★★★★☆

문제 9: 유저 ryu의 덤프파일을 estimate하고, DBA_EXTENTS 뷰와 비교하여라. (5분)

 

/* 통계 기반 estimation */
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ryu');
$ expdp system/asdf0930k directory=mydir dumpfile=estimate_01.dmp schemas=ryu estimate=statistics

/* 블록 기반 estimation */
$ expdp system/asdf0930k directory=mydir dumpfile=estimate_02.dmp schemas=ryu estimate=blocks


SELECT	segment_name obj_name, (blocks*8192)/1024 size_kb
FROM	dba_extents
WHERE	owner= 'RYU';

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 10: 덤프파일의 맥시멈 사이즈를 지정하여라. 하나의 덤프파일이 최대 10MB를 못 넘게 지정하여라. (6분)

 

/* 덤프파일이 100k, 즉 10mb를 넘어가면 에러를 발생시키고 export를 정지한다. */
$ expdp system/asdf0930k directory=mydir dumpfile=filesize.dmp filesize=100k schemas=ryu

$ expdp system/asdf0930k directory=mydir dumpfile=filesize%U.dmp filesize=100k

 

 

 

 

난이도: ★★★☆☆

문제 11: 유저 ryu에 DBA_OBJECTS를 참고하는 테이블을 생성한다. SCN 값을 확인하고, 모든 rows를 삭제하고 커밋한 후, 병렬도 2로 현재 상태에서는 삭제된 상태의 row를 포함한 테이블을 export한다. (12분)

 

conn ryu/asdf0930k

CREATE TABLE tbl_test
AS
SELECT	*
FROM	dba_objects;

SELECT	current_scn
FROM	v$database;

DELETE FROM tbl_test;
COMMIT;
SELECT count(1) FROM tbl_test;


$ expdp system/asdf0930k directory=mydir dumpfile=flashback_01.dmp schemas=ryu flashback_scn=3144231 parallel=2

 

 

 

 

 

난이도: ★★★☆☆

문제 12: 유저 ryu에 DBA_SEGMENTS 뷰를 바탕으로 테이블을 2개 생성하고, segment_type이 table이고, extents가 20보다 큰 값들을 export하여라. (12분)

 

$ vi /tmp/parfile01.dat
directory=mydir dumpfile=query01.dmp tables=TABLE01,TABLE02 query=ryu.TABLE01:"WHERE segment_type='TABLE'" query=ryu.TABLE02:"WHERE extents > 20"

$ expdp ryu/asdf0930k parfile=/tmp/parfile01.dat

 

 

 

 

 

난이도: ★★★★★

문제 13: 유저 ryu에 DBA_OBJECTS 뷰를 바탕으로 테이블을 1개 생성하고 expdp를 진행한다.  impdp 시 OBJECT_NAME 칼럼을 소문자로 변환할 수 있도록 한다. (14분)

 

CREATE OR REPLACE PACKAGE ryu.pkg_remap
AS
	FUNCTION make_lower (p_val VARCHAR2) RETURN VARCHAR2;
END pkg_remap;
/

CREATE OR REPLACE PACKAGE BODY ryu.pkg_remap
AS
	FUNCTION make_lower (p_val VARCHAR2) RETURN VARCHAR2
	IS
	BEGIN
		RETURN LOWER (p_val);
	END;
END;
/

$ impdp ryu/asdf0930k directory=mydir dumpfile=recreate.dmp tables=recreate remap_data=ryu.obj_name:ryu.pkg_remap.make_lower

 

 

 

 

 

 

그 외 :

 

Specify if the dump file should be reused if the same command is run again (REUSE_DUMPFILES)------------------------
REUSE_DUMPFILES parameter is used to overwrite the dump file if is already exist.

expdp system/asdf0930k parfile=/tmp/parfile03.dat reuse_dumpfiles=yes


Export specific percentage of data (SAMPLE)---------------------------------------------------------------------------------------------
Sometimes using QUERY parameter might not help to limit amount of rows being exported. Especially, if you export the schema with a lot of different tables, it would be hard to specify QUERY parameter for each table. Using SAMPLE parameter you can export specific percentage of data.

CREATE TABLE usr_dptest1.tbl_sample_test
AS
SELECT	*
FROM	dba_objects
WHERE	rownum <= 100;

expdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp schemas=usr_dptest1 sample=20


Load objects under different user (REMAP_SCHEMA)-----------------------------------------------------------------------
Using REMAP_SCHEMA you can import the database objects to the different user.

impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp remap_schema=usr_dptest1:usr_remap_test


Rename table during import (REMAP_TABLE)---------------------------------------------------------------------------------
To rename a table during an import, use REMAP_TABLE and provide a new name for the table.

impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp remap_table=NEW_TABLE:NEW_TABLE_RENAMED


Import objects to the different tablespace (REMAP_TABLESPACE)---------------------------------------------------------

CREATE TABLESPACE tbs_remap_test DATAFILE '/tmp/remap_test01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp remap_schema=usr_dptest1:usr_remap_test2 remap_tablespace=users:tbs_remap_test

SELECT DISTINCT tablespace_name FROM dba_segments WHERE owner= 'USR_REMAP_TEST2';


Skipping indexes with unusable state (SKIP_UNUSABLE_INDEXES)---------------------------------------------------
Using SKIP_UNUSABLE_INDEXES parameter, import can be interrupted or be proceeded if the index of  table is unusable.


Write all DDL commands to the file (SQLFILE)------------------------------------------------------------------------------
Using SQLFILE parameter, you can write all DDL commands that would have executed by impdp to the file. After having the file, you can edit and run if from SQL*Plus.

impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp sqlfile=mydir:sqlfile.sql
more /tmp/sqlfile.sql	


Specify what should happen if the table exists during the export process (TABLE_EXISTS_ACTION)-------------------
TABLE_EXISTS_ACTION parameter lets you specify the action that should be performed if the table that is imported already exists and accepts the following values:
SKIP – is the default value which skip the table that is already exists and move to the next object.
TRUNCATE – is used to truncate the table that already exists and load the data.
APPEND – is used to leave the rows that already exists and append a new data.
REPLACE – is used to drop the table and re-create it.

impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp table_exists_action=skip
impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp table_exists_action=truncate
impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp table_exists_action=append
impdp system/asdf0930k directory=mydir dumpfile=exp_sample_test.dmp table_exists_action=replace


/* To get information about data pump jobs, use DBA_DATAPUMP_JOBS and DBA_DATAPUMP_SESSIONS views. */
SELECT     w.sid, w.event, w.seconds_in_wait
FROM       v$session s, dba_datapump_sessions d, v$session_wait w
WHERE     s.saddr = d.saddr
AND          s.sid = w.sid;

SELECT     owner_name, job_name, operation, state, degree, attached_sessions
FROM       dba_datapump_jobs;	


Data Pump interactive-command mode-----------------------------------------------------------------------------------------
CONTINUE_CLIENT – is used to exit the interactive mode.
KILL_JOB – terminates the current job and detach the cilent sessions.
STOP_JOB – is used to stop the job and might be restarted again.
START_JOB – is used to restart a stopped job.
STATUS – is used to get the status of the current job.
PARALLEL – is used to change parallelism degree of the running job.