Updated on 3rd June 2025

After a fresh installation of Oracle 19c and patch update to 25, I couldn’t get the mksample.sql to work as explained below. The 12th parameter for connect string “localhost:1521/SCT” started throwing many errors like listed below:

SQL*Loader: Release 19.0.0.0.0 - Production on Tue Jun 3 13:47:18 2025
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

I checked tnsnames.ora and listener.ora files to ensure that the necessary entries were available for the PDB so that I can connect to the PDB directly from .Net applications. I couldn’t figure out what was going wrong, and decided to give it a try by connecting to the the PDB directly and executing the mksample.sql script. This also didn’t work as the script failed to connect to the connection string.

For a curiosity purpose, I decided to enter only the connect string name during the next attempt and the script executed without any troubles. If you are facing the same issue, make sure that the current CMD is completely closed and you will execute the script from a new command window. Let us see how it work.

SCT is my PDB and I am connecting to it as sys & executing the script mksample.sql

That’s it!

End of update 3rd June 2025

Oracle 19c comes with a single sample schema HR. For other sample schemas, we have to download the installation media from github repositories. Today we will see how to install the sample schemas on a pluggable database. We’ll be installing Oracle 19.2 sample schemas and please remember, there are possibilities that the sample schema scripts differ for different releases. you can download the 19.2 sample scripts from here

Now comes the difficult part. Once you extract the archive, it creates a folder “db-sample-schemas-19.2”, unless you modified the extract location. I’ve discussed about this on my other post about the Windows software that I made for replacing strings recursively

Basically, Oracle scripts for sample schemas refer to a path, that has to be changed within all the nested SQL and DAT files being referred by the installer script. This is not going to be an easy task for anyone if manually attempted, prompting me to develop the above discussed small utility. Another approach is to write a batch or Powershell script. I opted the latter. Save the below as PowerShell script. Make sure that you configured the environment to run remote signed scripts.

<# run once for *.sql and again for *.dat #>

$count = 0;

Get-ChildItem 'D:\db-sample-schemas-19.2\*.sql' -Recurse | ForEach { 

    $count = (get-content $_ | select-string -pattern '__SUB__CWD__').length

    if ($count -gt 0) {

        Write-Host $_ " has $count matches & a backup file $_.bak will be created."

        Copy-Item -Path $_ -Destination $_".bak"

(Get-Content $_ | ForEach { $_ -replace '__SUB__CWD__', 'D:/db-sample-schemas-19.2' }) | Set-Content $_

    }

}

The above script should be run twice, to iterate through two different types of files. First time for “.sql” and second time for “.dat”

Once the scripts are modified with the correct path, we can proceed with setting up sample schemas. We will use the script “mksample.sql”. Start sqlplus from the script root, eg: D:\db-sample-schemas-19.2. Don’t forget to alter session to your PDB prior executing the script! Please ensure that you have created a new a tablespace “EXAMPLE” if it doesn’t exist (You can use another existing tablespace for the purpose, however not recommended)

Usually the script completes generating few errors. You can check the log files for detailed information about what went wrong, that are insignificant as long as you are only looking at tables/views and indexes.

Hope this helps!