Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Duhvelopers

RE: Duhvelopers

From: <maheswara.rao_at_sungard.com>
Date: Mon, 14 Apr 2003 23:23:47 -0800
Message-ID: <F001.005811A4.20030414232347@fatcity.com>






RE: Duhvelopers




Melissa,

I also used to have these LOB data types and used to get the dump file from the  vendor who knows only SYSTEM tablespace in the database.  Here is what I used to do.

1. Create those tables that LOB data types in the user schema i.e., i run table creation DDL.  Now the tables are created in the user schema.(You can get the table DDL from export file)

2. Now import the dump file with ignore=y

3. All those tables that have LOB columns get imported into user's default tablespace instead of SYSTEM.

Rao


-----Original Message-----
From:   Deshpande, Kirti [mailto:kirti.deshpande@verizon.com]
Sent:   Mon 4/14/2003 11:43 PM
To:     Multiple recipients of list ORACLE-L
Cc:    
Subject:        RE: Duhvelopers
Melissa,

Not a bug.  It is how it works, unfortunately.

According the Utilities Guide:
 " By default, a table is imported into its original tablespace.
   If the tablespace no longer exists, or the user does not have sufficient quota in the tablespace, the system uses the default tablespace for that user, unless the table:
     Is partitioned
     Is a type table
     Contains LOB, VARRAY or OPAQUE type columns
     Has an index-organized table (IOT) overflow segment. "

So, you are stuck with these LOBs, unless you can get a new export file after they correct the source. But for now you may have to let the import dump these tables (with LOBs) into the original tablespace and then move them out later..

I had to deal with a similar case just last week.... with a Vendor supplied dump file containing tables with LOBs. I ended up creating the tablespaces they wanted...

HTH,

- Kirti

-----Original Message-----
Sent: Monday, April 14, 2003 10:04 PM
To: Multiple recipients of list ORACLE-L



The tables that didn't create all contain LOB's.  The tables without LOBs created with no problems.

CREATE TABLE ...........
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 4096 P"
 "CTVERSION 10 NOCACHE  STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTE"
 "NTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'SYSTEM'
IMP-00017: following statement failed with ORACLE error 1950:

Some bug probably.

-----Original Message-----
Sent: Monday, April 14, 2003 9:52 PM
To: 'ORACLE-L@fatcity.com'


List, 

It's late, and I'm scratching my head on this.

Oracle 9IR2 Sun Solaris

I've got a dump file from some consultant DUHvelopers.  All the tables were created in the system tablespace along with indexes etc....

In the past, I've been able to just import into another users schema and the tables are created in the default tablespace.  For some reason this export file is still looking for the system tablespace when trying to create the tables.

What am I missing here? 

TIA




-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Received on Tue Apr 15 2003 - 02:23:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US