Re: Moving TABLES to another TABLESPACE

I didn't find the original posting again. Sorry. But as I just dealt with exactly this problem I thought I put this in here anyway.

If you want to move all tables of one user from one tablespace to the other you proceed as follows:

As there is a problem with the import of grants you should do the following first:
connect as the user and start a spool into a file. Then do a

SELECT 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' FROM user_tab_privs;

then spool off
or better change the spool to a real protocol file.

Now you have to revoke the UNLIMITED TABLESPACE privilege from this user and explicitly set the quota on the old tablespace to 0. Then you export the user's tables and afterwards drop all these tables for this user. Then give the user quota on the new tablespace and import his data.

Afterwards you still have to modify the file with the grants. Which means the headerlines and footlines of the select have to be erased or have to become commentaries. Then you run this grant script and you are done.

I found this information in the Utilities User's Guide, chapter Import/Export Tips, Subject: Reorganizing Tablespaces (It's page 3-4 in the ORACLE7 Manual and 3-3 where in the ORACLE 6 Manual a similar procedure is given but they don't) say anything about the grants.

It worked fine with version 7 so I hope it will work with the old version as well. Apparently the UNLIMITTED TABLESPACE didn't exist under 6. But I would check on it.

If you only want to move one or two tables I would do it as described by Dave Roth.

Regards

Yvonne Baumert

Yvonne Baumert       LL     TTTTTTTTTT  UU    UU   The statements made 
LTU GmbH             LL     TT  TT  TT  UU    UU   represent my own opinion 
Airport, Hangar 8    LL         TT      UU    UU   and do not necessarily 
D-40474 Düsseldorf   LL         TT      UU    UU   correspond with my 
Germany              LL         TT      UU    UU   employers opinion.
y_baumert_at_decus.ch   LLLLLL    TTTT      UUUUUU     

PSI%(0262)45211013068::baumert Some countries don't need the leading 0!!