2007. 12. 11. 16:58 오라클
ORA-1536: When Inserting Into a Table
Problem Description:
====================
You are inserting into a table and receive ORA-1536 errors.
Ora-1536 "space quota exceeded for tablespace '%s'"
Cause:
The user is attempting to perform an operation which
Requires the creation of a new extent in a tablespace.
The user has already reached his quota of space in the tablespace.
Action:
A privileged user must grant additional resource quota on
the tablespace to the user that owns the table.
Solution Description:
====================
One or all of the following solutions may fix the problem:
1) Increase the tablespace quota allocated to that user by using the following
command:
ALTER USER <username> QUOTA <integer> [K/M] ON <tablespacename>
2) Grant unlimited quota to the user on that tablespace by using the following
command:
ALTER USER <username> QUOTA UNLIMITED ON <tablespacename>
If the problem occurs even after executing the above steps, then run the
following command:
GRANT RESOURCE TO <username>
Solution Explanation
====================
The problem is not caused by the lack of permissions from the users who are
using the application. The problem is due to the lack of permissions for
the owner of the object. By granting the owner of the table the Unlimited
Tablespace privilege (or sufficient quota on the particular tablespace),
the users were able to insert/update the table.
If you Grant the RESOURCE role, the Unlimited Tablespace privilege
will be included as well.
Examples:
====================
Please verify:
**You are the owner of the table with unlimited quota on the
tablespaces for both the table and the index.
Command:
Select OWNER, TABLESPACE_NAME, TABLE_NAME From dba_Tables Where
TABLESPACE_NAME =’<the_tablespace_the_error_is_occuring_on>’;
- Check the tablespace quotas for the schema under which the table is
being created, not under the user who is logged on as.
For example, if logged on as user SYSTEM and creating a table
scott.test, check for the quotas and system privileges such as unlimited
tablespace given to the user scott.
Tablespace quotas for a user can be checked in DBA_TS_QUOTAS.
A value of -1 in the column MAX_BYTES means the user has unlimited
tablespace quota on that tablespace.
Any system privileges give to a user can be verified in DBA_SYS_PRIVS or
USER_SYS_PRIVS.
In addition, please research if the table also has indexes owned by another
user (non-owner schema). If so, drop and recreate the indexes as the table owner.
ALSO VERIFY:
** The owner of the object has been granted Unlimited Tablespace
privilege. Even with autoextend enabled, if a particular user was created
with a quota limit on this tablespace, then when this limit is reached,
the user doesn't have any remaining quota on the tablespace.
You may wish to do the following to verify that the user's quota isn't
unlimited:
Command:
select username, tablespace_name, bytes, max_bytes
from dba_ts_quotas
where username = '<the_user_getting_the_error>'
and tablespace_name = '<the_tablespace_the_error_is_occuring_on>';
For example:
SQL> select username, tablespace_name, bytes, max_bytes
2 from dba_ts_quotas
3 where username = 'ERIC';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ---------- ----------
ERIC USERS 1048576 1048576
If you see a value for MAX_BYTES for this user,
then there is a limit on how much of the tablespace they can use.
You will likely see a value for BYTES that is equal to that of
MAX_BYTES for the user that encountered the ORA-01536 error.
If a user has unlimited quota, then MAX_BYTES will be (-1).
For example:
SQL> select username, tablespace_name, bytes, max_bytes
2 from dba_ts_quotas
3 where username = 'BOB';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ---------- ----------
BOB BOB 20480 -1
You can alter the user that encountered the problem to either give
them more quota on the tablespace or to give them unlimited quota on
the tablespace.
For example:
SQL> alter user eric
2 quota unlimited on users;
User altered.
SQL> select username, tablespace_name, bytes, max_bytes
2 from dba_ts_quotas
3 where username = 'ERIC';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ---------- ----------
ERIC USERS 1048576 -1
References:
=====================
Note 270582.1 ORA-01536, when enough space is available
====================
You are inserting into a table and receive ORA-1536 errors.
Ora-1536 "space quota exceeded for tablespace '%s'"
Cause:
The user is attempting to perform an operation which
Requires the creation of a new extent in a tablespace.
The user has already reached his quota of space in the tablespace.
Action:
A privileged user must grant additional resource quota on
the tablespace to the user that owns the table.
Solution Description:
====================
One or all of the following solutions may fix the problem:
1) Increase the tablespace quota allocated to that user by using the following
command:
ALTER USER <username> QUOTA <integer> [K/M] ON <tablespacename>
2) Grant unlimited quota to the user on that tablespace by using the following
command:
ALTER USER <username> QUOTA UNLIMITED ON <tablespacename>
If the problem occurs even after executing the above steps, then run the
following command:
GRANT RESOURCE TO <username>
Solution Explanation
====================
The problem is not caused by the lack of permissions from the users who are
using the application. The problem is due to the lack of permissions for
the owner of the object. By granting the owner of the table the Unlimited
Tablespace privilege (or sufficient quota on the particular tablespace),
the users were able to insert/update the table.
If you Grant the RESOURCE role, the Unlimited Tablespace privilege
will be included as well.
Examples:
====================
Please verify:
**You are the owner of the table with unlimited quota on the
tablespaces for both the table and the index.
Command:
Select OWNER, TABLESPACE_NAME, TABLE_NAME From dba_Tables Where
TABLESPACE_NAME =’<the_tablespace_the_error_is_occuring_on>’;
- Check the tablespace quotas for the schema under which the table is
being created, not under the user who is logged on as.
For example, if logged on as user SYSTEM and creating a table
scott.test, check for the quotas and system privileges such as unlimited
tablespace given to the user scott.
Tablespace quotas for a user can be checked in DBA_TS_QUOTAS.
A value of -1 in the column MAX_BYTES means the user has unlimited
tablespace quota on that tablespace.
Any system privileges give to a user can be verified in DBA_SYS_PRIVS or
USER_SYS_PRIVS.
In addition, please research if the table also has indexes owned by another
user (non-owner schema). If so, drop and recreate the indexes as the table owner.
ALSO VERIFY:
** The owner of the object has been granted Unlimited Tablespace
privilege. Even with autoextend enabled, if a particular user was created
with a quota limit on this tablespace, then when this limit is reached,
the user doesn't have any remaining quota on the tablespace.
You may wish to do the following to verify that the user's quota isn't
unlimited:
Command:
select username, tablespace_name, bytes, max_bytes
from dba_ts_quotas
where username = '<the_user_getting_the_error>'
and tablespace_name = '<the_tablespace_the_error_is_occuring_on>';
For example:
SQL> select username, tablespace_name, bytes, max_bytes
2 from dba_ts_quotas
3 where username = 'ERIC';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ---------- ----------
ERIC USERS 1048576 1048576
If you see a value for MAX_BYTES for this user,
then there is a limit on how much of the tablespace they can use.
You will likely see a value for BYTES that is equal to that of
MAX_BYTES for the user that encountered the ORA-01536 error.
If a user has unlimited quota, then MAX_BYTES will be (-1).
For example:
SQL> select username, tablespace_name, bytes, max_bytes
2 from dba_ts_quotas
3 where username = 'BOB';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ---------- ----------
BOB BOB 20480 -1
You can alter the user that encountered the problem to either give
them more quota on the tablespace or to give them unlimited quota on
the tablespace.
For example:
SQL> alter user eric
2 quota unlimited on users;
User altered.
SQL> select username, tablespace_name, bytes, max_bytes
2 from dba_ts_quotas
3 where username = 'ERIC';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
-------------------- -------------------- ---------- ----------
ERIC USERS 1048576 -1
References:
=====================
Note 270582.1 ORA-01536, when enough space is available
