tablespace reorganization - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: tablespace reorganization

  1. #11
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Ooops - forgot the online keyword in my test- Friday afternoon and all


    SQL> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production
    PL/SQL Release 8.1.6.0.0 - Production
    CORE 8.1.6.0.0 Production
    TNS for Solaris: Version 8.1.6.0.0 - Production
    NLSRTL Version 3.4.0.0.0 - Production

    SQL> create table t1 (col1 varchar2(10));

    Table created.

    SQL> alter table t1 move tablespace tools;

    Table altered.

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use DBMS_SPACE_ADMIN pkg to change DMT to LMT and vice versa.

    No need to move table.

    Tamil

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    Use DBMS_SPACE_ADMIN pkg to change DMT to LMT and vice versa.

    No need to move table.

    Tamil
    Disagree -- LMT is not the only desired outcome, and this option would leave the TS's v. messy.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #14
    Join Date
    Feb 2001
    Posts
    295
    What I want to do is:
    - create new LMT-ts (small, medium, large and huge);
    - 'alter table x move tablespace y online' all the tables to the correct ts;
    - 'alter index rebuild' all the indexes;
    - drop the now empty old ts.

    A piece of extra info: one can't move tables with LONG columns with ALTER TABLE ... MOVE

    Don't see an option other than exp/imp for these.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by adrianomp
    Don't see an option other than exp/imp for these.
    Another option would be using SQL*Plus's COPY command.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: tablespace reorganization

    Originally posted by marist89
    I don't think online table moves are available in 8i. Maybe I'm wrong.
    Any ways ONLINE only applies to IOT, so what big deal with 9.x or 8.x you gotta ur TRAN to wait until Move.

    Originally posted by adrianomp
    Don't see an option other than exp/imp for these.
    Yet another will be (IF no TRAN going on typically can be used in case of DW systems where TRAN will be done as BATCH processing)

    * Disable constraint (FKs)
    * Lock Table x in exlusive
    * Create Table X_TEMP Select * from X
    * Drop X
    * Rename X_Temp to X
    * Enable constraints.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #17
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Re: Re: tablespace reorganization

    Originally posted by abhaysk

    * Disable constraint (FKs)
    * Lock Table x in exlusive
    * Create Table X_TEMP Select * from X
    * Drop X
    * Rename X_Temp to X
    * Enable constraints.

    hmmm if you drop X you drop all constraints associated with it!

  8. #18
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Re: Re: tablespace reorganization

    Originally posted by pando
    hmmm if you drop X you drop all constraints associated with it!
    Oops, last step shud have been recreate constraints

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #19
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: Re: tablespace reorganization

    Originally posted by abhaysk
    Yet another will be (IF no TRAN going on typically can be used in case of DW systems where TRAN will be done as BATCH processing)

    * Disable constraint (FKs)
    * Lock Table x in exlusive
    * Create Table X_TEMP Select * from X
    Adrianomp was talking about tables with LONG columns. So no, CTAS will not work for them. The only two options for those are still exp/imp or COPY. No other alternative, AFAIK.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #20
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Wow! The thread live's on...

    Recently I did some testing with the OEM's reorganization wizard. It created some nice scripts.
    I think I'll test a script on a small table and work my way up to the larger ones...

    I'll keep you posted...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width