Change tablespace of a table 1

[lalleima]

HI

I have some tables in my oracle Database which belongs to the same Owner(say MyUser). However some of the tables are in the tablespace - SYSTEM and some are in another user tablespace(say - MyTbs).

What will be the most eficient way to change the table in tablespace - SYSTEM to MyTbs without disturbing the structure(constraints-index) and the data?

By the way I'm using Oracle 8i(Release 3 (8.1.7) for Windows NT )

Thanks

 

[alexhu]

checkout 'alter table <tablename> move in the docs

e.g

alter TABLE ACCOUNT move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);


Alex

 

[Turkbear]

Hi,
In my docs, it indicates that ( at least in 8i) the MOVE command only applies to Partitions or the index tree of an IOT - It does not appear to be able to move a table to a new tablespace.

 

[alexhu]

Well I don't have 8i (only 9, 8.0 & 7 ) so I looked here

http://download-uk.oracle.com/docs/cd/A87860_01/doc/index.htm

if you look at the move table clause of 'Alter table' it says

move_table_clause
For a heap-organized table, use the segment_attributes_clause of the syntax. The move_table_clause lets you relocate data of a nonpartitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

or am I confusing what it's saying ? (after all it is Friday pm here

Alex

 

[alexhu]

Further example from Tom Kytes site

http://asktom.oracle.com

Tom, What is the best way to move tables from one
tablespace to another tablespace in the same instance?

I'll give you the Oracle8.0 and before answer as well as the Oracle8i and above answer (in 8i, this is *trivial*, in 8.0 and before, it takes practice and time)....


There are 2 methods we can use to do this. One is to use a combination of
&quot;alter table X move tablespace Y&quot; and &quot;alter index X rebuild tablespace Y&quot; --
this works in Oracle8i release 8.1 and up ONLY.

Alex

 

[alexhu]

lalleima - try it and tell us the answer PLEASE!

Alex

 

[Turkbear]

Hi,
Thanks Alex, I missed that phrase ( It is 15 below zero (F) here and my brain must have frozen)

 

[dbtoo2001]

Didn't you ask this on dbForums, too?

ALTER TABLE MyUser.YourTable MOVE TABLESPACE New_Tablespace
/
ALTER INDEX MyUser.YourTable_IDX1 REBUILD
/
ALTER INDEX MyUser.YourTable_IDX02 REBUILD
/


If you move the table, you must rebuild the indexes.

 

[lalleima]

Thank You All. But my main motive for this Query was to skip the process of rebuilding all the Indexes. You see for around 350 tables number of Indexes goes to more than 500. I have tried on 2 tables and its working fine. Is there any other way?

 

[carp]

I think this is as clean and simple a method as you are going to find. For the number of tables you are talking about, you might want to launch a query that will write the script for you, then run the script.

Your indexes are going to take a beating anyway, since moving a table is going to change the rowids for all of the rows in the table. Since this is liable to lead to a lot of growth and dead entries in your index, rebuilding them would probably be a good idea.

Also, once you are done, don't forget to analyze your tables to reset your statistics (assuming you've analyzed them to begin with)!

Elbert, CO
1444 MST

 

[gunjan14]

Hi,

I am using Oracle 8.0.6 and my problem is same (changing the tablespace of the table). And since I cannot use alter table move, I guess I will have to fall back on Export and Import. But the problem comes what should be the sequence to achieve this. I am not that familiar with Export / Import. If I follow this sequence :-
1. Export the table1 (old_table) from tablespace X
2. Create a dummy table (table1_2) in another tablespace (Y) with no data
3. Drop the table1 in tablespace X
4. rename the newly created table (table1_2) (in tablespace Y to table1.
5. Import from the dump.

The table has to be in same owner.
But the problem is if any foreign key is pointing to this table (table1 in tablespace X) then if I drop it (in step 3) then we have to re-create it after importing. Is there any other way to achieve it without re-creating the foreign key pointing to the table or can you all suggest any sequence to be followed?

Thanks and Regards

 

[stefanhei]

@lalleima:
check if the indexes on those tables are in SYSTEM-tablespace too (usually they are). That would be a good reason to rebuild them anyway (in another tablespace).

Stefan

 

[avjoshi]

Gunjan,

There are other options availble.

Create a copy of table with either,
1. by using sql*plus copy command or
2. by &quot;create table as select&quot; command.

drop the original table and rename the new table to original name.



Anand

 

[gunjan14]

But with this option, I have to then create all the constraints (primary, unique, check) on the table. And I have to move some 100 tables interspersed in 17 tablespaces. It will be mammoth task.
Also even if I follow this route, it will not help if any foreign key is referencing to the current table. I have to re-create those constraints. I want to avoid it if it can be, which after reading through the document, I got the impression that I cannot. But I am hoping for some silver lining from your trick bags.

Another question is that I want to change the index tablespace. How can I achieve it, if I use Export/Import. Or do I have to re-create the index manually if I have to change the index tablespace?

Thanks and Regards,
Gunjan

 

[aryeh1010]

I'd like to point out that 350 tables and 500 indexes is not really a big deal, unless you are planning to write the commands manually.

The amount of time it will take for this project to complete is dependent on the size of the tables and indexes, more than the number of objects.

alter table <table_name> move tablespace

and

alter index <index_name> rebuild

are the standard way of handling this task.

By the way you can move a table in its own tablespace, if you want to reorganize it and keep it in the same tablespace.

Aryeh Keefe

 

[carp]

I don't know if the dbms_ddl package was available in 8.0. You might want to check and see if it is available to you or not. If it is, it might be very usefult to you!

 

[gunjan14]

Hi,

I have checked and found that dbms_ddl package is not there in Oracle 8. It seems that I have run out of ideas.


Gunjan

 

[SantaMufasa]

Gunjan,

It seems to me that you are making life MUCH more difficult than it needs to be. Here are the steps I suggest to ensure that your tables and indexes are in the correct tablespace and fully reorganized and defragmented:

1) Do a schema export of the schema in question.
2) Drop the schema.
3) Create the schema again, but this time ensure that the user/schema has no quota on any tablespace except the &quot;correct&quot; tablespace in which you want your objects to reside.
4) Import your schema dump file.

This simple list of activities should give you what you want.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:57 (03Feb04) GMT, 11:57 (03Feb04) Mountain Time)

 

[gunjan14]

Hi Dave,

It would have been easier if I can drop the schema but unfortunately I cannot. Because only selected tables need to be moved to new tablespace. The scenario is that user has some 30 tablespaces and out of this only some 100 tables interspersed in 17 tablespaces needs to be moved to a single tablespace. Rest of the objects in 13 tablespaces need not be touched or to remain in the same tablespace where it is.
I think I have made the situation now more clear.

Thanks and Regards
Gunjan

 

[SantaMufasa]

Gunjan,

Under these circumstances then, I recommend a slight variation on my suggestion, above:

1) Generate (using &quot;SQL writing SQL&quot; ) individual command-line &quot;exp&quot; statements for each of the tables that are in the &quot;wrong&quot; tablespace, then run the generated &quot;exp&quot; statements.
2) Drop the exported tables.
3) Ensure that the user/schema has no quota on any &quot;wrong&quot; tablespace; change the schema's DEFAULT TABLESPACE to the &quot;correct&quot; tablespace in which you want your objects to reside.
4) Generate (using &quot;SQL writing SQL&quot individual command-line &quot;imp&quot; statement for each of the exported tables, then run your generated &quot;imp&quot; statements.

Questions?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:39 (05Feb04) GMT, 11:39 (05Feb04) Mountain Time)