Insufficient Privileges after creating Pluggable Database

All we need is an easy explanation of the problem, so here it is.

I have to confess I have no idea about this pluggable database thing. I have experience in PostgresSQL, MySQL, SQL Server, but Oracle has me defeated.

I have Oracle 18c running in a Docker image. I want to create a pluggable database called bookshop. Following some instructions, I ran the following:

CREATE PLUGGABLE DATABASE bookshop ADMIN USER pdb_adm IDENTIFIED BY Oradoc_db1
    file_name_convert=('/opt/oracle/oradata/XE/pdbseed','/opt/oracle/oradata/XE/BOOKSHOP');

Next in the instructions, I run:

ALTER PLUGGABLE DATABASE xepdb2 OPEN READ WRITE;

except that I can’t. I get the message:

ORA-01031: insufficient privileges.

I then try:

DROP PLUGGABLE DATABASE bookshop;

and I get the same message.

When I try:

SELECT vp.name, vp.open_mode
FROM v$pdbs vp;

I get

NAME OPEN_MODE
PDB$SEED READ ONLY
XEPDB1 READ WRITE
XEPDB2 READ WRITE
BOOKSHOP MOUNTED

And finally, when I try to work out who I am:

SELECT user FROM dual;

I get SYSTEM.

At this point I have no idea how to get control over this pluggable database. Do I need to switch to another user? How?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

First, try connecting to your container database as SYS instead of SYSTEM. SYSTEM is a template DBA account, but SYS (or any user with SYSDBA privileges, which effectively makes them SYS) is more powerful and must be used for some specific commands.

connect sys as sysdba;

Next, your xepdb2 database is already open in "READ WRITE" mode, so you don’t need to open it again.

Last, open your BOOKSHOP database with:

alter pluggable database bookshop open;

Conceptually, think of pluggable databases the same way you would think of a virtual machine in relation to a bare metal server. It’s a logical construct that allows you to consolidate and share certain resources on the database server. The container database (CDB) is somewhat like your hypervisor: you generally don’t mess with it except to create pluggable databases (PDBs), or perform certain system-level activities like backup and recovery. All of your application schemas and constructs go in the PDBs.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply