Home » SQL & PL/SQL » SQL & PL/SQL » quota
quota [message #251932] Tue, 17 July 2007 03:34 Go to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Hi seniors,

my oracle version is 10g 10.1.0.2

I have created a use with quota 80k

create user bob
identified by bob
default tablespace test
quota 80k on test;

i am using locally managed tablespace and uniform extent size is 40k

after creation of user bob he can store data to a table more than 80k.why does it happen.If i am wrong in using quota..What is the benefit of using it and how can i use to restrict space to a user.

Your answer highly appreciated

blessings
unus
Re: quota [message #251933 is a reply to message #251932] Tue, 17 July 2007 03:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for bringing it back up. Keep it to one copy please, to avoid mistakes.

MHE
Re: quota [message #251936 is a reply to message #251932] Tue, 17 July 2007 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It all works for me.

Can you cut and paste from SQL*Plus an example that shows you creating this user, connecting as them, creating a table bigger than the quota, and also shows how you have determined that the table is too large.
Re: quota [message #251937 is a reply to message #251932] Tue, 17 July 2007 03:44 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
sorry mhe

when i tried to post this it gave me page can not be displayed error manytimes may be thats the reason not intentionally did.
Re: quota [message #251962 is a reply to message #251936] Tue, 17 July 2007 04:46 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Hi JRowbottom please the information you asked for..

USER CREATION
create user bob
identified by bob
default tablespace test
temporary tablespace temp
quota 80k on test;

create user succeeded.

GRANTING
grant resource,connect to bob
grant resource,connect succeeded.

QUOTA ChECKING
select username,tablespace_NAME,MAX_BYTES/1024 AS KB from dba_ts_quotas where username='BOB' AND TABLESPACE_NAME='TEST' ;

USERNAME TABLESPACE_NAME KB
------------------------------ ------------------------------ ----------------------
BOB TEST 80


CREATING TABLE AS BOB

create table test (name varchar2(15));
create table succeeded.

CREATE A PROCEDURE TO INSERT DATA ITERATIVELY

CREATE PROCEDURE INSERT_V IS
BEGIN
FOR I IN 1..500
LOOP
INSERT INTO TEST VALUES('NAME'||I);
END LOOP;
END;

PROCEDURE INSERT_V Compiled.

EXECUTING THAT PROCEDURE TO POPULATE TABLE

EXEC INSERT_V
anonymous block completed



CHEKING THE SIZE OF THE TABLE

select segment_name,owner,BYTES/1024 KB from dba_segments where segment_name='TEST' AND owner='BOB' ;

SEGMENT_NAME OWNER KB
--------------------------------------------------------------------------------- --------------------------
TEST BOB 120


I have specified only 80k for user bob now this table created by bob as 120k…

sorry if i put too much...
Re: quota [message #251976 is a reply to message #251962] Tue, 17 July 2007 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
RESOURCE role implies UNLIMITED TABLESPACE privilege which as its name indicates remove all limits on quota.

Never ever use RESOURCE role.
Use CONNECT role ONLY in 10g but prefer CREATE SESSION privilege.

CONNECT, RESOURCE and DBA roles are only there for pre-7 versions compatibility.

Regards
Michel
Re: quota [message #252016 is a reply to message #251932] Tue, 17 July 2007 12:28 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Thanx alot Michel now its working smoothly. Can u please give me a link to learn more about privileges.

Regards
unus
Re: quota [message #252022 is a reply to message #252016] Tue, 17 July 2007 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start with GRANT statement and follow the links in the page.

Regards
Michel
Re: quota [message #252030 is a reply to message #252022] Tue, 17 July 2007 12:47 Go to previous message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member




Thank You

Previous Topic: Changing column data
Next Topic: A PL/SQL Parse error occurred
Goto Forum:
  


Current Time: Sun Dec 04 04:16:33 CST 2016

Total time taken to generate the page: 0.10412 seconds