As mentioned in my above question ,
we have table x with BLOB column which stores pictures,has lobsegment size of 7.7 TB and table datasize 800 GB .
i would like to delete 7 cr records (10 % of total table count ) from table x and insert them into another table Z.
We are facing following challenges :
1.Insertion is slow ( we tried append hint - insert /*+ append */ into z select * from x , we also tried using forall , we tried table z with nologging , disable storage in row(as lob column data is more than 4000bytes) )
Kindly suggest any approach to tune insertion .
2. In Table x blob column is with PCTVERSION 10 and lob is stored as basic file .
we are deleting apprx 7 cr records from x table equilvalent to almost 700 GB which is 10% of total lobsegment size . so to store the undo it will use some amount of space in table x lobsegment (inside lobsegment ).
3.After this deletion there won't be any update and delete's on this table (table x) in future , only select and insert will come on this table . So clarify till how much time that undo data(genrated during deletion ) will be stored in lobsegment and space will get reuse .
4.As we are deleting approx 7 cr records (equivalent to 700 GB size ) from table x , To reclaim space after deletion we have used this query ->>ALTER TABLE x MODIFY LOB (image) (SHRINK SPACE) ; . we have executed this query and it was running for more than 6 to 8 hrs . so we can't implement it on production .
so Kindly suggest any alternative approach to reclaim the space .
Note : We have tried to replicate this scenario by creating a small table with lob segment size as (approx 30GB) with pctversion as 10 and storage of lob as basic file .In this scenario,we have observed that after deleting the data (approx 10% of the total lob size) space was getting reused when we tried to insert.