Comments inline.
---
"Dale" <dburnett_at_mindspring.com> wrote in message
news:512be560.0212020941.2bc76a08_at_posting.google.com...
> I am attempting to import a schema from a dmp file to a specific
> tablespace.
>
> I am importing to oracle 9i. My tablespace is a LMT with AUTOEXTEND
> ON.
>
> My tablespace was created with this statement:
> create tablespace mytablespace datafile
> 'c:\oracle\oradata\orcl\TS01.DBF'
> SIZE 50M REUSE AUTOEXTEND ON EXTENT MANAGEMENT local uniform size 64k;
>
> My import file is 256K and is only the schema with no data.
> Unfortunatly I dont know what version of oracle the export is from and
> I have no way to find out. I know it is 8i or 9i. The tabledefs in
> the dump file make me think the tablespace is dictionary managed.
>
> I have a user called MYUSER whos default tablespace is mytablespace.
> He has connect,resource and dba permissions.
>
> My import command is:
>
> imp file=file.dmp fromuser=myuser touser=myuser userid=myuser/password
> log=myimp.log
>
> If I have the datafile autoexend turned off then a few tables are
> imported before I receive a ora 01658 error "unable to create INITIAL
> extent for segment in tablespace". I check dba_free_space and the
> tablespace is not listed, which I understand to mean it is full.
>
> If I set autoextend on then the datafile just continues to grow and
> grow. It started out at 50M and is now up to 380M. Since the import
> contains no data and only the schema I dont understand what would
> cause it to grow so large.
>
> Do the table parameters listed in the export dump have to do with this
> problem?
They certainly do. If the original table definition states 'initial 75M',
then Oracle will create as many 64K extents as are needed to satisfy that
request. NEXT is a bit different, but initial will definitiely do this.
>Would the exported tables defs from a dictionary managed
> tablespace cause problems when importing into a locally managed
> tablespace?
Not in general, no. But in this case, yes.
Have a look at doing an import with ignore=yes rows=no and
indexfile=c:\sometext.txt... that will give you the table definitions. If
you then do a bit of an edit you can pre-create all the relevant tables and
then do an import with ignore=yes rows=yes. Where the table already exists,
import simply loads it with data, and the INITIAL and NEXT clauses have no
effect.
Regards
HJR
> Im confused,
> Dale
Received on Mon Dec 02 2002 - 12:57:16 CST
