Blog

How to create sample schema in oracle 19c: Easy step-by-step Guide

create sample schema in oracle 19c

I will provide a detailed guide on how to create sample schema in Oracle 19c in this article. We’ll walk you through each step of the process in my virtual environment.

1. Login to your database server as an Oracle user and set the environment:

login as: oracle
oracle@192.168.56.11's password:
Last login: Tue Jun  4 10:10:37 2024
[oracle@machine1 ~]$

[oracle@machine1 ~]$ . oraenv
ORACLE_SID = [test] ? test
The Oracle base remains unchanged with value /u01/app/oracle

2. Go to the location $ORACLE_HOME/rdbms/admin and search for utlsampl.sql file:

[oracle@machine1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@machine1 admin]$ ls -ltr *sam*
-rw-r--r-- 1 oracle oinstall  3978 May 29  2017 utlsampl.sql
-rw-r--r-- 1 oracle oinstall 27053 Jun 15  2017 dbmsamgt.sql

3. Connect to your Oracle database and check if the SCOTT user is present:

[oracle@machine1 admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 4 10:29:10 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select username from dba_users where username='SCOTT';
no rows selected

4. Now run the script utlsampl.sql:

SQL> @utlsampl.sql
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@machine1 admin]$

If you want to create HR schema also then run the below command:

$ORACLE_HOME/demo/schema/human_resources/hr_main_new.sql

5. Now login as sysdba and verify the SCOTT user:

[oracle@machine1 admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 4 10:30:08 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select username from dba_users where username='SCOTT';

USERNAME
--------------------------------------------------------------------------------
SCOTT

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Congratulations! I hope this article will help you to create sample schema in oracle 19c. If you do not have the Oracle VirtualBox for your practice environment, you can follow my other article, Setup Oracle VirtualBox.

Leave a Reply