Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: IMPORT error, initial extents cannot be created (HELP!!!)
Sorry to show you this, Niall, but here's the 9.0.1 output:
C:\>exp help=y
Export: Release 9.0.1.1.1 - Production on Tue Aug 21 14:45:14 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
You can let Export prompt you for parameters by entering the EXP command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) DIRECT direct path (N) TRIGGERS export triggers (Y) LOG log file of screen output STATISTICS analyze objects (ESTIMATE ROWS export data rows (Y) PARFILE parameter filenameCONSISTENT cross-table consistency CONSTRAINTS export constraints (Y)
FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file FLASHBACK_SCN SCN used to set session snapshot back to FLASHBACK_TIME time used to get the SCN closest to the specified time QUERY select clause used to export a subset of a table RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE TTS_FULL_CHECK perform full or partial dependency check for TTS TABLESPACES list of tablespaces to exportTRANSPORT_TABLESPACE export transportable tablespace metadata (N) TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
C:\>
-- HTH. Additions and corrections welcome. Pete Author of "Oracle8i: Architecture and Administration Exam Cram" Now got a life back again that the book is released! "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3b827360$0$8508$ed9e5944_at_reading.news.pipex.net...Received on Tue Aug 21 2001 - 16:46:05 CDT
> Others have told you correctly that your current setup will not allow the
> creation of a 2g initial extent.
>
> My advice would be
>
> 1. Reexport using compress=N.
> 2. Re import. You should find it 'magically' works with your current
> datafiles.
>
> In addition I have some further observations that I would hope help you a
> bit.
>
> Never ever ever export a database with the default clause compress=y. It
is
> this that stores the initial extent <some bloody ridiculous number> caluse
> in the dump file. This is probably one of the top 5 terrible defaults in
> Oracle. (at least up to 8i don't know about 9.)
>
> Use Locally managed tablespaces with a uniform extent size (not
> autoallocate). This will mean you never have to defrag again. Also if you
do
> forget to use compress=n you shouldn't be stuffed in this way because
oracle
> will just silently rewrite the storage clause.
>
> Use uniform datafile sizes and make them no more than 2gb in size. This
> means that you can rearrange datafiles across disks easily and logically.
3
> datafiles all of different sizes for one tablespace seems wrong.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
> "crumedgeon" <zimsbait_at_hotmail.com> wrote in message
> news:U4tg7.89888$ai2.6321225_at_bin2.nnrp.aus1.giganews.com...
> > Oracle wants a 2G initial extent (as shown in your import snippet). Your
> > largest possible extent is 1.5G (as per your create tablespace command
> > at the bottom). If there is something else in that tablespace, export it
> also,
> > drop the tablespace, delete the OS files (via os commands) and recreate
> > the tablespace as a single 3.5G file (versus 3 smaller files). Than
> import
> > all your tablespace objects.
> >
> >
> > "JK Yao" <jkyf0131_at_hotmail.com> wrote in message
> > news:93f65984.0108210018.6e4ed7ea_at_posting.google.com...
> > > dear all,
> > >
> > >
> > > Oracle8.1.6
> > > OS: Solaris5.7
> > > Import Utility
> > >
> > > full=y rows=n constraints=n indexes=n
> > >
> > > When I imported the object, there is a error showing that I am not be
> > > able to create a table. What caouse this? The tablespace is big
> > > enough. Any though? Following is the error mesg and my script of
> > > creation of the tablespace.
> > >
> > > Thanks in advance,
> > > JK
> > > ~~~~~~~~~~~~~~~
> > >
> > > ****** error codes *********
> > > IMP-00017: following statement failed with ORACLE error 1658:
> > > "CREATE TABLE "PART_COST_BUCKET_HISTORY_TAB" ("PART_NO" VARCHAR2(25)
> > > NOT NUL" ...) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
> > > STORAGE(INITIAL 2044334080 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS
> > > 2147483645 PCTINCREASE"
> > > " 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "MPC_DATA""
> > > IMP-00003: ORACLE error 1658 encountered
> > > ORA-01658: unable to create INITIAL extent for segment in tablespace
> > > MPC_DATA
> > >
> > > ****************
> > >
> > > ************* tablespace setting *************
> > > CREATE TABLESPACE mpc_data DATAFILE
> > > '/data2/P8/mpc_data.dbf' size 1500M,
> > > '/data2/P8/mpc_data1.dbf' size 1450M,
> > > '/data2/P8/mpc_data2.dbf' size 500M
> > > DEFAULT STORAGE
> > > ( INITIAL 2M NEXT 2M MINEXTENTS 1
> > > MAXEXTENTS 249 PCTINCREASE 0)
> > > ONLINE;
> > > ****************
> > >
> >
> >
>
>