Assignment of DEFAULT_TABLESPACE Quota
The query in the “Assignment of DEFAULT_TABLESPACE Quota” article aims to create the command, through a dynamic query, to assign a user’s quota on their default_tablespace, considering the quota assigned to an existing user in the database.
Every user created in the database has their own default tablespace, which is used as the area for object creation in case the destination TABLESPACE is not declared. In addition to the default TBSP, we also have the possibility to write to other tablespaces, but subject to the user quota set either during creation or subsequently with the statement provided at the end of the article.
Every user should have a write quota set on their reference TBSP. However, from my decade-long experience with Oracle databases, DBAs who set the Quota to a few MBs on the default TBSP are really few.
The clause that is set in most Oracle DBs is UNLIMITED.
With the UNLIMITED privilege, we can write without worrying about running out of quota.
But, in case we need to set a standard quota that the database-owning company relies on, the query below will be suitable.
Simply execute the query and provide the username from which we want to copy the quota when prompted.
Command creation for assigning user DEFAULT_TABLESPACE quota:
select 'alter user &userid quota '||
decode(max_bytes, -1, 'unlimited',
ceil(max_bytes / 1024 / 1024) || 'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = '&userid';To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining at our community:
Facebook
Youtube
Linkedin
0 Comments