Tuesday, October 9, 2007

Database Reorganization plan based on Alter Table

I want to share my experience with alter table move tablespace. I was handling a project to migrate data mart database tables from dictionary managed tablespace to locally managed tablespace.
Database version: 9.2.0.4

The m_gnap_records table size was around 40GB and it had 210 columns. When I started this assignment, the existing plan was based on the following approach:

alter table m_gnap_records move tablespace nologging;

Time taken: 6 hours 20 minutes

I changed the above plan to the following:

a. alter table m_gnap_records nologging;
b. alter table m_gnap_records move tablespace s_u_data nologging parallel 4;



Time Taken: 1 hour 55 minutes.

The logic was to disable the logging at the object level then use nologging for the alter table operation.

I tested the following also to verify the approach:


alter table m_gnap_records logging;
alter table m_gnap_records move tablespace s_u_data_test nologging parallel 4;

Time taken : 4 hours 48 minutes

5 comments:

Anonymous said...

Excellent post. I used to be checking constantly this blog and I am inspired!
Extremely useful information particularly the final section :)
I maintain such info a lot. I was seeking this particular information for a long time.
Thank you and best of luck.

Also visit my weblog; graduate certificate programs

dong dong23 said...

air jordans
burberry outlet online
michael kors outlet online
michael kors handbags
coach outlet
timberland boots
louis vuitton handbags
basketball shoes
cheap oakley sunglasses
nike store
louis vuitton handbags
michael kors outlet online
nike nfl jerseys
coach factory outlet
nike air force 1
fake watches
ed hardy clothing
tiffany jewelry
louis vuitton outlet
air jordan pas cher
cheap oakley sunglasses
jordan shoes
christian louboutin outlet
celine handbags
christian louboutin outlet
adidas shoes
oakley outlet
ray ban sunglasses outlet
louis vuitton outlet
ray ban sunglasses
coach outlet
nike air max 90
coach factory outlet
cheap toms shoes
timberland outlet
coach outlet
jordans
ralph lauren polo
coach outlet
jordan 4 toro
20166.4wengdongdong

raybanoutlet001 said...

ugg boots
michael kors outlet
ed hardy
ray ban sunglasses
coach outlet online
coach factory outlet
jordan shoes
ugg boots
ugg boots
hugo boss sale

jeje said...

moncler
harden shoes
kate spade outlet
kobe 11
yeezy
vans outlet
nike air max 90
adidas yeezy boost
true religion jeans
hermes belt

chenlili said...

yaoxuemei20180302christian louboutin outlet
moncler jackets
air max shoes
mont blanc outlet
coach outlet
christian louboutin outlet
uggs outlet
lacoste outlet
michael kors handbags
pandora jewelry outlet