OraFAQ Forum: Server Administration » How to query a tablespace being used by which object

Home » RDBMS Server » Server Administration » How to query a tablespace being used by which object

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to query a tablespace being used by which object [message #589460]

Sat, 06 July 2013 11:54

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

Report message to a moderator

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

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

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

Report message to a moderator

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

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/

Report message to a moderator

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

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..,

Report message to a moderator

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

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Oracle Server Standard Edition
Next Topic:	ASM Isnstaller Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 22 12:53:16 CST 2026