Oracle Delete tablespace report ORA01548

Tag: Global causes buffers offline table without star err variable

The Undo table space is set to automatically grow, resulting in the replacement of several undo table spaces, to delete the original undo table space to make room

But when you delete the error,

Sql> drop tablespace undotbs1 including contents and datafiles;
Drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
Ora-01548:active rollback segment ' _syssmu1_3780397527$ ' found, terminate
Dropping tablespace

Error reason: Deleting segment in Undo tablespace requires a reply workaround: Delete rollback segment Information
This is due to the fact that the distributed transaction is not committed, look at the table space segment

Sql> Select Segment_id,segment_name,status,tablespace_name
From Dba_rollback_segs where status is not in (' ONLINE ', ' OFFLINE '); 2

segment_id segment_name STATUS Tablespace_name
---------- ------------------------------ ---------------- ------------------------------
Ten _syssmu10_3550978943$ partly AVAILABLE UNDOTBS1
9 _syssmu9_1424341975$ partly AVAILABLE UNDOTBS1
8 _syssmu8_2012382730$ partly AVAILABLE UNDOTBS1
7 _syssmu7_3286610060$ partly AVAILABLE UNDOTBS1
6 _syssmu6_2443381498$ partly AVAILABLE UNDOTBS1
5 _syssmu5_1527469038$ partly AVAILABLE UNDOTBS1
4 _syssmu4_1152005954$ partly AVAILABLE UNDOTBS1
3 _syssmu3_2097677531$ partly AVAILABLE UNDOTBS1
2 _syssmu2_2232571081$ partly AVAILABLE UNDOTBS1
1 _syssmu1_3780397527$ partly AVAILABLE UNDOTBS1
_syssmu22_888340975$ partly AVAILABLE UNDOTBS2

segment_id segment_name STATUS Tablespace_name
---------- ------------------------------ ---------------- ------------------------------
_syssmu21_2646097468$ partly AVAILABLE UNDOTBS2
_syssmu20_1273092953$ partly AVAILABLE UNDOTBS2
_syssmu19_2470562502$ partly AVAILABLE UNDOTBS2
_syssmu18_420064975$ partly AVAILABLE UNDOTBS2
_syssmu17_1769081034$ partly AVAILABLE UNDOTBS2
_syssmu16_872709384$ partly AVAILABLE UNDOTBS2
_syssmu15_1829973224$ partly AVAILABLE UNDOTBS2
_syssmu14_2564326661$ partly AVAILABLE UNDOTBS2
_syssmu13_307811187$ partly AVAILABLE UNDOTBS2

The status is partly available description and the transaction is not over, see the segment that needs to be recovered

Sql> select Segment_name,tablespace_name,status from Dba_rollback_segs;

Segment_name Tablespace_name STATUS
------------------------------ ------------------------------ ----------------
System System ONLINE
_syssmu12_4168347562$ UNDOTBS1 OFFLINE
_syssmu11_4050559276$ UNDOTBS1 OFFLINE
_syssmu10_3550978943$ UNDOTBS1partly AVAILABLE
_syssmu9_1424341975$ UNDOTBS1partly AVAILABLE
_syssmu8_2012382730$ UNDOTBS1partly AVAILABLE
_syssmu7_3286610060$ UNDOTBS1partly AVAILABLE
_syssmu6_2443381498$ UNDOTBS1partly AVAILABLE
_syssmu5_1527469038$ UNDOTBS1partly AVAILABLE
_syssmu4_1152005954$ UNDOTBS1partly AVAILABLE
_syssmu3_2097677531$ UNDOTBS1partly AVAILABLE

Segment_name Tablespace_name STATUS
------------------------------ ------------------------------ ----------------
_syssmu2_2232571081$ UNDOTBS1partly AVAILABLE
_syssmu1_3780397527$ UNDOTBS1partly AVAILABLE
_syssmu25_243928486$ UNDOTBS2 OFFLINE
_syssmu24_4009307888$ UNDOTBS2 OFFLINE
_syssmu23_2616232510$ UNDOTBS2 OFFLINE
_syssmu22_888340975$ UNDOTBS2partly AVAILABLE
_syssmu21_2646097468$ UNDOTBS2partly AVAILABLE
_syssmu20_1273092953$ UNDOTBS2partly AVAILABLE
_syssmu19_2470562502$ UNDOTBS2partly AVAILABLE
_syssmu18_420064975$ UNDOTBS2partly AVAILABLE
_syssmu17_1769081034$ UNDOTBS2partly AVAILABLE

Segment_name Tablespace_name STATUS
------------------------------ ------------------------------ ----------------
_syssmu16_872709384$ UNDOTBS2partly AVAILABLE
_syssmu15_1829973224$ UNDOTBS2partly AVAILABLE
_syssmu14_2564326661$ UNDOTBS2partly AVAILABLE
_syssmu13_307811187$ UNDOTBS2partly AVAILABLE
_syssmu35_65240735$ UNDOTBS3 ONLINE
_syssmu34_3144578968$ UNDOTBS3 ONLINE
_syssmu33_3204464002$ UNDOTBS3 ONLINE
_syssmu32_3560741359$ UNDOTBS3 ONLINE
_syssmu31_3558146288$ UNDOTBS3 ONLINE
_syssmu30_2956244594$ UNDOTBS3 ONLINE
_syssmu29_2102016512$ UNDOTBS3 ONLINE

Segment_name Tablespace_name STATUS
------------------------------ ------------------------------ ----------------
_syssmu28_1084061334$ UNDOTBS3 ONLINE
_syssmu27_2646077545$ UNDOTBS3 ONLINE
_syssmu26_2780995135$ UNDOTBS3 ONLINE

found that the rollback segment status in undotbs1,2 is partly AVAILABLE /need recovery at this point Rman recovery is available if an Rman backup is available

Not resolved by setting the parameter file value:

Sql> create Pfile from SPFile;

Add this parameter and put the name of the segment that you want to restore in

Corrupted_rollback_segments= (_syssmu10_3550978943$,
_syssmu9_1424341975$,
_syssmu8_2012382730$,
_syssmu7_3286610060$,
_syssmu6_2443381498$,
_syssmu5_1527469038$,
_syssmu4_1152005954$,
_syssmu3_2097677531$,
_syssmu2_2232571081$,
_syssmu1_3780397527$,
_syssmu22_888340975$,
_syssmu21_2646097468$,
_syssmu20_1273092953$,
_syssmu19_2470562502$,
_syssmu18_420064975$,
_syssmu17_1769081034$,
_syssmu16_872709384$,
_syssmu15_1829973224$,
_syssmu14_2564326661$,
_syssmu13_307811187$)

Sql> create SPFile from Pfile;

Restart the database and remove the tablespace again

Sql> Startup
ORACLE instance started.

Total System Global area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 5234493392 bytes
Database buffers 1409286144 bytes
Redo buffers 34922496 bytes
Database mounted.
Database opened.
Sql> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Sql> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

Sql>

Oracle Delete tablespace report ORA01548