Oracle 12c Tutorial: Create Local User In Oracle Database 12c

In this tutorial you will learn how to create a local user in Oracle Database 12c and how to give admin rights to that user and how to import an user (schema) exported by Oracle 11g database into Oracle 12c database. Following is the step by step guide for Oracle 12c create pluggable database example.

First I will give you just a brief introduction about Oracle 12c Architecture. Prior to Oracle 12c like Oracle 11g and Oracle 10g there is happen to be only one database root in which Oracle Admin users like Sys, system etc installed and some sample schema also if you choose to install and on that root you just create your users / schema to store application data. But In Oracle 12c it is different and there is now two roots one is the Oracle's default admin user root which is called CDB$ROOT and another is PDB root in which you create your users / schema same as you create in Oracle 11g or 10g. As CDB$ROOT is the admin root you can not create your application's local user in that root, you must create the users in PDB (pluggable database root) because if you will try to create a user in CDB$ROOT then you will get the following errors.

 SQL> create user hms identified by hms;

create user hms identified by hms

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

Or if you try to create this way then you will receive this error:

SQL> grant connect, resource, dba to hms identified by hms;

grant connect, resource, dba to hms identified by hms

*

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

So I will tell you some important instructions on installing Oracle database 12c, which is when you install Oracle database 12c then you must choose the Pluggable database option while installing because you can create your application's user in PDB only or if you not selected PDB at the time of installation then I will tell you that how to create PDB database after installation.

To create a Pluggable database in Oracle 12c, click on start menu then choose Oracle12chome then Configuration and Migration Tools then Database Configuration Assistant then the following screen will appear:

Select Manage Pluggable Databases option as shown above and click on Next and the following screen will appear:

Select Create Pluggable Database and click on Next to continue...

Specify Sys user credentials then click on Next to continue...

Select Create a new Pluggable Database option then click on Next to continue..

Specify your Pluggable database name and user credentials then click on Next to continue.. I specified hms for my Hospital Management System database application to give you an example:

It will show you the summary of installation, just click on Finish to create Pluggable database and after completion the following screen will appear:

Now your Pluggable database creation is complete and now you need to connect it to do this you must specify its connection information in TNSNAMES.ORA file because this Pluggable database would be consider as a new Oracle database service.

Add the following in your tnsnames.ora file:

HMS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = hms)
)
)

Replace hms, host and port information according to your database and machine information and then save.

Now this pluggable database must be start to connect to it, so log in with Sys user credentials to your database and give the following command:

SQL> Alter Pluggable Database hms open;

After that you can connect as following:

Type Connect and give the credentials:

SQL> conn

Enter user-name: hms/[email protected]
Connected.
SQL>

Now we have connected but our schema is empty because we don't have any objects in it, so we will import a database dump in this Oracle 12c Pluggable database which have been exported from Oracle 11g.

For this user must have proper privileges to do this task, so first we will give admin privileges to hms user to perform import task and for this you have to reconnect using Sys user then give the DBA privilege to hms as following:

SQL> conn
Enter user-name: sys/[email protected] as sysdba
Connected.
SQL> rem set session to hms to access this db from sys
SQL> alter session set container=hms;

Session altered.

SQL> grant dba to hms;

Grant succeeded.

SQL> connect
Enter user-name: hms/[email protected]
Connected.
SQL>

Now you run IMP command to import a database dump file (.dmp) into this hms user, to perform this task do the following:

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:UsersVinish>IMP USERID=hms/[email protected] file=d:vinishhms14may.dmp full=y