4-4 Database Management - Administer External Tables

/* External Table은 데이터베이스에 소속되지 않은 데이터베이스 오브젝트이다. 오직 메타데이터만이 데이터 딕셔너리에 저장되어 있다. External Table은 Read only로만 생성될 수 있고, DML은 허용되지 않는다. external table을 읽기 위해 두 가지의 액세스 드라이버가 사용되는데, ORACLE_LOADER(to load)와 ORACLE_DATAPUMP(to unload)이다. */

 

vi /tmp/list_of_employees.dat
1;jonathan;watson;mg_team;3000;jwatson;1970
2;nikola;thomson;mg_team;4000;nthomson;1975
3;carl;max;vp_team;6000;cmax;1967
4;william;dalton;vp_team;6200;wdalton;1969
5;leonardo;crick;director;8000;lcrick;1965

CREATE DIRECTORY mydir AS '/tmp';

CREATE TABLE tbl_employees
(employee_id	NUMBER,
 name			VARCHAR2(20),
 surname		VARCHAR2(20),
 department		VARCHAR2(20),
 salary		NUMBER,
 username 		VARCHAR2(20),
 ddate		NUMBER)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY mydir
 ACCESS PARAMETERS (
			   records delimited by newline
			   badfile mydir: 'employees.bad'
			   logfile mydir: 'employees.log'
			   fields terminated by ';'
			   missing field values are null
			   (employee_id, name, surname, department, salary, username, ddate)
			 )
 LOCATION ('list_of_employees.dat')
 )
PARALLEL 3
REJECT LIMIT UNLIMITED;


/* badfile: 테이블로 load 실패한 rows를 기록하는 파일이다. */
/* reject limit: 지정 수 이상 테이블 load 실패하면 external table 생성을 실패로 인식하고 에러를 내보낸다. default 값은 0이다. */

 

CREATE TABLE tbl_external_dp1
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
 DEFAULT DIRECTORY mydir
 LOCATION ('ext_dmp1.dmp')
)
AS
SELECT     obj.object_name, obj.object_type, sgm.bytes / 1024 "size"
FROM       dba_objects obj, dba_segments sgm
WHERE      obj.object_name = sgm.segment_name
AND        obj.object_type = sgm.segment_type
AND        obj.owner = sgm.owner
AND        rownum <= 5;

SELECT * FROM tbl_external_dp1;

 

CREATE TABLE tbl_external_dp2
(
	object_name	VARCHAR2(20),
	object_type	VARCHAR2(20),
	obj_size	NUMBER
)
ORGANIZATION EXTERNAL
(
	TYPE		ORACLE_DATAPUMP
	DEFAULT DIRECTORY mydir
	LOCATION ('ext_dmp1.dmp')
);

CREATE TABLE tbl_external_dp3
ORGANIZATION EXTERNAL
(
	TYPE		ORACLE_DATAPUMP
	DEFAULT DIRECTORY	mydir
	LOCATION ('ext_dmp2.dmp', 'ext_dmp3.dmp')
) parallel 2
AS
SELECT	obj.object_name, obj.object_type, sgm.bytes / 1024 "OBJ_SIZE"
FROM	dba_objects obj, dba_segments sgm
WHERE	obj.object_name = sgm.segment_name
AND		obj.object_type = sgm.segment_type
AND		obj.owner = sgm.owner
AND		ROWNUM <= 5;

CREATE TABLE tbl_external_noncompr
ORGANIZATION EXTERNAL
(
	TYPE		ORACLE_DATAPUMP
	DEFAULT DIRECTORY mydir
	LOCATION ('ext_noncompr_test.dmp')
)
AS
SELECT * FROM dba_objects;

CREATE TABLE tbl_external_compr
ORGANIZATION EXTERNAL
(
	TYPE		ORACLE_DATAPUMP
	DEFAULT DIRECTORY mydir
	ACCESS PARAMETERS (COMPRESSION ENABLED)
	LOCATION ('ext_compr_test.dmp')
)
AS
SELECT * FROM dba_objects;

$ ls -ltrh ext_*compr*
-rw-r-----. 1 oracle oinstall  9.6M Jan 26 09:42 ext_noncompr_test.dmp
-rw-r-----. 1 oracle oinstall 1004K Jan 26 09:43 ext_compr_test.dmp

SELECT * FROM dba_external_tables;

 

 

 

 

 

난이도: ★★★☆☆

문제 1:  다음과 같이 콤마(,)로 분리된 텍스트 파일을 만들어라. 

1,oca,cert,oracle,associate

2,ocp,cert,oracle,professional

3,ocm,cert,oracle,master

4,oce,certoracle,expert

이 파일을 사용하여 external table을 생성하고 쿼리하여라. rejected 된 row의 개수를 조사하여라. 만약 inconsistent row가 있으면 에러를 확인하여라. (13min)

 

$ vi /tmp/text.dat
1,oca,cert,oracle,associate
2,ocp,cert,oracle,professional
3,ocm,cert,oracle,master
4,oce,certoracle,expert


CREATE TABLE text 
(id NUMBER, c1 VARCHAR2(20), c2 VARCHAR2(20), c3 VARCHAR2(20), c4 VARCHAR2(20))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY mydir
 ACCESS PARAMETERS (
					records delimited by newline
					badfile mydir: 'text.bad'
					fields terminated by ','
					missing field values are null
					(id, c1, c2, c3, c4)
					)
 LOCATION ('text.dat')
)
REJECT LIMIT UNLIMITED;

 

 

 

 

 

난이도: ★★★☆☆

문제 2: DBA_OBJECTS 테이블과 DBA_SEGMENTS 테이블을 조인하는 쿼리를 병렬도 3으로 compressed dump file로 생성하여라. external table을 생성할 때 이 덤프 파일을 사용하여라. (10분)

 

CREATE TABLE text2
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
 DEFAULT DIRECTORY mydir
 ACCESS PARAMETERS (COMPRESSION ENABLED)
 LOCATION ('text2.dat')
)
PARALLEL 3
REJECT LIMIT UNLIMITED
AS
SELECT     obj.object_name, obj.object_type, sgm.bytes / 1024 "size"
FROM       dba_objects obj, dba_segments sgm
WHERE      obj.object_name = sgm.segment_name
AND        obj.object_type = sgm.segment_type
AND        obj.owner = sgm.owner
AND		   rownum <= 100;