-
Hi Is there any way that i can aletr all the objects pointing to aperticular table space to pint to another table space,
-
Sorry, couldnt understand the pointing of abjects to a tablespace.
but to my understanding , may be you are talking about the objects in a tablespace. If so you can go for export all the objects in a tablespace and import to another tablespace , and drop the old one. Or you can do one by one if only few objects in a tablespace.
if the objects are indexes, simply you can rebuild them in a new tablespace....
Thanks,
Madhu
-
i am sorry for not being clear about my issue,
i have an export fiel in which the table space is test and i want to import it to another schema and the table space in it is name ddifferent,
for example the table x in expot file is pointing towards test and after importing it i want all the table in table space test to be as test1
is there any way that i can alter table space of al the tables at once
-
Here is an example which doesnt require EXP/IMP.
alter TABLE ACCOUNT move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);
First It will move the table and then it rebuilds the indexes on the table. Have a check on the constraints , they may become
invalid. Anyway U cn make them enabled.
PS: try it on some test on a small table , before you go production.
Thanks,
Madhu
-
hi madhu,
probably th escript that u have send me would work for one table but i have to do this for many tables,is there any way that i can do that al of them a time
thanks
-
I got the following script from metalink for you...
select decode( segment_type, 'TABLE',
segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || segment_name ||
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
chr(10) ||
' tablespace &1 ' || chr(10) ||
' storage ( initial ' || initial_extent || ' next ' ||
next_extent || chr(10) ||
' minextents ' || min_extents || ' maxextents ' ||
max_extents || chr(10) ||
' pctincrease ' || pct_increase || ' freelists ' ||
freelists || ');'
from user_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
order by 1, 2
/
You can remove the INDEX part in the above script,,, dont forget to edit the script , before you running...
Thanks,
Madhu
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|