export needs bigger RBS

Oracle People:

My RBSs are sized perfectly for my database. The TOAD tool even says that they are a bit big. The problem is that they can't handle the export of certain large tables:

*****************************************************************
exporting table NODE_VALUE
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 9 with name "RBS8" too small
*******************************************************************

I don't want to change my RBSs but I need to export. Is there a way to assign a special RBS to an export ??? Do I need to increase the size of all my RBSs ???

Thanks,

Roger

Hey Roger; Been there, done that.
I would create an export parfile and be sure to add
BUFFER=
COMMIT=Y

I usually set the BUFFER between 1000000 and 4000000, this is measured in bytes so 1 million is less than a meg ect...
This way you will commit every 1-4 megs rather than trying to import the entire table and then commit, which is the Oracle default.

Good Luck

MH

Take your export when the database is not busy. Don't put CONSISTENT=Y to your export command.

Quote:

---

Originally posted by Mr.Hanky
Hey Roger; Been there, done that.
I would create an export parfile and be sure to add
BUFFER=
COMMIT=Y

---

There is no INSERTS/UPDATES/DELETES performed during the export, so there is also no COMMIT= parameter available in EXPort! I belive you've confused this with IMPort.

Quote:

---

Originally posted by rogerF
Is there a way to assign a special RBS to an export?

---

No, there isn't. And it wouldn't make sence. Export doesn't need rollback segments for writing undo information into them (as it doesn't change any data, anyway), but to constructs the read consistent view of table's data as it was when the export of that table begun. If during the proces of exporting other sessions are changing the contents of that table, export must find the values as they were at the begining of the table's export (Time 0). It can find this information only from the rollback segments into which other processes have wriiten their undo information about their changes. So it is not one RBS, there might be many of them that would help exp process to reconstruct the image of the data at Time 0. If any of that information in one of the rollback segment is allready overwritten, you'll get ORA-1555.

So you either follow mike73 advice (export when there is not much changes performed on those tables and don't use CONSISTENT=Y if it is not necessary), or make your rollback segments bigger (and/or make more of them). Your RB segments might be sized OK for your transactions, but they obviously are not sized OK for long running queries like EXP.

Sorry my bad, this is the second time I've done this. It will help with import though.

MH

Quote:

---

Originally posted by Mr.Hanky
Sorry my bad, this is the second time I've done this. It will help with import though.

MH

---

We'll credit you with a correct answer to the wrong question http://www.dbasupport.com/forums/

THANKS !

I just got rid of the consistant=y, and I successfully got the table on export.

. . exporting table NODE_VALUE 1840118 rows exported

-Roger

I hope you're not using export for backup purposes. If you are, you have just reduced the limited effectiveness of exp.

Marist89:

I'll walk into the trap :-) and answer the question ... Yes, we do use them as a backup. The good news is that we are moving over to RMAN for this DB. I usually run HOTBACKUPS and have EXPORTS (on other DBs) to have the flexibility to quickly recover a users table if they make a stupid mistake. Obvoisly this database is getting too big for the export option.

Oracle actually recommended to me (during a course) to do the EXPORTS - as long as they didn't take too long or disturb the users.
This worked well for our small DBs.

So - can you expand on why ?

Thanks,
Roger

Well, I should have said I hope you're not using export as the only method for purposes of backup. If you are, you have just reduced the limited effectiveness of exp.

Using export as the only method of backup is a big no-no, IMHO. It limits your options for a recovery in so many scenarios. By using CONSISTANT=N, you are limiting yourself even more because your RI will most likely be out of whack in an active database.

Since you're using hot backups as the primary backup method (you've tested basic recovery scenarios, right?) and use the exp as a supplemental backup, you are covered for the overwhelming majority of failure scenarios.

THANKS !

I just got rid of the consistant=y, and I successfully got the table on export.

. . exporting table NODE_VALUE 1840118 rows exported

-Roger