OraFAQ Forum: Server Utilities » Problems in Import

Home » RDBMS Server » Server Utilities » Problems in Import

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problems in Import [message #136761]

Fri, 09 September 2005 23:46

Avra
Messages: 65
Registered: July 2004

Member

Hi,

This is Avra from India. I am using Oracle8i with Win2000 Prof. I am trying to import a table from a .dmp file which contains round about 49,00,000 rows. But it's throwing the following error after importing round about 17,00,000 rows.

imp 00058: oracle error 1562 occured

ora-01562: failed to extent rollback segment number3

ora-01628: max # extents (121) reached or rollback segment rb2

imp-00028: partial import of prev table rolledback

Pls help me to solve this problem.

Thanks in advance....

Avra

Report message to a moderator

Re: Problems in Import [message #136773 is a reply to message #136761]

Sat, 10 September 2005 01:29

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

I think ur import session is generating lots of rollback entries & rollback segments are not big enough to accomodate those rollback entries.

Create more rollback segments & try again.

regards,
tarun

Report message to a moderator

Re: Problems in Import [message #136781 is a reply to message #136773]

Sat, 10 September 2005 02:40

Avra
Messages: 65
Registered: July 2004

Member

Hi,

U r right.. It's huge table there.. Can u give me the syntax of the rollback segment which can be used for this huge table??

Avra

Report message to a moderator

Re: Problems in Import [message #136864 is a reply to message #136773]

Sun, 11 September 2005 23:12

Avra
Messages: 65
Registered: July 2004

Member

Hi Tarun,

As per ur advice, I ve created the following rollback segment, but still i am getting the same error. Pls help....

create tablespace "PLS" DATAFILE 'F:\Oracle\PLS1.DBF' SIZE 2000M ,'F:\Oracle\PLS2.DBF' SIZE 2000M autoextend oN
ONLINE PERMANENT

CREATE ROLLBACK SEGMENT RBS_PLS
TABLESPACE PLS
STORAGE
( INITIAL 1000M
NEXT 1000M
MAXEXTENTS UNLIMITED );

ALTER ROLLBACK SEGMENT RBS_PLS ONLINE;

CREATE USER AAA IDENTIFIED BY AAA DEFAULT TABLESPACE PLS TEMPORARY TABLESPACE TEMP

Do u suggest any changes??

Thanks,
Avra

Report message to a moderator

Re: Problems in Import [message #136925 is a reply to message #136864]

Mon, 12 September 2005 05:22

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

There are two options here.
1. Create a large RBS to hold the full transaction.
Seems even your NEW RBS is not enough.
Create a larger one, and assign that to this transaction ( As Tarun has said)

or
2. Use commit=y during import.
THis will slow down your import process.
But, the job will be done.

General tip:
Always export with compress=n
If the database is of signicant volume, consider importing only data and recreate the indexes manually with Parallel option
and followup the tips here
http://www.orafaq.com/faq/import_export

Report message to a moderator

Re: Problems in Import [message #137042 is a reply to message #136925]

Mon, 12 September 2005 23:22

Avra
Messages: 65
Registered: July 2004

Member

Hi Mahesh,

Thanks a ton... I am able to import entire table after giving 'COMMIT= Y' option...

Thanks once again...

Avra

Report message to a moderator

Previous Topic:	Migrating from Oracle 8i to 9i
Next Topic:	getting error SQL*Loader-350

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 06:40:14 CST 2024