PROBLEM:
Application team was running some upgrade and during that process, they got the below error. ORA-20101:
ERROR at line 1: ORA-20101: PGIDX is not a ASSM (Automatic Segment Space Management) tablespace,
cannot upgrade BLOB to securefile ORA-06512: at line 28, exiting
SOLUTION:
NOTE - > This solution contains steps, which may block transaction on the table during this activity and indexes might become unusable. For production please take necessary action accordingly.
From the error , it seems PGIDX is not ASSM. Lets check the status.
select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name='PGIDX';
TABLESPACE_NAME SEGMENT
--------------- -----------
PGIDX MANUAL
So first problem is the tablespace is not ASSM,And it is not possible to convert it to ASSM.
So what we can do is find the objects for which we are facing the error.
In our case we are getting error for BLOB. So we searched for the LOB segments in this tablespace.
1* select owner,table_name,column_name,segment_name from dba_lobs where tablespace_name='PGIDX'
SQL> /
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
------------ ---------------------------------- --------------- ---------------------------------------------
PGPLI GST_PREVVE_CLOCK CILEDATA GST_PREVVE_CLOCK_LOB_SEG
PGPLI GST_PREVVE_VERSIONS CILEDATA GST_PREVVE_VERSIONS_LOB_SEG
PGPLI GST_DEQ_FENTEXTS BONTEXT GST_DEQ_FENTEXTS_LOB_SEG
So we have planned to move these objects to a new tablespace( Where ASSM is enabled).
create tablespace PGLOB datafile '/u01/data/plob.dbf' size 5g autoextend on;
Tablespace created.
select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name='PGLOB';
TABLESPACE_NAME SEGMENT
--------------- -----------
PGLOB AUTO
--- Move the LOB segments to a new tablespace
SQL> alter table PGPLI.GST_PREVVE_CLOCK move lob ( CILEDATA) store as GST_PREVVE_CLOCK_LOB_SEG ( tablespace PGLOB);
Table altered.
SQL> alter table PGPLI.GST_PREVVE_VERSIONS move lob ( CILEDATA) store as GST_PREVVE_VERSIONS_LOB_SEG ( tablespace PGLOB);
Table altered.
SQL> alter table PGPLI.GST_DEQ_FENTEXTS move lob ( BONTEXT) store as GST_DEQ_FENTEXTS_LOB_SEG ( tablespace PGLOB);
Table altered.
Now if any indexes on these tablespace are unusable , then rebuild them.
select index_name,status,table_name from dba_indexes where table_name in ('GST_PREVVE_CLOCK','GST_PREVVE_VERSIONS','GST_DEQ_FENTEXTS') and status='UNUSABLE';
-- IF any index reports unusable. then rebuild them using
alter index pgpli.pg_idx rebuild
Now application team ran the upgrade script and this error was not reported.
ORA- TROUBLESHOOTING