ba6.us - Dave's Database Related Stuff

If you have a lot of LOB data to move around with Oracle import/export utilities this might help you out. The default for the COMMIT parameter is No.

COMMIT=N : For tables that have a LOB column, commit is performed after loading each table
This is much faster but be aware that you will use more Rollback space since each table will be committed in one large transaction.

COMMIT=Y : For tables that have a LOB column, commit is performed after each row (slow!, much overhead added because so many transactions are used to import the data)

So if you need to conserve UNDO space and don't care how long things take to import, COMMIT=Y is what you can use. If you need to import as fast as possible, use COMMIT=N and make sure your Rollback can handle the additional space needed.

The Oracle documentation for the 9i imp utility here says:

"For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, or DATE columns, array inserts are not done. If COMMIT=y, Import commits these tables after each row."

I believe "DATE" should be removed from that list. 95% of the tables in the schemas I use have DATE columns (and nothing else in that list) and I have not encountered this commit behavior for these tables when COMMIT=y.

– Dave