Moving around

[birdax1]

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?

[pipo]

in 8.1.5 (and above), you can :
ALTER TABLE ... MOVE TABLESPACE ...

[padmam]

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

[pipo]

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 ]

[padmam]

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

[padmam]

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

[jmodic]

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...

[padmam]

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