Hi Tom,

I have problem in this area but slightly different. I tried to enter as dba to my database, but I faced with this error:

ORA-12549: Operating system resource quota exceeded.

There is no any defined or specified quota from my server.
What is the problem?

Tom, 
This is on 9iR2

SQL>sho user
USER is "GE1STOP"
SQL>select * from USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
USERS                             5373952          0        656          0


oracle documentation says..
MAX_BYTES
 NUMBER
  User's quota in bytes, or -1 if no limit
 
and in my MAX_BYTES column i see only 0.

what does it say?.. 
Thanks
 

ps$tkyte@ORA920LAP> alter user ops$tkyte quota unlimited on users;

User altered.

ops$tkyte@ORA920LAP> select * from USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
USERS                             1572864         -1        192         -1
T_2M                             10485760          0       1280          0
T_1M                                    0          0          0          0

ops$tkyte@ORA920LAP> alter user ops$tkyte quota 0k on users;

User altered.

ops$tkyte@ORA920LAP> select * from USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
USERS                             1572864          0        192          0
T_2M                             10485760          0       1280          0
T_1M                                    0          0          0          0



<b>you have a 0k quota, not unlimited quota, on that tablespace</b>

if you are still able to create things however, you might be a dba with "unlimited tablespace" which overrides that setting. 

On Oracle 9i on NT, when I tried to run spcreate.sql I got

Enter value for perfstat_password:
old 29: connect perfstat/&&perfstat_password
new 29: connect perfstat/perfstat
SP2-0552: Bind variable "22" not declared.

Then I created user perfstat with following syntax,

create user perfstat identified by perfstst default tablespace users;

alter user perfstat quota 500k on users;

I gave required privileges and Im able to login to user perfstat

Then again I tried to run the spcreate.sql I got the message,

Enter value for perfstat_password: perfstat
old 29: connect perfstat/&&perfstat_password
new 29: connect perfstat/perfstat
SP2-0552: Bind variable "22" not declared.

How to go about it and why Im getting this ?

Please see:
After I moved data and dropped some TSs, I have following:

select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
RBS
USERS
TOOLS
INDX
DRSYS
OEM_REPOSITORY
ZEIKO
PROJECT_DATA
I_UPV_DATEN
TEMP_LM
T_UPV_DATEN
INDEX_LM
LOB_LM
DATA_LM
USERVERWALTUNG

select TABLESPACE_NAME,USERNAME,MAX_BYTES from dba_ts_quotas;
TABLESPACE_NAME USERNAME MAX_BYTES
------------------------------ ------------------------------ ----------
TEMP SYSMAN -1
OEM_REPOSITORY SYSMAN -1
USERS DCC_USER -1
TOOLS DCC_USER -1
INDX DCC_USER -1
DCC_ADMIN08 DCC_USER -1
DCC_ADMIN07 DCC_USER -1
DCC_ADMIN06 DCC_USER -1
DCC_ADMIN05 DCC_USER -1
DCC_ADMIN04 DCC_USER -1
DCC_ADMIN03 DCC_USER -1
DCC_ADMIN02 DCC_USER -1
DCC_ADMIN01 DCC_USER -1
TEMPREC DCC_USER -1

I do not understend where from is coming this inconsistency?
When I try:
alter user DCC_USER quota 0 on DCC_ADMIN01;
I got error: ORA-00959 tablespace 'string' does not exist

Any advice?

Tom,

If no quota is specified when we create a new user in 9i, what is their default quota on temporary?
Consider:

$ sqlplus /

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Oct 27 10:25:23 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> drop user craig_test
  2  ;

User dropped.

SQL> create user craig_test identified by passwd123   
  2  default tablespace mrdssusers;

User created.

SQL> select * from dba_ts_quotas where username = 'CRAIG_TEST';

no rows selected

SQL> select * from dba_users where username = 'CRAIG_TEST';

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ---------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
CRAIG_TEST                            143 E65E7CE6C93B35C1
OPEN                                       25-JAN-06
MRDSSUSERS                     MRDSSTEMP                      27-OCT-05

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ---------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
DEFAULT                        DEFAULT_CONSUMER_GROUP



SQL>  

Hey Tom,

I am very confused the way this view gives result...

1) Why DBA_TS_QUOTAS shows quotas for all of the users in database?

2) what is the default value of quota for a user on default tablespace?

ex. sql> create user test identified by test
default tablespace users
temporary tablespace temp;

in this case what would be the default quota on users tablespace for user test.

Thanks
Shailu

ops$tkyte%ORA10GR2> select username from all_users minus select username from dba_ts_quotas;

USERNAME
------------------------------
ANONYMOUS
AOL
BIG_TABLE
CGR_J2EE
CTXSYS
DBSNMP
DBUSER1

ops$tkyte%ORA10GR2> create user a identified by a default tablespace users temporary tablespace tempnew;

User created.

ops$tkyte%ORA10GR2> select * from dba_ts_quotas where username = 'A';

no rows selected

ops$tkyte%ORA10GR2> alter user a quota 500k on users;

User altered.

ops$tkyte%ORA10GR2> select * from dba_ts_quotas where username = 'A';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
USERS                          A                                       0     516096          0         63 NO

Hi Tom,

Thanks for your response on this.

You said if i dont specify any quota to user while creating it. It wont have any quota but how came that particular user able to create object?

Thanks
Shailu

Tom,

How does Oracle determine what a user's quota is if that had unlimited tablespace, they put a bunch of objects + data in various tablespaces, then you revoke the unlimited tablespace leaving them with quota on nothing?

I would have thought the user would get

ORA-01950: no privileges on tablespace

But instead, things worked for awhile and out of the blue on day the application got

ORA-01536: space quota exceeded for tablespace

I don't understand where this quota came from?

We've got three excellent Oracle DBAs (I'm a SQL Server DBA learning Oracle) where I work but, I bet all three would tell me I need to create a quota to create objects in a tablespace--it's just what you do. But, really GRANT UNLIMITED TABLESPACE TO <username> works and it's not really a quota at all--it just grants access to the tablespace. These answers are very refreshing and clear the air of misconceptions. No quota really does mean unlimited. (err.. ah... hope I got that technically right) Thank you. Mark S.

i have just downloaded sql8.0 and i entered username as scott and password as tiger but still it is saying that it is unable to connect to designation so please help me as iam new to do it.

Hi Tom

As we give tablespace quota to database user...

SQL> alter user <user_name> quota unlimited on users;


How to give quota on tablespace to "Enterprise User" ?   

(  Enterprise User is in Oracle Internet Directory (LDAP) and it is mapped to an enterprise role in OID and that is mapped to a database role )


Enterprise User Security
http://docs.oracle.com/cd/B28359_01/network.111/b28528/getstrtd.htm#CBHBGHDI

- Enterprise user created in Oracle Internet Directory.  Oracle Database is registered with it for users' authentication.
- Mapped to a Shared Schema.  ( CREATE USER global_ident_schema_user IDENTIFIED GLOBALLY ; )
- Assigned an Enterprise Role to user.
    SQL> CREATE ROLE hr_access IDENTIFIED GLOBALLY;
    SQL> GRANT SELECT ON hr.employees TO hr_access;


Thank in advance for any help or direction. 


Anand

Oracle Version : 11.2.0.3

I am getting ORA-01536 in alert log.

statement in resumable session 'User SYS(0), Session 509, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace 'XT_DATA'.

Trust this is because SYS is running SYS_AUTO_SQL_TUNING_TASK

But when I check the DBA_TS_QUOTAS, I don't see any user with any limits:

select * from dba_ts_quotas where max_bytes <> -1 or max_blocks <> -1;

What gives ?

drop user DATA_PUMP_USER;
CREATE USER DATA_PUMP_USER IDENTIFIED BY password100
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

revoke create session from DATA_PUMP_USER;
grant create session, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE, AQ_ADMINISTRATOR_ROLE to data_pump_user;

alter user DATA_PUMP_USER quota unlimited on users;

--===
expdp data_pump_user/password100@seaprrs parfile='C:\Documents and Settings\khughes\Desktop\kch_COT-MCB-BOC-001\Data_Pump\dpdump0\expdpORABOCW.par'


01536: space quota exceeded for tablespace 'USERS'.

i ham alrted 3 mb quota on user management.
and i want to delete that data , how?