Home » RDBMS Server » Server Administration » How can i see the table space of a user
How can i see the table space of a user [message #297978] Mon, 04 February 2008 06:50 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi Friends,

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

wbr
Kanish
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi
Both michel

wbr
kanish
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_users
dba_segments

Regards
Michel
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
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 Go to previous messageGo to next message
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';
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
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 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SELECT *
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE = 'SCOTT';

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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member


Same

No Records Found.

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 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
THerefore,there is no quota assigned to user scott.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
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 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Yes,try creating a table as user scott and see what errors you get.
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member


Sorry

No rows selected
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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.

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


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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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 Go to previous message
Michel Cadot
Messages: 64140
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
Previous Topic: Curses -- need my 10.2 DB backward compatible with 9.2
Next Topic: ORA-39778
Goto Forum:
  


Current Time: Fri Dec 09 04:24:09 CST 2016

Total time taken to generate the page: 0.09956 seconds