The DBMS_LOB package is used to access BasicFile and SecureFile LOBs, the SETOPTIONS stored procedures and the GETOPTIONS function allow compression, encryption, and duplication options to be set on each LOB column.
|
CREATE TABLE securefile_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE securefile_lob( encrypt compress ); INSERT INTO securefile_tab VALUES (1, 'ONE'); INSERT INTO securefile_tab VALUES (2, 'TWO'); COMMIT; SET SERVEROUTPUT ON DECLARE l_clob CLOB; BEGIN SELECT clob_data INTO l_clob FROM securefile_tab WHERE id = 1 FOR UPDATE; DBMS_OUTPUT.put_line('Compression : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_compress)); DBMS_OUTPUT.put_line('Encryption : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_encrypt)); DBMS_OUTPUT.put_line('Deduplication: ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_deduplicate)); ROLLBACK; END; / |
DBMS_SPACE includes the SPACE_USAGE stored procedure, which returns the consumption of LOB disk space in all LOB segments. This stored procedure can also be used in ASSM tablespaces.
|
|
segment_type => 'LOB',
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes);
DBMS_OUTPUT.put_line('segment_size_blocks:' || l_segment_size_blocks);
DBMS_OUTPUT.put_line('segment_size_bytes :' || l_segment_size_bytes);
DBMS_OUTPUT.put_line('used_blocks :' || l_used_blocks);
DBMS_OUTPUT.put_line('used_bytes :' || l_used_bytes);
DBMS_OUTPUT.put_line('expired_blocks :' || l_expired_blocks);
DBMS_OUTPUT.put_line('expired_bytes :' || l_expired_bytes);
DBMS_OUTPUT.put_line('unexpired_blocks :' || l_unexpired_blocks);
DBMS_OUTPUT.put_line('unexpired_bytes :' || l_unexpired_bytes);
END;
/
Migrate to SecureFile
There is currently no way to automatically migrate a BasicFile column to the SecureFile LOB column. Instead, you have to manually convert the data using the following method:
◆CREATE TABLE ... AS SELECT ...
◆INSERT INTO ... SELECT ...
◆Table online redefinition
◆Export/import
◆Create a new column, update the new column with the value of the original column, and then delete the old column
◆Create a new column, update the new column with the value of the original column, rename the table, and create a view that only references the new column with the original table name
Except for the export/import method, all options need to pay attention to the amount of disk space when converting LOBs containing a large amount of data.
Oracle streams currently do not support SecureFile, so we should avoid migrating LOB objects that use streams.