Home » RDBMS Server » Server Utilities » ORACLE import error (Oracle 8.1.7 )
ORACLE import error [message #544732] Thu, 23 February 2012 09:43 Go to next message
fvazharov
Messages: 6
Registered: October 2009
Location: Bulgaria
Junior Member
Dear All,

I have the problem with import in Oracle 8.1.7

The size of import file is 29600 kb and tablespace size is 16gb and when I try to make import oracle back this message:

IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 7 in tablespace DATA

The data tablespace is full. I think that the import file contains information about the original tablespace from which has made ​​export. But I don't now how to resolve the problem

Please, can anyone help?

BR,
Fil

Report message to a moderator

Re: ORACLE import error [message #544733 is a reply to message #544732] Thu, 23 February 2012 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE

Report message to a moderator

Re: ORACLE import error [message #544738 is a reply to message #544732] Thu, 23 February 2012 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
But I don't now how to resolve the problem


- Use "indexfile" to get the table (and indexes) definitions
- uncomment the CREATE TABLE statements
- modify the statements accordingly to youor needs
- execute the file (as a SQL script)
- import with the option IGNORE=Y

Regards
Michel

Report message to a moderator

Re: ORACLE import error [message #545458 is a reply to message #544738] Wed, 29 February 2012 09:42 Go to previous messageGo to next message
fvazharov
Messages: 6
Registered: October 2009
Location: Bulgaria
Junior Member
Thank you Michel,

I am greenhorn and I don't know what exactly to modify in the indexfile that I create with the following command:

imp demo/demo FROMUSER=DEMO INDEXFILE=d:\ind.txt

I copy here a part of the file contents, could you please, help me! What I need to change, so that the import to take normally space?


CREATE INDEX "DEMO"."COMPANYCUSTOMER113" ON "COMPANYCUSTOMER"
("DESTINATIONNUMBER" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
1105920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEMO"
LOGGING ;
CREATE INDEX "DEMO"."COMPANYCUSTOMER114" ON "COMPANYCUSTOMER" ("NAME1" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1105920 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEMO" LOGGING ;
CREATE INDEX "DEMO"."COMPANYCUSTOMER115" ON "COMPANYCUSTOMER"
("PAYERIDENTIFICATION" ) PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 1105920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DEMO" LOGGING ;
CREATE INDEX "DEMO"."COMPANYCUSTOMER116" ON "COMPANYCUSTOMER"
("PAYMENTCUSTOMER" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
1105920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEMO"
LOGGING ;
CREATE INDEX "DEMO"."COMPANYCUSTOMER117" ON "COMPANYCUSTOMER" ("TELEPHONE"
) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1105920 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEMO" LOGGING ;
CREATE INDEX "DEMO"."COMPANYCUSTOMER118" ON "COMPANYCUSTOMER"
("THECUSTOMERNUMBER" , "SETTLINGCOMPANY" ) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 1105920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "DEMO" LOGGING ;

Report message to a moderator

Re: ORACLE import error [message #545464 is a reply to message #545458] Wed, 29 February 2012 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I don't know what exactly to modify in the indexfile

Quote:
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE

See the last part starting with "or retry with...".
But if you have not enough space in the tablespace then you have enough space and you have to enlarge it anyway.

Regards
Michel

Report message to a moderator

Re: ORACLE import error [message #545465 is a reply to message #545464] Wed, 29 February 2012 10:28 Go to previous messageGo to next message
fvazharov
Messages: 6
Registered: October 2009
Location: Bulgaria
Junior Member
What value have to point, must be a multiple?

Report message to a moderator

Re: ORACLE import error [message #545468 is a reply to message #545465] Wed, 29 February 2012 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you use LMT (locally managed tablespace) or DMT (dictionary managed tablespace)?
Check the space used in the original database.
Change all MINEXTENTS to 1 and PCTINCREASE to 0.

Regards
Michel

[Updated on: Wed, 29 February 2012 11:26]

Report message to a moderator

Re: ORACLE import error [message #545538 is a reply to message #545468] Thu, 01 March 2012 02:30 Go to previous messageGo to next message
fvazharov
Messages: 6
Registered: October 2009
Location: Bulgaria
Junior Member
Michel,

Thank you very much!

My INITIAL_EXTENT for some tables was a huge. I solved the problem the following way:


CREATE TABLE new_table as select * from table;

DROP original table

RENAME new_table to table;

In this way the storage settings of the tables accept these of the tablespace.

Regards
Filip

Report message to a moderator

Re: ORACLE import error [message #545545 is a reply to message #545538] Thu, 01 March 2012 02:55 Go to previous messageGo to next message
fvazharov
Messages: 6
Registered: October 2009
Location: Bulgaria
Junior Member
Maybe the command 'deallocate_unused_clause' will do the same?

Report message to a moderator

Re: ORACLE import error [message #545555 is a reply to message #545538] Thu, 01 March 2012 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
In this way the storage settings of the tables accept these of the tablespace.


I don't understand what this means.

Quote:
Maybe the command 'deallocate_unused_clause' will do the same?


Same as what?

Regards
Michel

Report message to a moderator

Re: ORACLE import error [message #545570 is a reply to message #545555] Thu, 01 March 2012 03:55 Go to previous message
fvazharov
Messages: 6
Registered: October 2009
Location: Bulgaria
Junior Member
Michel Cadot wrote on Thu, 01 March 2012 03:24
Quote:
In this way the storage settings of the tables accept these of the tablespace.

I don't understand what this means.


When I recreate the tables become following:

Object DDL:

BEFORE

STORAGE ( INITIAL 8320K NEXT 8192K MINEXTENTS 1 MAXEXTENTS 249
PCTINCREASE 100 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

AFTER

STORAGE ( INITIAL 24K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

Quote:
Maybe the command 'deallocate_unused_clause' will do the same?

Same as what?


I thought that doing the same as above but no. I tried

Regards,
Filip

Report message to a moderator

Previous Topic: Import Dump file (merged 2)
Next Topic: Re: ORA-01847: day of month must be between 1 and last day of month -split from ancient thread by bb
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 20:02:22 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.