• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

tablespace schema

How will find out the tablespaces associated with a particular schema?
0
vangogpeter
Asked:
vangogpeter
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
To find out the default tablespace for a schema:

select username, default_tablespace from dba_users;

To find out all the objects that a schema has and where they are:

select segment_name, segment_type, tablespace_name
from dba_segments
where owner = 'SCHEMANAME';
0
 
slightwv (䄆 Netminder) Commented:
To add:
There really isn't a tablespace associated with a schema.  As mentioned above there is a 'default' but you shouldn't really use it.

Most databases have tablespaces for specific objects per application.  For example: tables go into a tables tablespace, indexes in another, etc...

So many schemas can share a common tablespace if so desired.

It is all up the the DBA on how it is set up.
0
 
DavidSenior Oracle Database AdministratorCommented:
An aside, make the script modular if you want to reuse it.  Preface the SCHEMANAME with an ampersand "&SCHEMANAME" to be prompted each time (in SQL*Plus).
0
 
vangogpeterAuthor Commented:
found out all the tablespace associated with a schema and degault..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now