Re: imports - which tablespaces will tables go into?

In our last gripping episode dmnwork_at_my-deja.com wrote:
>
>
> We're migrating from Oracle 8.1.5 on NT to Oracle 8.1.6.
>
> Rather than upgrade (we'd like this to be clean, and apparently Oracle
> takes it upon itself to leave a lot of junk/directories from the
 version
> being upgraded from), we'd like to do an export, uninstall 8.1.5,
> install a fresh copy of 8.1.6, then rebuild our database.
>
> But that looks like it could be a nightmare, necessitating a bunch of
> scripts to reconstruct things. Primarily, I think, because - so far
 as I
> know - the import utility won't know what tablespaces to put tables
> into.
>
> What's the default tablespace for creating tables, and can import be
> instructed where the correct tablespaces are?
>
> I'd like to make this as painless as possible, and having a script for
> generating each object doesn't make a lot of sense to me. I'd like to
> use the export file as much as possible.
>
> Any insights appreciated.
>
> Thanks very much.
>
> - dana
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

First off let's clear up the confusion about import and export. Export (exp) preserves the tablespace information for each table and index in the database so import (imp) will know where each table is to go and, if the tablespace exists, put it right where it belongs. On a full export the tablespaces themselves, as well as all datafile information for those tablespaces, is preserved in the export file so that a full import will recreate the tablespaces, including all datafiles, presuming that the directory structure stored in the datafile information is present on the destination server, that is to say, if /oracle/tablespace/user is a valid directory structure on both servers then, if the user tablespace datafile is in that directory the user tablespace will be recreated from a full import since /oracle/tablespace/user/user1.dbf can be created (again presuming that there is sufficient space in /oracle/tablespace/user on the destination server).

If the tablespaces cannot be recreated on the destination server then as long as the account importing the data does not have RESOURCE or a non-zero quota on the SYSTEM tablespace and the importing user's default tablespace is not SYSTEM the tables/indexes will be placed in the user account's default tablespace. Absolutely no write access to the SYSTEM tablespace can be present or the tables/indexes could end up there -- not a good thing to happen.

Yet another option is to use the indexfile parameter on the import command line:

imp user/pass file=filename.dmp full=y indexfile=myscriptname.sql

This will place into the file named in the indexfile parameter all CREATE TABLE and CREATE INDEX statements (the CREATE TABLE statements are remarked so the REM statements will need to be removed) including all parameters including tablespace. This file can now be edited to change the tablespaces for tables/indexes to tablespaces currently available in the new database. This script, once edited, can be run to create the tables and indexes in their new "homes". Import can then be instructed to ignore the table/index create errors, simply loading the data into the existing structures:

imp user/pass file=filename.dmp full=y ignore=y buffer=8192000 commit=y

This will allow you to relocate tables/indexes based upon the current tablespaces available in the new installation, even though they are different than in the source database.

I believe all of this information is available in the on-line documentation.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.