I have been informed by my DBAs that one of the production tables has got a high water mark which is excessive and needs to be brought down (average size of table 35GB but high water mark currently at 256GB).
I proposed the following:
1. Copy all contents of table to a temporary location
CREATE TABLE ITEMS_COPY TABLESPACE TMP_ITEMS NOLOGGING AS SELECT * FROM ITEMS;
2. Disable all foreign keys on items table
TRUNCTATE TABLE ITEMS DROP STORAGE;
4. Insert data from temp table:
INSERT INTO ITEMS SELECT * FROM ITEMS_COPY NOLOGGING;
5. Re-enable foreign keys
6. Rebuild indexes on table
ALTER INDEX ITEM_IDX REBUILD ONLINE TABLESPACE INDEX_TS NOLOGGING;
This requires application downtime and after the first attempt, the procedure had to be aborted because step one on its own ran over the allotted maintenance window.
Step one took 4.5 hours to complete, where maintenance window was 4 hours.
Is there any way of getting the desired end result quicker than with my queries?
I already considered simply copying table once to "items_copy" then renaming to "items" to save on one copy, but the "items" table has to reside in the allotted tablespace, and due to the huge high water mark of the original table, (i am told by the DBA) if the tablespace would be increased yet further, it would cause problems for the DB hence I have been forced to create the items_copy table in the tmp_items tablespace.
Any pointers would be gladly accepted.