Saturday, 24 March 2018

ORA-01950: no privileges on tablespace 'USERS'

Issue: ORA-01950: no privileges on tablespace 'USERS'

Scenario: I have created a new Schema GSMUSER. In that, I have created a table location_details using the below statement.

CREATE TABLE location_details (
    id             NUMBER(10),
    country        VARCHAR2(20),
    name           VARCHAR2(20),
    abbreviation   VARCHAR2(20),
    area           VARCHAR2(20),
    largest_city   VARCHAR2(20),
    capital        VARCHAR2(20),
    PRIMARY KEY ( id )
);

Now when I tried insert records into location_details I am getting the below error.

Error starting at line : 1 in command -
INSERT INTO location_details (
    id,
    country,
    name,
    abbreviation,
    area,
    largest_city,
    capital
) VALUES (
    '1',
    'India',
    'Meghalaya',
    'IN',
    '12345789SFT',
    'Shillong',
    'Shillong'
)
Error report -
ORA-01950: no privileges on tablespace 'USERS'

Cause of the issue: This is because we cannot insert data because we have a quota of 0 on the tablespace

Solution: Thus, we need to login to the ADMIN account and execute the below command:

First, check the TABLESPACE_NAME for USERS using the below query:
SELECT tablespace_name,status, contents FROM dba_tablespaces;
Now as a fix to ORA-01950: no privileges on tablespace 'USERS', execute the below statement.
ALTER USER gsmuser QUOTA UNLIMITED ON users;
Now try to insert the record in location_details table, and we can see that it can inserted successfully.
Hence the solution to the requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.