| How to find users quota as DBA [message #145794] |
Sun, 06 November 2005 19:46  |
dnvs_praveen
Messages: 47
Registered: July 2005
Location: bangalore
|
Member |
|
|
Hi
In my database i had 30 users, i alloted 28MB quota on my tablespace to each users, now the usage of some users are more and some users are less. But as a DBA how do i find which users quota is getting over? Than only i can allot more quota to that users, before getting error message.
Plz let me know the query for this. (As a DBA how do i know all users free space and used space in thier quotas)
(resource_limit is set to true, but when i give select * from dba_ts_quotas i can able to see users are crossing their 28MB quota but able to work until free space available in that tablespace. If the query is correct plz let me know where is mistake is going on? They are not getting error once if they reach their alloted quota? How do i restict them as per their quota?)
Regards,
Praveen
[Updated on: Sun, 06 November 2005 20:15] Report message to a moderator |
|
|
|
| Re: How to find users quota as DBA [message #145863 is a reply to message #145794] |
Mon, 07 November 2005 06:00  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Did you also create the users with resource role/unlimited tablespace ?
So this would have been the behaviour
User SCOTT has resource privelege. setting quota over it, has no effect.
User TEST has quota 10m , no resource privilege
scott@9i > select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;
USERNA TABLESPACE_NAME BYTES/(1024*1024) MAX_BYTES/(1024*1024)
------ ------------------------------ ----------------- ---------------------
SCOTT USERS .25 10
TEST USERS .0625 10
scott@9i > get emp_loopinsert;
1 begin
2 for mag in 1..15 loop
3 insert into test.emp (select * from test.emp);
4 end loop;
5 commit;
6* end;
scott@9i > /
begin
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at line 3
scott@9i > select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;
USERNA TABLESPACE_NAME BYTES/(1024*1024) MAX_BYTES/(1024*1024)
------ ------------------------------ ----------------- ---------------------
SCOTT USERS .25 10
TEST USERS 10 10
Now lets do the same for SCOTT
scott@9i > alter user scott quota 10m on users;
User altered.
scott@9i > select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;
USERNA TABLESPACE_NAME BYTES/(1024*1024) MAX_BYTES/(1024*1024)
------ ------------------------------ ----------------- ---------------------
SCOTT USERS .25 10
TEST USERS 10 10
scott@9i > get emp_loopinsert;
1 begin
2 for mag in 1..15 loop
3 insert into emp (select * from emp);
4 end loop;
5 commit;
6* end;
scott@9i > /
PL/SQL procedure successfully completed.
scott@9i > select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;
USERNA TABLESPACE_NAME BYTES/(1024*1024) MAX_BYTES/(1024*1024)
------ ------------------------------ ----------------- ---------------------
SCOTT USERS 22.1875 10
TEST USERS 10 10
--
-- Revoked resource and dba ( i gave DBA to scott) from scott.
-- rebuilt emp table with 14 rows.
-- inserted rows again.
-- You will see, what you expected to see.
scott@9i > revoke resource,dba from scott;
Revoke succeeded.
scott@9i > alter user scott quota 10m on users;
User altered.
scott@9i > get emp_loopinsert;
1 begin
2 for mag in 1..15 loop
3 insert into emp (select * from emp);
4 end loop;
5 commit;
6* end;
scott@9i > /
begin
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at line 3
scott@9i > select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;
USERNA TABLESPACE_NAME BYTES/(1024*1024) MAX_BYTES/(1024*1024)
------ ------------------------------ ----------------- ---------------------
SCOTT USERS 9.1875 10
TEST USERS 10 10
|
|
|
|
| Re: How to find users quota as DBA [message #145868 is a reply to message #145794] |
Mon, 07 November 2005 06:12 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Please see the following below written things
QUOTA :
SELECT * FROM dba_ts_quotas;
Check values of BYTES and BLOCKS. This values shows the amount of space taken by the objects created in the tablespace.
If the table created in users tablespace is dropped, the above mentioned fields will have ZERO values, OR space in users tablespace will be freed.
Now if you make zero quota on tablespace users of user scott
ALTER USER scott QUOTA 0 ON users;
& then try to Create table as scott user in users tablespace. U will not be allowed as the user scott does not have enough space right on the user tablespace.
Below query is for giving unlimited quota to scott on users tablespace;
ALTER USER scott QUOTA UNLIMITED ON users;
Check dba_ts_quotas; - max_bytes shows –1. that means unlimited.
Below Query is for user to whom u want to give 30M space on tablespace
ALTER USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 30M ON users;
As a dba u dont have to think that user quota is filled or not coz if there will be any problem then user will tell u .. and as a dba u have to increase quota for that user... Now if you are working or managing database of website hosting compnay were user have to pay money for space so whenever they required space they will contact u and you have to increase the space after there payment ok.
Frankly saying before u had design your database at that time u must have analysed the space required for all users.
Bytes field in dba_ts_quotas show how much bytes user is using of tablespace (note it is in bytes ===> it is not in Mega Bytes (MB))
Regards
Always Friend Sunilkumar
[Updated on: Mon, 07 November 2005 06:17] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
