How to unlock sample Scott Schema in Oracle
The sample Scott schema is good for learning Oracle but it does not come as default with Oracle installation. We need to install it separately. It is available to the user Scott. In this blog, we will see how to unlock sample Scott user so that we can access the sample schema.
Oracle does provide the script of this schema in the db_home directory. The name of the script is scott.sql. We will make small modifications to the file and run the script.
High-level steps
- Locate scott.sql in db_home directory
- Modify the connection string in the scott.sql
- Execute the script scott.sql in cmd
Modifying scott.sql
- The scott.sql file is located in the folders under db_home. If you have followed the installation instructions for Oracle 19c , your file will be in the path \app\db_home\rdbms\admin.
- Open this file in the notepad and search for “CONNECT SCOTT/TIGER”.
- To comment the above statement, add REM in front of it, Rem CONNECT SCOTT/TIGER
- Now copy and paste CONNECT SCOTT/TIGER in the next line and change it to CONNECT SCOTT/TIGER@orclpdb. orclpdb is the name of our pluggable database.
- Save and close scott.sql
Execute scott.sql in command prompt (cmd)
- Type cmd in your windows start a program to invoke the command prompt (cmd)
- Login to sqlplus as sysdba by using the command: sqlplus / as sysdba
- Check the container name by using the command: show con_name. The output displays that we are connected to container database CDB$ROOT.
- Use Alter command to switch from container database to orclpdb. Use the command shown below
- Check if the pluggable database orclpdb is open by querying the v$pdbs view. The OPEN_MODE of READ WRITE shows that the pluggable database is open.
- If OPEN_MODE shows as MOUNTED that means the database is not open and it needs to be open by using the Alter command. To open the pluggable database, use the command
- You can verify if Scott user is available in the pluggable database or not. Use the command.
- If scott user is not in the list of users, then execute the scott.sql script by using the @ symbol and path of your file. In my case, the path is D:\app\db_home\rdbms\admin\scott.sql. I will execute this script by using the command
- You can check the user list again by using Select username from all_users; You will see the user scott this time.
- Use the statement Select * from tab; to list all the tables in the scott schema.
If you are connected to hr user on cmd, then type quit. It will disconnect you.
Then type the following command to login as sysdba
To refresh about RDBMS concepts, click on Basics of RDBMS