Oracle Database

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: Negative aspects of the Oracle product: Other noteworthy aspects, some are not really negative for Oracle, but all of these have been deleted earlier without justification:
Resources

Useful queries for DBMS_JOB related things


"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}: [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: [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:

  1. SVRMGR> shutdown immediate;
  2. SVRMGR> startup open SomeInstance;
  3. SVRMGR> alter database datafile 'C:\ORACLE\ORADATA\SomeInstance\TEMP01.DBF' OFFLINE DROP;
  4. SVRMGR> shutdown immediate;
  5. SVRMGR> startup open SomeInstance;
  6. SVRMGR> drop tablespace temp;
  7. 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;
  8. 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

EditText of this page (last edited August 4, 2009) or FindPage with title or text search