clear_vir01: "no privileges on tablespace 'TSnx'

Description:  

Our clear_vir01 log has the following error messages multiple times:

ORA-01950: no privileges on tablespace 'TS1D'
ORA-01950: no privileges on tablespace 'TS2D'

and no vir01 tables are created.

Resolution:  

We saw the following in this case:

aleph_admin@ALEPH20> select TABLESPACE_NAME, BYTES, BLOCKS from dba_ts_quotas where username='VIR01';

TABLESPACE_NAME BYTES BLOCKS
------------------------------ ---------- ----------
SYSAUX 0 0
USERS 0 0
TSLOB 0 0
LOG 0 0
TS0 0 0
TS1 0 0


So we did this to correct it:

> sqlplus aleph_admin/aleph_admin

ALTER USER "VIR01" QUOTA unlimited on "TS1D";
ALTER USER "VIR01" QUOTA unlimited on "TS1X";
ALTER USER "VIR01" QUOTA unlimited on "TS2D";
ALTER USER "VIR01" QUOTA unlimited on "TS2X";
ALTER USER "VIR01" QUOTA unlimited on "TS3D";
ALTER USER "VIR01" QUOTA unlimited on "TS3X";
ALTER USER "VIR01" QUOTA unlimited on "TS4D";
ALTER USER "VIR01" QUOTA unlimited on "TS4X";

(Though the error is for TS1D and TS2D only, the privileges should be set for all tablespaces.)

• Article last edited: 04-May-2016