5-5 Data Warehouse Management - Create and manage LOB segments

LOB datatype that is used to store a multi-media such as videos, pictures, audit files and etc. A LOB can be either internal or external. Internal LOBs are stored in the database, while external LOBs are stored outside the database as a physical file.

-        CLOB is the character LOB that is used to store a character data in the database character set.

-        BLOB is a binary LOB that is used to store a binary data

-        NCLOB is a LOB that is used to Unicode character data.

 

BFILE(Binary File) is the only external LOB data type in Oracle:

-        The state of the LOB column might be NULL, EMPTY or populated.

-        A specific tablespace might be defined to store LOG segment. If not, tablespace of the table will be used to store the LOB data.

 

CHUNK parameter is the smallest unit of LOBSEGMENT allocation and is multiple of the database block size.

PCTVERSION parameter is not used for SecureFile LOBs and specifies the percentage of used BasicFiles LOB data space that may be occupied by old version of LOB data pages.

RETENTION parameter specifies the retention policy and configures the LOB column to store old versions of LOB data.

You can keep old version of LOB data blocks until the space has reached the size that is specified in the MAXSIZE parameter.

FREEPOOLS is not used for SecureFile LOBs and specifies the number of FREELIST groups for BasicFiles LOBs.

 

 

 

 

CREATE TABLE tbl_lob_table2
(id NUMBER, text CLOB, photo BLOB, intern_file BFILE)
LOB (text) STORE AS text_lob_segment
(TABLESPACE users
 CHUNK 4096
 CACHE LOGGING
 STORAGE (INITIAL 100K NEXT 50k PCTINCREASE 0)
 PCTVERSION 10
 FREEPOOLS 5)
LOB (photo) STORE AS photo_lob_segment
(TABLESPACE users
 CHUNK 4096
 NOCACHE LOGGING
 STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 5)
 PCTVERSION 20
 FREEPOOLS 20);

 

/* Modify LOB parameters */
ALTER TABLE tbl_lob_table2 MODIFY LOB(text) (NOCACHE LOGGING PCTVERSION 20 FREEPOOLS 10);
ALTER TABLE tbl_lob_table2 MOVE LOB(photo) STORE AS (TABLESPACE users);

 

/* Inserting data into LOB column */
CREATE TABLE tbl_lob_test_3
(
	id		NUMBER,
	text		CLOB DEFAULT EMPTY_CLOB(),
	photo	BLOB DEFAULT EMPTY_BLOB()
);

INSERT INTO tbl_lob_test_3 VALUES (1,'This is text', EMPTY_BLOB());

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 1: 다음과 같은 파라미터를 갖는 두 lob 세그먼트를 갖는 테이블 하나를 생성하여라. (8분)

CHUNK 4096

PCTVERSION 20

FREEPOOLS 4

STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 10)

NOCACHE

 

CREATE TABLE tbl_lob01
(id NUMBER, lob1 CLOB, lob2 BLOB)
LOB (lob1) STORE AS clob_segment
(TABLESPACE users
 CHUNK 4096
 FREEPOOLS 4
 STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 10)
 NOCACHE)
LOB (lob2) STORE AS blob_segment
(TABLESPACE users
 CHUNK 4096
 FREEPOOLS 4
 STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 10)
 NOCACHE);

 

 

 

 

난이도: ★★★★☆

문제 2: /tmp 디렉토리 아래에 10줄의 텍스트 파일을 생성하고, 그것을 테이블로 load하여라. (10분)

https://docs.oracle.com/en/database/oracle/oracle-database/19/administration.html

Oracle Database 19c - Administration

/* sqlldr로 lob 파일을 로드하여도 괜찮다. 아래 PL/SQL구문은, 
Oracle Docs에서 코드 예시를 제공해주기도 하니, 
그 코드를 복사한 후 INSERT INTO 절구를 끼워넣어 실행하면 된다. */

CREATE TABLE table1 (NO number, TEXT CLOB);
CREATE DIRECTORY mydir AS '/tmp';

$ vi /tmp/10text.dat
Donald John Trump (born June 14, 1946) is an American politician, media personality, and businessman who served as the 45th president of the United States from 2017 to 2021.
Trump graduated from the Wharton School with a bachelor's degree in 1968. He became president of his father's real estate business in 1971 and renamed it the Trump Organization. He expanded its operations to building and renovating skyscrapers, hotels, casinos, and golf courses and later started side ventures, mostly by licensing his name. From 2004 to 2015, he co-produced and hosted the reality television series The Apprentice. Trump and his businesses have been involved in more than 4,000 state and federal legal actions, including six bankruptcies.




DECLARE
	dst_loc		CLOB;
	src_loc		BFILE := BFILENAME ('MYDIR', '10text.dat');
	amt			NUMBER := DBMS_LOB.lobmaxsize;
	src_offset		NUMBER := 1;
	dst_offset		NUMBER := 1;
	lang_ctx		NUMBER := DBMS_LOB.default_lang_ctx;
	warning		NUMBER;
BEGIN
	DBMS_LOB.FILEOPEN (src_loc, DBMS_LOB.FILE_READONLY);
	DBMS_LOB.CREATETEMPORARY (dst_loc, TRUE);
	DBMS_LOB.LOADCLOBFROMFILE	(dst_loc,
								 src_loc,
								 amt,
								 dst_offset,
								 src_offset,
								 DBMS_LOB.default_csid,
								 lang_ctx,
								 warning);
	DBMS_OUTPUT.PUT_LINE (' Amount specified ' || amt);
	DBMS_OUTPUT.PUT_LINE (' Number of bytes read from source: ' || (src_offset - 1));
	DBMS_OUTPUT.PUT_LINE (' Number of characters written to destination ' || (dst_offset -1));
	IF (warning = DBMS_LOB.warn_inconvertible_char)
	THEN
		DBMS_OUTPUT.PUT_LINE (' Warning: Inconvertible character');
	END IF;
	INSERT INTO sys.table1 VALUES (1, dst_loc);
	DBMS_LOB.FILECLOSEALL ();
END;
/

 

 

 

 

난이도: ★★★☆☆

문제 3: 다음 DBMS_LOB 패키지의 프로시저들을 실습해 보자.

- DBMS_LOB.INSTR 으로 LOB 세그먼트에서 단어를 찾기 (8분)

- DBMS_LOB.SUBSTR 으로 LOB 세그먼트에서 substring하기 (8분)

- DBMS_LOB.COMPARE 으로 두 LOB 세그먼트를 비교하기 (8분)

- DBMS_LOB.GETLENGTH 으로 LOB의 길이를 확인하기 (8분)

 

SET SERVEROUTPUT ON;

DECLARE
	data1 CLOB := 'This is a Clob data';
	instr VARCHAR2(10);
BEGIN
	instr := DBMS_LOB.INSTR(data1,'Clob',1,1);
	DBMS_OUTPUT.PUT_LINE(instr);
END;
/

DECLARE
	data1 CLOB := 'This is a Clob data';
	substr VARCHAR2(10);
BEGIN
	substr := DBMS_LOB.substr(data1,5,10);
	DBMS_OUTPUT.PUT_LINE(substr);
END;
/

DECLARE
	data1 CLOB := 'This is a Clob data';
	data2 CLOB := 'This is a Clob data';
	compare VARCHAR2(10);
BEGIN
	compare := DBMS_LOB.COMPARE(data1, data2, 5, 1, 1);
	
	IF compare = 0
	THEN
		DBMS_OUTPUT.PUT_LINE('Both LOBs are same');
	ELSE
		DBMS_OUTPUT.PUT_LINE('Contents of LOBs are different');
	END IF;
END;
/

DECLARE
	data1 CLOB := 'This is a Clob data';
	getlength NUMBER;
BEGIN
	getlength := DBMS_LOB.GETLENGTH(data1);
	DBMS_OUTPUT.PUT_LINE(getlength);
END;
/