Oracle 11gR1中的SecureFile(4)

CREATE TABLE caching_and_logging_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE(
  NOCACHE
  FILESYSTEM_LIKE_LOGGING
);
ALTER TABLE caching_and_logging_tab MODIFY LOB(clob_data) (
  CACHE
);

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;
/

SET SERVEROUTPUT ON
DECLARE
  l_segment_size_blocks  NUMBER;
  l_segment_size_bytes   NUMBER;
  l_used_blocks          NUMBER;
  l_used_bytes           NUMBER;
  l_expired_blocks       NUMBER;
  l_expired_bytes        NUMBER;
  l_unexpired_blocks     NUMBER;
  l_unexpired_bytes      NUMBER;
BEGIN
  DBMS_SPACE.SPACE_USAGE(
    segment_owner         => 'TEST',
    segment_name          => 'SECUREFILE_LOB',
    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;
/