Dropping tablespace with LOB SEGMENT and INDEX
841124Feb 19 2011 — edited Feb 19 2011I 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