How to create the Scott Demo Database Schema?

Scott schema is the most famous oracle database demo schema that is available since 1990 !.

Scott is a database user used for demonstration purposes containing the famous EMP, DEPT, BONUS and SALGRADE tables.
This account was named after Bruce Scott (co-author and co-architect of Oracle v1 to v3) and the password was the name of his daughter’s cat, Tiger.

Scott schema is still very famous although of the other demo schemas (hr, oe, pm, ix, sh ,bi) that I explained how to install them in my previous post:
https://ahmedfattah.com/2019/12/23/installing-oracle-sample-schemas-19-3/

The scott schema contains four tables:

How to Install Scott Schema?

Oracle provides a script since the very early releases to create the scott schema. The script is called “scott.sql” and is located under $ORACLE_HOME/rdbms/admin.
The last time this script was updated is at 28-Jun-1995 !.
This script is still available in all the Oracle latest releases: 12.1 , 12.2 , 18c & 19.3.

The script is simply creates the scott user with password tiger, and then creates 4 tables and insert data into them.

To run the script and create the scott demo schema:
sqlplus / as sysdba
@?/rdbms/admin/scott.sql

In the following demo, I’m going to create the scott schema into a 19.3 pluggable database called “pdb1”.
Also, I modified line # 27 in the scott.sql script from:
CONNECT SCOTT/TIGER
to:
CONNECT SCOTT/TIGER@//localhost:1523/pdb1

That is all, now we have fully functional scott schema.
You can run the same script any number of times, it will drop the existing tables first, then creates a new copy.

Thanks
Ahmed

Donate to support our blogging work

$1.00

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