Oracle 12c: AFTER STARTUP trigger to open PDBs

Update: Starting with Oracle, it is possible to save a PDB state so this workaround is no longer necessary.

When starting up an Oracle 12c database with pluggable databases, the pluggable databases in a container database are not automatically opened, just placed in MOUNT mode. This can be a problem in some cases.

To automatically open the pluggable databases on startup, you’ll have to create an AFTER STARTUP trigger in the CDB. For my own reference, I will post such a trigger here:

CREATE TRIGGER open_all_pdbs
END open_all_pdbs;

ORA-31001: Invalid resource handle or path name “/sys/acls/myacl.xml”

So this happens when you try to add a privilege to an ACL that does not exist:

SQL> EXEC DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ('myacl.xml', 'SIMON', true, 'connect');
BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ('myacl.xml', 'SIMON', true, 'connect'); END;

ERROR at line 1: ORA-31001: Invalid resource handle or path name "/sys/acls/myacl.xml" ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 384 ORA-06512: at line 1

Oracle Database 11g introduced fine-grained access control to external network services using Access Control Lists (ACLs). This basically allows you to take control over which users access which network resources, regardless of package grants.

Read the rest of this entry

Disable all constraints of a table

During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.

Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…

Read the rest of this entry

Hello world

My name is Simon Krenger, I am a Technical Account Manager (TAM) at Red Hat. I advise our customers in using Kubernetes, Containers, Linux and Open Source.


  1. GitHub
  2. LinkedIn
  3. GitLab