Home » RDBMS Server » Server Administration » How to know this table belongs to which tablespace? (oracle10gR2 Solaris5.9)
How to know this table belongs to which tablespace? [message #307833] Thu, 20 March 2008 02:07 Go to next message
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?
Re: How to know this table belongs to which tablespace? [message #307838 is a reply to message #307833] Thu, 20 March 2008 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
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 Go to previous messageGo to next message
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';
Re: How to know this table belongs to which tablespace? [message #309317 is a reply to message #309242] Thu, 27 March 2008 02:03 Go to previous message
Michel Cadot
Messages: 64107
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
Previous Topic: Schedular not working
Next Topic: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK"
Goto Forum:
  


Current Time: Mon Dec 05 04:47:04 CST 2016

Total time taken to generate the page: 0.18974 seconds