Oracle user table space quota description

I. Solve the problem:

Solve the problem that the tablespace is large and the total display space is insufficient.
The ORA-01536: space quota exceeded for table space 'aaa'
Ii. Three solutions:
(1) alter user USERNAME quota 100 M on TABLESPACENAME;
(2) alter user USERNAME quota unlimited on TABLESPACENAME;
(3) grant unlimited tablespace to USERNAME;
Quota is designed to restrict the use of table space.
For example, if you restrict quota of user a in tablespace a to 10 MB, no matter how much space your tablespace a has after the data volume of user a in tablespace a reaches 10 MB, a can no longer use tablespace.
So you need
Alter user a quota 1000 M on;
Alter user a quota unlimited on;
Grant unlimited tablespace to
You can query dba_ts_quotas to obtain related information.

Ii. Quota description

The quota size refers to the size of the tablespace specified by the user. As mentioned in section 1.1, by default, users have no quota for all tablespaces, that is, they are not limited by space. Check the creation scripts of several users for verification:

CREATE USER SYSTEM

Identified by <password>

DEFAULT TABLESPACE SYSTEM

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

Account unlock;

-- 2 Roles for SYSTEM

GRANT AQ_ADMINISTRATOR_ROLE to system with admin option;

Grant dba to system with admin option;

Alter user system default role all;

-- 5 System Privileges for SYSTEM

Grant global query rewrite to system;

Grant create materialized view to system;

Grant create table to system;

Grant unlimited tablespace to system with admin option;

Grant select any table to system;

CREATE USER DAVE

Identified by <password>

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

Account unlock;

-- 2 Roles for DAVE

Grant connect to dave;

Grant resource to dave;

Alter user dave default role all;

-- 1 System Privilege for DAVE

Grant unlimited tablespace to dave;

From the two scripts, the unlimited tablespace permission is granted to the user by default. This is specified during creation. After a user is created, we can modify the user quota.

User quota operation instructions

1. Specify the quota when creating a user

SQL> conn/as sysdba;

Connected.

SQL> create user anqing identified by anqing default tablespace users temporary tablespace temp quota 10 M on users;

User created.

Query user quota information:

SQL> select tablespace_name, username, max_bytes from DBA_TS_QUOTAS where username = 'anqing ';

TABLESPACE_NAME USERNAME MAX_BYTES

--------------------------------------------------

Users anqing 10485760

2. Change the user's tablespace quota:

Do not control the table space quota for users:

SQL> grant unlimited tablespace to anqing;

Grant succeeded.

This method is global, that is, to modify the quota of multiple tablespaces.

If you want to modify a specific table space, you can use the following SQL statement:

SQL> alter user anqing quota unlimited on users;

User altered.

View quota:

SQL> select tablespace_name, username, max_bytes from DBA_TS_QUOTAS where username = 'anqing ';

TABLESPACE_NAME USERNAME MAX_BYTES

--------------------------------------------------

Users anqing-1

In this case, max_bytes is-1, which is unrestricted.

3. Reclaim the user's table space quota:

The same two methods,

Global:

SQL> revoke unlimited tablespace from anqing;

Revoke succeeded.

When viewing the quota, there is no relevant information:

SQL> select tablespace_name, username, max_bytes from DBA_TS_QUOTAS where username = 'anqing ';

No rows selected

For a specific tablespace:

SQL> alter user anqing quota 0 on users;

User altered.