How to Grant Quota Unlimited on Tablespace to User

 To grant quota on tablespace to users there are two options.

Specific Tablespace

SQL> create user webuser identified by 'backup hash key' default tablespace USERS temp tablespace TEMP;

User created.

SQL> alter user webuser quota unlimited on users;

User altered.

SQL> column tablespace_name format a30;

SQL> column "QUOTA(GB)" format a10;
SQL> select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from dba_ts_quotas where username = 'WEBUSER';

TABLESPACE_NAME      QUOTA(GB)
-------------------- ----------
USERS                UNLIMITED

Revoke Tablespace Quota

To quota off, just use 0 to revoke its usage on the tablespace.

SQL> alter user webuser quota 0 on USERS;

User altered.

To check as below:

SQL> select * from dba_ts_quotas where username = 'WEBUSER' and tablespace_name = 'USERS';

no rows selected


Another way to revoke the access:


SQL> revoke unlimited tablespace from erpapp;

Revoke succeeded.

Quota Unlimited

To open the limitation use UNLIMITED instead of an explicit value.

SQL> alter user webuser quota unlimited on users;

User altered.

All Unlimited Tablespace

SQL> grant unlimited tablespace to webuser;

Grant succeeded.

The user has the right to use any tablespace without any limitation.

Below DDL commands are useful :


SQL> SELECT DBMS_METADATA.GET_DDL('USER',username) as script from dba_users where username='&username';

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SSO_ID') from dual;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SSO_ID') from dual;



Comments

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

PRVG-11250 : The check "RPM Package Manager database" was not performed because

How to troubleshoot Long Running Concurrent Request in EBS 12.2

ORA-00257:archiver error, connect internal only until freed

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

CPU Patch Analysis

How to write to a CSV file using Oracle SQL*Plus

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"