Skip to Content
avatar image
Former Member

"orphan" lobsegment

Hi all.

After completion of the reorganization of the tablespace was one lobsegment.
I can not drop or move it. I can not drop the tablespace also (ORA-22868: table with LOBs contains segments in different tablespaces).
Oracle 10.2.0.2.

SQL> select SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name = 'PSAPI01';

SEGMENT_NAME                         SEGMENT_TYPE
----------------------------------------------
SYS_IL0000275664C00016$$       LOBINDEX
SYS_LOB0000275664C00016$$      LOBSEGMENT

SQL> select OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME LIKE '%275664C%' or SUBOBJECT_NAME LIKE '%275664C%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------------------------------------------------------------
SYS_LOB0000275664C00016$$      LOB

BUT:

SQL> SELECT * FROM ALL_LOBS WHERE segment_name LIKE '%275664C%';
no rows selected

SQL> SELECT * FROM DBA_LOBS WHERE segment_name LIKE '%275664C%';
no rows selected

SQL> SELECT * FROM USER_LOBS WHERE segment_name LIKE '%275664C%';
no rows selected

SQL>  select * from RECYCLEBIN;

no rows selected

Regards.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 23, 2015 at 01:00 AM

    Hi Alexander,

    I meet the problem once. The problem is caused by unused columns. To query the exact object has the orphan log, we need to query oracle internal table, which is oracle does not want to share with us.

    However we can do this way, check oracle dicc

    DBA_UNUSED_COL_TABS

    to see which tables in database contains unused columns. If there are not many tables, then we can do the online reorg of the table, so that the problem may get solved.


    Best regards,

    James

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Stefan,


      Great, you know the sql that queries oracle internal table.

      I asks the sql from oracle once, but they didn't give me, so I think for normal user, we cannot query out the exact object that owns the lob.

      Thanks for the sharing.

      Best regards,
      James