Oracle question

[Moose423956]

Before I run a process to delete a lot (thousands) of records from an Oracle table, is there an easy way of backing those records up first in case it all goes wrong, so they can be restored if necessary? Like copying the records to a text file or something like that?

I'm relatively new to Oracle, so be gentle with me.

[TheFaQQer]

CREATE TABLE blah AS ( SELECT * FROM original );

Creates a new backup table with them all in.

[DBA_bloke]

Use Oracle export, why clog-up the DB with table copies? You'll forget about them - I do!

Oracle Export:
exp userid=<schema-owner> file=MyFile log=MyLog compress=n consistent=y recordlength=65000 buffer=512000 direct=y tables=table1,table2, etc.

You'll be prompted for the schema-owner's database password.

[TheFaQQer]

Use Oracle export, why clog-up the DB with table copies? You'll forget about them - I do!

Oracle Export:
exp userid=<schema-owner> file=MyFile log=MyLog compress=n consistent=y recordlength=65000 buffer=512000 direct=y tables=table1,table2, etc.

You'll be prompted for the schema-owner's database password.

I was thinking along the lines of quickly being able to recover from an error without anyone noticing! Exp/Imp might be too visible!!

[DBA_bloke]

I was thinking along the lines of quickly being able to recover from an error without anyone noticing! Exp/Imp might be too visible!!

I like your sneaky style! 10 points.

[lilelvis2000]

well, if you don't commit then no one (but you) can see your changes.

So do your work. Then test everything. checksum. if all okay, commit. otherwise rollback and start all over.

If you are thinking Oracle works like Access or SQL Server - it don't - it works much better.

[TheFaQQer]

well, if you don't commit then no one (but you) can see your changes.

So do your work. Then test everything. checksum. if all okay, commit. otherwise rollback and start all over.

If you are thinking Oracle works like Access or SQL Server - it don't - it works much better.

But if there are "lots" of records, then there is a chance that you might blow the rollback segment.

And, you may need to commit before seeing the impact (e.g. deleting data and then check that your workflow / form / report / etc works.)

[DBA_bloke]

But if there are "lots" of records, then there is a chance that you might blow the rollback segment.

And, you may need to commit before seeing the impact (e.g. deleting data and then check that your workflow / form / report / etc works.)

Agreed.

[TheFaQQer]

Agreed.

Cor, that OCP is really paying off now!

[lilelvis2000]

Gosh! You gurus.