Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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!

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.