Hi,

This is a really elementary question. What is the command to list all
the tablespaces in the database? Thanks.

Grace

Grace Tsai <gtsai (AT) bnl (DOT) gov> wrote:


Select distinct tablespace_name from dba_tables order by tablespace_name;

Variants will be
all_tables ( all tables the user logged in has access to)
user_table ( tables owned by logged in user)
- These will limit the tablespaces you get back to those used by the table in the set..

"Grace Tsai" <gtsai (AT) bnl (DOT) gov> a écrit dans le message de news:cpq0mt$jfo$1 (AT) news (DOT) bnl.gov...
select tablespace_name from dba_tablespaces;

Regards
Michel Cadot

"Michel Cadot" <micadot{at}altern{dot}org> wrote:


OK, so do it the easy way...

;-)

"Turkbear" <john.g (AT) dot (DOT) spamfree.com> a écrit dans le message de
news:tlc1s0tk9ighne76n90qs5kp72d8ng4b0h (AT) 4ax (DOT) com...
We can also use user_tablespaces to to limit those on which we have access.

;-)
Michel Cadot

SQL> select tablespace_name from dba_tablespaces minus
2 Select distinct tablespace_name from dba_tables order by
tablespace_name;

TABLESPACE_NAME
------------------------------
INDX
RBS
TEMP_LOCAL
USERS

:-O

jg
--
@home.com is bogus.
Wondering why I get an extra blank row when I Select distinct
tablespace_name from dba_tables order by tablespace_name;

On 15 Dec 2004 15:47:38 -0800, joel-garry (AT) home (DOT) com wrote:

SQL> create table t1 (
2 c number not null,
3 constraint t1_pk
4 primary key (c)
5 );

Table created.

SQL> create table t2 (
2 c number not null,
3 constraint t2_pk
4 primary key (c)
5 )
6 organization index;

Table created.

SQL> create global temporary table t3 (
2 c number not null,
3 constraint t3_pk
4 primary key (c)
5 );

Table created.

SQL> select table_name, tablespace_name from user_tables
2 order by table_name;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 USERS
T2
T3

SQL> select index_name, tablespace_name from user_indexes
2 order by index_name;

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1_PK USERS
T2_PK USERS
T3_PK

--
Andy Hassall / <andy (AT) andyh (DOT) co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool