DELETING Millions of rows from a table

[dmcadams]

I need help deleting 300 Millions of rows from a table and then reclaim the space in the table after completing the delete process. The database needs to be online and available to the users. There is one bitmapped index on the field which is the criteria field used for selecting which records to delete. That field is TIME_ID There is one trigger. There is one public synonym

It is a narrow table.

IOSEQ NUMBER(6),
A VARCHAR2(8 BYTE),
B VARCHAR2(8 BYTE),
C VARCHAR2(8 BYTE),
D VARCHAR2(8 BYTE),
E VARCHAR2(20 BYTE),
F VARCHAR2(3 BYTE),
G VARCHAR2(10 BYTE),
TIME_ID NUMBER(10) NOT NULL

The operating system is Solaris 5.9
The database is Oracle 9.2.0.6.0
E3500 configured as follows:
========================= CPUs =========================
Run Ecache CPU CPU
Brd CPU Module MHz MB Impl. Mask
--- --- ------- ----- ------ ------ ----
3 6 0 336 4.0 US-II 2.0
3 7 1 336 4.0 US-II 2.0
5 10 0 336 4.0 US-II 2.0
5 11 1 336 4.0 US-II 2.0

========================= Memory =========================
Intrlv. Intrlv.
Brd Bank MB Status Condition Speed Factor With
--- ----- ---- ------- ---------- ----- ------- -------
3 0 1024 Active OK 60ns 2-way A
5 0 1024 Active OK 60ns 2-way A

There is a 2 gig datafile in the UNDOTBS01

I believe that the database or at least the tablespace will have to be offline at some point as the initial source table against which I am wanting to execute deletes will have to be truncated. The table has 470 million rows and will therefore have 170 million rows when this process is complete. I know that I can use the /+APPEND+/ hint on the insert statement into a temporary table, and it will do a direct path insert. ( For speed). I need help with putting it all together.

[SQLSilverSurfer]

untill I saw that 170m will remain, TRUNC was my first thought. Perhaps a parameter driven delete on a timed sequence trigger. However, the rollback will need to be disabled for this, and not sure about 300M in a LOCK EXCLUSIVE MODE.

I'd like to know the final outcome