Avatar of walkerdba
walkerdba
 asked on

tablespace location

How would I find the tablespace of a particular schema?

Please help..
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steve Wales

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
walkerdba

ASKER
SQL> select tablespace_name from dba_ts_quotas where username = 'SCOTT';

no rows selected

SQL> select tablespace_name from dba_ts_quotas where username ='SCOTT';

no rows selected

SQL>

Why it is like this.....?
Steve Wales

This means that SCOTT has no tablespace as his default and has no quotas on any tablespaces.

If you want to see the tablespaces you do have:

select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_temp_files;
walkerdba

ASKER
How can a schema sit nowhere without tablespace?
Scott should be somewhere right?

My mission is to find out which tablespace SCOTT..
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve Wales

As slightwv mentioned, a schema in Oracle is just a user that owns objects.

It is possible to create a user that does not have any objects (and hence, all of the above queries would return nothing).

johnsone also makes a valid point, you'd need to check dba_sys_privs (to see which users have that system privilege):

select * from dba_sys_privs where privilege = 'UNLIMITED TABLESPACE';

It sounds like you misunderstand what a schema is in Oracle.  It is a different concept to SQL Server, for example.

It is very possible to have users without objects in Oracle.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
walkerdba

ASKER
I've requested that this question be closed as follows:

Accepted answer: 0 points for walkerdba's comment #a38731912

for the following reason:

yes
slightwv (䄆 Netminder)

>> closed by accepting walkerdba's comment #a38731912 (0 points) as the solution for the following reason:  yes

I'm afraid I'll have to object to the way you want to close this.
Please accept the post or posts that assisted you in answering the question.

I suggest a split of the points since it appears several posts helped.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.