Re: Online "tablespace" reorg ?

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Wed, 29 Jun 2005 10:30:59 -0700 (PDT)
Message-ID: <20050629173100.64262.qmail@web31205.mail.mud.yahoo.com>

Bob,

There could be more than 1 reason to use the dbms_redef, like modifying column or, in our case, move tables around.

I am attaching a zip file as an example to move a source table TEST_REDEF (can be anywhere) to a target table with same name, but in a tablespace of your choice.

Look at the script 'redef_TEST_REDEF.sql' that shows the steps.

Thanks,
Deepak

Bob <bobmetelsky_at_comcast.net> wrote:

> Hi Deepak, just a couple of ? if you dont mind.
>
> Why do we need to use dbms_redef on the tale level.
> Im not planning on
> modifying columns...
>
> Do you have info/ script examples of using redef on
> a tablespace to
> "shrink the TS" ?
>
> Im not understanding why all the redef is needed, In
> my case wouldnt
> move do the job?
>
> Thanks for your time
> bob
>
> Deepak Sharma wrote:
>
> > We are using ASSM on most of our tablespaces in a
> ~6
> > TB DW without any issues so far, where tablespace
> size
> > varying from 1 G to 700G.
> >
> > One instance where we used dbms_redef was to
> 'shrink'
> > tablespaces that had lot of unused datafiles. -
> >
> > Basically,
> > 1. Created new scratchpad tablespace.
> > 2. REDEF All tables and moved to the scratchpad
> TS.
> > Used dbms_metadata to generate DDLs before
> redefining,
> > and using shell scripts to modify the tablespace
> info
> > etc. The redef was done using parallel (need to
> set
> > parallel dml at session level).
> > 3. Dropped and recreated old tablespace (same name
> but
> > smaller # of datafiles)
> > 4. REDEF all the tables back to the 'old'
> tablespace.
> >
> > The process went without any issues except for few
> > staging tables that did not have a PK. In such
> cases,
> > after redefining, Oracle would add a hidden
> column,
> > that can either be 'left there', or in case you
> don't
> > want it, it needs to be dropped manually.
> >
> > Thanks,
> > Deepak
> >
> >

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l



application/x-zip-compressed attachment: 907226530-redef.zip

Received on Wed Jun 29 2005 - 13:36:24 CDT