IOUG Live! sent me into rapturous joy. More on that later. I can't believe I was seriously considering not going.

Here's one of the problems that the conference set my mind to.

Some of my homemade procedures fill up some tables with huge amounts of somewhat ephemeral logging data. It's only needed for a couple weeks, after which I need to delete the rows. Doing it the conventional way generates cruel amounts of redo. I've wanted a good script to duplicate the effects of the imaginary command
DELETE FROM logtable WHERE blah blah blah NOLOGGING

The basic algorithm I've seen is
CREATE TABLE newlogtable AS
SELECT * FROM logtable
WHERE (criteria for keeper rows)
UNRECOVERABLE;
DROP TABLE logtable;
RENAME newlogtable TO logtable;

The trouble is that it's hard to get all the dependent DDL to move along. Especially since I don't want any names to change - I name all my constraints, down to the NOT NULLS. The script gets more complicated then.

I found out about DBMS_METADATA.GET_DDL and DBMS_METADATA.GET_DEPENDENT_DDL at Joe Trezzo's "New 10g & 9i features", and it looks like that might be a good basis. It's still tricky, though, because I can't create new dependent DDL until the originals have been dropped. It feels like juggling: generate the new DDL, drop the original dependent objects but not the original table, create the new table with its data based on the stored DDL, then finally drop the original table... hmm.

I'm thinking of relying on the recycle bin (more new IOUG knowledge) for some help here. Deleting objects in 10g really only renames them and designates them as being in a "recycle bin"; you can still access them, you just need to consult the right views to learn their new obscure system-assigned names. (They make an awful clutter in your schema viewed through TOAD, btw.)

Could I drop the table (with its objects) first, then build a new table based directly on the one in the recycle bin? That means all the old dependent object names would already be out of the way, and I can do it all in fewer steps.

Something seems scary about relying on the recycle bin that way, though. Like mailing checks against the deposit you expect to make while your envelopes are still in the mail. I guess Oracle makes no guarantees about the continued availability of objects in the recycle bin; they stay until they're manually purged or the tablespace fills up. But, since it is a very bulky table I'm working with, it's not too farfetched that the recycle-bin version could become unavailable while I'm halfway through this process. Hmm. Darn.