ORA-01950: no privileges on tablespace 'USERS'

 In this article, we will try to resolve error ORA-01950: no privileges on tablespace 'USERS' received during insert command,

I was able to create table under schema TECHNO_USER, but I was not able to insert data into it (as shown below)

 SQL> insert into techno_user.dummy values (10);

insert into techno_user.dummy values (10)

                        *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

Then I checked default tablespace and quota on default tablespace or any other tablespace 

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

TECHNO_USER                    USERS

 

SQL> select distinct tablespace_name, USERNAME from dba_ts_quotas where USERNAME='TECHNO_USER';

 

no rows selected

 

SQL>

 

As per output of above commands, we can say this user does not have quota on any tablespace, insert/update command will require quota on tablespace, so I have granted quota on tablespace USERS with below command 

SQL> alter user TECHNO_USER quota unlimited on USERS;

 

User altered.

 

SQL> select distinct tablespace_name, USERNAME from dba_ts_quotas where USERNAME='TECHNO_USER';

 

TABLESPACE_NAME                USERNAME

------------------------------ ------------------------------

USERS                          TECHNO_USER

 

Also read changing default tablespace

 

After doing this, I was able to resolve error and was able to insert data in the table

  

SQL> insert into techno_user.dummy values (10);

 

1 row created.

 

SQL> insert into techno_user.dummy values (20);

 

1 row created.

 

SQL>

Read More

Changing default tablespace for user in oracle

In my oracle database, one of the users was having its default tablespace as SYSAUX, and having default tablespace as SYSAUX is not a good idea in the oracle database, here we are going to discuss changing the default tablespace for oracle user

Let’s see how to check default tablespace using dba_users

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

-------------------- ------------------------------

TECHNO_USER          SYSAUX

 

Here we can see, default tablespace is SYSAUX, now change it USERS tablespace

SQL> alter user TECHNO_USER default tablespace USERS;

 

User altered.

 

verify the result

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

-------------------- ------------------------------

TECHNO_USER          USERS

 

In this we can simply alter default tablespace for any user.

Read More

Lock and Unlock table stats in oracle database

There are unlimited features provided by oracle with an upgraded version, one of the feature we are going to discuss is stats lock and unlock in the oracle database,

Assume the application team has set up stats gather job from application end but that causing issue to the database like CPU/Memory utilization getting high during the job execution, being a DBA you can ask them to stop/disable the job but in case if it’s not feasible then you can lock stats on tables and unlock whenever required.

Another scenario- you have locked a good execution plan for ETL/Job run and that should not change with stats gathered, then you can lock the stats for the table.

Below we will discuss how to do this exactly, I have a table called LOCATIONS under user TECHNO, I do not want to gather stats on the same, in the below query, if stats are locked, the output will be ALL if not then it will be blank, you can stats are not locked

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

 

 

SQL>

Now I will lock stats and execute the query again

SQL> exec dbms_stats.lock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

ALL

 

SQL>

Now I will try to gather stats on the same table, it should throw error 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

BEGIN dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS'); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 40751

 [Also Read - All About Database links]

Now we will see how to unlock stats and gather stats

SQL> exec dbms_stats.unlock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL>

Read More