Create Sample Schema HR in Oracle Database

Create Sample Schema HR in Oracle Database

1. Check the Oracle home location has script to create the Sample Schema

%ORACLE_HOME%\demo\schema

Note: it has folder like humanresource, etc

2. I am using container database, so i want to create sample schema in pluggable database.

SQL> alter session set container=pdb2;
Session altered.

3. Verify the user HR is already present or not.

SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

-- For container database
column username for a10
column account_status for a14
select username,account_status,con_id,created from cdb_users where username='HR';
no rows selected

--For non container database
column username for a10
column account_status for a14
select username,account_status from dba_users where username='HR';

4. Before creating check the tablespace present in you database or you want to use for HR schema.
You can also create new tablespace for your HR schema.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

5 rows selected.

5. Go to the script location and execute the HR_MAIN.SQL. It call other statement for creating objects in HR schema.

SQL> @D:\Oracle\18.0.0\dbhomeXE\demo\schema\human_resources\hr_main.sql;

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: users

specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp

specify log path as parameter 4:
Enter value for 4: D:\

PL/SQL procedure successfully completed.
User created.
User altered.
User altered.
Grant succeeded.
Grant succeeded.
Session altered.
Session altered.
Session altered.

****** Creating REGIONS table ....
Table created.
Index created.
Table altered.

****** Creating COUNTRIES table ....
Table created.
Table altered.

****** Creating LOCATIONS table ....
Table created.
Index created.
Table altered.
Sequence created.

****** Creating DEPARTMENTS table ....
Table created.
Index created.
Table altered.
Sequence created.

****** Creating JOBS table ....
Table created.
Index created.
Table altered.

****** Creating EMPLOYEES table ....
Table created.
Index created.
Table altered.
Table altered.
Sequence created.

****** Creating JOB_HISTORY table ....
Table created.
Index created.
Table altered.

****** Creating EMP_DETAILS_VIEW view ...
View created.
Commit complete.
Session altered.

****** Populating REGIONS table ....
1 row created.
1 row created.
1 row created.
1 row created.

****** Populating COUNTIRES table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

****** Populating LOCATIONS table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

****** Populating DEPARTMENTS table ....
Table altered.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

****** Populating JOBS table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

****** Populating EMPLOYEES table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

****** Populating JOB_HISTORY table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Table altered.
Commit complete.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Commit complete.
Procedure created.
Trigger created.
Trigger altered.
Procedure created.
Trigger created.
Commit complete.
Comment created.
Comment created.

Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Commit complete.
PL/SQL procedure successfully completed.

6. You can also check log file created by script.

7. Verify the user HR is created.

column username for a10
column account_status for a14
select username,account_status,con_id,created from cdb_users where username='HR';

USERNAME ACCOUNT_STATUS CON_ID CREATED
---------- -------------- ---------- ---------
HR OPEN 4 13-MAR-19

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.