ORA-01950: no privileges on tablespace xxxx, noprivileges

ORA-01950: no privileges on tablespace xxxx, noprivileges

Case scenario:

After creating a tablespace, TBS_MARKET_DAT wants to place the archived data in the tablespace.

SQL> CREATE TABLESPACE TBS_MARKET_DAT

  2  DATAFILE '/u04/oradata/gps/bookt_data_arch_01.dbf'

  3  SIZE 100M

  4  AUTOEXTEND ON

  5  NEXT 10M

  6  MAXSIZE 4G

  7  EXTENT MANAGEMENT LOCAL

  8  SEGMENT SPACE MANAGEMENT AUTO ONLINE ;

User A (the default tablespace is TBS_ODS_DAT) when creating A table, specify that the table is stored in the tablespace TBS_MARKET_DAT, encounter ORA-01950: no privileges on tablespace 'tbs _ MARKET_DAT'

 

Cause analysis:

As shown below, you are not authorized to allocate space in the specified tablespace. You only need to grant the tablespace permission to this user.

[Oracle @ DB-Server ~] $ Oerr ora 1, 01950
01950,000 00, "no privileges on tablespace '% S '"
// * Cause: User does not have privileges to allocate an extent in
// Specified tablespace.
// * Action: Grant the user the appropriate system privileges or grant the user
// Space resource on the tablespace.

Solution:

 

Method 1: grant the user an UNLIMITED quota for the tablespace.

 

SQL> ALTER USER ETL QUOTA UNLIMITED ON TBS_MARKET_DAT;

User altered.

SQL>

Method 2: After the resource role is re-authorized to the user, you can create a table.

 

SQL> GRANT RESOURCE TO ETL;