Re: Error importing table using IMP (Oracle 8i)

ORA-2195: Attempt to create PERMANENT object in a TEMPORARY tablespace

This error is encountered anytime you attempt to create a permanent segment (table, index, etc) in a tablespace that is defined as TEMPORARY tablespace.

Tablespaces of type TEMPORARY became available as of ORACLE7.3. Prior to that,
all tablespaces were PERMANENT (even though they could be designated as temporary tablespaces).

If a tablespace is defined as type TEMPORARY, ORACLE will not allow the creation of anything besides temporary segments (used for disk-based sorting).

Some applications (e.g., GL) create temporary tables for internal use. If the
application has a tablespace of type TEMPORARY designated as its "temporary tablespace", you will receive the ORA-2195 error when it tries to create the table.

You can solve this in one of two ways:

1. Redesignate a PERMANENT tablespace as the application's temporary tablespace

or

2. Redefine the tablespace as a PERMANENT tablespace (this requires DBA privileges). The syntax is:

ALTER TABLESPACE ts_name PERMANENT;
(where ts_name is the name of the tablespace you wish to redefine).

"Kim" <kim_at_costa.no_remove> wrote in message news:MPG.16afe9323ff4c505989681_at_news.spaniatjenester.es...
> Hi, on linux (Suse) I am trying to import a users tables from an export
> done on a win2000 I get a strange error.
>
> Most tables are imported without any problems.. But ONE table failes
> with the following error. And as far as I know there is not really much
> difference between the table that failes and the tables that get
> imported. Any clue ??
>
> IMP-00017: following statement failed with ORACLE error 2195:
> "CREATE TABLE "DATA" ("ID" NUMBER, "DATA1" NUMBER, "DATA2" NUMBER,
> "DATA3" V"
> "ARCHAR2(1000)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
> STORA"
> "GE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50
> FREE"
> "LISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
> "TEMPORARY_DATA""
> IMP-00003: ORACLE error 2195 encountered
> ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace
>
>
>
> --
> Mvh - Kim
Received on Thu Jan 17 2002 - 21:43:56 CST