Difference: OracleBasics (1 vs. 8)

Revision 824 Jun 2003 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
The Oracle graphical console is called oemapp. You'll most likely want to run oemapp dbastudio, make sure that X is working first.
Line: 24 to 24
  In sqlplus, set timing on gives elapsed time for sql commands.
Added:
>
>

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

Revision 703 Dec 2002 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
The Oracle graphical console is called oemapp. You'll most likely want to run oemapp dbastudio, make sure that X is working first.
Line: 42 to 42
 execute dbms_transaction.purge_lost_db_entry( ' local id from previous query ');
commit; (don't forget this!)
Added:
>
>

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.
 
Changed:
<
<
-- TobyCabot - 23 May 2002 - 08 Nov 2002
>
>
	 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: 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

-- TobyCabot - 23 May 2002 - 03 Dec 2002

Revision 608 Nov 2002 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
The Oracle graphical console is called oemapp. You'll most likely want to run oemapp dbastudio, make sure that X is working first.
Line: 24 to 24
  In sqlplus, set timing on gives elapsed time for sql commands.
Changed:
<
<
-- TobyCabot - 23 May 2002 - 19 Jul 2002
>
>

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

-- TobyCabot - 23 May 2002 - 08 Nov 2002

Revision 529 Aug 2002 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
The Oracle graphical console is called oemapp. You'll most likely want to run oemapp dbastudio, make sure that X is working first.
Line: 6 to 6
  Get a table's structure: desc tablename.
Changed:
<
<
Date format: '7-Dec-2001'.
>
>
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.

Revision 414 Aug 2002 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
Added:
>
>
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.

Revision 319 Jul 2002 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
Get a list of tables: select table_name from all_tables.
Line: 6 to 6
  Date format: '7-Dec-2001'.
Changed:
<
<
JDBC connection URL format: jdbc:oracle:thin:@hostname:port:instance e.g. jdbc:oracle:thin:@poohpa:1521:CABOT.
>
>
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

Changed:
<
<
-- TobyCabot - 23 May 2002
>
>
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.

-- TobyCabot - 23 May 2002 - 19 Jul 2002

Revision 223 May 2002 - TobyCabot

Line: 1 to 1
 
META TOPICPARENT name="DatabaseTips"
Get a list of tables: select table_name from all_tables.
Line: 8 to 8
  JDBC connection URL format: jdbc:oracle:thin:@hostname:port:instance e.g. jdbc:oracle:thin:@poohpa:1521:CABOT.
Changed:
<
<
-- TobyCabot - 07 Dec 2001
>
>
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

-- TobyCabot - 23 May 2002

Revision 107 Dec 2001 - TobyCabot

Line: 1 to 1
Added:
>
>
META TOPICPARENT name="DatabaseTips"
Get a list of tables: select table_name from all_tables.

Get a table's structure: desc tablename.

Date format: '7-Dec-2001'.

JDBC connection URL format: jdbc:oracle:thin:@hostname:port:instance e.g. jdbc:oracle:thin:@poohpa:1521:CABOT.

-- TobyCabot - 07 Dec 2001

View topic | History: r8 < r7 < r6 < r5 | More topic actions...
Copyright © 2008-2024 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