This forum is currently locked. You can't register or post questions at this time. (read more)

112.0.3 Entreprise table compression questions

All posts relating to Oracle database administration.

Moderator: Tim...

jrmtl
Senior Member
Posts: 203
Joined: Tue Feb 22, 2005 7:08 pm
Location: Montreal

112.0.3 Entreprise table compression questions

Postby jrmtl » Thu Jul 18, 2013 2:29 pm

Hello all,

I am trying to accomplish the task of table compression on a table of 60gb.
These are the steps I am trying to follow and would like to know if there is any online impact during the process?
How would I actually shrink the datafile ?


1) SELECT def_tab_compression, compress_for FROM dba_tablespaces WHERE tablespace_name = 'xxxx';

2) ALTER TABLESPACE ELLIPSE_TAB DEFAULT COMPRESS FOR ALL OPERATIONS;

3) alter table xxxx enable row movement;

4) alter table xxxx shrink space

Thx !

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: 112.0.3 Entreprise table compression questions

Postby Tim... » Thu Jul 18, 2013 3:40 pm

Hi.

This will probably not achieve what you are expecting. Why? Because COMPRESS FOR ALL OPERATIONS does not affect existing data in the table.

But I am doing a shrink I hear you cry. Yes, but shrink only moves rows if there is space in existing blocks to compact the rows. If you have a bunch of full blocks, the shrink won't cause any of the rows to be moved, so nothing will get compressed.

The only way to guarantee compression is to rebuild the table, either manually or with an online table redefinition. Both these methods will cause some downtime, but the latter causes much less. Hopefully, just a split second.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jrmtl
Senior Member
Posts: 203
Joined: Tue Feb 22, 2005 7:08 pm
Location: Montreal

Re: 112.0.3 Entreprise table compression questions

Postby jrmtl » Thu Jul 18, 2013 3:55 pm

Thanks for that explanation ...

JR

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: 112.0.3 Entreprise table compression questions

Postby Tim... » Thu Jul 18, 2013 4:10 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 6 guests

cron