A huge tunable RelationalDatabase engine that is expensive to maintain but very efficient when handling huge numbers of concurrent users with large data loads.
See http://www.oracle.com/
Good things about the Oracle product:
- Largest market share. (PHBs really love this one!)
- Oracle has very similar look & feel, tools and interfaces on all platforms -- helping make Oracle applications portable to other platforms that support Oracle.
- Tools and development style similar to IBM's DB2 product -- which makes it easier to transition developers and support staff in a mainframe environment.
- Generates and caches execution plans at run time rather than requiring parallel migration of code and execution plan files, as done with DB2.
- Oracle competes well, on large hardware, with IBM's DB2 product.
- Can be tuned to your requirements.
- Can compile Java code to native C code (with Oracle 8.1.7)
- More books & references to learn from than any other database.
- ExpensiveAdministrators (hey, it's a good thing if you're one of them)
- MultiversionConcurrencyControl. Oracle never locks on reads unless you explicitly mark the read as FOR UPDATE. Readers do not block writers and writers do not block readers. It does this by using the rollback segment for reading old versions of data when performing reads. This can happen on a per-query or per-transaction basis.
- Snapshot isolation. Oracle's serializable isolation level takes a "snapshot" of all values as queried at the beginning of your transaction. This ensures the 3 ConcurrentUpdatePhenomena will not occur. The interesting thing is that Oracle will not block on a write/write conflict. It will instead immediately issue an error, upon which you must either commit the changes to this point, or abort and retry the transaction. This gives more throughput with certain classes of systems, with a tradeoff (see below).
- Very efficient row locking. Locks never escalate in Oracle. This helps to prevent deadlocks.
Negative aspects of the Oracle product:
- JeffGrigg observed to have poor performance on very low-end platforms -- relative to light weight products like MicroSoft SqlServer. (Sometimes by a factor of 2x.)
- SnapshotIsolation. Serializable transactions in Oracle are different from almost every other database as it doesn't conform precisely to the mathematical notion of serializability. Apparently, however, it is consistent with SQL-92 isolation level 3 (SERIALIZABLE) -- which is defined within context of preventing all of the ConcurrentUpdatePhenomena. Notwithstanding this, Oracle potentially allows for histories that are not provably serializable. This may require the user to ensure an exclusive lock is obtained before updating data.
- Flexibility and power come at huge usability costs: HugeManual?, ThousandsOfReferences?, ExpensiveAdministrators?.
- Mixes up the concept of users and schemas, i.e. you have to create user X to get a schema called X.
- Uses its own proprietary SQL syntax for OUTER RIGHT/LEFT/FULL JOIN (until 9iR2, which supports full ANSI join syntax).
- Treats zero length strings as NULL, contrary to Codd's Laws. ( = ) = false; ('' is null) = true. See NullsInSql for the problems this causes.
- Tunable parameters create large, largely under-tested and unpredictable spaces in the tool. I.e. Oracle only tests at 8kB block sizes (http://www.dba-oracle.com/oracle_news/news_blocksize_bugs.htm), and there are bugs discovered irregularly for 16kB and 32kB blocks. Likely due to a bug regarding record reordering in a block, there are instances of users obtaining two orders-of-magnitude in speed by reducing block sizes from 16kB to 4kB (http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/).
Other noteworthy aspects, some are not really negative for Oracle, but all of these have been deleted earlier without justification:
- Practically all "enterprise" RDMBS can be tuned to your requirements.
- Is there real data to back up the "Largest market share" claim? or is this just another urban myth? I have read a book in early 90s that quote shares at 3x% vs 3x% for Oracle vs DB2, the lead is only a few percent. More recently, most claims where Oracle has largest market share usually exclude large hosts computers. Since many DB2s are installed in IBM host machines, counting market share of in terms of server installations vs user count vs total TPC power will give vastly different numbers.
- IBM's DB2 also has very similar look & feel, tools and interfaces on all platforms and I imagine true for other contenders like Sybase also. Both DB2 and Oracle use Java based admin tools.
- Tools and development style similar to IBM's DB2 product, BUT ONLY SUPERFICIALLY. I used DB2 quite a lot and Oracle less so. Moving from DB2 to Oracle will give you lots of nasty surprises, such as Oracle's (lack of) schema support, lack of ANSI SQL support for outer joins until 9i, fewer support for foreign key update constraint (I forget if it is ON UPDATE RESTRICT or ON DETELE RESTRICT, will check on request), etc.
Resources
Useful queries for DBMS_JOB related things
- select text from all_source where name = 'DBMS_JOB' and type = 'PACKAGE' order by line;
- You can query the view user_jobs to see if there is a job waiting. The "WHAT" column contains the command to be run.
"Whip me. Beat me. Make me install Oracle!" -- anon
There are DatabaseBestPractices in regards to versioning the database.
[Question]
I'm somebody who does more programming than database design, and have been working on Oracle for the last four months. Are there many people out there who've done extensive work with Oracle's language -- PL/SQL -- using procedures, triggers, etc.? My impression of them is that while they're quite powerful, they're not very graceful yet. I love being able to rely on triggers to enforce certain states and conditions, and am intrigued by the idea of pushing as much logic into Oracle as possible. But it occurs to me that if I did that, the resulting code might be much less readable.
[Answer]
PL/SQL is a good structured procedural language with well defined exception processing.
But putting business logic in stored procedures and triggers can be a big mistake {or a big advantage}:
- You abandon the benefits of OO programming (until you can use Java inside Oracle). {Not everyone agrees that OO is superior, and it locks data-related info into one language/product}
- It splits your business logic across implementation languages, making it difficult to refactor or reuse code. {This can increase reusability, refactor it in one place and many languages can use it. Most large shops use multiple languages to access the database anyhow. The best approach IMO would be to allow any language for SP's as long as it supports API's}
- Communication across the host program to stored procedure boundary is extremely limited. Data transfer is limited and callbacks are impossible.{Limits are good. All OO method interface are limited too. You are limited to invoking the SP exactly the way the writer intended, and if the resulting SP dataset only needs to be verified once, even if it's invoked in 100 places.}
- Release control and version management can be a big problem: You'll find it generally necessary to coordinate host and database code releases, as mixed versions wouldn't have been tested and most likely won't work. Given that most large companies require that the production releases be done by different groups according to different rules, getting production releases to go right can be a big problem.
- Triggers can give you unexpected and unpleasant side effects when you're thinking "all I have to do is fix this value here, and then everything will be all right."{They can also give expected and pleasant side effects. Debiting an account should trigger a side effect of crediting another account etc.}
[Answer 2]
Yes, creating PL/SQL packages to isolate business logic is a perfectly acceptable practice in Oracle.
Triggers can get quite ugly and their functionality should be restricted to relatively minor functions. Much of the reason for using them in the first place has been taken over by standard oracle functionality (i.e. referential integrity and constraints).
If you write a package, call it an entity, give it insert, update, and delete functionality, and create methods to access the data in optimal ways (in oracle, through ref cursors) you've basically created an object. Of course, this won't soothe the OO purists, because it can't be extended, etc. However, it will still provide you with a central point of entry for given information. On the security side, you can grant execute on the package and give no privileges to the underlying tables.
Even better, as of Oracle 8.1.5 you can write a Java object to manage functionality. The Java object can even talk to other systems via CORBA and IIOP. If you're into Microsoft, you can write COM+ objects for the same thing. ( See CommonObjectRequestBrokerArchitecture )
Remember, the whole point of this exercise, from a programming standpoint, is to put all logic in a single place with a consistent interface. Changes need only be applied to a single object, with none of the clients needing to worry about it. As long as you follow this model, regardless of whether it's true OOP under the hood, you've achieved one of your primary goals of modular design.
OO programming is quickly infiltrating the RDBMS world. Some ideas are good, some are OOP or XML for the simple sake of doing it. I once discussed Java objects and Oracle with the head of Oracle's Java development team ( brilliant fellow, Ed something ). He believed the future of data access was objects with methods and properties being the brokers for all information transfer. That was a year ago and I'm beginning to believe he was correct. -- JoeYoung
An ActiveServerPages application can connect to an oracle db.
[Question]
http://www.freshwater.com/support/notes/noteTN10389.htm
[Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation
This seems to happen when my computer is busy doing something when my oracle ASP application is running.
http://www.freshwater.com/support/notes/noteTN10389.htm says:
"This is an error seen for a Database monitor running against an Oracle database. Change the value of the timeout for the monitor. Either increase the value or set the value to 0. If you choose to set the value to 0, be careful it may cause monitors to skip."
That sounds right on the money. So How do I change the value of the timeout for the monitor? Is that a freshwater specific thing or are they talking about my Oracle/ODBC driver? I heard something about changing the timeout on a command.
[answer]
http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=55&t=000177
disable the "Enable Query Timeout" flag in the ODBC Driver Configuration of the driver
change the CPTIMEOUT in the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle in OraHome8I to 0
'Oracle Install Problem / Solution'
[Question]
How does one move an Oracle database from one Windows2K machine using C:\ to a different Windows2K machine which uses E:\ ?
The following has been looked at, but none of it seems clear
It seems that a full export / full import would be the answer. However, errors about users not existing and Tables already created happe For example:
- imp system/thepasswordfull=y file=E:\downloads\whatever_export.dmp
- :
- . importing SOMEUSER_ADMIN's objects into SOMEUSER_ADMIN
- IMP-00017: following statement failed with ORACLE error 1435:
- "ALTER SCHEMA = "SOMEUSER_ADMIN""
- IMP-00003: ORACLE error 1435 encountered
- ORA-01435: user does not exist
- :
[answer]
You can try the following at OracleDatabasesMakeCopy
[Question]
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\SOMEINSTANCE\TEMP01.DBF'
This is what was used to create the datafile.
CREATE TABLESPACE "TEMP" DATAFILE 'C:\ORACLE\ORADATA\SOMEINSTANCE\TEMP01.DBF' SIZE 218890240 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M DEFAULT STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) ONLINE TEMPORARY
If the file gets corrupted. Can a person just delete the TEMP tablespace and recreate it?
[answer]
From http://www.oracle.com/forums/message.jsp?id=1422839&gid=515246
Response: Of course you can.
The following worked:
- SVRMGR> shutdown immediate;
- SVRMGR> startup open SomeInstance;
- SVRMGR> alter database datafile 'C:\ORACLE\ORADATA\SomeInstance\TEMP01.DBF' OFFLINE DROP;
- SVRMGR> shutdown immediate;
- SVRMGR> startup open SomeInstance;
- SVRMGR> drop tablespace temp;
- SVRMGR> create tablespace temp DATAFILE 'C:\ORACLE\ORADATA\SomeInstance\TEMP01.DBF' SIZE 218890240 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M DEFAULT STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) ONLINE TEMPORARY;
- SVRMGR> startup open SomeInstance
I think someone should move these Q+A's to a new page like OracleQuestions?...
See DatabaseBestPractices, ActiveServerPages, OracleVsMicrosoft, IsOracleTooComplex
CategoryDatabase
OracleVsMicrosoft
Oracle Database