I'd like to know if there's any sql command to retrieve tablespace??? 2

[babe1898]

Hi all,

I'd like to know if there's an SQL command that could retrieve the name of the tablespace of my table?

I tried the following:

desc <Name_Of_Table>

But, this command only shows the column(s) of this table.

Thank you so much in advance.


John

 

[SantaMufasa]

John,

Here is a simple query that discloses the tablespace name for one of the tables you own:

select tablespace_name
  from user_segments
 where segment_name = 'S_EMP';

TABLESPACE_NAME
---------------
DATA2

Simply replace "S_EMP" with the name of the table in which you are interested.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[babe1898]

Hi SantaMufasa,

Thank you very much! It worked like a charm. Just one thing though, is there a special character that I could use so the SQL command would query for all the tables starting with TEST?

For instance, the SQL below should query on all the tables starting with TEST*. I'm not quite sure if this ispossible in SQL. Any tips would be greatly appreciated. Thanks in advance.

select tablespace_name
from user_segments
where segment_name = 'TEST*';



Cordially,
John

 

[SantaMufasa]

Yes...the wildcard characters, "%" for 0-to-unlimited character positions and "_" for a single position are available with the "LIKE" operator:

select segment_name, tablespace_name
  from user_segments
 where segment_name LIKE 'S_E%';

SEGMENT_NAME    TABLESPACE_NAME
--------------- ---------------
S_EMP3          DATA1
STEVE_TARGET    DATA1
STEVE_SOURCE    DATA1
S_EMP           DATA2
S_EMP2          DATA1
S_EMP_ID_PK     DATA1
S_EMP_USERID_UK DATA1
S_EMP_ID_PK1    DATA1

Let us know if you have additional questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[babe1898]

Hi SantaMufasa,

A millions thanks to you...It worked! have a great day!


Cordially,
John

 

[KenCunningham]

'Cordially' but not 'starfully' John??

I want to be good, is that not enough?

 

[babe1898]

Hi KenCunningham,

What are you trying to insinuate?

Thanks,
John

 

[DeepDiverMom]

John, I believe what Ken is suggesting is that when we are grateful for the efforts or suggestions of others here on Tek-Tips, the method of expressing that gratitude is both by cordial acknowledgements such as yours and by clicking the [Thank <poster_screen_name> for this valuable post!] link, which awards that poster a purple star (which I highly recommend, as well).

Am I correct, Ken?

 

[KenCunningham]

Indeed DeepDiverMom, that was my only intention. No offence at all John, my guess is you weren't aware of this facility.

I want to be good, is that not enough?

 

[SantaMufasa]

Whoever was the benefactor of the

, Thank you!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[babe1898]

Hi KenCuningham DeepDiverMom,

Thank you so mch for clarifying it...I just didn't get what you meant. Yes, you are right I was not aware of this "START" facility. I'll do it from now on...


Thanks again,
John

 

[babe1898]

Hi KenCuningham and DeepDiverMom,

Thank you so mch for clarifying it...I just didn't get what you meant. Yes, you are right I was not aware of this "START" facility. I'll do it from now on...


Thanks again,
John