cancel
Showing results for 
Search instead for 
Did you mean: 

Tablespace drop activity after upgrade from ECC5 to ECC6 EHP7 with oracle 11.2.0.4

Former Member
0 Kudos

Hi Team

While doing the tablespace drop activity after upgrade from ECC5 to ECC6 EHP7 with oracle 11.2.0.4 and AIX 7.1 version in our development system we have encountered a problem where the tablespace PSAPEL640D is not getting dropped as it has some objects with type LOB.

There are no tables in the tablespace PSAPEL640D.

When checked in DB02, we see that these are LOBSEGMENT residing in the tablespace.

Owner Name Type

1 SAPR3 SYS_LOB0000006513C00013$$    LOB

2 SAPR3 SYS_LOB0000006513C00014$$    LOB

3 SAPR3 SYS_LOB0000017900C00004$$    LOB

4 SAPR3 SYS_LOB0000018021C00005$$    LOB

Because of this we are not able to drop the tablespace. We have also tried to reorganize the tablespace PSAP640D but we are not able to which we suspect since there are no tables in this tablespace.

Please help to get a solution for these 4 objects and what can be done so we can drop the tablespace.

Thanks,

Avinash

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Avinash,

Firstly you need to find the table that is associated with the LOB segment.

select owner, table_name from dba_lobs where segment_name = '<your lob segment name>' and then we can see the next action such as a reorg.

KR,

Amerjit

Former Member
0 Kudos

Hi Amerjit,

We have checked that LOB Segments are not pointing to any table and SQL level the tablespace PSAP640D is also empty.

SQL> select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME from dba_lobs where segment_name = 'SYS_LOB0000006513C00013$$';
no rows selected

SQL> select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME from dba_lobs where segment_name = 'SYS_LOB0000006513C00014$$';
no rows selected

SQL> select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME from dba_lobs where segment_name = 'SYS_LOB0000017900C00004$$';
no rows selected

SQL> select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME from dba_lobs where segment_name = 'SYS_LOB0000018021C00005$$';
no rows selected

SQL> select table_name from dba_tables where tablespace_name='PSAPEL640D';

no rows selected

-----------------------------------------------------------------

SQL> select owner, object_name from dba_objects where object_name = 'SYS_LOB0000006513C00013$$';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SAPR3
SYS_LOB0000006513C00013$$


SQL> select owner, object_name from dba_objects where object_name = 'SYS_LOB0000006513C00014$$';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SAPR3
SYS_LOB0000006513C00014$$

Please let me know if you need any additional details...thank you very much

Thanks,

Avinash

Former Member
0 Kudos

Hello Avinash,

Indeed it does look like there are no tables in the tablespace but the LOBSEGMENT of a table(s) have remained (potentially orphaned).

1. Now even though you should have recyclebin turned off, can you cross check anyway:

select * from user_recyclebin;


2. As per note 1891692 - Got ORA-01792 error when activate the table after adding a new field could you check unused columns of a table as follows:


select * from dba_unused_col_tabs ;


From the result of the above query you will have to work back to the table. Given that the t/s is PSAPEL640D I might expect the table name to contain the text "LOAD" in the name.


Please try the two SQL statements and feedback the info.


KR,


Amerjit


Former Member
0 Kudos

Hi Amerjit,

Thanks for your reply.

please find the output of commands requested

SQL> select * from user_recyclebin;

no rows selected

SQL> select * from dba_unused_col_tabs ;

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 CACS00_CAS 8

SAPR3 CACS00_SUMRE 2

SAPR3 ZTADRBRT 8

SAPR3 ZTV_CDD_CODE_SET 1

SAPR3 ZCDT_UOM_ID 2

SAPR3 CE1AV10 2

SAPR3 ZVRI_TP_CODE_SET 1

SAPR3 CE4AU00_ACCT 18

SAPR3 ZRAT_C50_AU 2

SAPR3 AKB_STATISTICS 1

SAPR3 ZTLWHCT 5

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZISSUANCE_HDR 1

SAPR3 ZEUMM_STATUSRULE 3

SAPR3 QCMZPO_ERROR 1

SAPR3 RSBSERVERPROP 1

SAPR3 CE4A300 1

SAPR3 EKKO 1

SAPR3 MARA 8

SAPR3 VDBILL_CONTROL 1

SAPR3 ZRAT_SCHD_BAT_AU 1

SAPR3 ZRAT_TTRECORD_AU 1

SAPR3 ZRF_CYCLECNT_HDR 1

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZRAT_K30_AU 1

SAPR3 ZZGFXA 2

SAPR3 ZMKT_RCV_D_KR 3

SAPR3 ZRF_TOUR_PICK 1

SAPR3 ZEBM_COMP 1

SAPR3 ZAPMM_AL_REQUEST 1

SAPR3 /ISDFPS/PNMPO 1

SAPR3 COMM_IL_DTYPE 1

SAPR3 MSEG 2

SAPR3 WITH_ITEM 9

SAPR3 AUFK 1

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 MARC 1

SAPR3 MARD 4

SAPR3 ZTMNR04 2

SAPR3 ZTVPREMIUM 1

SAPR3 CE1A300 82

SAPR3 ZLAT_RFPKTOUR_AU 2

SAPR3 ZVRI_TP_R_USED 6

SAPR3 ZVRI_TP_USED_QTY 2

SAPR3 ZRF_AU_NZ_TMS_DT 3

SAPR3 ZVRI_OS_AUART 1

SAPR3 ZRAT_INTF_RUNER 2

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZZGFXO 6

SAPR3 ZTRBPARA1 1

SAPR3 ZTRBPARA2 4

SAPR3 ZTRECPDTL 4

SAPR3 ZTCPROD_VS_MOL 1

SAPR3 ZTVEDI_EMAIL 1

SAPR3 RSDDAGGRDIR 3

SAPR3 VBAK 3

SAPR3 VBAP 1

SAPR3 VDBILL_ITEMS 1

SAPR3 ZLCT_PICK_REC 1

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZLCT_PICK_TC_REC 2

SAPR3 ZVRI_TP_TKT_OCPY 3

SAPR3 ZWM_GRP_NEW_TO 2

SAPR3 ZVRI_SA_EXTERN 2

SAPR3 ZTFBANK_INFO1 1

SAPR3 ZTFLNINC 1

SAPR3 ZTFSVAT 5

SAPR3 ZRAT_C40_AU 1

SAPR3 ALMDRULES 2

SAPR3 ZEUMM_CTC_PLANTS 3

SAPR3 CE3AV10 2

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 LQUA 1

SAPR3 RSAPOADM 3

SAPR3 CACS00_DOCHD 8

SAPR3 CE4A300_ACCT 1

SAPR3 COMM_IL_SCPCHR 1

SAPR3 EKPO 1

SAPR3 PDBKUR 1

SAPR3 /BMC/YTR_DB2_SQ 1

SAPR3 RSCRT_DTA_DS_LG 1

SAPR3 TDIPDPLAN 13

SAPR3 STKO 1

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZCDT_MACHTYPE_ID 1

SAPR3 ZVRI_ZX_VEHMAK 1

SAPR3 ZRAT_STORES_AU 1

SAPR3 ZVRI_TP_OE_MAT 1

SAPR3 ZTFBANK_INFO 1

SAPR3 CE4AU00 15

SAPR3 ZPROVER 2

SAPR3 ZTK_TAP_TRAN 3

SAPR3 BRR_TEXT 1

SAPR3 /ISDFPS/MPO_PROT 4

SAPR3 LTAP 1

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 RSMDATASTATE 13

SAPR3 RSDDCHNGPROT 1

SAPR3 ZTAVRP 9

SAPR3 RSSBAUTHTRACE 18

SAPR3 ZDELDATE 2

SAPR3 ZLCT_LIPS_SN 3

SAPR3 ZVAT_WEBSPR02_AU 1

SAPR3 ZVRI_ADM_EXTERN 1

SAPR3 ZVRI_TP_R_D_RS 5

SAPR3 ZRF_DEVICES 1

SAPR3 ZEUMM_APAC_MAIL 2

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZAPMM_TP0_PLANTS 6

SAPR3 ZTZ_MAPPING 1

SAPR3 CE3A300 81

SAPR3 STPO 4

SAPR3 COMM_IL_DSCOPE 1

SAPR3 FICOT_AMTPOS 1

SAPR3 MKPF 2

SAPR3 RSDRHLRUBUFFER 1

SAPR3 ZEUPUR_ZBTO 1

SAPR3 CACS00_B_VAR_CAS 8

SAPR3 AFWCH_STR_SAMP 7

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 COMC_PR_SYSSTAT 1

SAPR3 FRE_SEND_OPTION 1

SAPR3 REGUH 10

SAPR3 ZTMVMI 2

SAPR3 ZTVSIBLDET 1

SAPR3 ZTVUNCON 1

SAPR3 ZTV_CAR_C80 1

SAPR3 ZCDT_TRACEHOSTID 1

SAPR3 ZCONDT_AU_NZ 1

SAPR3 CE1AU00 21

SAPR3 ZLCT_TIRE_FLOW 1

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZVRI_ECLAIM_ITEM 1

SAPR3 ZRAT_B20_AU 1

SAPR3 ZZGFXT 2

SAPR3 ZTRAC 1

SAPR3 ZTF_UNSUC_OCR_WF 1

SAPR3 ZTVINLAND 1

SAPR3 ZAPMM_CY_COUNTRY 4

SAPR3 QSCENARIOROLE 2

SAPR3 SAUNIT_NO_LEGACY 1

SAPR3 TDBILL_PAR 5

SAPR3 ZTVSIMAIN1 16

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 ZLCT_ONFK_REC 1

SAPR3 ZVRI_TP_TICKET 1

SAPR3 CE4AU00_FLAG 18

SAPR3 ZFKT_VAT_KR 1

SAPR3 ZEBM003 1

SAPR3 ZEUPM_PLANT_PRM 1

SAPR3 ZBDC_SEGPFR 1

SAPR3 ZTZCVC_UPD 6

SAPR3 CE3AU00 6

SAPR3 BRR_TEXT_C 1

SAPR3 BSIUDTA 4

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

SAPR3 QCMZEUMM_APAC_M 1

144 rows selected.

Thanks,

Avinash

Former Member
0 Kudos

Hello Avinash,

If you follow the thread shown below, you will see that

Please try it and see if the table name(s) returned correspond to a table returned from your query on dba_unused_col_tabs and then try: alter table <table name> drop unused columns;

I haven't tried it (so I can't validate it) as I'm not at a system right now.

KR,

Amerjit