Re: how to change table's tablespace?

This is (a) bad DBA work in the first place (every user with the rights to create things should have a setting for DEFAULT TABLESPACE), and (b) the fix is version specific.

To sort out point (a) first: do a select * from dba_users where default_tablespace = 'SYSTEM'. Anyone listed there should have a proper tablespace set as their default: alter user blah default tablespace X

If you have 8.1.5 or above, then you can do an alter table Y move tablespace Z ... and the table is simply moved with all indexes and permissions intact (however, you must rebuild the indexes since the leaf node entries are all pointing at the old blocks, not the new).

Anything earlier than 8.1.5, and I suspect you are going to need to do a 'create table new_one tablespace X as select * from old_one'. You then verify the new table exists with all appropriate records, and then drop the old table. Finally, you rename the new table to be the same name as the old one used to be. Only problem with this approach is that the new table is a *new* table -hence, no-one has permissions on it until they are granted. Equally, there are no indexes on the table, so they have to be re-created from scratch. Likewise with triggers.

There is an alternative approach... instead of doing a 'normal' import, you do an import where ignore=y, indexes=n, indexfile=name_of_text_file. This generates a text file which you can edit that has all the SQL commands necessary to re-create tables and indexes -so you could get in there and edit the tablespace names associated with each of the offending tables. That gives you a SQL script which will create all the tables. You then run import a second time, again with ignore=y but this time with rows=y, and theoretically, the import skips trying to create the tables, and gets on with the job of inserting the data into the newly-housed tables.

And once you've done this once, you will realise why preventing the problem from arising in the first place is so important.

Oh -one last thing. Not only should all Users have a default tablespace which isn't system, but no user should have quota on the system tablespace, just to be doubly sure this sort of thing doesn't happen again. Alter user X quota 0 on SYSTEM. Just make sure that an 'quota unlimited' hasn't snuck in by doing daft things like 'grant resource to X' -revoke it if so. And then test to make sure.

Regards
HJR <wy_at_fudan.edu> wrote in message news:8ss1nj$1d3$1_at_nnrp1.deja.com...
> someone creates a user without specifying the user tablespace.
> and have created 100 tables and indexes on the system tablespace.
>
> I want to change these tablespaces(system) to user_data.
> How can I do?
>
> I tried exp/imp, but not work!
>
>
> Thanks.
>
> wy.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Oct 21 2000 - 08:43:41 CDT