We operate an Oracle 19c database.
Customer would like us to online reorganize some tables that has CLOBs fields stored in secure file.
There is no "low-load" time frame so the online reorgs fail with ORA-1555 every time. ... and in DBA_LOBS view the PCTVERSION, RETENTION fields are empty.
So the question is how could we increase the chance that online reorg finishes successfully?
Thankyou, Darkvood
On
A reorg would only free up space if you have deletes opening up room in blocks that isn't getting reused, or have row chaining, or employ compression and some rows have gotten decompressed, or blew out the # of ITL slots with massive concurrent updates on rows in the same block (unlikely). For most tables there really no benefit to reorganizing the segment unless it is to implement a change (move to a new tablespace, compress, decrease PCTFREE, etc..). Normal delete and conventional insert uses space well enough, and what you squeeze out will soon return to its current state.
If the goal of freeing up space is performance (not space savings), then the only situation where this would help is if your queries are doing full table scans and one of the above scenarios has significantly enlarged the table over what it needs to be (or you have extensive row chaining). If your queries are using indexes it won't make a difference how large the table is.
If however you decide you really need to do a reorg, a simple segment reorg is done with the ALTER TABLE MOVE command:
ALTER TABLE mytable MOVE
This will move the table segment as well as any LOBs it has. By "move" I mean move it to new extents, to a new region of the tablespace. Doing this recreates the physical table and LOB segment(s) which is what accomplishes the physical reorganization at the block level. It doesn't have to involve moving it to a different tablespace (it can if you want).
Now the bare command above will be single threaded and therefore slow on a large table, will block your app from doing any DML on it while it's happening, and will leave your indexes unusable requiring manual index rebuilds afterwards. All of this is a problem for a large, busy table. So in your situation we need to add some additional options. You can make faster with parallelism (and NOLOGGING, if that's acceptable, which it usually is not in a production database). You can have it automatically rebuild indexes for you with UPDATE INDEXES and you can also do it while DMLs are happening with the ONLINE option, which sounds like in your case you may need it):
ALTER TABLE mytable MOVE PARALLEL (DEGREE 8) ONLINE UPDATE INDEXES
If you employ parallelism like this you may be able to get the work done fast enough to avoid that ORA-1555. If you have lots of CPU cores available, increase the parallelism to whatever you can get away with. 16, 32, 64, whatever, just leave enough CPU for your other workloads to not be impacted.
On
meanwhile I found the answer.
Oracle automatically tunes undo retention time if there is free space in undo tablespace so UNDO_RETENTION parameter is technically obsolete and should not be modified.
But ... this is not valid for LOBs. In case of concurrent DML queries:
A DML query shorter than "UNDO_RETENTION Seconds" does not fail with ORA-1551 Snapshots too old even though the DML modifies the LOB. Queries that last longer than "UNDO_RETENTION Seconds" may fail.
If your want a different behavior, you need to increase the "N seconds".
So the solution is to increase UNDO_RETENTION from default 900 secs to a BIG value and make sure that there is enough space.
Regards,
Darvud.
I'm guessing you're using DBMS_REDEFINITION? In any event, the first phase of a online reorg is a 'snapshot' of the current data, which provides a base from which we can later apply deltas to keep the process up to date whilst still being online.
So in effect you'd need an undo retention (and similarly sized undo tablespace) for as long as it would take to do a "create-table-as-select" on your table.
You can experiment with a copy using subset of the data and extrapolate from there - it should be reasonably linear.
Also worth exploring some of the caching/logging options for your LOB to see if tweaking those improves the performance of the CTAS.