Spludlow Web Header

Oracle - Basics


Oracle Database 11g Express Edition (Oracle Database XE)

Oracle Database Express Edition 11g Release 2 for Windows x64


The express edition is much easier to install and use than the full edition of Oracle.

SQL Workshop -> Object Browser

You can browse and create tables from here

Oracle Database 12c Release 2 (

Standard Edition 2 and Enterprise Edition

Oracle Database 12c Release 2 ( for Microsoft Windows (x64)



ODAC Runtime Downloads (Oracle .net client DLL)

The “XCopy” download is just a zip, the “OUI” version includes an installer program.

For .net you want the “Managed ODP.NET”:


Link to:




Other program?


Oracle Standard

Let’s run through installing Oracle Standard Edition. I didn’t use the XE edition as it can’t do IDENTITY (until it gets to version 12c). I opted for advanced install just so I can select the demo databases.

Server Install

·         Download and extract the database server installer

·         Run the setup “C:\winx64_12102_SE2_database\database\setup.exe”

·         Select “Create and configure a database”

·         Select “Server class”. When I tried “Desktop class” it didn’t configure a network listener, so the database was inaccessible from other hosts. If your client will only be local or you know how to configure listeners, then you can use “Desktop class”.

·         Select “Single Instance Database”.

·         Select “Advanced Install”. (The only reason I chose Advanced is because I wanted the demo databases, all other settings will be left at default)

·         Next to Languages

·         Next to Edition

·         Choose the Windows Account the database server will run as. Although you should never do it, if you are setting up a quick test system you should be safe using “Windows Built in Account” (NT AUTHORITY\SYSTEM).

·         Software location gave me an error so I changed the path “C:\app\Administrator\product\12.1.0\dbhome0_1” (was “dbhome_1”).

·         Next to General Purpose

·         Next to Database Names and leave container ticked

·         Memory Tab->You may well want to turn this down a bit on a test system.

·         Character Set->Leave default

·         Sample Schemas->Tick this if you want

·         Next to Storage Options

·         Next to cloud control leave unticked

·         Next to recovery options leave unticked

·         Set the admin password

·         Review details then click install.

·         Wait for it to complete (Takes ages).

·         Open the port in Windows Firewall

After install the database server is up and running you should be able to connect using the DAL; service name “ORCL” and the database login “SYSTEM” with the password specified in setup. Do a select “SELECT * FROM dba_users” to see if it’s working.

Enabling the demo schemas like HR

After installing I thought I would be able to access the HR demo schema, when I tried it with XE edition I just needed the command “ALTER USER HR IDENTIFIED BY demopass ACCOUNT UNLOCK;” and I was in play. Unfortunately, it’s not that simple on 12c, something to do with Oracle pluggable databases.

I found this excellent video here “Oracle Database 12c Tutorial 2: How To Unlock HR schema in Oracle Database 12c” https://www.youtube.com/watch?v=F81gfUnznZY give it a watch.

Here is a rundown of what the video explains, provided you are using the defaults for everything. I had to add the last line that wasn’t in the video.

·         Edit C:\app\Administrator\product\12.1.0\dbhome2_1\NETWORK\ADMIN\tnsnames.ora duplicate the ORCL section and rename it in 2 places to PDBORCL / pdborcl

·         Run the command C:\app\Administrator\product\12.1.0\dbhome2_1\BIN\LSNRCTL.EXE reload

·         ALTER SESSION SET CONTAINER = pdborcl; SHOW con_name;

·         ALTER PLUGGABLE DATABASE open; May already be open!

·         SELECT name,open_mode FROM v$pdbs; Confirm READ WRITE



To Auto start all pluggable databases








Restart container database to test:

shutdown immediate



You should now be able to connect the DAL to the HR schema. The instance name in “PDBORCL” not “ORCL”, username is “HR” and password is “demopass” if you used the commands above. Run a command like “SELECT * FROM user_tables” to see if it’s working.


By default, all database logins except SYS and SYSTEM are locked. The password for these accounts was set at install. If you want to unlock any other logins, and set the password, do it with the following command (here for the “HR” demo schema):


When using container databses you also need the command



The HR (Human Resources) database has a small row count, so quick to play with, also it demonstrates a few less obvious relation techniques like:

                Self-referencing foreign key – EMPLOYEES have a manager who is in the same EMPLOYEES table.

                Circular foreign key reference – EMPLOYEES belong to a DEPARTMENTS which have a manager who is in EMPLOYEES

The SH (Sales History) database has a heavier row count (COSTS 80K, CUSTOMERS 50K, and SALES 900K). So operations on this database will take a while, took me about 45 minutes using the DAL to copy to SQL Server or MySQL (Not including FK in MySQL).

There are other test databases (BI, OE, PM, and IX) but I ran into various problems with them so I just skipped them, HR and SH are fine for now.



Quick Removal of Oracle Standard Edition on Windows



When I first started playing with Oracle I was getting in all kinds of pickles, when this happens, as with any unfamiliar software, I like to un-install and just start again. Here is a quick way to get it off your system:

·         Task Manager -> More Details -> Processes (sort by command line)

·         Kill all Oracle related processes. Look out for “RemoteExecService.exe” and “oravssw.exe” (The may also be some Java processes running client tools)

·         Look in Computer Management->Services and confirm all Oracle related services are not running. Stop them if they are.

·         Enter the following at the command prompt to delete all Windows Services (Note the database or instance name is “ORCL” yours may be different and you may have more than one):

sc delete OracleJobSchedulerORCL

sc delete OracleOraDB12Home1MTSRecoveryService

sc delete OracleRemExecServiceV2

sc delete OracleServiceORCL

sc delete OracleVssWriterORCL

sc delete OracleOraDB12Home1TNSListener

·         Open the registry editor “regedt32.exe” delete the following:


[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ODP.NET, Managed Driver]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ODP.NET, Unmanaged Driver]


·         Delete the following file system directories:

C:\app (If you used the default)

C:\Program Files\Oracle

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraDB12Home1 (Or whatever you have here)


·         To be extra safe you can delete the contents of “C:\Users\Administrator\AppData\Local\Temp” or whatever your user’s temp folder is.

You should not need to reboot.



You can work with diagrams using a separate piece of software; the “Oracle SQL Developer Data Modeller” it’s free, download it from here:


NOTE: It requires the JDE be installed. Different downloads options can include this.

Extract the zip and put it somewhere sensible, so you end up with something like: “C:\Program Files\Oracle\datamodeler\datamodeler.exe”.

Running the program for the first time it may ask for the path to JDK browse to “C:\Program Files\Java\jdk1.8.0_102” (the root directory not in bin).

When the program starts you start with an empty unnamed document the explorer panel to the left is showing you the emmmmmmpptty

To create a diagram from and existing database: Data Modeller->File->Import->Data Dictionary. A wizard should appear. Add a new connection, name it and put in the login and password, click the test button then save and connect. This connection should now be listed, select it and click “Next”. Tick the schema (database) you are interested in click “Next”. Select all the tables click “Next”. You end up with a diagram. You can save this out as a “.dmd” (Data Modeller Design) file.

NOTE: The diagram within the model is located at Designs [1]->MyProject->Relational Models [1]->MyModel Right click and select "Show". If you close the windows.

 To update the diagram: Make changes in the diagram…..

Double clicking a table will bring up its properties, you can edit the columns here.

Select the “New Table” tool then draw a box on the diagram to add a table.

No way to automatically synchronise.


Oracle Standard


Spludlow Web Footer