"orphan" lobsegment

[ncrozby]

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.

[JamesZ · Accepted solution]

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

[stefan_koehler · Accepted solution]

Hi James,

the following should be sufficient as well.

SQL> alter table <table name> drop unused columns;

Regards

Stefan

[ncrozby · Accepted solution]

Hi Stefan.

I found table wich use this LOBSEGMENT and I run

SQL> alter table SAPI01.ZBD_HRF_CELLS drop unused columns;

alter table SAPI01.ZBD_HRF_CELLS drop unused columns

*

ERROR at line 1:

ORA-12996: cannot drop system-generated virtual column

Now I think about how drop column or move segment.

[JamesZ · Accepted solution]

Hi Stefan,

Yes, but the problem here is that we may not be able to find the exact object that has this lob.
So dropping all the tables' unused columns is good idea?

Best regards,
James

[ncrozby · Accepted solution]

Hi Lames.

Thank you.

In table dba_unused_col_tabs (select * from dba_unused_col_tabs;) I saw a lot of tables, I check them and found needed table. After I run

  alter table mytab drop unused columns;

It don't help I sow error ORA-12996. Finally I made online reorganization this table use DBMS_REDEFINITION.

Best regards.

Alexander Ignatenko

[stefan_koehler · Accepted solution]

Hi James,

> Yes, but the problem here is that we may not be able to find the exact object that has this lob. So dropping all the tables' unused columns is good idea?

Why shouldn't we find the corresponding table with orphaned lobs? Just use the following query.

SQL> select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name),
lo.name, io.name, l.chunk * ts.blocksize, l.pctversion$, decode(bitand(l.flags, 27), 1, 'NO', 2,
'NO', 8, 'CACHEREADS',16, 'CACHEREADS', 'YES'), decode(bitand(l.flags, 18), 2,
'NO', 16, 'NO', 'YES'), decode(bitand(l.property, 2), 2, 'YES', 'NO')
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
where o.owner# = u.user# and o.obj# = c.obj# and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000275664C00016$$';

Regards

Stefan

[JamesZ · Accepted solution]

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