/*
Securefiles is a LOB storage architecture that is used to store unstructured data and contains numerous features as deduplication, compression, encyption and etc. A Securefile LOB can be created only in a tablespace managed with Automatic Segment Space Management (ASSM).
DB_SECUREFILE parameter
- NEVER is used to make all LOBs that are created as a SecureFile to be created as a BasicFile LOB.
- PERMITTED is a default value that allows LOBs to be created as a SecureFile using STORE AS SECUREFILE clause.
- ALWAYS is used to make all LOBs in a ASSM tablespace be created as a SecureFile LOBs.
- IGNORE is used to ignore store as securefile clause and creation of SecureFile LOBs are permitted. */
난이도: ★☆☆☆☆
문제 1: Basic LOB을 만들어라. (4분)
CREATE TABLE tbl_basicfile_lob
(id NUMBER, lob_data CLOB)
LOB (lob_data) STORE AS BASICFILE;
INSERT INTO tbl_basicfile_lob VALUES (1,'This is my long text');
COMMIT;
SELECT * FROM tbl_basicfile_lob;
난이도: ★★☆☆☆
문제 2: CLI와 OEM에서 SecureFile LOB을 생성하여라. (8분)
CREATE TABLE tbl_securefile_lob
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE (tablespace users);
INSERT INTO tbl_securefile_lob VALUES (1,'This is my long text');
COMMIT;
SELECT table_name, column_name, segment_name, securefile
FROM dba_lobs
WHERE table_name LIKE 'TBL%';
난이도: ★★★☆☆
문제 3: Compressed LOB과 Noncompressed LOB 세그먼트를 사용해 두 테이블을 만들고, 사이즈 차이를 보여라. (10분)
CREATE TABLE tbl_lob_nocompress
(id NUMBER, lob_data CLOB)
LOB (lob_data) STORE AS SECUREFILE (TABLESPACE users NOCOMPRESS);
INSERT INTO tbl_lob_nocompress
SELECT object_id, rpad('',5000,object_name)
FROM dba_objects
WHERE rownum<=1000;
CREATE TABLE tbl_lob_compress
(id NUMBER, lob_data CLOB)
LOB (lob_data) STORE AS SECUREFILE (TABLESPACE users COMPRESS HIGH);
INSERT INTO tbl_lob_compress
SELECT object_id, rpad('',5000,object_name)
FROM dba_objects
WHERE rownum<=1000;
EXEC dbms_stats.gather_table_stats('sys','tbl_lob_compress');
EXEC dbms_stats.gather_table_stats('sys','tbl_lob_nocompress');
SELECT table_name, segment_name, securefile, compression
FROM dba_lobs
WHERE table_name LIKE '%COMPRESS%';
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN ('SYS_LOB0000073205C00002$$', 'SYS_LOB0000073208C00002$$');
난이도: ★★★☆☆
문제 4: CLI와 OEM에서 SecureFile LOB을 생성하여라. (8분)
$ cd $ORACLE_HOME
$ mkdir -p ./wallet
$ cd /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/
$ vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/product/19.3.0/dbhome_1/wallet)))
SELECT status, wrl_parameter FROM v$encryption_wallet;
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "asdf0930k";
SELECT status, wrl_parameter FROM v$encryption_wallet;
CREATE TABLE tbl_lob_encrypt (id NUMBER, lob_data CLOB)
LOB (lob_data) STORE AS SECUREFILE (TABLESPACE users ENCRYPT USING 'AES128');
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "asdf0930k";
난이도: ★★☆☆☆
문제 5: SecureFile LOB 세그먼트를 이용해 두 개의 테이블을 만들어라. 첫 번째 LOB에는 Duplicate를 허용하고, 두 번째 LOB은 Deduplicate를 사용하라. 그리고 사이즈 차이를 보여라. (10분)
CREATE TABLE tbl_lob_duplicate
(id NUMBER, lob_data CLOB)
LOB (lob_data) STORE AS SECUREFILE (TABLESPACE users KEEP_DUPLICATES);
INSERT INTO tbl_lob_duplicate
SELECT object_id, rpad(' ',5000,'This is test')
FROM dba_objects
WHERE rownum <= 3000;
CREATE TABLE tbl_lob_deduplicate
(id NUMBER, lob_data CLOB)
LOB (lob_data) STORE AS SECUREFILE (TABLESPACE users DEDUPLICATE);
INSERT INTO tbl_lob_deduplicate
SELECT object_id, rpad(' ',5000,'This is test')
FROM dba_objects
WHERE rownum <= 3000;
EXEC dbms_stats.gather_table_stats('sys','tbl_lob_duplicate');
EXEC dbms_stats.gather_table_stats('sys','tbl_lob_deduplicate');
SELECT table_name, segment_name, securefile, deduplication
FROM dba_lobs
WHERE table_name LIKE '%DUPLICATE%';
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN ('SYS_LOB0000073214C00002$$', 'SYS_LOB0000073217C00002$$');
난이도: ★☆☆☆☆
문제 6: CACHE와 LOGGING 옵션을 사용하여 LOB 세그먼트를 생성하여라. (8분)
/* you have to think twice before enabling caching option for the LOB segments, because it can flush necessary block from the buffer cache and can affect the performance of the database. */
/* Using a logging feature, you can specify how the LOBs are recorded to the redo log stream. */
CREATE TABLE tbl_lob_cache (lob_data CLOB)
LOB (lob_data) STORE AS SECUREFILE (tablespace users CACHE LOGGING);
ALTER TABLE tbl_lob_cache MODIFY LOB (lob_data) (CACHE);
난이도: ★☆☆☆☆
문제 7: BasicFile LOB 파일을 SecureFile LOB으로 바꾸어라. (10분)
CREATE TABLE tbl_basic_lob
(id NUMBER PRIMARY KEY, clob_data CLOB)
LOB (clob_data) STORE AS BASICFILE (TABLESPACE users);
/* Securefile이기 때문에 당연히 lob 데이터의 deduplicate 처리가 안 될 것이다. */
ALTER TABLE tbl_basic_lob MODIFY LOB (clob_data) (CACHE DECUPLICATE); /* error */
CREATE TABLE tbl_securefiles_lob
(id NUMBER, clob_data CLOB)
LOB (clob_data) STORE AS SECUREFILE (TABLESPACE users);
EXEC dbms_redefinition.start_redef_table('c##ryu','tbl_basic_lob','tbl_securefiles_lob');
set serveroutput on
DECLARE
error_count PLS_INTEGER := 0;
BEGIN
dbms_redefinition.copy_table_dependents
('c##ryu',
'tbl_basic_lob',
'tbl_securefiles_lob',
1,
TRUE,
TRUE,
TRUE,
FALSE,
error_count);
dbms_output.put_line ('errors := ' || to_char(error_count));
END;
/
EXEC dbms_redefinition.finish_redef_table('c##ryu','tbl_basic_lob','tbl_securefiles_lob');
ALTER TABLE tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);
