CODEWITHSUNDEEP
oracle11g
Bibh koutsopoulou
Bibh koutsopoulou

Reputation: 11

How to resolve ORA-1031: insufficient privileges for altering tablespace

I am trying to change the tablespace on my user and what i am getting is insufficien privileges.Does anybody know how to solve this problem? Example:

SQL> alter user test_
  2  default tablespace users temporary tablespace temp
  3  quota 800M on users
  4  /
alter user test_
*
ERROR at line 1:
ORA-01031: insufficient privileges

Upvotes: 1

Views: 4719

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

As Documentation says:

You must have the ALTER USER system privilege. However, you can change your own password without this privilege.

So, if you connect as a privileged user (such as SYS) and grant it to, for example, SCOTT, then SCOTT will be able to do that:

SQL> connect sys/syspwd@xe as sysdba
Connected.

SQL> grant alter user to scott;

Grant succeeded.

SQL> connect scott/tiger@xe
Connected.

SQL> alter user scott default tablespace users temporary tablespace temp quota unlimited on users;

User altered.

SQL>

Upvotes: 1

Related Questions