The Caboteria / Tech Web / DatabaseTips / OracleBasics (24 Jun 2003, TobyCabot)
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 or something like that.

init.ora parameters:

"Oracle views":

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:

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;
rollback force ' local id from previous query ';

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 ');
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.

    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);

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 SimpleDate:

Here's the Oracle doc on Date Element Conversion Strings:

-- TobyCabot - 23 May 2002 - 03 Dec 2002

Edit | Attach | Print version | History: r8 < r7 < r6 < r5 < r4 | Backlinks | Raw View | Raw edit | More topic actions
Copyright © 2008-2018 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding The Caboteria? Send feedback