OracleBasics - Tech - The Caboteria

The Oracle graphical console is called =oemapp=.  You'll most likely want to run =oemapp dbastudio=, make sure that X is working first.

Get a list of tables: =select table_name from all_tables=.

Get a table's structure: =desc tablename=.

To see dates in a useful format =alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS';=.  Otherwise the default date format is: ='7-Dec-2001'=.  

Oracle lives on the machine's filesystem at =$ORACLE_BASE=.  =$ORACLE_HOME= is the root for the version that's currenlty being used.

When starting up a database, Oracle will look in =$ORACLE_HOME/dbs for the init.ora file.

JDBC connection URL format: =jdbc:oracle:thin:@hostname:port:instance= e.g. =jdbc:oracle:thin:@poohpa:1521:CABOT=.  Oracle packages their jdbc driver in a .zip file (why should they follow the standards?), so look for a file named =classes111.zip= or something like that.

=init.ora= parameters: http://otn.oracle.com/doc/server.815/a67790/ch1.htm

"Oracle views": http://otn.oracle.com/doc/server.815/a67790/ch1.htm

To export a database use =exp=, =exp help=y= will give you help.

To see what database you're currently connected to, =select name from u$database=

In sqlplus, =set timing on= gives elapsed time for sql commands.

---+++ Rollback Segments

Rollback segments don't come online automatically unless you hack Oracle's startup config files: http://www.jlcomp.demon.co.uk/faq/rbk_online.html:

=rollback_segments = (r01, r02, r03, r04)=

---+++ In-doubt xactions

If you end up with "in-doubt transactions" then you could be in a world of hurt since I've seen cases where they locked tables and hung around for days.  You can't always force commits or rollbacks in OEM, so here's a recipe for getting rid of them when they happen (props to Kurt Stam):

Log in as sys sysdba.

=select local_tran_id from dba_2pc_pending;= <br/>
=rollback force '= _local id from previous query_ =';= <br/>

This might not work; in that case you need to get a little more aggressive:

If the server is in "auto undo mode" then you'll need to put it in manual mode (comment out the entries in the init.ora that put it in auto mode - manual is the default) and bounce it.

Then connect as sys sysdba again and:

=execute dbms_transaction.purge_lost_db_entry( '= _local id from previous query_ =');= <br/>
=commit;= _(don't forget this!)_

---+++ Java and Oracle Dates
Kind of a mess.  Try to use bind variables, but if you're building SQL strings then you'll have to format things explicitly on both the Java side and the Oracle side in order to be sure that the formats agree.

<pre>
    static final String DB_DATE_FORMAT = "yyyy-MM-dd H:mm:ss";
    SimpleDateFormat dateFormat = new SimpleDateFormat(DB_DATE_FORMAT);
    Date now = new Date();
    String nowString = dateFormat.format(now);
</pre>

Now in the query itself you need to format the date (again):

="to_date(" + nowString + ", 'YYYY-MM-DD HH24:MI:SS')"=

Here's the javadoc on <nop>SimpleDate: http://java.sun.com/j2se/1.4.1/docs/api/java/text/SimpleDateFormat.html

Here's the Oracle doc on Date Element Conversion Strings: http://download-west.oracle.com/docs/cd/A97630_01/server.920/a96540/sql_elements4a.htm#34950

-- Main.TobyCabot - 23 May 2002 -  03 Dec 2002 <br />