Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Issue on tablespace Quotas (Oracle 8.1.7)

SOLVED
Go to solution
Chris Fung
Frequent Advisor

Issue on tablespace Quotas (Oracle 8.1.7)

Hi there,

I have tried to create a user test with "1K" quota on "users" tablespace....

I would expect I can create database object that cannot exceed this "1K" value as far as storage is concern....

However, how come I can create several..."1M" or even 10M tables ??

Could anyone explain the mechanism(s) behind ??

Cheers,

Chris
4 REPLIES
Yogeeraj_1
Honored Contributor
Solution

Re: Issue on tablespace Quotas (Oracle 8.1.7)

hi,

you could have created a user with the quota, but after that granted him a role that gives him "unlimited tablespace" system privilege which overrides it!

can you post the output of the following sql code?

select /*+ CHOOSE */ privilege, admin_option
from SYS.dba_sys_privs
where GRANTEE='&GRANTEE'
order by 1

============================================================
Anyway, i did the following test for you.

yd@YDDB.MU> CREATE USER CHRIS IDENTIFIED BY CHRIS
DEFAULT TABLESPACE SLX1_DEV_LMT_SMALL
TEMPORARY TABLESPACE TEMP_LMT
QUOTA 1K ON SLX1_DEV_LMT_SMALL;

User created.

Elapsed: 00:00:00.06
yd@YDDB.MU> GRANT CREATE SESSION TO CHRIS;

Grant succeeded.

Elapsed: 00:00:00.01

yd@YDDB.MU> grant create table to chris;

Grant succeeded.

Elapsed: 00:00:00.01
yd@YDDB.MU> grant create table to chris;
yd@YDDB.MU> @connect chris/chris
Connected.
chris@YDDB.MU> CREATE TABLE YDTAB1 (
CLICOD VARCHAR2 (8))
TABLESPACE SLX1_DEV_LMT_SMALL
PCTFREE 10
PCTUSED 75
INITRANS 5
MAXTRANS 100
STORAGE (
INITIAL 65536
NEXT 65536
PCTINCREASE 0)
;
CREATE TABLE YDTAB1 (
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SLX1_DEV_LMT_SMALL'


Elapsed: 00:00:00.02
chris@YDDB.MU> @connect yd
Enter password:
Connected.
yd@YDDB.MU> grant resource to chris;

Grant succeeded.

Elapsed: 00:00:00.02
yd@YDDB.MU> @connect chris/chris
Connected.
chris@YDDB.MU> CREATE TABLE YDTAB1 (
CLICOD VARCHAR2 (8))
TABLESPACE SLX1_DEV_LMT_SMALL
PCTFREE 10
PCTUSED 75
INITRANS 5
MAXTRANS 100
STORAGE (
INITIAL 65536
NEXT 65536
PCTINCREASE 0)
;

Table created.

Elapsed: 00:00:00.04
chris@YDDB.MU> @connect yd
Enter password:
Connected.
yd@YDDB.MU> select /*+ CHOOSE */ privilege, admin_option
2 from SYS.dba_sys_privs
3 where GRANTEE='&GRANTEE'
4* order by 1
Enter value for grantee: CHRIS

CREATE SESSION NO
CREATE TABLE NO
UNLIMITED TABLESPACE NO

Elapsed: 00:00:00.01
yd@YDDB.MU> revoke resource from chris;

Revoke succeeded.

Elapsed: 00:00:00.04
yd@YDDB.MU> select /*+ CHOOSE */ privilege, admin_option
from SYS.dba_sys_privs
where GRANTEE='&GRANTEE'
order by 1
2 3 4 5
yd@YDDB.MU> /
Enter value for grantee: CHRIS

CREATE SESSION NO
CREATE TABLE NO

Elapsed: 00:00:00.00
yd@YDDB.MU>
============================================================

Hope this helps!

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
malay boy
Trusted Contributor

Re: Issue on tablespace Quotas (Oracle 8.1.7)

well nothing left to say here.Our database guru have spell everything out.

regards
mB

There are three person in my team-Me ,myself and I.
Indira Aramandla
Honored Contributor

Re: Issue on tablespace Quotas (Oracle 8.1.7)

Hi chris,

You can control the tablespace quotas for users by allocating a certain quota. But the moment you grant the role RESOURCE to the user this grants the system privilege "UNLIMITED TABLESPACE".

Yogeeraj has demonstrated excellently with an example.
Never give up, Keep Trying
Chris Fung
Frequent Advisor

Re: Issue on tablespace Quotas (Oracle 8.1.7)

Hi all,

Thanks for Yogeeraj demonstrated a very detailed example !!

On the other hand, Indira used a few word to point out exactly what my problem was - "Resource" role.

I just remembered that I grant resource to the user after I create the user with quota on some tablespaces.

Many thanks,

Chris,