Unlimited Tablespace in Oracle
There are two approaches you can take when you don't want to restrict the amount of disk space assigned to a user.
One approach is to assign that user unlimited quota on all the tablespaces in the database. That approach requires a lot of typing and, as you add tablespaces, you need to remember to assign the user quotas on those tablespaces.
An easier approach is to grant the user the UNLIMITED TABLESPACE system privilege.
This is done with the GRANT command and it allows a user to use any amount of disk space in any tablespace.
Question:
How can I use an unlimited amount of any tablespace?
This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas.
You cannot grant this system privilege to roles.
FOR TRIGGERS:
| Privilege |
Description |
| CREATE TRIGGER |
Create a database trigger in grantee's schema |
| CREATE ANY TRIGGER |
Create database triggers in any schema except SYS |
| ALTER ANY TRIGGER |
Enable, disable, or compile database triggers in any schema except SYS |
| DROP ANY TRIGGER |
Drop database triggers in any schema except SYS |
| ADMINISTER DATABASE TRIGGER |
Create a trigger on DATABASE. (You must also have the CREATE TRIGGER or CREATE ANY TRIGGER privilege.) |
FOR TYPES:
| Privilege |
Description |
| CREATE TYPE |
Create object types and object type bodies in grantee's schema |
| CREATE ANY TYPE |
Create object types and object type bodies in any schema except SYS |
| ALTER ANY TYPE |
Alter object types in any schema except SYS |
| DROP ANY TYPE |
Drop object types and object type bodies in any schema except SYS |
| EXECUTE ANY TYPE |
Use and reference object types and collection types in any schema except SYS, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant EXECUTE ANY TYPE to a role, users holding the enabled role will not be able to invoke methods of an object type in any schema
|
FOR USERS:
| Privilege |
Description |
| CREATE USER |
Create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces,
assign a profile as part of a CREATE USER statement |
| ALTER USER |
Alter any user. This privilege authorizes the grantee to Change another user's password or authentication method,
Assign quotas on any tablespace, Set default and temporary tablespaces, and Assign a profile and default roles |
| BECOME USER |
Become another user. (Required by any user performing a full database import.) |
| DROP USER |
Drop users |
FOR VIEWS:
| Privilege |
Description |
| CREATE VIEW |
Create views in grantee's schema |
| CREATE ANY VIEW |
Create views in any schema except SYS |
| DROP ANY VIEW |
Drop views in any schema except SYS |