Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dropping tablespace with LOB SEGMENT and INDEX

841124Feb 19 2011 — edited Feb 19 2011
I have deleted a project in oracle but the tablespaces of that are still remaining in the Oracle.

I Have following Oracle version

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production

I tried

DROP TABLESAPCE TBLSPC_DAT_PRJ_XXXX INCLUDING CONTENTS AND DATAFILES;

But I am getting

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

On running following SQL statement

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'TBLSPC_DAT_PRJ_XXXX' and segment_type='LOBSEGMENT'or segment_type='LOBINDEX';

I am getting a list of 840 with SEGMENT_TYPE of LOBSEGMENT or LOBINDEX.

Can any one please help me in removing this tablespace. I am an end-user not a DBA or Oracle operator a complete noob. In our organization we do not have any oracle expert also. So please can you give me step by step instruction so that I can drop it or let me know the url where I can get the steps
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2011
Added on Feb 19 2011
3 comments
1,453 views