Oracle question

[DBA_bloke]

If no constraints, then, after CTAS:

"DROP TABLE orginal_table PURGE;"
then:
"ALTER TABLE backup_table RENAME TO original_table;"

[Moose423956]

I guess it didn't go well

It was a "just in case" question. Just done the delete, and it went okay.

[TheFaQQer]

Supplementary question.

Given that I have gone for the "CREATE TABLE..." option, what code would I need to write to copy them back into the original table from the backup table, after deleting them from the original table?

I'm going to have a go at writing it...please don't laugh if I get it wrong.

INSERT INTO original_table
SELECT * FROM backup_table

Is it that simple?

I guess it didn't go well

Yes, that code should do it.

[lilelvis2000]

It looks okay to me....unless of course there is a sequence counter and you need to preserve the sequence number. In which case you'll need to turn that off.

Otherwise looks good...but I'm not a guru.

[Moose423956]

Supplementary question.

Given that I have gone for the "CREATE TABLE..." option, what code would I need to write to copy them back into the original table from the backup table, after deleting them from the original table?

I'm going to have a go at writing it...please don't laugh if I get it wrong.

INSERT INTO original_table
SELECT * FROM backup_table

Is it that simple?

[lilelvis2000]

Gosh! You gurus.

[TheFaQQer]

Agreed.

Cor, that OCP is really paying off now!

[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]

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.)

[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.

[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.

[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]

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]

CREATE TABLE blah AS ( SELECT * FROM original );

Creates a new backup table with them all in.

[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.