Ora-01536：超出了表空间users的空间限量

野性酷女 2021-06-26 16:06 500阅读 0赞

正在开会，同事跑过来说数据库有问题，通讯程序不能入库，赶快获取一条`insert into a values()`语句后在`toad`工具中手动插入，发现报错：`Ora-01536：超出了表空间users的空间限量。`

该表`a`的是用户`A`下的一个大表，表空间是`users`，而非`A`用户的默认表空间。`users`表空间有大约70%的空闲空间，为什么`a`表就不能使用了呢？从网上搜索后终于明白：

`ora-1536` 是指的你建表的那个`user` 所能使用的空间没有了，不是那个表所在的`tablespace` 没有`free space` 了。你需要做的是给那个`user` 对那个`tablespace` 有更多的`space` 可以使用。

解决办法增大能够使用的表空间数或授予无限制的使用权限

ALTER USER A QUOTA 50M ON users;
    
    ALTER USER  A  QUOTA UNLIMITED ON users;

但为什么会出现这样的问题呢，原来设计的时候用户`A`，赋予DBA和`resource`权限，并没有设置表空间限额。但现在怎么会出现这么多的空间限额呢？

会不会是上次更改用户A的默认表空间造成的呀？

上次为了数据库便于维护，将用户A的表从别的表空间移到了表空间中，然后更改表空间`TS_A` 为用户`A`的默认表空间

select 'alter table '||table_name||' move tablespace TS_A ;'
    
    from user_tables
    
    where tablespace_name='LEE_TEST' ;
    
    alter user A DEFAULT TABLESPACE TS_A;

把用户A下的表全部移到表空间TS\_A下，但因为a是一个比较大的表，所以没有移动成功，a的表空间仍然是`users`；

估计跟这次操作有关系 ，改天进行测试。那天进行移动表的存储空间时还造成索引无效的错误，看来这次操作带来负面影响还是真不少。

创建一个用户,分配了400M的表空间,结果在用到13.3M时报错:

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

经查,表空间跟表空间限额两个值是不一样的.  
推测按默认的话oracle应该会给每个用户分配一个默认的表空间限额,具体比例待查,但这比例肯定远小于100%.  
所以说分配了400M的表空间未必能存储400M的数据.

解决办法如下:

查看用户表空间的限额

select * from user_ts_quotas;

`max_bytes`字段就是了  
\-1是代表没有限制,其它值多少就是多少了.

不对用户做表空间限额控制:

GRANT UNLIMITED TABLESPACE TO ***(用户);

这种方式是全局性的.  
或者

alter user ***(用户名) quota unlimited on ***(表空间);

这种方式是针对特定的表空间的.

可以分配自然也可以回收了:

revoke unlimited tablespace from ***(用户)

或者

alter user *** quota 0 on ***

表空间的大小与用户的配额大小是两种不同的概念。表空间的大小是指实际的用户表空间的大小，而配额大小指的是用户指定使用表空间的的大小

把表空间文件增大，还是出现这个问题，用户在使用表空间的同时使用空间的限额，如果超出限制，就算有空的地方，也不会让用户使用。

遇到`ORA-01536`错误，首先要查看用户的表空间的限额

select * from dba_ts_quotas;
    
       select * from user_ts_quotas;

`max_bytes`字段-1是代表没有限制,其它值多少就是多少.

`dba_ts_quotas` ：描述所有用户表空间的限额

`user_ts_quotas` ：描述当前用户表空间的限额。

如果查询结果中`max_bytes`字段不为-1，修改为无限制或者指定的大小。

不对用户做表空间限额控制:

GRANT UNLIMITED TABLESPACE TOuser;

这种方式是全局性的。 或者

alter user  user  quota unlimited on  user_tablespace;

这种方式是针对特定的表空间的.

回收表空间限额控制:

revoke unlimited tablespace from  user;

或者

alter user  user  quota 0 on  user_tablespace;

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

## ORACLE 表和索引迁移表空间 ##

表做空间迁移时，使用如下语句：

例1：`alter table tb_name move tablespace tbs_name;`

索引表空间做迁移，使用如下语句：

例2：`alter index index_name rebuild tablespace tbs_name;`

对于含有`lob`字段的表，在建立时，oracle会自动为`lob`字段建立两个单独的`segment`,一个用来存放数据，另一个用来存放索引，并且它们都会存储在对应表指定的表空间中，而例1：只能移动非`lob`字段以外的数据，所以在对含有`lob`字段的表进行空间迁移，需要使用如下语句：

例3：`alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as(tablesapce tbs_name);`

项目实例：

**表空间迁移**

select 'alter table' ||table_name|| 'move tablespace tbs_name;' table_name from dba_tables where wner='%***%' and table_name like '%***%'

**带lob字段**

select 'alter table' ||table_name|| 'move lob('||index_name||') store as (tablespace tbs_name);' from dba_indexes where wner='%***%' and index_name like '%***%'

**索引表空间**

select 'alter index' ||index_name|| 'rebuild tablespace tbs_name;' index_name from dba_indexes where wner='%***%' and table_name like '%***%'

以上在oracle 的`SQL*Plus Worksheet`中运行，将得出的运行结果再运行一次即可