After the announcement of Oracle XE I got really curious about the possibilities of a free Oracle10g database. OK, it is limited to 4GB and it doesn’t allow things like partitioning but still… very usefull for smaller companies and web applications. So, I downloaded the Win32 version from OTN to my laptop (1.1GHz cpu, 512MB memory) and started playing with a DBA point of view.
First thing to notice is the fact that Oracle doesn’t use the well known Universal Installer but a simple Install Shield Wizard instead which makes the installation process quite easy. The Linux version is an RPM so I guess installation won’t be a problem there either. Haven’t tried it…. yet.
The installation wizard creates an entire OFA-tree so just follow its suggestion to use ‘c:\oraclexe’ as the oracle home directory. At first I used an existing oracle home directory which ended up in rather long pathnames. Besides a password for the SYS and SYSTEM account no other user input is required, the wizard is taking care of the services, the listener, the configuration of the webserver and it creates a default database with ‘XE’ as SID… it’s the Express Edition. After the installation has finished you can go to the Oracle XE startpage which leads to a HTML DB application for maintenance.
Oracle XE doesn’t use archive logging by default which isn’t a good thing in my opinion; I’m a DBA, I like availability and the possibility to recover. However this can be changed very easy. Go to the ‘<oracle_home>\database’ directory and add the line “LOG_ARCHIVE_DEST_1=’LOCATION=C:\oraclexe\oradata\XE'” to the initxe.ora file. Shutdown the database, start and mount it, type ‘alter database archivelog;’ open your database and you have a database in archive mode.
If you have more than one oracle home on the machine running Oracle XE it may be wise to remove the path settings to XE. There are no real client applications in its directory tree, besides a SQL*Plus command line interface which can be started from the start menu, so there is no reason to have it in the path. Even worse: it can disable the other Oracle products that you have already installed. My Oracle10g R1 client didn’t work anymore after I installed XE but worked smoothly again after I removed the path-settings to XE
Since Oracle XE is just a normal Oracle database with some restrictions you can use a regular Oracle client to connect to it and it’s also possible to use RMAN for backup and restore. RMAN is not part of the XE distribution which is a great miss because in my opinion it’s the best backup/recover tool for Oracle databases. It is however possible to use RMAN from any other Oracle10g directory.
Most of the database management is done with the webbased interface. Nice look and feel but not everything can be done from here. E.g. it seems to be impossible to change the maximum size of a datafile or add tablespaces. So for some things we still have to use the good ol’ SQL*Plus interface.
As far as I’ve seen Oracle XE is a real nice product. It’s easy to install and doesn’t need much maintenance. It comes with HTML DB for quick development of (demo)applications and works fine with Java IDE’s such as JDeveloper. Next step: the Linux version…
Can I use legal free license Oracle XE for our commercial products?
Rman not included in XE? It is in the windows version I have installed.
I suggest you create a databaselink in your Oracle 8i database that points to the Oracle XE database. Then simply create either views or synomyms to the tables that you want to query and your done.
For sending mail from your database you can use the DBMS_MAIL package.
Hello
I am developing an application using Oraacle XE named-Complaint system
We already have an Oracle 8i as databse for other applications and i would like to use the existing Oracle 8i. I already have some table that i need there. For the moment I have created a materialized view but I would like to save the data in the Orcale 8i databse so that other applications can view the data in real time.
Can you please suggest me how can i do it??
for the same application that i am designing i have to include a function where i have to send some users mail when there is a complaint that has not been solved for a secific date.
How can i automate this part so that the system itself send the mail to notify the users?
if you need more details please email me on: N.Nabeebaccus@happyworldfoods.com
Thanks and Regards
Nazeedah
I want to know more details about session management and types of sessions (current, active ,idle, long ransaction)usage in ORACLE DATABASE XE
The 4GB data limit appears to be enforced at the datafile level, ie. the total size of all datafiles comprising the database cannot exceed 4GB.
This is easy to test, just try to exceed the 4GB total datafile size for your XE database.
This restriction also applies to SYSTEM, UNDO, SYSAUX tablespaces.
If your total database size is currently 3GB you cannot add or extend any datafiles by more than another 1GB.
Eg.
alter database datafile ‘/usr/lib/oracle/xe/oradata/XE/system.dbf’ resize 5000M
*
ERROR at line 1:
ORA-12952: The request exceeds the maximum allowed database size of 4 GB
@Leonard: You could also check http://www.php.net and search for Oracle (spanish documentation is availlable if I’m correct)
@bertjan / Marco: granting unlimited tablespace to a user doesn’t work either (had to change my default script, which I could have known…)
Leonard, a lot of documentation on PHP and Oracle XE can be found on :
http://download-uk.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25317/toc.htm
but only in English I’m afraid… 😉
Hello, I´ve installed oracle xe over linux suse 9.3 there is no problem, but I need help about how to connect php with oracle xe, there is a documentation at Oracle site, but the link is dead, Im from Mexico so my english is not very good
@Jacco: it seems that DB-links are availabel in XE if I understand the following post correct:
http://blogs.ittoolbox.com/oracle/guide/archives/006839.asp?rss=1
Pretty neat.. 😉
@Marco: I’ve tried to create a partioned table but that failed with a verry specific errormessage about partitioning nog being available. Quite sure it doesn’t work… 😉
@Jacco: Are u sure partionioning won’t work? As said to BertJan, i was amazed that “LOG_ARCHIVE_DEST_1” worked…
LOG_ARCHIVE_DEST_1 is an Enterprise Edition parameter.
If system, undo, etc are not limited by the 4Gb boundery, than its probably an base table hack worth, to lift it…
@Jun Yang: The amount of resources your database takes just all depends on settings. I run an oracle 10.1 standard database on my powerbook (1.6 Ghz CPU with 1 GB RAM). The database runs in as little as 85MB RAM. Therefore I disagree with you for the better part…
For just a plain and simple website you are correct: mysql is best. For the more complex things postgresql would be an option, if you didn’t have to find yourself digging around google for a day or two to get some proper answers for questions (e.g. downsizing a database to take less memory, or the other way around: upscaling it). The lack of (commercial) support is rather dangerous if you are entering corporate environments…
@Bertjan, then it was a matter of properly reading the documentation ;). I do wonder what happens if you run the XE linux version in a jail (= sort of a virtual server). This does enable the fully separated environment and is therefore used by some ISP’s to grant shel access. Maybe I need to start testing some more…
Jacco, it is possible to create your own database but you have to stop the default XE instance first. As mentioned in the documentation you can run only one instance on a server so it’s not a matter of skills 😉
And yes, VMWare is the answer to that problem I guess. That way you can give each customer his own (virtual) environment fully seperated from other clients which is imo also good from a security point of view.
I tried it too but was dismayed to learn how much more resources it takes than PostgreSQL or MySQL. The htmldb admin interface is sluggish to say the least. I have since taken Oracle out as a development database. PostgreSQL and MySQL (especially PostgreSQL) are just so much eaiser, leaner and faster.
I have performed some testing with the XE database as found some interesting things as well:
As a trough enfant terrible the first thing I tried was to create a new database. Since no java bases are included I tried this using sqlplus. I wasn’t able to create a new database, which either shows that Oracle made some changes to the engine, or that my skills are below the level that should be expected 😉 This makes the XE database rather useless in a virtual host or ASP environment. If you want to give more then one customer an oracle database, you need to get a new machine or start using VMWare.
Next thing I noticed is that the 4GB is pure data, so system,undo,temp,redolog tablespaces are excluded. This seems to offer a nice “trick” to avoid the 4GB limit, but I haven’t finished testing this (yet).
I also noticed the “gift” called htmldb. htmldb is used for the management website, but also offers some nice features for webdevelopers that are into pl/sql. htmldb itself should be just a shell for most developers, exit to a “normal” package as soon as posible and just use the session and logon stuff is my advice. The expert level of knowledge of htmldb (= understanding the mind of the “creator” of htmldb) before you can create something decent is just a bit too much for normal human beings (especially since creating a package with the htp package is about 100 times easier). Again, the session management does its work rather good. It can hardly be used without htmldb though.
I do disagree on one thing with bertjan on one issue. If you have a limitation to 4 GB data, the lack of partitioned tables should be no issue. The amount of data is limited, no database links are availlable, so if you have a table that big that you need to partion it, either the database design is “broken”, or the sql used to retrieve data is crap. The dbms_sqltune package can help you with this one… (that package is worth an article on this site anyway ;))
I think that 256MB isn’t enough to install XE. I have 1.1GHz Centrino and 512MB and that’s no problem. Try yhe documentation at http://www.oracle.com/pls/xe102/homepage.
Thanks for this post… I have ask – do you have some hardware problem with Oracle XE? I don’t know If I can install it to Centrino 1,6MHz and 256RAM.
Some info about installing it on Debian Linux on http://www.it-eye.nl/weblog/2005/10/31/oracle-xe-installation-on-debian/.