-
hi all,
Is there a way to move a table from one tablespace to another?
thanks in advance.
cheers,
junn
-
you can if you use 8i otherwise you have to more things
in 8i you just do
Code:
alter table byebye move tablespace index
/
-
When you move a table to a new tablespace, all of its indexes become invalid and you will need to rebuild them.
Good luck.
David Knight
OCP DBA 8i, 9i, 10g
-
thanks for the advise, dknight mentioned about rebuilding indexes, what about views, do I need to rebuild them as well?
thanks,
junn
-
If the user has select/update privileges on the new tablespace (in case of updateable views) then there is no problem.
If the user/query has no access to that tablespace, then he may need to recreate them. Since view is an object, the view is not moved along with the table, especially when the view is referring to other objects as well. You need to create /replace the view too in that case.
Cheers,
Vinit
-
Just FYI
Using DBA_OBJECTS, you can find all of the objects with a status other than 'VALID'.
Indexes for the moved table will be invalid. In addition, any packages, procedures, or functions based on the table will be invalid, but they will be recompiled the first time they are called.
Good luck.
David Knight
OCP DBA 8i, 9i, 10g
-
Originally posted by vinit
If the user has select/update privileges on the new tablespace (in case of updateable views) then there is no problem.
If the user/query has no access to that tablespace, then he may need to recreate them. Since view is an object, the view is not moved along with the table, especially when the view is referring to other objects as well. You need to create /replace the view too in that case.
I'm sorry, the above is all wrong.
- User can never have any privilege granted on a particular tablespace. Privileges are granted on objects, no matter in which TS those objects reside.
- You don't have to do anything with views when moving the tables, views are note affected with the MOVE command. Even if they become invalid (I'm not sure this realy happens when moving tables) they will be recompiled automaticaly whene they are referenced for the first time. You certanly don't need to recreate them, as in that case you would have to take care of the grants on those views again!
[Edited by jmodic on 09-19-2001 at 07:28 AM]
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi
How I can see this table is in which tablespace ?
Thanx
-
Originally posted by Jani
Hi
How I can see this table is in which tablespace ?
Thanx
select
TABLE_NAME, TABLESPACE_NAME
from
USER_TABLES
order by
TABLESPACE_NAME, TABLE_NAME;
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
|