SQL to Set Tablespace Quota for Oracle User

advertisements

_____________________________________________________________________________________________________________________

Create a tablespace - USER_DATA

create tablespace user_data 
datafile '/u02/oradata/CDB/user_data_01.dbf' size 500m;

Create a user - USER_APP

create user user_app identified by user ;

Allocate Quota to the user USER_APP on tablespace USER_DATA

alter user user_app quota 100m on user_data;

SQL Query to check the quota details.

set pages 1000 lines 120
col usr_name for a20
col tbs_name for a20
col Quota_allocated for a15
select
  username          usr_name,
  tablespace_name   tbs_name,
  decode(max_bytes, -1, 'UNLIMITED', max_bytes/1024/1024) Quota_allocated,
  bytes/1024        used,
  dropped
from
  dba_ts_quotas
where username='USER_APP'
order by
  1,2
/

advertisements

 

SQL to allocate unlimited quota on tablespace

alter user user_app quota unlimited on user_data;

Execution

SQL> create tablespace user_data datafile '/u02/oradata/CDB/user_data_01.dbf' size 500m

Tablespace created.

SQL> create user user_app identified by user ;

User created.

SQL> alter user user_app quota 100m on user_data;

User altered.

SQL> set pages 1000 lines 120
col usr_name for a20
col tbs_name for a20
col Quota_allocated for a15
select
  username          usr_name,
  tablespace_name   tbs_name,
  decode(max_bytes, -1, 'UNLIMITED', max_bytes/1024/1024) Quota_allocated,
  bytes/1024        used,
  dropped
from
  dba_ts_quotas
where username='USER_APP'
order by
  1,2
/
SQL>

USR_NAME      TBS_NAME    QUOTA_ALLOCATED USED DRO
-------------------- -------------------- --------------- ---------- ---
USER_APP      USER_DATA    100      0 NO

SQL> alter user user_app quota unlimited on user_data;

User altered.

SQL> 
set pages 1000 lines 120
col usr_name for a20
col tbs_name for a20
col Quota_allocated for a15
select
  username          usr_name,
  tablespace_name   tbs_name,
  decode(max_bytes, -1, 'UNLIMITED', max_bytes/1024/1024) Quota_allocated,
  bytes/1024        used,
  dropped
from
  dba_ts_quotas
where username='USER_APP'
order by
  1,2
/

USR_NAME      TBS_NAME    QUOTA_ALLOCATED USED DRO
-------------------- -------------------- --------------- ---------- ---
USER_APP      USER_DATA    UNLIMITED     0 NO

_____________________________________________________________________________________________________________________

Website Stats