Oracle Notes

How do I use a literal ampersand within a SQL statement for INSERT, SELECT, etc.?

This is a sqlplus issue, I believe. See this link for a workaround:
If you're on a *nix box - yasql is a great replacement for sql*plus.

Is there an equivalent of Postgres' vacuum analyze

No, not really, but you can do something similar to speed up a table:
 analyze table ticket_xrefs compute statistics;
This will look at the usage statistics, and update them. This can dramatically increase performance when the amount of data in a table has changed a lot.

Moving one Oracle instance to another server

Apparently, Oracle installations are fairly self-contained. In theory, at least, you should be able to move an installation from one server to another by shutting down the server, tarring up the /ora8 directory, sftping it to another server, untarring it, and possibly running the script.

Needless to say, this is much easier than reinstalling, exporting the database, and importing it back in. No guarantees on how well it works, however.

Turning on autotrace

Turning on Autotrace

Hierarchical queries and getting around join problem with CONNECT BY

Getting [too long] messages?

If you're using Oracle, this thread describes how to set up Aolserver so that you receive the entire error message from the Oracle database driver. If you get an error message starting with SQL: [too long], then you need to read this. Sometimes, for long error messages, your error messages are truncated, which makes tracking down the errors more difficult.

Efficient updates

You can do this
  (SELECT col1, value
     FROM t1, t2
    WHERE t1.key = t2.key
    AND t2.col2 = :other_value)
SET col1 = value

Using lots of dynamic SQL (more than 32768 chars?)

You cannot use a clob, so use the dbms_sql package:
    l_stmt          dbms_sql.varchar2s;
    l_cursor        integer default dbms_sql.open_cursor;
    l_rows          number  default 0;
    l_stmt(1) := 'insert';
    l_stmt(2) := 'into foo';
    l_stmt(3) := 'values';
    l_stmt(4) := '( 1 )';

    dbms_sql.parse( c             =>   l_cursor,
                    statement     => l_stmt,
                    lb            => l_stmt.first,
                    ub            => l_stmt.last,
                    lfflg         => TRUE,
                    language_flag => dbms_sql.native );

    l_rows := dbms_sql.execute(l_cursor);

    dbms_sql.close_cursor( l_cursor );
This is from Tom Kyte, Oracle God.

Online table updates

DBMS Redefinition package

PL/SQL exception handling

  • Exception handling in PL/SQL

    SPfile and Pfile startups

    $ sqlplus /nolog
    SQL*Plus: Release - Production on Tue Feb 28 19:04:30 2006
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    19:04:34 > connect / as sysdba
    19:05:21 sys@vs> startup pfile=/u01/app/oracle/admin/vs/pfile/init.ora.192006104950
    ORACLE instance started.
    Total System Global Area  285212672 bytes
    Fixed Size		    1218992 bytes
    Variable Size		   92276304 bytes
    Database Buffers	  188743680 bytes
    Redo Buffers		    2973696 bytes
    Database mounted.
    Database opened.
    19:05:37 sys@vs> 19:05:37 sys@vs> 
    19:05:38 sys@vs> create spfile from pfile='/u01/app/oracle/admin/vs/pfile/init.ora.192006104950';
    File created.
  • Comments