-1

I have a list of 100 PDBs in the CDB, say the account_user exists in 50 PDBs and to create the same account_user in other 50 PDBs instead of logging into each PDB and creating user, is there a way to do this with single command or script?

When I create the user from CDB, I get below error:

ORA-01920: user name 'account_user' conflicts with another user or role name

nor container=all will work coz I don't want to drop the user form first 50 PDBs and re-create all over again.

2

Sure.

SQL> conn / as sysdba
Connected.
SQL> select pdb_name from cdb_pdbs order by 1;

PDB_NAME
--------------------
PDB$SEED
PDB1
PDB2
PDB3

SQL> select u.username, p.pdb_name from cdb_users u 
     join cdb_pdbs p on (u.con_id = p.pdb_id) where u.username = 'BP';

USERNAME             PDB_NAME
-------------------- --------------------
BP                   PDB1

Just generate the script:

SQL> select 'alter session set container=' ||pdb_name || ';' || chr(10) || 'CREATE USER BP IDENTIFIED BY BP;' as script from cdb_pdbs p where not exists (select 1 from cdb_users u where u.username = 'BP' and u.con_id = pdb_id) and pdb_name not in ('CDB$ROOT', 'PDB$SEED') ;

SCRIPT
--------------------------------------------------------------------------------
alter session set container=PDB3;
CREATE USER BP IDENTIFIED BY BP;

alter session set container=PDB2;
CREATE USER BP IDENTIFIED BY BP;

Then execute it:

SQL>

alter session set container=PDB3;
CREATE USER BP IDENTIFIED BY BP;

alter session set container=PDB2;
CREATE USER BP IDENTIFIED BY BP;

Session altered.

SQL>
User created.

SQL> SQL>
Session altered.

SQL>
User created.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select u.username, p.pdb_name from cdb_users u
     join cdb_pdbs p on (u.con_id = p.pdb_id) where u.username = 'BP';

USERNAME             PDB_NAME
-------------------- --------------------
BP                   PDB1
BP                   PDB2
BP                   PDB3

SQL>
1
  • Cool, i did the same but by manually editing the create commands in editor right after session alter commands and ran in container. Thanks.Wish there is a bulk user creation process by Oracle in multiple databases – sunnybjorn Jul 30 '20 at 12:51
1

Short answer: No.

Each PDB is [guaranteed to be] completely separate from every other PDB. That's kind of the whole point of them.

There is no way of "influencing" multiple databases at once in the way you describe.

You'll just have script up the change and apply it to each of the 50 databases in turn.

I would go one step further and suggest that, with this many accounts and this many databases to look after, it's probably time to start looking into automating your account management process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.