Oracle tablespace quota

Oracle TABLESPACE quota details when you CREATE a USER: SQL> create USER user01IDENTIFIED BY oracleDEFAULT TABLESPACE tbs1TEMPORARY TABLESPACE tempPROFILE defaultSQL> GRANT create session, CREATE table TO user01; while executing the command to create a table, the user gets the following error message and the CREATETABLE .. command fails. ERROR at line 1: ORA-01950: no privileges on tablespace interpretation: default tablespace is defined by the user in The tablespace used when the tablespace is specified. Due to the requirements of Table space management, oracle must be able to manage the available size of the table space (user) and have the quota clause. GRANT create session, create table TO user01 indicates that user01 has the permission TO create a table. oracle also knows that user01 has the default tablespace, but the default tablespace does not allocate space TO it, so an error occurs. You can add quota 200 M on tbs1 (giving him 200 M space) or directly alter user user01 unlimited on tbs1 (allowing him to use the tbs1 tablespace at Will) when creating a user ). Therefore, in the process of creating a user, you need to allocate space to the user by default tablespace (you can also allocate unlimited use). When the user uses the tablespace quota, the user can no longer use space, unless you apply again. Table space quota Overview the Oracle official website defines quota as follows: A limit on a resource, such as a limit on the amount of database storage used by a database user. A database administrator can set tablespace quotas for each Oracle Database username quota daily management FAQ ORA-01536: space quota exceeded for table space 'cyyd 'ORA-01950: no privileges on tablespace solution: alter user USERNAME quota 100 M on TABLESPACENAME; alter user USERNAM E quota unlimited on TABLESPACENAME; grant unlimited tablespace to USERNAME; Note: quota is used to restrict the use of table space. For example, you can restrict the quota of Guotu in tablespace CYYD to 10 MB, after the data volume of Guotu in tablespace CYYD reaches 10 MB, no matter how much space your tablespace CYYD has, Guotu can no longer use tablespace CYYD. So you need: alter user aGuotu quota 1000 M on CYYD; alter user Guotu quota unlimited on CYYD; grant unlimited tablespace to Guotu dba_ts_quotas and quota-related data dictionary view is dba_ts_quotas, the following information is related to Assigning a Tablespace Quota for the UserYou can assign each user a tablespace quota for any tablespace (partition t a temporary tablespace ). assigning a quota accomplishes the following: Users with privileges to create certain type S of objects can create those objects in the specified tablespace. oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota. by default, a user has no quota on any tablespace in the database. if the user has the privilege to create a schema object, then you must assign a quota to allow the user to crea Te objects. at a minimum, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects. you can use the following statement to create user jwardIDENTIFIED BY passwordDEFAULT TABLESPACE data_tsQUOTA 100 m on test_tsQUOTA 500 k on data_tsTEMPORARY TABLESPACE temp_tsPROFILE clerk; you can specify a user either individual quotas for a specific Amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. specific quotas prevent a user's objects from using too much space in the database. you can assign quotas to a user tablespace when you create the user, or add or change quotas later. (You can find existing user quotas by querying the USER_TS_QUOTAS view .). If a new quota is less than the old one, then the following conditions remain true :( 1) If a user has already exceeded a new tablespace quota, then the objects of a user in the tablespace cannot be allocated more space until the combined space of these objects is less than the new quota. (2) If a user has not exceeded a new tablespace quota, or if the space used by the objects of the user in the tables Pace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota. restricting the Quota Limits for User Objects in a Tablespace You can restrict the quota limits for user objects in a tablespace by using the alter user SQL statement to change the current quota of the user to zero. after a quota of zero is assigned, the objects of the user in the tablespace Remain, and the user can still create new objects, but the existing objects will not be allocated any new space. for example, you cocould not insert data into one of this user's exiting tables. the operation will fail with an ORA-1536 space quota exceeded for tables error. granting Users the unlimited tablespace System PrivilegeTo permit a user to use an unlimited amount of any tablespace in the datab Ase, grant the user the unlimited tablespace system privilege. this overrides all explicit tablespace quotas for the user. if you later revoke the privilege, then you must explicitly grant quotas to individual tablespaces. you can grant this privilege only to users, not to roles. before granting the unlimited tablespace system privilege, you must consider the consequences of doing so. advantage: You Can grant a user unlimited access to all tablespaces of a database with one statement. disadvantages: (1) The privilege overrides all explicit tablespace quotas for the user. (2) You cannot selectively revoke tablespace access from a user with the unlimited tablespace privilege. you can grant selective or restricted access only after revoking the privilege. listing All Tablespace QuotasUse the DBA_TS_QU OTAS view to list all tablespace quotas specifically assigned to each user. for example: SELECT * FROM orders; tablespace username bytes MAX_BYTES BLOCKS MAX_BLOCKS ------------ ---------- ------- ---------- USERS jers 0 512000 0 250 USERS limit 0-1 0-1 When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. this number is always a multip Le of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. unlimited quotas are indicated by-1. note: When a resource role is assigned to a user, the system permission of the unlimited tablespace is also granted. For details, see create user SQL> create User test_privs identified by test_privs default tablespace users; user created. SQL> select * from dba_sys_privs where GRANTEE = 'test _ PRIVS '; no rows selected grant the resource role SQL> Grant resource to TEST_PRIVS; grant succeeded. query the system permissions of the resource role SQL> select * from dba_sys_privs where GRANTEE = 'resource'; GRANTEE PRIVILEGE ADM -------------------------------------------------------- -------------- --- Resource create trigger noresource create sequence noresource create type noresource create procedure noresource create cluster noresource create operator noresource create indextype noresource create table no view User Role SQL> select * from role where GRANTEE = 'test _ PRIVS '; GRANTEE GRANTED_ROLE adm def ------------------------------ ---------------------------- --- TEST _ Privs resource no yes query the user's system permissions SQL> select * from dba_sys_privs where GRANTEE = 'test _ PRIVS '; GRANTEE PRIVILEGE ADM ---------------------------- role --- TEST_PRIVS UNLIMITED TABLESPACE NO can be seen, oracle grants the system permission of unlimited tablespace to the user to query table space SQL> select TABLESPACE_NAME, USERNAME, BYTES, MAX_BYTES from dba_ts_quotas; TABLESPACE_NAME USERNAME BYTES MAX_BYTES ---------- Certificate ---------- indx hr 65536 10485760 sysaux olapsys 16318464-1 users hr 196608-1 sysaux sysman 54460416-1 sysaux dmsys 262144 209715200 10485760 TRANS 0 can be seen for systems with unlimited tablespace Permissions user, it is not reflected in dba_ts_quota. Here I would like to add that when creating a user, if there is no special requirement, you only need to assign the resource and connect roles to the user. SQL> select * from dba_sys_privs where GRANTEE = 'connection'; role privilege adm ------------------------------ success --- connect create session no SQL> grant resource, CONNECT to test_privs; Grant succeeded.