Home » RDBMS Server » Server Administration » How to query a tablespace being used by which object
icon5.gif  How to query a tablespace being used by which object [message #589460] Sat, 06 July 2013 11:54 Go to next message
setsuna
Messages: 32
Registered: November 2008
Member
Hi all,

My Oracle db have this tablespace named INDX which is 80% full.
I tried to query on the table, user using it but there is no one using this tablespace

May I know if there is a query that i can used to find out which dba_object is consuming the 80% of this tablespace

Thanks alots ^^

Setsuna
Re: How to query a tablespace being used by which object [message #589461 is a reply to message #589460] Sat, 06 July 2013 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

SELECT segment_name, 
       SUM(bytes) 
FROM   dba_extents 
WHERE  tablespace_name = 'INDX' 
GROUP  BY segment_name 
ORDER  BY 2; 

[Updated on: Sat, 06 July 2013 12:02]

Report message to a moderator

Re: How to query a tablespace being used by which object [message #589466 is a reply to message #589461] Sat, 06 July 2013 22:29 Go to previous messageGo to next message
setsuna
Messages: 32
Registered: November 2008
Member
Hi Black swan,
sorry about that, perhaps i did not phrase my question in detail.

Actually, I have this DB which is created by my predecessor long time ago.
Currently, I am doing database cloning and I saw this INDX tablespace.
However, i tried query user, indexes and dba_extends and there is no information associated to what objects are using it.
I couldn't import it from my dump file so thus I am searching for ways to verify the objects using it, before making a decision as to whether to leave it out from my current cloning.

My bad for the misunderstanding and hope to hear from u soon Smile

Thanks again for your kind assistance Black Swan. I truly appreciate it ^^

Sincerely,
Setsuna
Re: How to query a tablespace being used by which object [message #589467 is a reply to message #589466] Sat, 06 July 2013 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

with regard to export & import, you never specify tablespace name directly.
If there are no objects residing in INDX tablespace during export, the it does not need to exist during import.
If INDX tablespace does not exist & objects really did reside in it, then error get thrown during import.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Re: How to query a tablespace being used by which object [message #590946 is a reply to message #589467] Wed, 24 July 2013 05:32 Go to previous messageGo to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
dba_objects view generally used to see valid objects present and sometimes used to find how many invalid objects present in the database.,
you need to check with dba_segments for what are all sizes of each corresponding objects occupied in the database..,

Re: How to query a tablespace being used by which object [message #592381 is a reply to message #590946] Wed, 07 August 2013 05:47 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Part 1) What this has to do with the question?
Part 2) Already posted with more details

One more useless posts from you.
Choose open topics and give answers that are related to the question and has not already be given.

Regards
Michel
Previous Topic: Oracle Server Standard Edition
Next Topic: ASM Isnstaller Error
Goto Forum:
  


Current Time: Thu Mar 28 11:34:19 CDT 2024