OraFAQ Forum: Server Administration » How to know this table belongs to which tablespace?

Home » RDBMS Server » Server Administration » How to know this table belongs to which tablespace? (oracle10gR2 Solaris5.9)

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

How to know this table belongs to which tablespace? [message #307833]

Thu, 20 March 2008 02:07

wxfjordan
Messages: 92
Registered: December 2006

Member

How to know this table belongs to which tablespace?

Can I use a SQL statements to query this?

Report message to a moderator

Re: How to know this table belongs to which tablespace? [message #307838 is a reply to message #307833]

Thu, 20 March 2008 02:25

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

dba_tables
dba_segments

Regards
Michel

[Updated on: Thu, 20 March 2008 02:26]

Report message to a moderator

Re: How to know this table belongs to which tablespace? [message #309242 is a reply to message #307833]

Wed, 26 March 2008 19:55

rafaelrois
Messages: 3
Registered: March 2008
Location: brazil

Junior Member

You can use the following query to know the tablespace_name which your tables are in:

select tablespace_name from dba_tables
where table_name='YOUR_TABLE';

Report message to a moderator

Re: How to know this table belongs to which tablespace? [message #309317 is a reply to message #309242]

Thu, 27 March 2008 02:03

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I already post that and this is wrong! there are many cases where table has no associated tablespace in dba_tables.

Oracle should remove this column from %tables views.
A table is a logical object.
A logical object is not physical and so has no tablespace.
A table may be associated to 0 to N segments.
A segment is a physical object that is stored in a tablespace.

So the good answer is dba_segments.

Regards
Michel

Report message to a moderator

Previous Topic:	Schedular not working
Next Topic:	ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 20:05:19 CDT 2024