Problem solve Get help with specific problems with your technologies, process and projects.

Errors and slowness during export

I am exporting using Oracle on Sun 5.8. The problem is recently I received the ORA-1555, ORA-1650, ORA-1593 and ORA-1594 errors.

I am exporting with parameter file=*** log=*** full=y compress=n consistent=y feedback=50000. I am using Oracle on Sun 5.8. The problem is recently I received the ORA-1555, ORA-1650, ORA-1593 and ORA-1594 errors. Then I added rollback segments to locally managed tablespaces where storage parameters were ignored. To overcome this problem I created a dictionary-managed tablespace and then created five RBs with initial=next=6m minextents=12 opt=75m, and all are online.

Now I am using all RBs present in LMT and DMT. But now exp with the same parameters hangs at big tables (around 20 million rows) for more than six hours. An earlier export was successful with six RBs and tablespace size 500m. Now I am using 12 RBs and size 2 GB. The earlier exp duration was two to three hours (running in cron). Now I am not able to take exp either through cron or manually. After 24 hours the exp was not completed. When I start exp initially it can be seen using the top command but later it disappears, but it can be traced using ps -ef. I tried all options. How can I proceed?

The ORA-1555 errors are generated, in part, because you specified CONSISTENT=Y in your parameters. This parameter tells the export process that all tables in the dump file must be consistent with the point in time of when the export was started. In a highly active database, you may have lots of changes taking place and the rollback segments cannot hold all of the undo records needed to take a snapshot of the database at one time. One option is to use CONSISTENT=N. With this parameter setting, data in one table is consistent with a single point in time, but data between tables may not be consistent.

You do have a few options in Oracle 8i. One, create more rollback segments and make them larger as well. That can eliminate your ORA-1555 errors, but depending on your database changes, you may still hit these errors. If you were using Oracle 9i or 10g, then using Automated Undo would help greatly. Unfortunately, that is not an option for you.

Another option is to perform your export when you can guarantee that the database is not undergoing changes. Start your database in restricted mode and then perform the export as a user who can sign on to the database (like SYS or SYSTEM) when in restricted mode.

This was last published in October 2005

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.