oracleoracle12cpluggable-database

How to create user profile in pluggable oracle db

This is my query :

CREATE PROFILE user_prof
LIMIT password_life_time unlimited;

I am getting ORA-65040: operation not allowed from within a pluggable database.

How to resolve this?

I create a user with name pp

CREATE USER pp IDENTIFIED BY ppop
PROFILE user_prof

When i tried to drop this user using :

ALTER SESSION SET CONTAINER = orclpdb;
DROP USER pp cascade;

I am getting :

ERROR at line 1:
ORA-28014: cannot drop administrative users
Solution

  • To create a profile in a container database ( affects all pluggable databases in the container )

    SQL> CREATE PROFILE xxxxx LIMIT PASSWORD_REUSE_MAX 10
    PASSWORD_REUSE_TIME 30 CONTAINER=ALL;

    If you want to create a profile only for your pluggable database

    SQL> CREATE PROFILE xxxxx LIMIT PASSWORD_REUSE_MAX 10
    PASSWORD_REUSE_TIME 30 CONTAINER=CURRENT;

    Keep in mind that in order to specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify CONTAINER = ALL, the current container must be the root. To specify CONTAINER = CURRENT, the current container must be a pluggable database (PDB).

    Update

    To drop the administrative user

    SQL> alter session set "_oracle_script"=true;
SQL> drop user pp cascade;

    Note

    Be careful when dropping users this way. Some of the users might have been indeed created by scripts supplied by Oracle and might be needed for the components running inside that pluggable database.