ORA-01536
Tried to use CTAS to make a table in a tablespace, but it failed with ORA-01536.
SQL> create table t1 tablespace users as select * from sh.sales;
create table t1 tablespace users as select * from sh.sales
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-01536 means that the user has no right to use the tablespace or its used space has exceeded the allowable quota on that tablespace.
Let's see current quota on all tablespace.
SQL> column tablespace_name format a20;
SQL> column "QUOTA(GB)" format a10;
SQL> select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from dba_ts_quotas where username = 'HR';
TABLESPACE_NAME QUOTA(GB)
-------------------- ----------
EXAMPLE UNLIMITED
USERS 100
As we can see, there's a quota limit 100GB imposed on tablespace USERS.
Solution
To solve ORA-01536, we should grant more quota on the tablespace to the user, say, 200GB in this case.
SQL> show user
USER is "SYSTEM"
SQL> alter user hr quota 200g on users;
User altered.
Then we CTAS onto the tablespace again.
SQL> create table t1 tablespace users as select * from sh.sales;
Table created.
We solved it.