List of tablespaces available for the current user

web...@hotmail.com

unread,

Jan 9, 2006, 4:02:19 PM1/9/06

to

Hello everybody,

I'm no Oracle expert, not even newbie level :-)
I just need to know if it is possible at all to obtain the list of
tablespaces for the logged-in user.

For example, after I log in to the SQL Plus console and type a command
such as

SELECT * FROM DBA_TABLESPACES (or whatever sys table has that
information)

I get table does not exist, obviously because I do not have admin
priviledges.
So how can I then find out the tablespaces my login credentials grant
me access to?
By tablespaces I mean "databases" coming from SQL server world. So If
my login credentials allow me access to tablespace DATABASE1 and
DATABASE2, I would like to get such list somehow from some system
table.

Is this possible without admin rights?

Thanks all!

webO

Chuck

unread,

Jan 9, 2006, 4:18:07 PM1/9/06

to

select * from all_tablespaces;

In fact there's a corresponding ALL_ view for most of the DBA_ views.
They show what's accessible to the current user. All these can be found
in the docs at http://tahiti.oracle.com.

--
To reply by email remove "_nospam"

Michel Cadot

unread,

Jan 9, 2006, 4:28:09 PM1/9/06

to

"Chuck" <skilove...@bluebottle.com> a écrit dans le message de news: jqAwf.27464$v84.4183@trnddc06...

I don't think all_tablespaces exists:

SQL> desc all_tablespaces
ERROR:
ORA-04043: object all_tablespaces does not exist

Regards
Michel Cadot

Michel Cadot

unread,

Jan 9, 2006, 4:29:53 PM1/9/06

to

<web...@hotmail.com> a écrit dans le message de news: 1136840539.5...@g44g2000cwa.googlegroups.com...

user_ts_quotas

SQL> desc user_ts_quotas
Name Null? Type
----------------------- -------- ----------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
MAX_BYTES NUMBER
BLOCKS NUMBER
MAX_BLOCKS NUMBER
DROPPED VARCHAR2(3)

Regards
Michel Cadot

Chuck

unread,

Jan 9, 2006, 5:08:58 PM1/9/06

to

Michel Cadot wrote:

>
> I don't think all_tablespaces exists:
>
> SQL> desc all_tablespaces
> ERROR:
> ORA-04043: object all_tablespaces does not exist
>
> Regards
> Michel Cadot
>
>

You are correct. My mistake. I meant to say..

select * from USER_TABLESPACES;

Joel Garry

unread,

Jan 9, 2006, 5:44:39 PM1/9/06

to

web...@hotmail.com wrote:
> Hello everybody,
>
> I'm no Oracle expert, not even newbie level :-)
> I just need to know if it is possible at all to obtain the list of
> tablespaces for the logged-in user.
>
> For example, after I log in to the SQL Plus console and type a command
> such as
>
> SELECT * FROM DBA_TABLESPACES (or whatever sys table has that
> information)
>
> I get table does not exist, obviously because I do not have admin
> priviledges.
> So how can I then find out the tablespaces my login credentials grant
> me access to?
> By tablespaces I mean "databases" coming from SQL server world. So If

The closest Oracle concept to those kind of "databases" is schemata.
See the Oracle Concepts manual at tahiti.oracle.com so you talkee the
right wordees, otherwise people will answer the question you asked.

select unique owner from user_tab_privs;

Will show the schemata.

> my login credentials allow me access to tablespace DATABASE1 and
> DATABASE2, I would like to get such list somehow from some system
> table.

select owner, table_name, privilege from user_tab_privs;

>
> Is this possible without admin rights?

There are a lot of roles and privileges, you can only see that which
you have been allowed.

Take a look at all_objects to see what you can see.

desc all_objects

jg
--
@home.com is bogus.
What's in your database?
http://www.signonsandiego.com/uniontrib/20060109/news_1n9pot.html

web...@hotmail.com

unread,

Jan 10, 2006, 9:09:12 AM1/10/06

to

Thanks very much everybody!
You've provided me the right answers.

webO

manjsuha

unread,

Jan 10, 2006, 10:38:40 AM1/10/06

to

Hi,

Is there any view like all_tablespaces in 8i?

Thanks
Usha

Turkbear

unread,

Jan 10, 2006, 11:46:35 AM1/10/06

to

"manjsuha" <manjush...@gmail.com> wrote:

Yes..