Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Tablespace not empty even though I emptied it, trying to drop data file
Nigel Dams
post Jul 5 2017, 04:28 AM
Post #1


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Hi all - I've taken over management of a system that has been running for a couple of years and some of the tables have grown to be 40Gigs in size, with lots of wastage. So I archived off all but the last 60 days of live data and then did an export. Now I have a bunch of small dmp files, and I want to get rid of two out of three data files, by saying 'alter tablespace ... drop datafile ...'

So I run the following query:

CODE
select a.owner, a.segment_name, a.segment_type, b.file_name
from dba_segments a, dba_data_files b
where a.file_id = b.file_id and b.tablespace in (list of tablespaces I'm interested in) and file_name like (the ones I want to dtop)


This query shows me a bunch of tables and indexes properly distributed between 'table' and 'index' tablespaces. I drop all the tables I'm about to re-import, and run the query again. It returns 'no rows' so I think I'm good to go.

I run 'alter tablespace ... drop datafile .....' but I get ORA-03262: the file is non-empty

So I run the query again and rub my eyes - not a single segment has the file_id of that file. But if I run Enterprise Manager I find a world of stuff, user views, indexes, enormous things named 'BIN$.......'

My question is, how do I find all those things and move or delete them so that I can drop the soon-to-be redundant datafile?
Go to the top of the page
 
+Quote Post
burleson
post Jul 5 2017, 06:47 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13



Hi Nigel,

Good question!

The bin$ objects are from the recycle bin but I do not understand why they are on a user tablespace.

Please run the query here:


http://www.dba-oracle.com/t_oracle_recycle_bin.htm

It should drop after you empty the recyclebin!

Here is a good tablespace free space query:

http://www.dba-oracle.com/t_tablespace_script.htm

Let us know your solution.

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jul 6 2017, 07:52 AM
Post #3


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Hi Donald, and thanks for your reply.

After purging recyclebin, the BIN$ objects have gone, but there are still loads and loads of others, with object type in 'USER','VIEW','TRIGGER'

The weirdest thing is that (in Enterprise Manager) the same user shows up in the listings for EVERY datafile.

And not a single one of these things can be found by doing a query on dba_extents.

Stumped (english version of 'caught behind the 8 ball' blink.gif )
Go to the top of the page
 
+Quote Post
burleson
post Jul 6 2017, 10:12 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13



Hi Nigel,

You need to look using dba_segments.

Try this query (just remove the line that says "see code depot").

http://www.dba-oracle.com/t_display_tablespace_contents.htm

It's weird, as trigger and view code is kept in the data dictionary!

Let me ruminate on this and get back to you.


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Jul 6 2017, 10:20 AM
Post #5


Advanced Member
***

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13



Hi Nigel,

Aha! The recyclebin did not drop all contents!

See the MOSC note referenced here:

https://asktom.oracle.com/pls/apex/f?p=100:...265800346481025

This should allow you to drop the tablespace.

Let us know if it works!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jul 13 2017, 08:35 AM
Post #6


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Hi again Donald, and thanks for all your help.

Unfortunately nothing works - I ran all those checks and queries and recyclebin purges, and whatever query I run shows that the tablespace I'm talking about is just empty. Nothing there.
But if I try to resize the datafile, it keeps saying there's data getting in the way, and if I use Enterprise Manager it shows me more than a thousand objects ..... users, views, indexes, tons of stuff.

Weird.
Go to the top of the page
 
+Quote Post
burleson
post Jul 13 2017, 08:54 AM
Post #7


Advanced Member
***

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13



Hi Nigel,

Check for file corruption:

http://www.dba-oracle.com/t_repair_corrupt_blocks.htm

And try this tablespace map script:

http://www.dba-oracle.com/class_tuning/tsmap.htm


Can you open a SR on MOSC?

Please advise! Thanks!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jul 13 2017, 09:20 AM
Post #8


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Fixed it - though I'm not sure why.

I just put the tablespace offline, then back online and then it allowed me to alter the size of the file, where moments before it had been saying 'file contains used data .... '

Enterprise manager still shows the same list of things it thinks are in there, but the file is now gratifyingly small, so I'm gonna have a beer.

Thanks again for all your help. If you have any Gandalf insight on what I've just told you, I'd be glad to hear it.

Go to the top of the page
 
+Quote Post
burleson
post Jul 13 2017, 11:22 AM
Post #9


Advanced Member
***

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13



Hi Nigel,

Have a beer for me too!

I'm still not happy with this since the tablespace still exists, right?

I strongly suspect a bug, but you can force the drop of a tablespace if you are positive that it is empty.

Also, you can nuke the tablespace data files from the OS, and then force the TS to drop.

I don't want to publish any dictionary hacks, but sometimes they are needed!

Can you check for this technique on MOSC?

Just google for force tablespace drop. You really want to clean up any false entries in the data dictionary!

Enjoy your beer! Are you in the UK?


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jul 18 2017, 05:12 AM
Post #10


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



I am in the UK - but I still like cold beer smile.gif

Thanks for the suggestions, I'm still trying to figure out what's going on, as I don't like not knowing
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th October 2017 - 04:17 PM