Printable View
I created a punch of tables using export and import in the wrong table space. Is there anyway to move them to the proper table space ?
If not what parameter I should add in the export and import to place them in the proper table space.
I need an answer like yesterday !
Thanx
Just check create table syntex in manual. You can create table as select * from tab tablespace tab.
I do not know the exact syntex.
If you use 8i you can use ALTER TABLE ... MOVE command:
ALTER TABLE emp MOVE TABLESPACE users;
If you don't use 8i, revoke UNLIMITED TABLESPACE privilege from the user, and give user only quota on the right tablespaces.
ALTER USER scott QUOTA 100M ON users;
ALTER USER scott QUOTA 0 ON system;
Thank you everybody for your quick help, you guys R awsome.
This may be an added experience to some readers here.
I exported tables from user1 to export1.dmp. Those tables were originally placed in tablespace TB1
Then I logged in as user2 who has dba role and default tablespace TB2
I imported export1.dmp fromuser=user1 touser=user2
I got the tables places in TB1 owned by user2 !! they were supposed to go to the default table space TB2
(This when I needed to move the tables from TB1 to TB2)
I revoked the DBA role from user2 and reimported export1.dmp using User2, then the tables went to the default tablespace TB2
Regards