Oracle Database 11g Express Edition (Oracle Database XE)
Oracle Database Express Edition 11g Release 2 for Windows
x64
OracleXE112_Win64.zip
The express edition is much easier to install and use than
the full edition of Oracle.
http://127.0.0.1:8080/apex/
SQL Workshop -> Object Browser
You can browse and create tables from here
Oracle Database 12c Release 2 (12.2.0.1.0)
Standard Edition 2 and Enterprise Edition
Oracle Database 12c Release 2 (12.2.0.1.0) for Microsoft
Windows (x64)
winx64_12201_database.zip
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”:
ODP.NET_Managed_ODAC122cR1.zip
Link to:
Oracle.ManagedDataAccess.dll
Other program?
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.
·
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.
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
·
ALTER USER HR IDENTIFIED BY demopass ACCOUNT UNLOCK;
·
GRANT RESTRICTED SESSION TO hr;
To Auto start all pluggable databases
CREATE OR REPLACE TRIGGER startup_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END;
/
Restart container database to test:
shutdown immediate
startup
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):
ALTER USER HR IDENTIFIED BY demopass ACCOUNT
UNLOCK
When using container databses you also need the command
GRANT RESTRICTED SESSION TO hr;
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.
https://oracle-base.com/articles/misc/manual-oracle-uninstall
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\SOFTWARE\ORACLE]
[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:
http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html
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