Re: Import Question

On May 31, 11:36 am, Sidhu <amardeepsi..._at_gmail.com> wrote:
> On May 31, 2:27 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > ame..._at_iwc.net schrieb:
>
> > > Hi,
>
> > > This is supposed to be very simple, but it is not happening.
>
> > > I exported a table. Big deal right? I want to import that table into
> > > another database in which the tablespace the table originally existed
> > > does not exist.
>
> > > I am receiving ORA-00959 errors. I have tried importing as system, as
> > > myself, with the ignore flag, without the ignore flag, using fromuser
> > > & touser..........it just keeps failing.
>
> > > IMP-00003: ORACLE error 959 encountered
> > > ORA-00959: tablespace 'DATA_HOLDER_DATA' does not exist
> > > Import terminated successfully with warnings.
>
> > > Though it says successful, the table is nowhere to be found in
> > > DBA_TABLES. We are on Oracle 8i.......
>
> > > Any suggestions??
>
> > I assume, this question is related to your previous post regarding
> > nested tables.
> > Generally, you have two options by import to avoid ORA-00959:
> > a) Get ddl of your table, adjust it and create your table before import
> > in desired tablespace, then import with ignore=y
> > b) Create tablespace with same name as in the source database.
> > This apply to all tables which consists of more than 1 segments (hence
> > have multiple storage clauses, i.e. partitioned tables, tables with
> > LOB's, nested tables etc.)
> > Only simple heap tables which consists of 1 segment will be created in
> > default tablespace if original tablespace doesn't exist.
>
> > Best regards
>
> > Maxim
>
> the reason is clear, you are getting this error because the tablespace
> does not exist in the new database. just run the import with show=Y
> option and from the logfile, extract the tablespace creation
> script,create the tablespace manually and then run the import. It
> should be successfull now :)
>
> Sidhu- Hide quoted text -
>
> - Show quoted text -

The reason he's getting this error is because he's not following the procedure necessary to import tables into the destination user's default tablespace; the original tablespace need not exist in the destination database if the proper method is adhered to. The 'destination' user cannot have unlimited tablespace privilege else the original tablespace will be expected and, not finding it, imp will throw the error the OP reports. This privilege can be revoked:

SQL> revoke unlimited tablespace from <username>;

You will see one of two responses to this from Oracle:

Revoke succeeded.
or
ERROR at line 1:
ORA-01952: system privileges not granted to <username>

The destination user also needs an unlimited quota on his/her default tablespace and on the temporary tablespace:

alter user <username> quota unlimited on <default tablespace>; alter user <username> quota unlimited on <temporary tablespace>;

The destination user is now prepared to create the table or tables in his/her default tablespace, no matter which tablespace was used in the source database. A simple import will do the deed:

imp <username>/<password> file=.... full=y ignore=y

The table or tables will now be in the user's default tablespace, again regardless of where they were in the source database.

David Fitzjarrell Received on Thu May 31 2007 - 13:56:08 CDT