Oracle – Restrict User to Use USERS Tablespace

oracleoracle-11g-r2oracle-12c

I have a schema 'TEST' that schema has own tablespace 'TEST'. But when I create index on USERS tablespace, it created. How can I restrict to use USERS tablespace in ORACLE database?

CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
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"

It success, I want TEST user can't create any object on USERS tablespace. It has own TEST tablespace. Is there any configuration? Should user use TABLESPACE "TEST" instead of TABLESPACE "USERS" ?

Best Answer

The user TEST may have the UNLIMITED TABLESPACE privilege, or a quota defined on the USERS tablespace. To revoke them:

revoke unlimited tablespace from test;
alter user test quota 0 on users;

If the user had the UNLIMITED TABLESPACE privilege without any quota defined, you need to define a quota on the TEST tablespace, so the user can use it again after the above commands:

alter user test quota unlimited on test;
Related Question