OraFAQ Forum: Server Administration » How can i see the table space of a user

Home » RDBMS Server » Server Administration » How can i see the table space of a user

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can i see the table space of a user [message #297978]

Mon, 04 February 2008 06:50

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Hi Friends,

I Want to see the user Table Space. How can i ?

wbr
Kanish

Report message to a moderator

Re: How can i see the table space of a user [message #297982 is a reply to message #297978]

Mon, 04 February 2008 06:58

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What do you mean "user Table Space"?
Do you mean default tablespace?
Do you mean space used by user?
...

Regards
Michel

Report message to a moderator

Re: How can i see the table space of a user [message #298022 is a reply to message #297978]

Mon, 04 February 2008 11:16

varu123
Messages: 754
Registered: October 2007

Senior Member

To see the tablespace "users"

SQL>select * from dba_tablespaces where tablespace_name='USERS';

To see which tablespace user has,

SQL>select default_tablespace from dba_users where username=upper('username');

[Updated on: Mon, 04 February 2008 11:30]

Report message to a moderator

Re: How can i see the table space of a user [message #298039 is a reply to message #297978]

Mon, 04 February 2008 14:04

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

hi
Both michel

wbr
kanish

Report message to a moderator

Re: How can i see the table space of a user [message #298040 is a reply to message #298039]

Mon, 04 February 2008 14:09

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

dba_users
dba_segments

Regards
Michel

Report message to a moderator

Re: How can i see the table space of a user [message #298067 is a reply to message #297978]

Mon, 04 February 2008 22:33

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Thank Michel,

One More question when i try to create a table on scott user im getting the below error message

ERROR at line 1: 
ORA-01536: space quota exceeded for tablespace 'USERS'

I want to know the lower limit and upper limit of the user
space. So pls guide me

wbr
kanish

Report message to a moderator

Re: How can i see the table space of a user [message #298072 is a reply to message #297978]

Mon, 04 February 2008 23:08

manjuwkh
Messages: 7
Registered: January 2008
Location: Bangalore

Junior Member

Hey Manish,

DBA_SEGMENTS check this one...
u May get something about size, user, tablespace...
SELECT * FROM DBA_SEGMENTS;

Thanks,
Manju

Report message to a moderator

Re: How can i see the table space of a user [message #298074 is a reply to message #297978]

Mon, 04 February 2008 23:17

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Hi manju,

Ya i had, i can see lot of info in dba_segment. Im not dba guy so im not able understand terminalogy.

im getting ORA-01536. So table space is exceed the quota for the user then, I Want to know
1. How much Quota for the user SCOTT
2. How to Improve the table_Space.

Thanks

Kanish

Report message to a moderator

Re: How can i see the table space of a user [message #298083 is a reply to message #297978]

Mon, 04 February 2008 23:59

varu123
Messages: 754
Registered: October 2007

Senior Member

SELECT USERNAME,
       TABLESPACE_NAME,
       BYTES / 1048576 "quota"
FROM   DBA_USERS ,
       USER_TS_QUOTAS 
WHERE  TABLESPACE_NAME = DEFAULT_TABLESPACE
       AND USERNAME = 'SCOTT';

Report message to a moderator

Re: How can i see the table space of a user [message #298107 is a reply to message #298074]

Tue, 05 February 2008 00:49

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

2. How to Improve the table_Space

You have to first use Oracle words.
"table_Space" does this mean "table space" or "tablespace"?

Regards
Michel

Report message to a moderator

Re: How can i see the table space of a user [message #298352 is a reply to message #297978]

Tue, 05 February 2008 21:26

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Hi Varu,

I feel correct but i am getting none of records for that Query,I know there is no records for the user 'SCOTT', ok and why it is not there, then where i can see the quote of scott.

wbr
kanish

Report message to a moderator

Re: How can i see the table space of a user [message #298359 is a reply to message #297978]

Tue, 05 February 2008 22:36

varu123
Messages: 754
Registered: October 2007

Senior Member

SELECT *
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE = 'SCOTT';

Report message to a moderator

Re: How can i see the table space of a user [message #298364 is a reply to message #297978]

Tue, 05 February 2008 23:01

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Same

No Records Found.

Report message to a moderator

Re: How can i see the table space of a user [message #298369 is a reply to message #297978]

Tue, 05 February 2008 23:21

varu123
Messages: 754
Registered: October 2007

Senior Member

THerefore,there is no quota assigned to user scott.

Report message to a moderator

Re: How can i see the table space of a user [message #298372 is a reply to message #298352]

Tue, 05 February 2008 23:33

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I know there is no records for the user 'SCOTT', ok and why it is not there, then where i can see the quote of scott.

2 points:
1/ If there is no line then quota is 0
2/ But if user has UNLIMITED TABLESPACE privilege (as SCOTT) then it is not limited by his quota.

This is explained in documentation:
Database Security Guide
Chapter 11 Administering User Privileges, Roles, and Profiles
Section 11.1.1.4 Assigning Tablespace Quotas

Regards
Michel

Report message to a moderator

Re: How can i see the table space of a user [message #298373 is a reply to message #297978]

Tue, 05 February 2008 23:38

varu123
Messages: 754
Registered: October 2007

Senior Member

Yes,try creating a table as user scott and see what errors you get.

Report message to a moderator

Re: How can i see the table space of a user [message #298456 is a reply to message #297978]

Wed, 06 February 2008 02:39

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

SELECT USERNAME,
       TABLESPACE_NAME,
       BYTES / 1048576 "quota"
FROM   DBA_USERS ,
       USER_TS_QUOTAS 
WHERE  TABLESPACE_NAME = DEFAULT_TABLESPACE
       AND USERNAME = 'SCOTT';

it is not working but both table have scott user and both tablespace_name & default_tablespace value is 'USERS'

why it is not working

wbr

kanish

Report message to a moderator

Re: How can i see the table space of a user [message #298472 is a reply to message #298456]

Wed, 06 February 2008 03:10

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What do you mean it is not working?
It perfectly works:

SQL> SELECT USERNAME,
  2         TABLESPACE_NAME,
  3         BYTES / 1048576 "quota"
  4  FROM   DBA_USERS ,
  5         USER_TS_QUOTAS 
  6  WHERE  TABLESPACE_NAME = DEFAULT_TABLESPACE
  7         AND USERNAME = 'SCOTT';
USERNAME                       TABLESPACE_NAME                     quota
------------------------------ ------------------------------ ----------
SCOTT                          TS_D01                            12.0625

1 row selected.

Regards
Michel

Report message to a moderator

Re: How can i see the table space of a user [message #298489 is a reply to message #297978]

Wed, 06 February 2008 04:08

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

The same query is not working in my side.

Even the query is correct and data also correct

Report message to a moderator

Re: How can i see the table space of a user [message #298497 is a reply to message #298489]

Wed, 06 February 2008 04:20

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

The same query is not working in my side.

This is not an Oracle error message.
What do you mean "is not working"? (second time)

Regards
Michel

[Updated on: Wed, 06 February 2008 04:20]

Report message to a moderator

Re: How can i see the table space of a user [message #298510 is a reply to message #297978]

Wed, 06 February 2008 04:40

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Sorry

No rows selected

Report message to a moderator

Re: How can i see the table space of a user [message #298516 is a reply to message #298510]

Wed, 06 February 2008 04:47

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you read that:

Michel Cadot wrote on Wed, 06 February 2008 06:33

Quote:

I know there is no records for the user 'SCOTT', ok and why it is not there, then where i can see the quote of scott.

Report message to a moderator

Re: How can i see the table space of a user [message #298826 is a reply to message #297978]

Thu, 07 February 2008 21:43

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

hi,

SQL> SELECT * FROM USER_TS_QUOTAS;

TABLESPACE_NAME                    BYTES MAX_BYTES    BLOCKS MAX_BLOCKS
------------------------------ --------- --------- --------- ----------
SYSTEM                                 0         0         0          0
USER_DATA                      4.490E+09        -1    548096         -1

USERNAME                         USER_ID PASSWORD
------------------------------ --------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE
-------------------------------- --------- --------- ------------------------------
TEMPORARY_TABLESPACE           CREATED   PROFILE
------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------
SCOTT                                631 F894844C34402B67
OPEN                                                 USERS
TEMPACCL                       26-DEC-05 DEFAULT
DEFAULT_CONSUMER_GROUP

check two different table output.

kanish

Report message to a moderator

Re: How can i see the table space of a user [message #298831 is a reply to message #298826]

Thu, 07 February 2008 23:05

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The first query is on USER_ts_quotas, the second against DBA_users (even if you didn't post the query).
This does not prove the current user is SCOTT.
Post "show user" and query against USER_USERS.

Regards
Michel

Report message to a moderator

Re: How can i see the table space of a user [message #298835 is a reply to message #297978]

Thu, 07 February 2008 23:37

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

SQL> show user
USER is "SCOTT"

SQL> select * from user_users;

USERNAME                         USER_ID ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
------------------------------ --------- -------------------------------- --------- ---------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ---------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------
SCOTT                                631 OPEN
USERS                          TEMPACCL                       26-DEC-05
DEFAULT_CONSUMER_GROUP

[Updated on: Fri, 08 February 2008 00:49] by Moderator

Report message to a moderator

Re: How can i see the table space of a user [message #298857 is a reply to message #298835]

Fri, 08 February 2008 00:50

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And then?
Post the session of what you want to show in a whole.

Regards
Michel

Report message to a moderator

Previous Topic:	Curses -- need my 10.2 DB backward compatible with 9.2
Next Topic:	ORA-39778

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 20:05:06 CDT 2024