oracle+超出表空间限额,ORA-01536: 超出表空间 'USERS' 的空间限额

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> conn mytest/mytest@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as mytest

SQL> create table t_clob(column1 clob,column2 number(18));

create table t_clob(column1 clob,column2 number(18))

ORA-01536: 超出表空间 'USERS' 的空间限额

/*注释：查看一下当前用户mytest对users表空间的使用配额是多少*/

SQL> select * from user_ts_quotas;

TABLESPACE_NAMEBYTESMAX_BYTESBLOCKS MAX_BLOCKS DROPPED

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

USERS65536080 NO

/*注释：太奇怪了，竟然是0

分配指定配额给mytest用户。

*/

SQL> alter user mytest quota 50m on users;

alter user mytest quota 50m on users

ORA-01031: 权限不足

/*注释：权限不足，切换到sys用户下*/

SQL> conn sys / as sysdba;

Not logged on

SQL> conn sys/ as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> alter user mytest quota 50m on users;

User altered

SQL> select * from user_ts_quotas;

TABLESPACE_NAMEBYTESMAX_BYTESBLOCKS MAX_BLOCKS DROPPED

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

/*注释：切换回mytest用户*/

SQL> conn mytest/mytest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as mytest

/*注释：查看一下当前用户mytest对users表空间的使用配额是多少*/

SQL> select * from user_ts_quotas;

TABLESPACE_NAMEBYTESMAX_BYTESBLOCKS MAX_BLOCKS DROPPED

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

USERS655365242880086400 NO

/*注释：已经有50m的使用权，继续建表*/

SQL> create table t_clob(column1 clob,column2 number(18));

Table created

/*注释：创建成功*/

SQL> desc t_clob;

NameTypeNullable Default Comments

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

COLUMN1 CLOBY

COLUMN2 NUMBER(18) Y

/*注释：查看一下users表空间大小*/

SQL> select round(max_extents/1024/1024/1024,2) || 'G' from user_tablespaces where tablespace_name = 'USERS';

ROUND(MAX_EXTENTS/1024/1024/10

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

2G

/*注释：竟然有2G，那为什么会报上面的错误，ORA-01536: 超出表空间 'USERS' 的空间限额？

*/

/*分析：oracle的表空间与表空间限额是两个不同的值，用户默认分配到一定限额的使用权，超过限额就会报错，需要指定用户限额为某一个值，也可以无限制。*/

SQL> conn sys/ as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> alter user mytest quota unlimited on users;

User altered

SQL> conn mytest/mytest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as mytest

SQL> select * from user_ts_quotas;

TABLESPACE_NAMEBYTESMAX_BYTESBLOCKS MAX_BLOCKS DROPPED

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

USERS262144-132-1 NO