Shulou(Shulou.com)06/01 Report--

First, solve the problem:

Solve the problem of large table space and insufficient display space.

ORA-01536:space quota exceeded for table space 'AAA'

Second, three solutions:

(1) alter user USERNAME quota 100m on TABLESPACENAME

(2) alter user USERNAME quota unlimited on TABLESPACENAME

(3) grant unlimited tablespace to USERNAME

Quota is designed to limit users' use of tablespaces

For example, if you limit the quota of user an in tablespace a to 10m, when user a has 10m data in tablespace a, no matter how much space there is in your tablespace a, tablespace a can no longer be used by user a.

So you need

Alter user a quota 1000M on a

Alter user a quota unlimited on a

Grant unlimited tablespace to a

You can query dba_ts_quotas for relevant information.

II. Quota description

Quota size refers to the size that the user specifies to use the tablespace. As mentioned in Section 1.1, by default, users have no quota for all tablespaces, that is, they are not limited by space. Check out the creation scripts of several users to verify:

CREATE USER SYSTEM

IDENTIFIED BY

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

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

Judging from these two scripts, by default, the user is given the right to unlimited tablespace. This is specified at the time of creation, and after our users have been created, we can also modify the user quota.

Operation instructions about the user's quota

1. Specify a quota when you create a user

SQL > conn / as sysdba

Connected.

SQL > create user anqing identified by anqing default tablespace users temporary tablespace temp quota 10m 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

two。 Change the user's tablespace limit:

No tablespace quota control for users:

SQL > grant unlimited tablespace to anqing

Grant succeeded.

This approach is of an overall importance. That is, modify the quota for all tablespaces with more users.

If we want to change a specific, that is, a specific tablespace for the user, we can use the following SQL:

SQL > alter user anqing quota unlimited on users

User altered.

View quotas:

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

TABLESPACE_NAME USERNAME MAX_BYTES

USERS ANQING-1

At this time, the max_bytes is-1, that is, there is no restriction.

3. Recycle user quotas for tablespaces:

The same two ways

Global:

SQL > revoke unlimited tablespace from anqing

Revoke succeeded.

After checking 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 particular tablespace:

SQL > alter user anqing quota 0 on users

User altered.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.