Need to drop a tablespace EXAMPLE

[jayjaybigs]

I wanted to drop the tablespace EXAMPLE and I got this error - ORA-23515: materialized views and/or their indices exist in the tablespace

Therefore I used the statement below to find the existing objects in the tablespace;
select owner, object_name, from dba_segments where tablespace_name = 'EXAMPLE'

However, this gave me so many objects that I thought it would not be wise to delete all these objects.

Please advise.

 

[SantaMufasa]

JayJay,

First of all, could you please shed some light on why you want to "DROP TABLESPACE..."?

Another issue is, who are the owners of objects in the "EXAMPLE" tablespace? If the owners are all unneeded/extraneouse users, meaning that you don't care about the objects that they own (materialized views, indices, et cetera) then you can do a:

DROP TABLESPACE <name> INCLUDING CONTENTS;

...which should blow away the tablespace and all of its remaining objects.

Remember, however, that once you do successfully drop the tablespace, the files at the operating system level still exist and you will need to erase/rm those files manually.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jayjaybigs]

I wanted to drop EXMAPLE tablespace because it only holds example datafiles which we don't need. This EXAMPLE was created by the Oracle database configuration Assistance.

I have used the DROP TABLESPACE EXAMPLE INCLUDING CONTENTS AND DATAFILES.

I got the 'ORA-23515: materialized views and/or their indices exist in the tablespace' after that.

 

[SantaMufasa]

(<rant>First, all the "crap" that results from the Oracle database configuration Assistant is precisely why I run my own db creation scripts. The extraneous overhead the gets into an Oracle-created database is a joke.</rant>)

If the DROP TABLESPACE...INCLUDING CONTENTS command doesn't work for you (at this point), then DROP the stumbling-block objects first. Are you confident writing a SQL query that will compose the "DROP <object>" statements for you?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jayjaybigs]

I hoping to drop the objects withing the tablespace first.

However when I go to drop the user with cascade option to drop the objects as well, I ws getting the following errors.

ORA-03113: end-of-file on communication channel

DROP USER "QS" CASCADE

ORA-01001: invalid cursor
ORA-00600: internal error code, arguments: [qmxiUnpPacked2], [121], [], [], [], [], [], []

DROP USER "HR" CASCADE

 

[SantaMufasa]

I despise ORA-600 errors, as well.

If I were at your keyboard, I would next try to drop the objects that "QS" owns, then attempt to drop the user once the schema is empty. If the ORA-600 error persists, then it's time to log a service request on Metalink. The techs there have a special script (available only to them) that interprets the arguments to the ORA-600 error.

Let us know of your progress.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

Your symptoms are the same as those discussed in Metalink note 339070.1. Depending on which DBCA options you use, you may get an Oracle catalog which is still version 9.2.0.1. The fix is to run the catpatch.sql script to upgrade the catalog to your current patch set level.