Tablespace contains 2 object(s) - can not drop it

[former_member196171]

Hi ,

I am trying to drop a empty tablespace after Enhancement Pack upgrade , but brspace ends up with the following warning or error:

Tablespace PSAPSR3702 contains 2 object(s) - option 'force' can be used to drop it

before running brspace I have also checked with following sql script  and all showed that the tablespace PSAPSR3702, there is no data

select count (*) from dba_segments where tablespace_name='PSAPSR3702'

select tablespace_name, segment_type, count(segment_name) from dba_segments group by tablespace_name, segment_type;

select USERNAME, DEFAULT_TABLESPACE from dba_users where DEFAULT_TABLESPACE='PSAPSR3702';

Steps followed with screenshots are attached, I donot want to use force option at this point, can anyone advise me how can I check the object and delete / move to other tablespace?

Please help

Thanks

Al Mamun

error with droping a tablespace.txt.zip

[Former Member · Accepted solution]

Hi Al Mamun,

Check the USER_SEGMENTS tables, below;

SQL> select count (*) from user_segments where tablespace_name='PSAPSR3702';

Best regards,

Orkun Gedik

[former_member196171 · Accepted solution]

Hi Orkun, Thanks for your prompt reply , I have just checked user_segments table as below with 0 count :

SQL> select count (*) from user_segments where tablespace_name='PSAPSR3702';

  COUNT(*)
----------
         0

SQL>

[Former Member · Accepted solution]

Could you check the objects on DB02 -> Space -> Segments -> Detailed Analysis, executing by the 'PSAPSR3702' tablespace criteria

[former_member196171 · Accepted solution]

Hi Orkun,

Thanks for your reply , I  ran your DB02 but DB stat isnot uptodate for few days , I will be running  that this evening , but I found one table and it's index in the PSAPSR3702 by the following SQL,

SQL> select table_name from dba_tables where tablespace_name = 'PSAPSR3702';

TABLE_NAME

------------------------------

SEGSYB

SQL> select index_name from dba_indexes where tablespace_name = 'PSAPSR3702';

INDEX_NAME

------------------------------

SEGSYB~0

SQL>

And I have just moved this table and index to PSAPSR3702X , now the tablespace,PSAPSR3702  should be empty, after stat generation and offline back up , I will try to drop the tablespace again, I will update you once it is done

Thanks for all the clues

Al Mamun

[Former Member · Accepted solution]

You did right, here is a bit of background information why you didn't see the objects.

The problem here was, that you obviously have Oracle 11g with deferred segment creation active. Thus you should never rely just on dba_segments / user_segments.

Also do not use old brtools, their tsdrop function will drop those tablespaces just because of that. The only good thing is that only empty tables can exist without segment. So you would still be able to recreate those tables from SE11.

This is the select the newer brspace do:

select object_name from dba_objects where object_id in (
 SELECT OBJECT_ID FROM SYS.SYS_OBJECTS WHERE TS_NUMBER = (
 SELECT TS# FROM V$TABLESPACE WHERE NAME = ' PSAPSR3702' ) );

You will not see these objects in DB02 as only segments are beeing shown.

Cheers Michael

[achmad_dimyati5 · Accepted solution]

Hi Al Mamum,

I had exactly the same issue after twice implementation of EHP5. First was to upgrade from EHP4 (got PSAPSR3702) and second was to install new technical usage PS (got PSAPSR3702X).

The reason is the table SEGSYB has been delivered since EHP5 but has missing proper entry for phase EU_SWITCH. It should be an entry in table PUTTBX for that table.

Because of that when EHPI has switched from tablespace PSAPSR3702 to PSAPSR3702X, this table was left behind. SAP will provided an update to solve this issue.

One thing for your information, if you use EHPInstaller, you will end up get another tablespace. (PSAPSR3702 --> PSAPSR3702X). But if you use SUM and choose Single System, it will be no new tablespace, SUM will ask to enhance the current tablespace (PSAPSR3702). 

EHPInstaller shouldn't be used anymore, SUM is the tool.

Regards

Dimyati

[premsukh_bishnoi]

Hi,

Please do reorg of tablespace PSAPSR3702 via BRTOOLS.

Once reorg is completed you are able to drop it.

Regards, Premsukh

[Former Member]

Hello Premsukh,

I tried your option to reorganize via brtools.

This doesn 't work at our site we keep getting the error:

Tablespace PSAPSR3702 contains 2 object(s) - option 'force' can be used to drop it.

Kind regards,

Leo

[former_member196171]

Hi Leo ,

It looks like table space may  not be emply yet , you need to move the table(s) to other tablespace , then you can delete the emply tablespace.

Here is the command to move

for Example Moving table, SEGSYB to new tablespace PSAPSR3702X from PSAPSR3702.

You need to change SID with your SID name

brspace -p initSID.sap -s 20 -l E -f tbreorg -a reorg -mode online -s PSAPSR3702 -o SAPSR3 -t SEGSYB -n PSAPSR3702X