今天在测试库上创建一张表时报ORA-01536: space quota exceeded for tablespace错误。如下：

SQL> conn scott/scott
Connected.
SQL>
SQL> create table test_lock (id int primary key,name varchar2(50));
create table test_lock (id int primary key,name varchar2(50))
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

查询了一下该错误：
Error: ORA 1536
Text:   space quota exceeded for tablespace ""
-------------------------------------------------------------------------------
Cause: The space quota in the tablespace is already used up and the operation
        attempted the creation of a new extent in the tablespace.
Action: Either
        Drop unnecessary objects in the tablespace to reclaim space.
        Ask the database administrator to increase the tablespace quota.

该错误的处理方式：
The DBA must grant additional resource quota on the tablespace to that user.

One or all of the following may fix the problem:

1) Increase the tablespace quota allocated to that user by using the following command:
ALTER USER QUOTA [K/M] ON

2) Grant unlimited quota to the user on that tablespace by using the following command:
ALTER USER QUOTA UNLIMITED ON

If the problem occurs even after executing the above steps, then run the following command:
GRANT RESOURCE TO

处理过程，给scott账户分配500m使用空间在users表空间
SQL> alter user scott quota 500m on users;

User altered.

测试：
SQL> conn scott/scott
Connected.
SQL>
SQL>
SQL> create table test_lock (id int primary key,name varchar2(50));

Table created.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST_LOCK

SQL>
SQL> insert into test_lock values (1,'zhangsan');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from test_lock;

        ID NAME
---------- --------------------------------------------------
         1 zhangsan

SQL>

测试成功。

该问题只是一个小错误，这里记录一下。

ITPub博客

ORA-01536: space quota exceeded for tablespace

最新文章