Month: January 2024

ORA-01950: no privileges on tablespace ‘USERS’

SQL> create table test ( id number );
create table test ( id number )
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’

SOLUTION

SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='SCOTT';

no rows selected
SQL> alter user scott default tablespace users quota 10m on users;



User altered.

SQL>  create table test ( id number );



Table created.
SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='SCOTT';



TABLESPACE_NAME      USERNAME       MAX_BYTES      BYTES

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

USERS                SCOTT      10485760      65536

How to Drop a Pluggable Database

Close the Pluggable Database

SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE INSTANCES=ALL;

Drop PDB but Keep Datafiles

SQL>  DROP PLUGGABLE DATABASE PDB1 keep datafiles;

Drop PDB Including Datafiles

SQL>  DROP PLUGGABLE DATABASE PDB1 including datafiles;

ORA-65036: pluggable database not open in required mode

When clone a pluggable database from remote CDB database, the following error occurs:

SQL> create pluggable database PDB1 from PDB1@remote_db_link;
create pluggable database PDB1 from PDB1@remote_db_link
*
ERROR at line 1:
ORA-17628: Oracle error 65036 returned by remote Oracle server
ORA-65036: pluggable database not open in required mode

SOLUTION

Make sure source PDB is opened on all instances, otherwise on source CDB:

SQL>  alter pluggable database pdb1 open instances=all;



Pluggable database altered.

If still not working, then check “LOCAL_UNDO_ENABLED” enabled or not.

What is SYSRAC ?

The SYSRAC administrative privilege is used only by the Oracle agent of Oracle Clusterware to connect to the database using operating system authentication.

Unlike SYSDBA, SYSDG, SYSBACKUP and SYSKM , the SYSRAC privilege only allows OS authentication by the Oracle agent of Oracle Clusterware. Password files and strong authentication cannot be used with the SYSRAC privilege.

SQL> grant sysrac to testuser container=all;

grant sysrac to testuser container=all

*

ERROR at line 1:

ORA-28190: SYSRAC administrative privilege cannot be granted to other users

The administrative privilege SYSRAC is not supported in the password file.

When you create an Oracle database, the following users are automatically created to facilitate separation of duties for database administrators: SYSBACKUP, SYSDG, SYSKM, and SYSRAC.

  • SYSBACKUP facilitates Oracle Recovery Manager (RMAN) backup and recovery operations either from RMAN or SQL*Plus.
  • SYSDG facilitates Data Guard operations. The user can perform operations either with Data Guard Broker or with the DGMGRL command-line interface.
  • SYSKM facilitates Transparent Data Encryption keystore operations.
  • SYSRAC facilitates Oracle Real Application Clusters (Oracle RAC) operations by connecting to the database by the Clusterware agent on behalf of Oracle RAC utilities such as SRVCTL.
  • The SYSRAC administrative privilege cannot be granted to database users and is not supported in a password file. The SYSRAC administrative privilege is used only by the Oracle agent of Oracle Clusterware to connect to the database using operating system authentication.

SYSBACKUP, SYSDG, SYSKM, and SYSRAC user accounts cannot be dropped.

Could not load program sqlplus:Dependent module libsqlplus.so could not be loaded. Could not load module libsqlplus.so

Trying to run sqlplus from Oracle 19c client on AIX, the following errors occur:

$ sqlplus

Could not load program sqlplus:

        Dependent module libsqlplus.so could not be loaded.

Could not load module libsqlplus.so.

System error: No such file or directory

SOLOTION

$ oslevel

7.1.0.0


$ export LIBPATH=$LIBPATH:/u01/oracle/product/19.0.0/client_1/lib

It seems AIX does not use LD_LIBRARY_PATH.