Not so long ago - I found myself in a conversation, with a younger programmer - currently studying, at the business school for computer science in Copenhagen. They where just in the beginning, of learning Oracle, using JDeveloper, and where told by their teacher - that installing, developing and testing at home - where not an option; Because it where very difficult, and expensive and required large computers (something not many students can afford).
Heads up! here's the good news and its called Oracle XE;
Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for:
-
Developers working on PHP, Java, .NET, and Open Source applications
-
DBAs who need a free, starter database for training and deployment
-
Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
-
Educational institutions and students who need a free database for their curriculum
So lets start downloading Oracle XE, installing it on our system, getting it to run in a matter of ..... 30 min ? Sounds so easy - lets give it a try.
Oracle and their partners also offers great tools to work with Oracle XE, like JDeveloper, Zend Core and more. You are properly thinking now, if you have worked with Oracle for a while - yeah right but what about TOAD and all my other great tools - will they work ? The answer is YES. Then you are proberly thinking; Oracle XE - is that some sort of beginners database and nothing for us hardcore driven developers ? NO its not a beginners database - Its made easy to install, run and deploy - however its a 10G database - PLSQL and SQL development is all up to you just how advanced you want it to be.
1. Oracle express XE can be downloaded from here.
Other products of interest can be downloaded from the same place;
JDeveloper for Java developers, Oracle Developer tools for Visual Studie .NET, Zend Core
To be able to download you need to register yourself with Oracle Technology Network. That will give you a lot of options when it comes to downloads, articles, support and more - so do that right away.
Installing Oracle XE
I my environment - I am running on a Toshiba laptop with 512 Meg. of Ram, and I've set of 2 Gig of disk space to hold the installation. I am running on a Windows Professional with all updates.
Oracle XE comes in 2 versions; a West European, single byte with a User interface (UI) available only in English and a Universal edition, multi byte - which makes possible to deploy in all languages, UI comes likewise in many different available languages.
For this installation i choose the single byte edition;
1. When your download are done. You start oracleXE.exe, an self-extracting archive, to begin the installation. The installation launches, an install wizard that will guide you, through the installation steps, in order to get you set.
The installation wizard prompts you for destination path, and I entered the following path, to my installation;
c:\u01\oraclexe\
The installation wizard prompts you for an password for the oracle SYSTEM account. You choose, your password, mine are MANAGER. This password, can alway be changed later (ALTER USER SYSTEM IDENTIFIED BY new_password) when logged in as SYS og SYSTEM.
Thats it !
The installation wizard presents your choices; and installation starts - and from here and on there are no more questions. When finished you have and Oracle XE on your system.
Destination Folder: C:\u01\oraclexe\
Port for 'Oracle Database Listener': 1521
Port for 'Oracle Services for Microsoft Transaction Server': 2030
Port for HTTP Listener: 8080
Oracle XE Database structure
Lets have a look at where and what was installed. As you saw above I choose
c:\u01\oraclexe\
to be my installation directory. Using file manager I see that 2 catalogs have been created below
1. c:\u01\oraclexe\APP
Under APP we only find c:\u01\oraclexe\app\ORACLE, and under that you will find
- admin\XE\ - XE is the default Oracle SID, a common base installation practice, or in case of multiple homes. The catalog contains instance specific information - so giving the catalog, the name of the SID (Oracle system identifier), makes good sence. Under XE you will find bdump, cdump, pfile,-
bdump is also called the background dump destination, and is where Oracle logs errors, and warnings, into a central file called alert_XE.ora. Again you see the default Oracle SID, being used to identify the database. Besides the alertfile, you will also find trace files here. This is where you should pay attention, if for some reson, there is a problem with your oracle database - support, forums and other helpdesks, will ask you for trace and alert file(s).
- admin\product\10.2.0 - This is where the oracle software itself is located; But you will also find the files TNSNAMES.ora and LISTENER.ora under this catalog (see below) which descripts connections to oracle.
- admin\doc - an html file called "getting started" which gives a tutorial and presents the basics on how to work with your new Oracle XE database. Very use full and I will recommend you to read it.
2. c:\u01\oraclexe\ORADATA
- oradata\XE is where you will find Oracle default data files, control files, and log files. Default your Oracle XE runs with minimum requirements regarding log files thats - 2 groups with minimum 1 log file - and both are placed inside the XE catalog.
TNSNAMES.ora and LISTENER.ora
As mentioned earlier the network files TNSNAMES.ora and LISTENER.ora have been placed in
c:\u01\oraclexe\app\oracle\product\10.2.0\server\network\admin
The "tns" in "tnsnames.ora" stands for transparent network substrate. Without going into what all the network layers are, the operative word here is transparent. For the most part, you really do not need to know or care how Oracle networking works. However within this file a descriptor descripes how your tools should connect to Oracle.
In order to get tools like TOAD and SQL Developer to work against your Oracle XE installation - lets look at the TNS file;
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MOT-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
XE is the description name, you would use when connecting, from your tools, that along with a user name, and password. In the case you have more that one Oracle installation, on your system, it might be a good idea to set the following variable in your environment; TNS_ADMIN
Before a database server can receive connections from SQL*Net version 2 (and later) clients, a listener must be active on the server platform. On most platforms, a network listener is used. The configuration file for the network listener is LISTENER.ORA.
The listener name can be any easy-to-use name. The default listener name is LISTENER. If you starts services.msc from your windows - run -> you will see a service called OracleXETNSListener, which is your default listener on your system. There is also a second process/service OracleserviceXE - which takes care of mounting the data files to the system, starting up the default SGA etc. By default these processes have been set to automatic - meaning that they will start at you starts your system - set them to manuel if you want to control it yourself.
Oracle XE Windows environment
Select the START button -> Programs -> Oracle Database 10g Express edition
As you can see your Oracle XE installation have created several menu entries,- Backup database, Recover Database, Go to homepage and getting started.
Startup of Oracle XE
First things first - start your favorite browser and enter the address http://127.0.0.1:8080/ you should now see a login screen - your should enter SYSTEM as user name and the password you gave during installation - mine was MANAGER.
When logged on, you see Oracle HTMLdb - Yes its inclusive on the XE installation. First of all you will see 4 large icons, Administration, Object browser, SQL and Utilities. To your far right, you will find good info/links to XE related articles, forums etc. Below, still to your right you will see info on users, memory, disk, archiving etc.
The administration gives you an overview on diskspace, users, memory, monitoring, top session, tablespaces, datafiles etc.
If you come from an MySQL world - this UI will remind you a little bit of phpMyAdmin,- design is different - but you will quickly get the hang of it.
Oracle XE comes with a set, of default user accounts. Among those the user HR. That account are pr. default locked and you will have to unlock it, in order to use it. Lets try doing that using the XE UI.
1. http://127.0.0.1:8080/
2. Choose administration icon, click Database Users, click HR skema
3. Below manage users,
- Enter a PASSWORD, lets write HR and Confirm PASSWORD: HR
- Account status: will be set to unlocked
- Roles: check roles CONNECT and RESOURCE are active
Using the XE interface as a development platform - i never think was the purpose of the UI - you would have to move towards TOAD or SQL Devleoper. The good thing are; They work against XE.
Now lets try TOAD, we have set TNS_ADMIN above, so it should be very easy. Please NOTE, that a default XE installation, does however have an SQLNET.ORA file located in your network/admin catalog. Rename that file to .OLD or something so you dont have to specify your connect description to be XE.world something.
After you are connected - you should be able to query Oracle XE.
From here you are set. Good luck with your Oracle XE installation.
TOAD can be downloaded from here. An alternative to TOAD is SQL Developer from Oracle
Oracle XE limitations
1. First limit is memory. Oracle XE only addresses up to 1G of memory. It should be more that enough, for a developer database.
2. XE only makes use of 1 CPU - that doesnot mean XE will not multitask - it will however, only perform one tast at a time. XE can be run on machines with more than one CPU, but will only use one.
3. XE only runs against one computer.
4. There is a 4G limit on disk space use. Again, it should cover most needs - if not you should consider an enterprise or standard edition.
Conclution
An Oracle XE installation, is out of the box, very easy. The administration interface via web, gives a good overview of users, sessions, tablespaces etc. I does however prefer my all in one tools - where i can develop and have full DBA rights. However this is more than enough to get you started and 4-5 years in with a god solig 10G oracle database.
Futher infomation about Oracle XE:
Oracle Express XE - Getting started 10.2 Dokumentation
Step by Step installation (Linux) af Christoffer Jones og Alison Holloway
Guides, Tutorials, Software and much more are found here