-- Get the current tablespace quota information of an user
set lines 299
select TABLESPACE_NAME,BYTES/1024/1024 "UTILIZIED_SPACE" ,MAX_BYTES/1024/1024 "QUOTA_ALLOCATED" from dba_ts_quotas where username='&USER_NAME';
TABLESPACE_NAME UTILIZIED_SPACE QUOTA_ALLOCATED
------------------------------ --------------------------- ------------------------
USERS .0625 1024
--- Change the tablespace quota for the user to 5G
ALTER USER SCOTT QUOTA 5G ON USERS;
--- Grant unlimited tablespace quota:
ALTER USER SCOTT QUOTA UNLIMITED ON USERS;
View Privileges granted to an user
-- System privileges granted to an user ( scott)
SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';
-- Roles granted to an user ( scott)
SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';
-- Object privileges granted to an user ( SCOTT)
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';
-- Column specific privileges granted
SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';
Grant table/column privilege to user
-- Table privileges
GRANT READ ANY TABLE TO SCOTT;
GRANT SELECT ANY TABLE TO SCOTT;
GRANT INSERT, UPDATE, DELETE ON TESTUSER1.EMPTABL on SCOTT;
GRANT ALL ON TESTUSER1.EMPTABL on SCOTT;
-- Grant privilege on few columns of a table
--Only INSERT,UPDATE can be granted at COLUMN level.
GRANT insert (emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
GRANT UPDATE(emp_id) ON TESTUSER1.EMPTABL TO SCOTT;