In this Oracle tutorial we will install sample schema and sample users in oracle 12c. We will use these table through out this SQL Basics course. Hence, it is a must if you want to practice along with studying.
We are going to create a new pluggable database. So, we will use the Database Configuration Assistant that comes with oracle. The Database Configuration Assistant is also known as DBCA.
This page contains (Skip to topic)..
- #1 Open dcba.bat in admin privilege
- #2 Which operation to perform in the container database
- #3 Select a container Database
- #4 Create pluggable database using PDB file set
- #4.1 Select “create PDB using PDB file set”
- #4.2 Browse and select the sampleschema.xml file
- #4.3 Search for sampleschema.xml file in dcba>templates directory
- #4.3 Choose sampleschema.xml file and hit OK
- #4.4 Ensure pluggable database datafile backup is detected
- #4.5 Browse and select the sampleschema.dfb file as shown below If not Auto populated
- #5 Configure new PDB and create an administrator for it
- #6 Save the Newly created pluggable database details
- #7 Click Finish and wait for it to complete
- #8 Add newly created PDB to tnsnames.ora file
- #9 Reload the tns listener of oracle database
- #10 Try to Test connection using SQL Developer
- #11 Unlock hr, sh, oe schemas
- #12 Login to HR user and start practicing
#1 Open dcba.bat in admin privilege
Goto the search bar and search for dbca.bat . If you are not able to find in search then open the run dialog box and type dbca.bat . Please open this with admin privilege. You can do this by right clicking on the same dbca.bat and choose “Run as administrator”.
#2 Which operation to perform in the container database
As we are going to install the demo schema and users in a new pluggable database, we will select the “Create a Pluggable Database” option and click Next.
#3 Select a container Database
In the third step, you have to choose a container in which you will create the pluggable database. I have only one container i.e. ORCL. Hence, it is selected by default. Then click next and go ahead.
#4 Create pluggable database using PDB file set
In the normal case, we choose the first option and oracle will install a pluggable database. However, as we need to install sample schema and users, we will go with the third option.
#4.1 Select “create PDB using PDB file set”
Select the “create pluggable database using pdb file set” option. Then we need to select pluggable database metadata file and pluggable database datafile backup.
#4.2 Browse and select the sampleschema.xml file
Click on the browse button next to pluggable database metadata file. Select sampleschema.xml and click okay. In case you do not see it, try to find the same in the folder shown in #4.3.
#4.3 Search for sampleschema.xml file in dcba>templates directory
You can find the sampleschema.xml file in app > user > product > 12.1.0 > dbhome > assistants > dbca > templates directory.
#4.3 Choose sampleschema.xml file and hit OK
#4.4 Ensure pluggable database datafile backup is detected
Once you select the sampleschema.xml file, pluggable database datafile backup should be auto populated.
#4.5 Browse and select the sampleschema.dfb file as shown below If not Auto populated
In case the pluggable database datafile backup is not detected, click browse and select the sampleschema.dfb. Click ok and click next.
#5 Configure new PDB and create an administrator for it
- Provide a name for the pluggable database, I have given otPDB.
- Select storage type as ile system.
- Provide admin username. I have given oracletutorial.
- Provide admin password couple of times. You need to provide the same password.
- Leave the lock all existing pdb users unchecked
- Click Next.
#6 Save the Newly created pluggable database details
Please save these details somewhere. You will need these info later to connect to the newly created pluggable database.
#7 Click Finish and wait for it to complete
Wow !! Congrats !! You just created a new pluggable database. Next step is adding the same info to tnsnames.ora file.
#8 Add newly created PDB to tnsnames.ora file
Please add the following piece of code in the tnsnames.ora file. This normally resides in the ORACLE_HOME/network/admin/ directory.
<addressname> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>)) ) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) )
Once you have added the same at the end of the tnsnames.ora file, you should save and close it. We need to reload the tns listener next.
#9 Reload the tns listener of oracle database
Open command prompt as administrator, then type lsnrctl reload and hit enter. This should reload the listener. Our newly added entry in tnsnames.ora should be visible now.
#10 Try to Test connection using SQL Developer
Try to test the connection the following information.
- Connection name : Any
- Username: Newly created admin user during pluggable database creation
- Password: Same as configured during installation
- Service name: The newly created PDB i.e. otPDB for our case.
Once you entered all the info, click test. The status at the bottom left corner should show as success.
#11 Unlock hr, sh, oe schemas
Now we are a few steps away from using the sample schema. We need to unlock them from sys account before using them. Run the following commands.
SQL> conn sys/admin@otpdb as sysdba Connected. SQL> alter user hr identified by hr account unlock; User altered. SQL> alter user sh identified by sh account unlock; User altered. SQL> alter user oe identified by oe account unlock; User altered.
#12 Login to HR user and start practicing
Test if you are able to connect to the sample schema either in sql developer or in cmd prompt as shown below.
SQL> conn hr/hr@otpdb Connected. SQL> conn oe/oe@otpdb Connected. SQL> conn sh/sh@otpdb Connected.
Here are the different tables in HR user. We can use these schema and datas for this oracle 12c sql basics course.