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

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Import datafile growing

Re: Oracle Import datafile growing

From: Svend Jensen <Svend_SPAMKILL_at_OracleCare.Com>
Date: Mon, 02 Dec 2002 20:06:13 +0100
Message-ID: <3DEBAF25.5090204@OracleCare.Com>


Dale wrote:
> 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?

Yes, they are most certainly defined with large initial extents. Try import with show=y and check the log file. Edit that to sql statements with reasonable sizes (or none). And run. Run the import again with ignore=y, table create will fail, but you had just created them - no big deal. The rest should go in smooth if you didn't make any mistakes. :-)
/svend

Would the exported tables defs from a dictionary managed
> tablespace cause problems when importing into a locally managed
> tablespace?
>
> Im confused,
> Dale
Received on Mon Dec 02 2002 - 13:06:13 CST

Original text of this message

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