Warning: session_start(): open(/tmp/sess_b4p7q30m0nu4qlf9c6lsg419n2, O_RDWR) failed: No space left on device (28) in /razi/eraazi/darbari/mercy/index.php on line 15
EraAzi | ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Managemen
 

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


Related Topics

ORA-16855: transport lag has exceeded specified threshold
ORA-32771: cannot add file to bigfile tablespace
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
ORA-01536: space quota exceeded for tablespace
TNS-12542: TNS:address already in use
ORA-32773: operation not supported for smallfile tablespace
TNS-01106: Listener using listener name has already been started
ORA-32774: more than one file was specified for bigfile tablespace
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace

You May Also Like

Hive installation with mysql database
Generate custom report from OEM cloud control
How to install OPatch in ORACLE RAC
How to flashback a Pluggable database ( PDB) in oracle 12.2
How to recreate physical standby controlfile
oraversion utility in oracle 18c - New feature
Lock account automatically with INACTIVE_ACCOUNT_TIME
Find sessions consuming lot of CPU
Tablespace monitoring shell script
CONTENT parameter in datapump

From This Website

how to Print execution time of tasks in ansible
SQLCL UTILITY IN ORACLE
DEFERRED_SEGMENT_CREATION parameter in oracle
Upgrade database using OEM 12C cloud control
Deinstall Management Agents Oracle 12c cloud control
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
COMPRESSION in datapump oracle
Change dbsnmp password for target db in oem 12c
Flashback primary database in dataguard environment
ORA-01536: space quota exceeded for tablespace