Oracle Database Discussions

I have a scenario where I have to enable compression for a particular tablespace in an 11g db.

I tested the case by doing the following steps:

1)Created a normal table in a tablespace without compression.

BYTES/1024/1024 SEGMENT_NAME OWNER SEGMENT_TYPE
--------------- ---------------------------------------- ------------------------------ ------------------

496 TEST_PMT SCOTT TABLE

SQL> select count(*) from SCOTT.TEST_PMT;

COUNT(*)
----------
5051013


2) I then created a new tablespace with 'compress' clause.

SQL> CREATE TABLESPACE test_ts
DATAFILE '/oradata01/db10/test_ts01.dbf'
SIZE 500m
DEFAULT COMPRESS FOR ALL OPERATIONS; 2 3 4

Tablespace created.

SQL> select TABLESPACE_NAME , COMPRESS_FOR from dba_tablespaces where tablespace_name in ('USERS_DATA01','TEST_TS');

TABLESPACE_NAME COMPRESS_FOR
------------------------------ ------------
TEST_TS OLTP
USERS_DATA01

3) Create a table under the new tablespace test_ts:
------------------------------------------------------------------------

CREATE TABLE SCOTT.TEST_PMT_TS_COMP
(
PMT_ID INTEGER NOT NULL,
PMT_AGCY_NBR CHAR(7 BYTE),
PMT_SBAGCY_NBR CHAR(7 BYTE),
PMT_PAY_DT DATE,
PMT_POL_NBR CHAR(14 BYTE),
PMT_POL_SEQ_NBR CHAR(2 BYTE),
PMT_PRM_INSD_NM VARCHAR2(30 BYTE),
PMT_PAY_TYP_DESC VARCHAR2(20 BYTE),
PMT_PAY_AMT NUMBER(11,2),
PMT_POST_DT DATE
)
TABLESPACE TEST_TS;

4) Inserted the records from the test table created in step 1:
-------------------------------------------------------------------------------------

SQL> insert into SCOTT.TEST_PMT_TS_COMP select * from SCOTT.TEST_PMT;

5051013 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from SCOTT.TEST_PMT_TS_COMP;

COUNT(*)
----------
5051013


Checking Space:
---------------

SQL> select bytes/1024/1024, segment_name, owner, segment_type from dba_segments where tablespace_name='TEST_TS';

BYTES/1024/1024 SEGMENT_NAME OWNER SEGMENT_TYPE
--------------- ---------------------------------------- ------------------------------ ------------------
272 TEST_PMT_TS_COMP SCOTT TABLE



SQL> select owner, table_name, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TEST_PMT_TS_COMP';

OWNER TABLE_NAME COMPRESS COMPRESS_FOR
-------------------- ------------------------------ -------- ------------
SCOTT TEST_PMT_TS_COMP ENABLED OLTP


Hence, from the test I found around ~ 50% reduction in the space occupied by the new table.

Now, coming to the real scenario where I need to add the compress clause. In this schema I have around 500 tables so this approach of inserting records in each table by using 'Select *..' is not feasible. I thought of doing it by using export/import.
Following steps I performed for the schema.

1) Took export backup for ABC schema.

2) Current size of the tablesapces.
--------------------------------
SQL> select sum(bytes)/1024/1024, owner from dba_segments where tablespace_name='ABC_DATA01' group by owner;
SUM(BYTES)/1024/1024 OWNER
-------------------- ------------------------------
388.6875 ABC
SQL> select sum(bytes)/1024/1024, owner from dba_segments where tablespace_name='ABC_INDEX01' group by owner;
SUM(BYTES)/1024/1024 OWNER
-------------------- ------------------------------
504.1875 ABC

3) Dropped all the objects of the ABC schema.
4) Dropped both the tablespaces.

5) Recreated both the tablepsaces with compress clause
-------------------------------------------------------

SQL> CREATE TABLESPACE ABC_DATA01 DATAFILE
2 '/oradata01/db10/ABC_DATA01.01.dbf' SIZE 1024M AUTOEXTEND OFF
LOGGING
ONLINE
3 4 5 PERMANENT
6 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
7 BLOCKSIZE 16K
8 SEGMENT SPACE MANAGEMENT AUTO
9 FLASHBACK ON
10 DEFAULT COMPRESS FOR ALL OPERATIONS;
Tablespace created.

SQL> CREATE TABLESPACE ABC_INDEX01 DATAFILE
2 '/oradata01/db10/ABC_INDEX01.01.dbf' SIZE 1024M AUTOEXTEND OFF
LOGGING
3 4 ONLINE
5 PERMANENT
6 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
7 BLOCKSIZE 16K
8 SEGMENT SPACE MANAGEMENT AUTO
9 FLASHBACK ON
10 DEFAULT COMPRESS FOR ALL OPERATIONS;
Tablespace created.

6) Recreated all the objects under ABC schema( took the create script from Toad)

7) Re-imported the objects data into the new tablespaces using imp.

8) Checked the tablespace size again.

SQL> select sum(bytes)/1024/1024, owner from dba_segments where tablespace_name='ABC_DATA01' group by owner;
SUM(BYTES)/1024/1024 OWNER
-------------------- ------------------------------
388.6875 ABC

SQL> select sum(bytes)/1024/1024, owner from dba_segments where tablespace_name='ABC_INDEX01' group by owner;
SUM(BYTES)/1024/1024 OWNER
-------------------- ------------------------------
504.1875 ABC

SQL> select TABLESPACE_NAME , COMPRESS_FOR from dba_tablespaces where tablespace_name in ('ABC_DATA01','ABC_INDEX01');
TABLESPACE_NAME COMPRESS_FOR
------------------------------ ------------
ABC_DATA01 OLTP
ABC_INDEX01 OLTP


As we see from the above test there is no change in space utilization which is contrary to what I tested. Please advise me if I have done something wrong. Or should I try any other approach.
I tried datapump (expdp/impdp) also, but no gain.


Thanks,
Shah