If you are willing to learn SQL and run some test scripts, you will need to install oracle sample schemas.
On this post we are going to install oracle database sample schema on few steps.
Reasons why to Install Oracle Sample Schema:
Oracle Sql Practise.
Building Oracle Applications on Test.
Self-Study Education.
Database Sample Schemas includes:
HR user: Human Resource.
OE user: Order Entry.
PM user: Product Media.
SH user: Sales History.
IX user: Information Exchange.
In order to install these sample schemas you must follow the following steps:
Step 1: Download Sample schema.
To download sample schema files please check this link.
You can check all database schemas versions on the below link:
https://github.com/oracle-samples/db-sample-schemas/releases
If you willing to download version 19.2 check the below URL:
https://github.com/oracle-samples/db-sample-schemas/releases/tag/v19.2
Step 2: Move schema files to location /demo/schema.
After downloading the file, you must extract it and move it to your oracle home location
on the path /demo/schema/.
Open oracle location files, navigate to /demo/schema, rename schema file to schema_old.
Create new folder name its schema, then copy all the downloaded files to schema folder.
Step 3: Edit some SQL files to correct the location path.
Open mksample.sq with file editor on schema folder, find and replace all default locations from
SUB__CWD__ to the correct path.
On my case it is C:\WINDOWS.X64_193000_db_home\demo\schema
On the same path find and replace all the signs from / to \ .
In addition to the previews step, repeat the same process for the below files:
bi_main.sql under bus_intelligence folder.
hr_main.sql under human_resources folder.
ix_main.sql under info_exchange folder.
oe_main.sql under order_entry folder.
pm_main.sql under product_media folder.
sh_main.sql under sales_history folder.
qs_main.sql under shipping folder.
Step 4: Open Sql developer, connect sys/password@orcl as sysdba.
sys/abc@orcl as sysdbaStep 5: run the command: alter session set “_ORACLE_SCRIPT”=true;
alter session set "_ORACLE_SCRIPT"=true;Step 6: Execute mksample.sql file.
Execute the files mksample.sql by running the below command.
sqlplus system/systempw@connect_string
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string
on my case the command is:
@C:\Users\ERP\Desktop\db-sample-schemas-12.2.0.1\mksample.sql sysempw syspw hrpw oepw pmpw ixpw shpw bipw users temp C:\WINDOWS.X64_193000_db_home\log localhost:1521/orclpdb
On this example we have provided the below input for the populated parameters:
- sys password: type your sys password
- system password: type your system password, (please check if it is locked or not).
In case system password is locked, you can run the below command to unlock it:
Alter user system identified by yourpassword account unlock;- HR schema password: hrpw.
- OE schema password: oepw.
- PM schema password: pmpw.
- IX schema password: ixpw .
- SH schema password: ixpw.
- BI schema password: bipw.
- Tablespace: type users tablespace.
- Temp tablespace: type temp tablespace.
- Log file path: C:\WINDOWS.X64_193000_db_home\log
- connect_string localhost:1521/orclpdb
Mistakes to avoid.
During this process try to avoid the below issues on order to create sample schemas successfully:
Make user orclpdb is up and running.
Make sure you have connect the path on sql files correctly.
Make sure the user system is unlocked.
Open sample schema:
Open Sql developer and create new connection for HR schema to be sure the process have been completed successfully.
Leave a Reply