It will take about 22 minutes 30 seconds to read
난이도: ★★★☆☆
문제 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.
