Title
ORA-02266 when trying to truncate B2B.TIP_DATASTORAGE_RT

Date
2008-02-19

Summary
The B2B.PURGE_RUNTIME procedure deletes records from 8 runtime data tables, but for some reason does not clear up the massive LOB_SEGMENT in the B2B_LOB tablespace, so the steps below purges all of this runtime data. Do not follow the steps below if you want to preserve any historical runtime data.

Details
1. Log in as user B2B and truncate and delete 7 of the 8 runtime data tables referenced in B2B.PURGE_MESSAGE. 
TRUNCATE TABLE b2b.tip_businessmessagereceiver_rt;
TRUNCATE TABLE b2b.tip_businesstransactioninst_rt;
DELETE FROM b2b.tip_businessmessage_rt;
COMMIT;
DELETE FROM b2b.tip_wiremessage_rt;
COMMIT;
DELETE FROM b2b.tip_collaborationinstance_rt;
COMMIT;
DELETE FROM b2b.tip_recordinstance_rt;
COMMIT;
DELETE FROM b2b.tip_runtimedatainstance_rt;
COMMIT;

2. Confirm that all of the following tables have 0 records in them. If not, do not proceed. 
SELECT COUNT(1) FROM b2b.tip_eventbodyelementinstanc_rt;
SELECT COUNT(1) FROM b2b.tip_recordelementinstance_rt;
SELECT COUNT(1) FROM b2b.tip_runtimedatainstance_rt;
SELECT COUNT(1) FROM b2b.tip_businessmessage_rt;
SELECT COUNT(1) FROM b2b.tip_wiremessage_rt;

3. Disable foreign key constraints that reference the primary key on B2B.TIP_DATASTORAGE_RT. This is because these table have foreign keys that reference the primary key of B2B.TIP_DATASTORAGE_RT. 
ALTER TABLE b2b.tip_eventbodyelementinstanc_rt DISABLE CONSTRAINTS tip_datastorage_rfk;
ALTER TABLE b2b.tip_recordelementinstance_rt DISABLE CONSTRAINTS tip_datastorage_rfk_1;
ALTER TABLE b2b.tip_tip_runtimedatainstance_rt DISABLE CONSTRAINTS tip_datastorage_rfk_2;
ALTER TABLE b2b.tip_businessmessage_rt DISABLE CONSTRAINTS tip_attachmentstorage_rfk;
ALTER TABLE b2b.tip_businessmessage_rt DISABLE CONSTRAINTS tip_payloadstorage_rfk;
ALTER TABLE b2b.tip_wiremessage_rt DISABLE CONSTRAINTS tip_componentstorage_rfk;
ALTER TABLE b2b.tip_wiremessage_rt DISABLE CONSTRAINTS tip_packedmessagestorage_rfk;
ALTER TABLE b2b.tip_wiremessage_rt DISABLE CONSTRAINTS tip_payloadstorage_rfk_1;

4. Running a DELETE on B2B.TIP_DATASTORAGE_RT takes too long, so a TRUNCATE is preferred. Now that the constraints are disabled, you can run a TRUNCATE on this table without getting an ORA-02266. 
TRUNCATE TABLE b2b.tip_datastorage_rt;

5. Re-enable the constraints. 
ALTER TABLE b2b.tip_eventbodyelementinstanc_rt ENABLE CONSTRAINTS tip_datastorage_rfk;
ALTER TABLE b2b.tip_recordelementinstance_rt ENABLE CONSTRAINTS tip_datastorage_rfk_1;
ALTER TABLE b2b.tip_tip_runtimedatainstance_rt ENABLE CONSTRAINTS tip_datastorage_rfk_2;
ALTER TABLE b2b.tip_businessmessage_rt ENABLE CONSTRAINTS tip_attachmentstorage_rfk;
ALTER TABLE b2b.tip_businessmessage_rt ENABLE CONSTRAINTS tip_payloadstorage_rfk;
ALTER TABLE b2b.tip_wiremessage_rt ENABLE CONSTRAINTS tip_componentstorage_rfk;
ALTER TABLE b2b.tip_wiremessage_rt ENABLE CONSTRAINTS tip_packedmessagestorage_rfk;
ALTER TABLE b2b.tip_wiremessage_rt ENABLE CONSTRAINTS tip_payloadstorage_rfk_1;

6. Get the high water mark for the B2B_LOB tablespace in order to shrink it, since this is the largest growing B2B tablespace. 
SELECT   file_id, MAX(block_id+blocks-1) hwm
FROM     dba_extents
WHERE    file_id = (SELECT file_id FROM dba_data_files WHERE tablespace_name = 'B2B_LOB')
GROUP BY file_id;

7. Using the high water mark value above, get the size that you can shrink the datafile to. This statement assumes you have a block size of 8192, so adjust accordingly. 
SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' RESIZE ' || CEIL( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd
FROM   dba_data_files a, (SELECT &&hwm_value hwm FROM dual) b
WHERE  a.file_id = (SELECT file_id FROM dba_data_files WHERE tablespace_name = 'B2B_LOB')
AND    CEIL(blocks*8192/1024/1024) - CEIL((nvl(hwm,1)* 8192)/1024/1024 ) > 0;

8. The output above will give you an SQL statement to alter the datafile.
For example: 
ALTER DATABASE DATAFILE '/u01/oradata/reposdb/b2b_lob.dbf' RESIZE 2689m;

Applicable Versions
Oracle B2B 10g (10.1.2.0.2)

References
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html
Ahmed Aboulnaga
.com .com