Oracle SecureFile Compression

Question

Where can I read database compression properties after the creation of an oracle database / tables? Is there a special view where the info is stored and can be retieved?

Answer 1

Assuming you have the appropriate DBA-level permissions, you can try this to get some basic information on the SecureFile/LOBs in your system:

SELECT table_name, segment_name, index_name, securefile, in_row
FROM user_lobs;

You can get the size on disk for a specific table name (your table with a SecureFile/LOB) by doing this:

SELECT segment_name, segment_type, segment_subtype, bytes/1024/1024 meg_bytes
FROM user_segments
WHERE table_name = 'yourTableName';

To get the actual compression rating (no/low/high), try this using your table name:

SELECT table_name, column_name, partition_name, tablespace_name, compression, deduplication
FROM user_lob_partitions
WHERE table_name = 'yourTableName';

Hope this helps!