Moving around
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Moving around

  1. #1

    Arrow

    Hi,

    Does anyone know if it is possble to reallocate tables without using export/import ?

    I need to move a table from one tablespace to another....If so can this be done dynamically?

    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    in 8.1.5 (and above), you can :
    ALTER TABLE ... MOVE TABLESPACE ...

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    As select * from

    Hi, 25th April 2001 19:08 hrs chennai

    try with this.

    Constaruct a query in a sql script like this

    Creating a table as select * from the exiting user with schema.table name first.

    then drop the table in that schema.

    Please try it

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    hmmm, problem could be that indexes, grants and so on will not follow on the new table ... this is the main advantage of the move function [less work to do ]

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Re: Based on Storage

    Hi PIPO, 25th April 2001 19:16 hrs chennai

    As you have said is fine but i have some questions.

    If you move the TS if it contains only the table then Ok if it contains other objects wont it be a problem?

    I think it all depends on the volume of Data to be moved from the TS. ?

    Am i correct please.

    From Oracle DOCS
    ----------------------

    Moving data by transporting tablespaces can be orders of magnitude faster than either export/import or unload/load of the same data, because transporting a tablespace involves only copying datafiles and integrating the tablespace metadata. When you transport tablespaces you can also move index data, so that you do not have to rebuild the indexes after importing or loading the table data.

    In the current release, you can transport tablespaces only between Oracle databases that use the same data block size and character set, and that run on compatible platforms from the same hardware vendor.

    ===
    To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use export/import to move the database information (metadata) stored in data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these filescan be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs.

    After copying the datafiles and importing the metadata, you can optionally put the tablespaces in read-write mode.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  6. #6
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    If INDEXES are stored in a Sep TS

    Hi PIPO, 25th April 2001 19:20 hrs chennai

    How about a situation where INDEXES are stored in another TS and other dependencies also in a same way.?

    So i think it all depends on the size and storage the user has to plan am i right ?

    But i do accept creating a table as select * from doesnt create the indexes as was in the original table.Which i didnt think over.

    In this scenario once we had to create a table simillar to this structure (constraints etc)and moved the data's.The data volume was also huge.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Padmam,

    Pipo hasn't suggest moving tablespaces (as with transportable tablespaces), he suggested to use ALTER TABLE ... MOVE command to rebuild the table in some other tablespace. Using this method you don't have to wory about all the grants, indexes, constraints, triggers etc etc...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Thanks a lot

    Dear JMODIC, 25th April 2001 19:33 hrs chennai

    Thanks a lot .

    Any how the DB which i worked on was 7.X in COSL about 8 months back.

    Cheers

    padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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