Cannot install HR schema for Oracle 21c express

Question

I watched video tutorial at https://www.youtube.com/watch?v=IalqQN09OaA . My Oracle database version

select * from v$version;

Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

I download sample at https://github.com/donhuvy/db-sample-schemas/tree/main/human_resources . Inside Oracle SQL Developer version 22.x , with user system, Run command

@ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql

I entered

Input 1: Password for HR:       123456
Input 2: Tablespace:            SYSTEM
Input 3: Temporary tablespace:      TEMP
Input 4: Password for SYSTEM user:  12345678
Input 5: Log path:          C:\
Input 6: Connection string:         localhost:1521/xe

My steps

localhost:1521/xe

Error

specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify password for SYS as parameter 4:

specify log path as parameter 5:

specify connect string as parameter 6:


Error starting at line : 86 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
DROP USER hr CASCADE
Error report -
ORA-01918: user 'HR' does not exist
01918. 00000 -  "user '%s' does not exist"
*Cause:    User does not exist in the system.
*Action:   Verify the user name is correct.

Error starting at line : 95 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
CREATE USER hr IDENTIFIED BY &pass
Error report -
ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
           that was not valid for common users or roles. In addition to the
           usual rules for user and role names, common user and role names
           must consist only of ASCII characters, and must contain the prefix
           specified in common_user_prefix parameter.
*Action:   Specify a valid common user or role name.

Error starting at line : 97 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
ALTER USER hr DEFAULT TABLESPACE &tbs
              QUOTA UNLIMITED ON &tbs
Error report -
ORA-01918: user 'HR' does not exist
01918. 00000 -  "user '%s' does not exist"
*Cause:    User does not exist in the system.
*Action:   Verify the user name is correct.

Error starting at line : 100 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
ALTER USER hr TEMPORARY TABLESPACE &ttbs
Error report -
ORA-01918: user 'HR' does not exist
01918. 00000 -  "user '%s' does not exist"
*Cause:    User does not exist in the system.
*Action:   Verify the user name is correct.

Error starting at line : 102 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr
Error report -
ORA-01917: user or role 'HR' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.

Error starting at line : 103 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr
Error report -
ORA-01917: user or role 'HR' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.
Connected.

Error starting at line : 110 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
GRANT execute ON sys.dbms_stats TO hr
Error report -
ORA-01917: user or role 'HR' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.
Error starting at line : 116 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
  connect ...
Error report -
Connection Failed
  USER          = hr
  URL           = jdbc:oracle:thin:@localhost:1521/xe
  Error Message = ORA-01017: invalid username/password; logon denied
Commit
SP2-0640: Not connected
SP2-0640: Not connected

Error starting at line : 124 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_cre
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_cre.sql"

Error starting at line : 130 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_popul
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_popul.sql"

Error starting at line : 136 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_idx
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_idx.sql"

Error starting at line : 142 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_code
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_code.sql"

Error starting at line : 148 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_comnt
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_comnt.sql"

Error starting at line : 154 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_analz
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_analz.sql"

How to fix?

Update

Use command

alter session set "_oracle_script"=true;
@ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql

Session altered.


specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify password for SYS as parameter 4:

specify log path as parameter 5:

specify connect string as parameter 6:


User HR dropped.


User HR created.


User HR altered.


User HR altered.


Grant succeeded.


Grant succeeded.

Connected.

Grant succeeded.

Connected.

Session altered.


Session altered.


Error starting at line : 124 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_cre
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_cre.sql"

Error starting at line : 130 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_popul
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_popul.sql"

Error starting at line : 136 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_idx
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_idx.sql"

Error starting at line : 142 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_code
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_code.sql"

Error starting at line : 148 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_comnt
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_comnt.sql"

Error starting at line : 154 File @ C:\Users\Administrator\Downloads\db-sample-schemas-21.1\human_resources\hr_main.sql
In command -
@__SUB__CWD__/human_resources/hr_analz
Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_analz.sql"
Connection created by CONNECT script command disconnected

PL/SQL procedure successfully completed.

DBMS_SYSTEM.GET_ENV
Alias sqlcl_int_runme dropped

How to fix error

Error report -
SP2-0310: Unable to open file: "__SUB__CWD__/human_resources/hr_idx.sql"

Answer 1

Steps to unlock HR user in Oracle Database 21c:

I am assuming that you have downloaded the required software/database :--OracleXE213_Win64 and --sqldeveloper-23.1.1.345.2114-x64 --

install the OracleXE213 first!

Get the Source Code STEP 1:downlaod the Oracle Database Sample Schemas 23c from the GIT repository, inks: https://github.com/oracle-samples/db-sample-schemas/releases/tag/v23.3

STEP 2: Place the downloaded folder named schemas at : C:\app\Aarna\product\21c\dbhomeXE\demo\schema (unzip if you need to) I suggest you also watch (and follow him if you want to) this YouTube video by Manish Sharma @Rebellionrider : https://www.youtube.com/watch?v=wnYi2RqiDvk

go to C:\app then look for your computer name--in my case its Aarna and it could be anything for you. The rest should be similar...all you need to do is look for is product\21c\dbhomeXE\demo\schema

THE MOST IMPOSRTANT THING before unlocking The HR user/schema: We need to modify hr_main.sql file to replace all occurrences of SUB__CWD/human_resources/

locate the hr_main.sql script which is inside the folder named “human_resources” we need to edit the hr_main.sql script.

open the script in notepad or notepad++ come to the end of this script where you see some reference links to SUB__CWD. This section

We neecd to change this reference link with the actual location path of the scripts to run the script -----------------the origional----------------------------- --@SUB__CWD/human_resources/hr_cre

-- populate tables

--@SUB__CWD/human_resources/hr_popul

-- create indexes

--@SUB__CWD/human_resources/hr_idx

-- create procedural objects

--@SUB__CWD/human_resources/hr_code

-- add comments to tables and columns

--@SUB__CWD/human_resources/hr_comnt

-- gather schema statistics

--@SUB__CWD/human_resources/hr_analz -------------------Change like this------------------------------

@C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_cre

-- populate tables

@C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_popul

-- create indexes

@C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_idx

-- create procedural objects

@C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_code

-- add comments to tables and columns

@C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_comnt

-- gather schema statistics

@C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_analz

---

STEP 3: open the CMD command Open the Run menu with Windows Key + R, then type "cmd." Press "Enter" to open the regular Command Prompt.On a Mac the Cmd key essentially takes on the role of the Ctrl key on a PC STEP 4: in the command prompt you will see: C:\Users(your computer name)> Example C:\Users\Aarna> type sqlplus

C:\Users\Aarna>sqlplus It will ask you to log in as a system user in my case I used system while installed OracleXE213 , REMEMBER the user-name and password you supplied when installing OracleXE213

Enter user-name: system Enter password: Last Successful login time: Fri Jun 14 2024 08:38:59 -04:00

SQL> SHOW con_name;

CON_NAME

CDB$ROOT SQL> ALTER SESSION SET CONTAINER = xepdb1; Session altered. SQL> COLUMN name FORMAT a15; SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

---

XEPDB1 READ WRITE

SQL> @C:\app\Aarna\product\21c\dbhomeXE\demo\schema\human_resources\hr_main.sql hr users temp system $ORACLE_HOME/demo/schema/log/ ( replace with your computer name and password and log file location plus the connect string ) specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify password for SYS as parameter 4:

specify log path as parameter 5:

specify connect string as parameter 6: Enter value for 6: localhost:1521/XEPDB1

Answer 2

Unfortunately the default sample script installation process is not great for the novice. Please head over to this blog post

https://connor-mcdonald.com/2021/11/02/quick-and-easy-sample-data/

which describes why this is the case, but more importantly will give you a workaround which involves just running a single script in SQL*Plus. That script will also try to guide you through the process to avoid any errors, eg

| 1) Preliminary checks
| =====================
|
| You should be connected to the database at this point.
| If you are, then you will see the following:
|
|  >>> Connected as: YOUR_USER <<<
|
| If you are not, you're will see the following
|
|  >>>> SP2-0640: Not connected <<<<
|
| If you get this error, press Ctrl-C to exit this script and
| connect first before running it again.
|
| Tip: For Express Edition, the command to connect is *probably*
|
| SQL> connect system/yourpassword@//localhost/XEPDB1
|
| Once you are connected OK, then press Enter to proceed
|
Connected as: MCDONAC

Enter to proceed, Ctrl-C to stop
|
| Checking that we can write a file to the current directory
| If we can't, then this script will exit here. Please make
| you are running the script from the directory you saved it to
| and this directory is writable
|
File test passed!
|
|
| Now checking database details. If any of these fail,
| the script will exit with the error that you need
| to resolve.
|
Container database. PDB PDB1 will be used for installation...proceeding
|
| Checking current user details
|
You are connected as MCDONAC, ie, not the HR schema.
Hence this installation will drop the HR schema entirely
and recreate it. If this was not what you wanted, then press Ctrl-C
the installation, otherwise press Enter to continue

Enter to proceed, Ctrl-C to stop
|
| Checking required privileges
|
Privilege ALTER ANY TABLE..................OK
Privilege ALTER ANY TRIGGER................OK
Privilege ALTER SESSION....................OK
Privilege ALTER USER.......................OK
Privilege ANALYZE ANY......................OK
Privilege COMMENT ANY TABLE................OK
Privilege CREATE ANY CLUSTER...............OK
Privilege CREATE ANY INDEX.................OK
Privilege CREATE ANY INDEXTYPE.............OK
Privilege CREATE ANY OPERATOR..............OK
Privilege CREATE ANY PROCEDURE.............OK
Privilege CREATE ANY SEQUENCE..............OK
Privilege CREATE ANY SYNONYM...............OK
Privilege CREATE ANY TABLE.................OK
Privilege CREATE ANY TRIGGER...............OK
Privilege CREATE ANY TYPE..................OK
Privilege CREATE ANY VIEW..................OK
Privilege CREATE SESSION...................OK
Privilege CREATE USER......................OK
Privilege DELETE ANY TABLE.................OK
Privilege DROP USER........................OK
Privilege GRANT ANY OBJECT PRIVILEGE.......OK
Privilege GRANT ANY PRIVILEGE..............OK
Privilege INSERT ANY TABLE.................OK
Privilege SELECT ANY TABLE.................OK
Privilege UPDATE ANY TABLE.................OK
Privilege SELECT ON GV$SESSION.............OK
|
| Checking tablespaces
|
Checks .......OK
|
| Checking existing HR details
|
Checks .......OK
|
| The new/replaced HR schema will be created now.
|
| Note down this password for the HR schema. You will need it to connect
|
| Password (case-sensitive): DAjcBlFOJq$986
|
| The script will exit on any error encountered, because it should run
| to completion with no errors at all
|
Press Enter to start
******  Creating REGIONS table ....

Table created.


Index created.


Table altered.

 etc etc