Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Toivo.

Asked: May 12, 2022 - 7:32 am UTC

Last updated: May 25, 2022 - 3:45 am UTC

Version: 12.2

Viewed 100+ times

You Asked

I have a DB on ExaCC Gen1 platform with a big LOB segment and when I try to compress it I ran into undo problems.

Table size 2,68G
LOB segment size 14TB

UNDOTBS1 size 544G
UNDOTBS2 size 128G

Sys@DBname1> show parameter undo_retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 3000000
Sys@DBname1> set timing on
Sys@DBname1> ALTER TABLE TABLE.NAME MOVE LOB(bfiledata) STORE AS (TABLESPACE TS2 COMPRESS DEDUPLICATE) online;
ALTER TABLE TABLE.NAME MOVE LOB(bfiledata) STORE AS (TABLESPACE TS2 COMPRESS DEDUPLICATE) online
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old


Elapsed: 75:11:23.77

I am played around with undo_retention parameter, however this seems not help me in this matter.
What else could I try in order to overcome this issue?

Additional information:


CREATE TABLE "UCMMASTER"."FILESTORAGE"
( "DID" NUMBER(*,0) NOT NULL ENABLE,
"DRENDITIONID" VARCHAR2(30 CHAR) NOT NULL ENABLE,
"DLASTMODIFIED" TIMESTAMP (6),
"DFILESIZE" NUMBER(*,0),
"DISDELETED" VARCHAR2(1 CHAR),
"BFILEDATA" BLOB,
CONSTRAINT "PK_FILESTORAGE" PRIMARY KEY ("DID", "DRENDITIONID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UCMMASTER" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UCMMASTER"
INMEMORY PRIORITY HIGH MEMCOMPRESS FOR QUERY LOW
DISTRIBUTE AUTO DUPLICATE ALL
LOB ("BFILEDATA") STORE AS SECUREFILE (
TABLESPACE "UCMMASTER" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
CACHE ENABLE ROW MOVEMENT


and Connor said...

Request: Can we get the full DDL for the table please (via DBMS_METADATA) so we can see the existing LOB settings?

OK, you don't have a MAXSIZE or explicit RETENTION setting, so by default we're going to generally try to keep a read consistent view of the lob data bounded by either undo_retention or the upper limit on your undo tablespaces.

If that's a busy system, you might still be recycling undo too quickly because 75hrs is obviously a fairly heavy duty amount of work going on.

You can run

select BEGIN_TIME,END_TIME,MAXQUERYLEN,TUNED_UNDORETENTION
from v$undostat;


to get some idea of what true undo retention you are getting.

Perhaps an option here is to look at a "manual" solution to give you more control of the undo, eg

- create table FILESTORAGE_NEW with the new LOB parameters you want
- put a ins/upd/del trigger on FILESTORAGE being to grab to a temp table
- the DID (assuming thats the primary key)
- the operation (ins,upd,del)
- a sequence number so you know what order they occurred in
- now over the next few days you'll do

insert into FILESTORAGE_NEW
select * from FILESTORAGE
where DID between :x and :y;
commit;

choosing suitable ranges for :x and :y to keep the duration down to (say) a couple of hours to ensure no undo issues.

- Eventually you'll have FILESTORAGE_NEW being a copy of FILESTORAGE_NEW , but any updates since you started are missing.
- Build all the indexes etc you need on the new table to match the old one
- Now you loop through your temp table applying any changes (in sequence order) to bring FILESTORAGE_NEW up to date.
- For cutover, it is then
- take a small app outage
- apply final set of updates from the temp table
- rename FILESTORAGE_NEW to FILESTORAGE


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here