Remove most of the data out of a table

I’ve been asked to archive off most of the data from a test environment.

As per usual, most of the data is in about 10 tables, that makes my job easy.

Client tells me I can use UPMJ to cull data, my job is easier…

Use this script

It’s pretty cool.

Essentially it:

• drops all indexes
• drops constraints
• renames table
• recreates original table
• inserts required records
• drops the renamed edition
• generates all indexes

When you try and delete 90% of a large table, it’s going to kill the temp table space and redo if archive logging is enabled.  This method reduces the amount of temp needed and also reduces the amount of redo.  It creates nice contiguous tables and indexes too.

See that it does the security once again, because you are creating the table!