10 things you didn’t know about tablespace quotas
by Brian Fitzgerald
Introduction
DBAs use tablespace quotas to limit where segments can be placed. Sometimes grant and revoke interact with quotas in surprising ways.
Setup
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 31 22:59:29 2022 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> set linesize 32767 SQL> set trimspool on SQL> col segment_name format a30 SQL> col granted_role format a30 SQL> create user U identified by U default tablespace USERS; User created.
1. When you grant role DBA to a user, unlimited tablespace gets granted.
Unlimited tablespace is a separate, special case side effect of granting DBA that is internal to oracle.
SQL> grant dba to U;
Grant succeeded.
SQL> select privilege from dba_sys_privs where grantee = ‘U’;
PRIVILEGE
—————————————-
UNLIMITED TABLESPACE
2. When you revoke DBA from a user, unlimited tablespace gets revoked.
Exercise care when tightening security. Before you revoke DBA from a user, check whether that user owns segments. Compensate by grant tablespace quotas. Failure to grant a tablespace quota will lead to a loss of ability to insert into tables owned by that user.
SQL> revoke dba from U; Revoke succeeded. SQL> select privilege from dba_sys_privs where grantee = 'U'; no rows selected
3. Unlimited tablespace gets revoked even if it had been granted separately.
SQL> grant dba to U; Grant succeeded. SQL> grant unlimited tablespace to U; Grant succeeded. SQL> revoke dba from U; Revoke succeeded. SQL> select privilege from dba_sys_privs where grantee = 'U'; no rows selected
4. If you revoke unlimited tablespace from a user with DBA role, that user keeps DBA role.
SQL> grant dba to U; Grant succeeded. SQL> revoke unlimited tablespace from U; Revoke succeeded. SQL> select granted_role from dba_role_privs where grantee = 'U'; GRANTED_ROLE ------------------------------ DBA
5. You cannot grant unlimited tablespace privilege to a role.
SQL> create role R; Role created. SQL> grant unlimited tablespace to R; grant unlimited tablespace to R * ERROR at line 1: ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
No role has unlimited tablespace privilege, not even DBA.
6. You can revoke a quota from that a segment needs the quota.
This is seldom done intentionally. If you do, the segment cannot extend.
SQL> alter user U quota unlimited on USERS; User altered. SQL> create table U.T ( N number) segment creation immediate; Table created. SQL> SQL> select grantee, privilege from dba_sys_privs 2 where grantee = 'U' and privilege = 'UNLIMITED TABLESPACE'; no rows selected SQL> SQL> select segment_name, bytes, extents from dba_segments 2 where owner = 'U' 3 and segment_name = 'T' 4 and segment_type = 'TABLE'; SEGMENT_NAME BYTES EXTENTS ------------------------------ ---------- ---------- T 65536 1 SQL> alter user U quota 0 on USERS; User altered. SQL> insert into U.T select level from dual connect by level <= 1; 1 row created. SQL> insert into U.T select level from dual connect by level <= 1000000; insert into U.T select level from dual connect by level <= 1000000 * ERROR at line 1: ORA-01536: space quota exceeded for tablespace 'USERS'
7. Without a quota, you cannot move a table.
Moving a table can save space, but with an insufficient quota, you can’t do it
SQL> alter table U.T move; alter table U.T move * ERROR at line 1: ORA-01536: space quota exceeded for tablespace 'USERS'
8. When you revoke unlimited tablespace you also revoke all limited quotas.
SQL> grant unlimited tablespace to U; Grant succeeded. SQL> alter user U quota 10g on USERS; User altered. SQL> create tablespace U2; Tablespace created. SQL> alter user U quota unlimited on U2; User altered. SQL> SQL> select username, tablespace_name, max_blocks 2 from dba_ts_quotas where username = 'U'; U TABLESPACE_NAME MAX_BLOCKS - ------------------------------ ---------- U USERS 1310720 U U2 -1 SQL> SQL> revoke unlimited tablespace from U; Revoke succeeded. SQL> SQL> select username, tablespace_name, max_blocks 2 from dba_ts_quotas where username = 'U'; U TABLESPACE_NAME MAX_BLOCKS - ------------------------------ ---------- U U2 -1
Notice that the limited quota on USERS got revoked, but the unlimited quota on U2 remains. That’s not exactly what the manual says: “If you later revoke the privilege, then you must explicitly grant quotas to individual tablespaces.”
Because revoking DBA revokes unlimited tablespace, it follows that revoking DBA revokes limited (finite) quotas.
9. You can grant a quota greater than 2 TB.
SQL> alter user U quota 10T on U2;
User altered.
The manual says “The maximum amount of space that you can assign for a tablespace is 2 TB.”
10. A quota could exceed the tablespace’s maximum size
SQL> select username, tablespace_name, max_blocks 2 from dba_ts_quotas where username = 'U'; U TABLESPACE_NAME MAX_BLOCKS - ------------------------------ ---------- U U2 1342177280 SQL> select sum(maxblocks) from dba_data_files 2 where tablespace_name = 'U2'; SUM(MAXBLOCKS) -------------- 4194302
So here are 10 things you did not know about tablespace quotas. Here is one more:
Bonus: A user running import does not require a quota.
The owner of the segment needs the quota, not the user running import. That’s how it works. If you pre-create the user, you must grant the quota ahead of time. If the segment owner has no quota, then you will get ORA-01950: no privileges on tablespace.
SQL> create user U identified by U default tablespace USERS;
User created.
SQL> alter user U quota unlimited on USERS;
User altered.
SQL> create table U.T ( N number) segment creation immediate;
Table created.
$ cat exp.u.par
directory=d
dumpfile=exp.u.t.dmp
logfile=exp.u.t.log
reuse_dumpfiles=true
tables=
u.t
$ expdp "'/ as sysdba'" parfile=exp.u.par
Export: Release 19.0.0.0.0 - Production on Thu Sep 1 00:16:02 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" parfile=exp.u.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "U"."T" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u99/exp/d/exp.u.t.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Sep 1 00:19:07 2022 elapsed 0 00:03:05
SQL> drop user U cascade;
User dropped.
SQL> create user U identified by U default tablespace USERS;
User created.
$ cat imp.u.par
directory=d
dumpfile=exp.u.t.dmp
logfile=imp.u.t.log
$ impdp "'/ as sysdba'" parfile=imp.u.par
Import: Release 19.0.0.0.0 - Production on Thu Sep 1 00:24:48 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" parfile=imp.u.par
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"U"."T" failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'
Failing sql is:
CREATE TABLE "U"."T" ("N" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10
PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Sep 1
00:24:57 2022 elapsed 0 00:00:08
Cleanup
SQL> drop user U cascade; User dropped. SQL> drop role R; Role dropped. SQL> drop tablespace U2 including contents and datafiles; Tablespace dropped.
Conclusion
Exercise care when revoking DBA or unlimited tablespace. Be sure to compensate by issuing needed quotas. Otherwise, users or applications will get ORA-01536: space quota exceeded.