how to enable advanced compression option in Oracle 11g

1. Your database version is 11.1.0.6 or higher.

2. Your client software supports 11.1.0.6 or higher.

If the above two conditions are met you don't need SecureFiles, you don't need any init parameters, you just need the following:


CREATE TABLE regtab
AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'

CREATE TABLE comptab
COMPRESS AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');


exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');


SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');