Re: moving one user schema out of system tablespace

Hi,

beside setting quotas oracle provides another way to control the location of an import.

" imp [some other parameters] indexfile=$INDEXFILE " produces an sql-file with all "create table"- and "create index"- statements that would be executed by imp.

And now the good news: each of those statements contains a tablespace clause. Just give it a try, it's easy.

You can change those tablespace-clauses in the sql-file to whereever you want your tables and indexes to be created.

After all tables are dropped you may execute it (it's sql) as the destination user.
(If you are too lazy for dropping hundreds of tables, just drop the user and create a new one)

Now you have empty tables and indexes in the desired tablespaces.

Final step is to import the data.
imp [some other parameters] ignore=y

ignore=y avoids complaints about those already existing tables.

Done.

If you want it a bit more sophisticated, you can separate that sql-file into two files, one containing "create table"-statements and the other one for the indexes. Then import the data with "imp indexes=n" and afterwards create the indexes. Depending on your system this way may be faster.

have fun
Hermann Received on Sun Mar 09 2003 - 18:07:51 CST