Shulou(Shulou.com)06/01 Report--

This article mainly introduces the database delete tablespace ORA-22868 error how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

When testing the CONVERT DATABASE migration command, the tablespace of one of the OFFLINE was not migrated because the contents of that tablespace could not be recovered.

After the migration is complete, it is found that the tablespace and data file information is still in the data dictionary, so the error is caused by an attempt to clear the information.

SQL > select name from v$tablespace

NAME

-

SYSTEM

UNDOTBS1

SYSAUX

EXAMPLE

YANGTK

MGMT_TABLESPACE

TEST

MGMT_ECM_DEPOT_TS

USERS

TEMP

10 rows selected.

SQL > select name from v$datafile

NAME

/ data/oradata/ytktran/SYSTEM01.DBF

/ data/oradata/ytktran/UNDOTBS01.DBF

/ data/oradata/ytktran/SYSAUX01.DBF

/ opt/ora10g/product/10.2.0/db_1/dbs/MISSING00004

/ data/oradata/ytktran/EXAMPLE01.DBF

/ data/oradata/ytktran/YANGTK01.DBF

/ data/oradata/ytktran/MGMT.DBF

/ data/oradata/ytktran/YANGTK02.DBF

/ data/oradata/ytktran/TEST01.DBF

/ data/oradata/ytktran/MGMT_ECM_DEPOT1.DBF

10 rows selected.

SQL > select file_name

2 from dba_data_files

3 where tablespace_name = 'USERS'

FILE_NAME

/ opt/ora10g/product/10.2.0/db_1/dbs/MISSING00004

Obviously, the USERS tablespace is the tablespace to be deleted:

SQL > drop tablespace users

Drop tablespace users

*

ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL > drop tablespace users including contents

Drop tablespace users including contents

*

ERROR at line 1:

ORA-22868: table with LOBs contains segments in different tablespaces

Because the tablespace is not empty, the tablespace needs to be deleted by INCLUDING CONTENTS, but an ORA-22868 error occurs.

The error message is clear that the USERS table space contains the LOB table, while the LOB objects in the LOB table are stored outside the USERS table space.

Just find these objects and delete them to solve this problem:

SQL > col owner format A15

SQL > col tablespace_name format A15

SQL > col column_name format A30

SQL > select a.owner, a.table_name, b.column_name, b.tablespace_name

2 from dba_tables a, dba_lobs b

3 where a.owner = b.owner

4 and a.table_name = b.table_name

5 and a.tablespace_name = 'USERS'

6 and b.tablespace_name! = 'USERS'

No rows selected

SQL > select a.owner, a.table_name, b.column_name, b.tablespace_name

2 from dba_tables a, dba_lobs b

3 where a.owner = b.owner

4 and a.table_name = b.table_name

5 and a.tablespace_name = 'USERS'

No rows selected

Oddly, there is no match for LOB objects where the table is in the USERS table space and the LOB object is outside the USERS table space. In fact, all tables that contain LOB are not in the USERS table space.

So why did Oracle make the above error:

SQL > select count (*)

2 from dba_lobs

3 where tablespace_name = 'USERS'

COUNT (*)

-

ten

SQL > select a.owner, a.table_name, b.column_name, b.tablespace_name

2 from dba_tables a, dba_lobs b

3 where a.owner = b.owner

4 and a.table_name = b.table_name

5 and b.tablespace_name = 'USERS'

No rows selected

SQL > select owner, table_name, column_name, tablespace_name

2 from dba_lobs

3 where tablespace_name = 'USERS'

OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME

-

OE LINEITEM_TABLE "PART". "SYS_XDBPD$" USERS

OE LINEITEM_TABLE SYS_XDBPD$ USERS

OE ACTION_TABLE SYS_XDBPD$ USERS

OE PURCHASEORDER "XMLDATA". "LINEITEMS". "SYS_XDBPD$" USERS

OE PURCHASEORDER "XMLDATA". "SHIPPING_INSTRUCTIONS". "SYS_XDBPD$" USERS

OE PURCHASEORDER "XMLDATA". "REJECTION". "SYS_XDBPD$" USERS

OE PURCHASEORDER "XMLDATA". "ACTIONS". "SYS_XDBPD$" USERS

OE PURCHASEORDER "XMLDATA". "SYS_XDBPD$" USERS

OE PURCHASEORDER "XMLEXTRA". "EXTRADATA" USERS

OE PURCHASEORDER "XMLEXTRA". "NAMESPACES" USERS

10 rows selected.

The query found that the USERS tablespace contains 10 LOB objects. However, the query associated with DBA_TABLES found that no records could be found.

SQL > SELECT OWNER, OBJECT_NAME, OBJECT_TYPE

2 FROM DBA_OBJECTS

3 WHERE OBJECT_NAME = 'ACTION_TABLE'

OWNER OBJECT_NAME OBJECT_TYPE

-

OE ACTION_TABLE TABLE

SQL > SELECT OWNER, TABLE_NAME, TABLESPACE_NAME

2 FROM DBA_TABLES

3 WHERE TABLE_NAME = 'ACTION_TABLE'

No rows selected

You can see this object from the DBA_OBJECTS view, and the object type is TABLE, but the table information is not found in DBA_TABLES. Is it possible that the data dictionary is inconsistent during the execution of the CONVERT DATABASE command.

Query the DBA_TABLES view information:

SQL > SET LONG 10000

SQL > SELECT TEXT

2 FROM DBA_VIEWS

3 WHERE VIEW_NAME = 'DBA_TABLES'

TEXT

Select u.name, o.name, decode (bitand (t.propertyline 2151678048), 0, ts.name, null)

Decode (bitand (t.property, 1024), 0, null, co.name)

Decode ((bitand (t.property, 512) + bitand (t.flags, 536870912))

0, null, co.name)

Decode (bitand (t.trigflag, 1073741824), 1073741824, 'UNUSABLE',' VALID')

Decode (bitand (t.property, 32-64), 0, mod (t.pctfreechains, 100), 64, 0, null)

Decode (bitand (ts.flags, 32), 32, to_number (NULL)

Decode (bitand (t.property, 32 / 64), 0, t.pctusedlegs, 64, 0, null))

Decode (bitand (t.property, 32), 0, t.initrans, null)

Decode (bitand (t.property, 32), 0, t.maxtrans, null)

S.iniexts * ts.blocksize

Decode (bitand (ts.flags, 3), 1, to_number (NULL)

S.extsize * ts.blocksize)

S.minexts, s.maxexts

Decode (bitand (ts.flags, 3), 1, to_number (NULL)

S.extpct)

Decode (bitand (ts.flags, 32), 32, to_number (NULL)

Decode (bitand (o.flags, 2), 2,1, decode (s.lists, 0,1, s.lists)

Decode (bitand (ts.flags, 32), 32, to_number (NULL)

Decode (bitand (o.flags, 2), 2,1, decode (s.groups, 0,1, s.groups)

Decode (bitand (t.property, 32-64), 0

Decode (bitand (t.flags, 32), 0, 'YES',' NO'), null)

Decode (bitand (t.girls, 1), 0,'y, 1,'n,'?')

T.rowcnt

Decode (bitand (t.property, 64), 0, t.blkcnt, null)

Decode (bitand (t.property, 64), 0, t.empcnt, null)

T.avgspc, t.chncnt, t.avgrln, t.avgspc_flb

Decode (bitand (t.property, 64), 0, t.flbcnt, null)

Lpad (decode (t.degree, 32767, 'DEFAULT', nvl (t.degree1)), 10)

Lpad (decode (t.instances, 32767, 'DEFAULT', nvl (t.instances1)), 10)

Lpad (decode (bitand (t.flags, 8), 8, 'Yee,' N'), 5)

Decode (bitand (t.flags, 6), 0, 'ENABLED',' DISABLED')

T.samplesize, t.analyzetime

Decode (bitand (t.property, 32), 32, 'YES',' NO')

Decode (bitand (t.property, 64), 64, 'IOT'

Decode (bitand (t.property, 512,512, 'IOT_OVERFLOW')

Decode (bitand (t.flags, 536870912), 536870912, 'IOT_MAPPING', null

)))

Decode (bitand (o.flags, 2), 0,'N', 2,'Y','N')

Decode (bitand (o.flags, 16), 0,'N', 16,'Y','N')

Decode (bitand (t.property, 8192), 8192, 'YES'

Decode (bitand (t.property, 1), 0, 'NO',' YES'))

Decode (bitand (o.flags, 2), 2, 'DEFAULT'

Decode (s.cachehint, 0, 'DEFAULT', 1,' KEEP', 2, 'RECYCLE', NULL))

Decode (bitand (t.flags, 131072), 131072, 'ENABLED',' DISABLED')

Decode (bitand (t.flags, 512), 0, 'NO',' YES')

Decode (bitand (t.flags, 256), 0, 'NO',' YES')

Decode (bitand (o.flags, 2), 0, NULL

Decode (bitand (t.property, 8388608), 8388608

'SYS$SESSION',' SYS$TRANSACTION'))

Decode (bitand (t.flags, 1024), 1024, 'ENABLED',' DISABLED')

Decode (bitand (o.flags, 2), 2, 'NO'

Decode (bitand (t.property, 2147483648), 2147483648, 'NO'

Decode (ksppcv.ksppstvl, 'TRUE',' YES', 'NO')

Decode (bitand (t.property, 1024), 0, null, cu.name)

Decode (bitand (t.flags, 8388608), 8388608, 'ENABLED',' DISABLED')

Decode (bitand (t.property, 32), 32, null

Decode (bitand (s.spare1, 2048), 2048, 'ENABLED',' DISABLED'))

Decode (bitand (o.flags, 128,128, 'YES',' NO')

From sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o

Sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi

Where o.owner# = u.user#

And o.obj# = t.obj#

And bitand (t.property, 1) = 0

And bitand (o.flags, 128) = 0

And t.bobj# = co.obj# (+)

And t.ts# = ts.ts#

And t.file# = s.file# (+)

And t.block# = s.block# (+)

And t.ts# = s.ts# (+)

And t.dataobj# = cx.obj# (+)

And cx.owner# = cu.user# (+)

And ksppi.indx = ksppcv.indx

And ksppi.ksppinm ='_ dml_monitoring_enabled'

There are not too many restrictions in the DBA_TABLES view, so the reason why there is no record in the DBA_TABLES is mostly on the connection.

Check the OBJ$ and TAB$ tables:

SQL > SELECT OBJECT_ID

2 FROM DBA_OBJECTS

3 WHERE OBJECT_NAME = 'ACTION_TABLE'

OBJECT_ID

-

52449

SQL > SELECT OBJ#, DATAOBJ#, NAME FROM OBJ$ WHERE OBJ# = 52449

OBJ# DATAOBJ# NAME

52449 ACTION_TABLE

SQL > SELECT OBJ#, DATAOBJ#, TS#, BOBJ# FROM TAB$ WHERE OBJ# = 52449

OBJ# DATAOBJ# TS# BOBJ#

--

52449 0 52450

The DATAOBJ# of the current object is empty, which means there is no corresponding storage space for the object. You can see that the BOBJ# of this object is 52450. Query the DBA_OBJECTS view:

SQL > SELECT OWNER, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE

2 FROM DBA_OBJECTS

3 WHERE OBJECT_ID IN (52449, 52450)

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

OE ACTION_TABLE 52449 TABLE

OE SYS_IOT_OVER_52449 52450 52450 TABLE

Obviously this ACTION_TABLE is an index organization table. Query the index information corresponding to ACTION_TABLE:

SQL > SELECT OWNER, INDEX_NAME, INDEX_TYPE

2 FROM DBA_INDEXES

3 WHERE TABLE_NAME = 'ACTION_TABLE'

OWNER INDEX_NAME INDEX_TYPE

-

OE ACTION_TABLE_DATA IOT-TOP

OE SYS_IL0000052449C00004 $$LOB

It seems that ACTION_TABLE is not only an index organization table, but also a LOB object. And this may be the reason for the ORA-22868 error encountered earlier.

But there is still a doubt that even if the index organizes the table, it should be available for query in the DBA_TABLES view.

Thank you for reading this article carefully. I hope the article "what to do if ORA-22868 errors occur in deleting tablespaces in the database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.