What’s up internet? Welcome back once again
I am Manish from RebellionRider.com. Today in this Oracle Database tutorial we
will learn how to install sample schemas in Oracle Database 12c. And by sample schemas
I mean HR, SH OE and many more. As I have already mentioned in my previous
tutorials that in Oracle Database 12c only pluggable database can have the user data
and not the container database. Thus we can install sample schema only in a pluggable
database rather than the container database. Therefore today in this tutorial we will create
a pluggable database in which we will install all our sample schemas.
In case you want to learn how to unlock sample schema in default pluggable database you can
refer to my tutorial 61, link is in the description box. By default pluggable database I mean
the one which is created either during the installation of the oracle database 12c or
during the creation of a new container database. Ok now let’s move ahead and start the tutorial.
As we are going to create a new pluggable database thus we will use “Database Configuration
Assistant” also know by the name of DBCA, a utility provided by oracle itself. I would
like to mention here that I am using Oracle Database 12c Enterprise Edition in case you
were wondering. To launch DBCA utility just click and open
your start menu and search for DBCA. And then right click on DBCA.bat file and choose run
as administrator. Here on the first screen of the DBCA you have
5 database operations among these, choose the last one which is “Manage Pluggable
Databases” and then click next. In the Next step you have to select the operation
which you want to perform on your pluggable database. As in our case we want to create
a new pluggable database thus we will go with the first option which is “Create a Pluggable
database”. Select the first option and click next.
In the third step you have to choose the Container Database in which you want to create your
pluggable database. In my case I have only one container database which ORCL so I will
go with this one only. Which is already selected ok. Click next and move ahead
4th step is the most important one because here we will install all our sample schemas
in our pluggable database. In normal case we select the first option and move on to
the next step. But in this scenario Oracle engine will create a pluggable database for
you but without any sample schema. Since we want to install the sample schemas
in our pluggable database thus we will select the 3rd option which is “Create pluggable
database using PDB file set”. Choosing this option will enable both its text fields, where
in the first field we will have to specify the location of metadata file. The name of
the metadata file is “Sampleschema.xml”. This file comes pre-installed with the oracle
database software but in case you don’t have this file then check the documentation
of your software. To select the metadata file, click the browse
button by default it will open location of the “Templates” folder and as you can
see, here is our sampleschem.xml file. But in case it doesn’t, then let me tell
you where you can find this template folder, which has our sampleschema.xml metadata file.
For that first I have to zoom out a little bit. And here we go. Now first go to your
“Oracle Home” Directory. And here I am. Right now I am in my dbhome_1 directory which
is my Oracle Home in your case it may be different. Now next you have to go to the “Assistants”
directory. And inside the assistants directory go to this “dbca” directory, and as you
can see this dbca directory has our “templates” folder which has the metadata file “Sampleschema.xml”.
Let me show you and here it is. Now you know the location of your metadata
file and can easily navigate to it so let’s get back to our dbca window. Let me set zoom
before moving ahead. I think now its ok nicely focused and clearly visible.
Now here select “Sampleschema.xml” as you can see software has automatically filled
the location of data-file backup. If it doesn’t then choose the “Sampleschema.dfb” file
from the same templates folder. Once you are done with this just click next
and jump to the next step. Here in the 5th step you will have to do some
configuration setting for your pluggable database. Let’s do it.
First I will name my pluggable database as YouTubePDB. Next is storage if you have configured
ASM then you choose ASM here and configure accordingly but I will let it be on file system.
In the database location field you have to give the location where oracle can create
and save files such as data files, log files and so on and so forth. I will again let it
be on default. But if you want then you can change it and give a location of your choice.
Next is PDB user where you can create an administrative user for your PDB. Through this user you can
administer your PDB. Let me quickly make a PDB admin user.
Let this “lock all existing PDB users” box remain unchecked. Now hit next and move
ahead. Here you can see the summary.
Next you have to hit the Finish button; doing so will start the creation of PDB along with
the installation of our sample schemas. Once this installation is finished we will
have to do the further configurations. This will gonna take time thus I will fast forward
this process. Ok creation of pluggable database is done.
Hit ok and then hit close Now let’s quickly check whether our pluggable database is created
or not. For that we have to log on to our database using sys user.
Here I am, now let’s query v$pdbs view to check the existence of our pluggable database.
SELECT NAME, open_mode FROM v$pdbs WHERE name=’MANN’
As you can see we have our pluggable database created and it’s open for read and write operations.
Next we have to update the TNSNAME.ORA file and add a TNS service entry in it for our
pluggable database YouTubePDB. First open your TNSNAME.ORA file for that
you have to go to your Oracle Home. Here I am now go to the NETWORK folder and next go
to ADMIN folder. Here is our TNSNAME.ORA file. Open this file with any editor of your choice.
Next to make a TNS service entry copy this whole ORCL block and paste it at the end of
the file. Now change the name of this block from ORCL to YouTubePDB which is the name
of our pluggable database and also change the value of this SERVICE_NAME tag from ORCL
to YouTubePDB. In most of the cases the name of database
and its associated service is same but if you want you can confirm it using v$active_services
view. To know how you can confirm the name of associated service please visit my blog
on the same topic link is in the description box.
Save and close this file. As we just changed the listener settings so
to make them active we have to reload the listener. Open your command prompt with administrative
privilege. Ok here write lsnrctl reload.
Everything is done. Now the only step which remains left is to
unlock the users or say sample schemas. For that we need to log on to our pluggable database
using sys user. To log on to the specific pluggable database
which is YouTubePDB in our case we have to specify the name of the TNS service while
executing connection command using @sign. Let me show you how.
Sqlplus sys/[email protected] as sysdba. Here in this connection command I specified
the name of my TNS service which we just created in TNSNAME.ORA file using @sign right after
the username and password of my sys user. Let’s hit enter and connect to the database.
Here we are. Let me clear the screen for you first.
Now I will unlock the HR, SH and OE sample schemas using ALTER user DDL commands.
ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK; HR account is unlocked. Let’s unlock other
accounts also. ALTER USER sh IDENTIFIED BY sh ACCOUNT UNLOCK;
SH account unlocked ALTER USER oe IDENTIFIED BY oe ACCOUNT UNLOCK;
OE also unlocked. Similarly you can unlock other sample users
also. Let’s confirm whether we have successfully
unlocked these accounts or not by logging on to the database using all these accounts
one by one. Conn hr/[email protected]
Connected, Let me quickly show you the user. SHOW USER;
User is HR which means user HR is successfully unlocked. Now let’s check SH user
Conn sh/[email protected] Connected SHOW USER;
As you can see the user is SH which means SH is also unlocked
Conn oe/[email protected] OE also Connected let me quickly show you
the user also SHOW USER; User is OE which means OE is also successfully
unlocked. So guys that’s how we install sample schema
or say sample user in oracle database 12c. I have also done a video on how to unlock
HR user in Oracle 12c you can also watch that tutorial. The link is in the description box.
You can also check my blog on the same topic for more in-depth knowledge. Again, all the
links are in the description box. With this I will wind up today’s tutorial
on How to install sample schema in oracle database 12c or should I say how to create
pluggable database with sample schema in oracle database 12c.
Hope you have learnt something and enjoyed watching. Please tell me how you like this
video for that you can either leave a comment or write me a tweet @RebellionRider. And if
you liked this video then please hit that thumbs up button also don’t forget to subscribe
to my channel. Will see you soon with another hot and crispy
oracle database tutorial, till then take care. This is Manish from RebellionRider.