Search · Index

Oracle notes

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: http://www.jlcomp.demon.co.uk/faq/litampersand.html
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 setup_stubs.sh 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

http://openacs.org/forums/message-view?message_id=125969

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
UPDATE
(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:
declare
l_stmt dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
begin
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 );
end;
/
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 10.2.0.1.0 - Production on Tue Feb 28 19:04:30 2006

    Copyright (c) 1982, 2005, Oracle. All rights reserved.


    19:04:34 > connect / as sysdba
    Connected.
    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.
  • Troubleshooting Oracle Dates

    Oracle has an internal representation for storing the data based on the number of seconds elapsed since some date. However, for the purposes of inputing dates into Oracle and getting them back out, Oracle needs to be told to use a specific date format. By default, it uses an Oracle-specific format which isn't copacetic. You want Oracle to use the ANSI-compliant date format which is of form 'YYYY-MM-DD'.

    To fix this, you should include the following line in $ORACLE_HOME/dbs/initSID.ora or for the default case, $ORACLE_HOME/dbs/initora8.ora

    nls_date_format = "YYYY-MM-DD"

    You test whether this solved the problem by firing up sqlplus and typing:

    SQL> select sysdate from dual;

    You should see back a date like 2000-06-02. If some of the date is chopped off, i.e. like 2000-06-0, everything is still fine. The problem here is that sqlplus is simply truncating the output. You can fix this by typing:

    SQL> column sysdate format a15
    SQL> select sysdate from dual;

    If the date does not conform to this format, double-check that you included the necessary line in the init scripts. If it still isn't working, make sure that you have restarted the database since adding the line:

    [joeuser ~]$ svrmgrl
    SVRMGR> connect internal
    Connected.
    SVRMGR> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SVRMGR> startup
    ORACLE instance started.

    If you're sure that you have restarted the database since adding the line, check your initialization scripts. Make sure that the following line is not included:

    export nls_lang = american

    Setting this environment variable will override the date setting. Either delete this line and login again or add the following entry to your login scripts after the nls_lang line:

    export nls_date_format = 'YYYY-MM-DD'

    Log back in again. If adding the nls_date_format line doesn't help, you can ask for advice in our OpenACS forums.

    Useful Procedures

    • Dropping a tablespace

      • Run sqlplus as the dba:

        [oracle ~]$ sqlplus system/changeme
      • To drop a user and all of the tables and data owned by that user:

        SQL> drop user oracle_user_name cascade;
      • To drop the tablespace: This will delete everything in the tablespace overriding any referential integrity constraints. Run this command only if you want to clean out your database entirely.

        SQL> drop tablespace table_space_name including contents cascade constraints;

    For more information on Oracle, please consult the documentation.

    Creating an appropriate tuning and monitoring environment

    The first task is to create an appropriate environment for finding out what is going on inside Oracle. Oracle provides Statspack, a package to monitor and save the state of the v$ performance views. These reports help finding severe problems by exposing summary data about the Oracle wait interface, executed queries. You'll find the installation instructions in $ORACLE_HOME/rdbms/admin/spdoc.txt. Follow the instructions carefully and take periodic snapshots, this way you'll be able to look at historical performance data.

    Also turn on the timed_statistics in your init.ora file, so that Statspack reports (and all other Oracle reports) are timed, which makes them a lot more meaningful. The overhead of timing data is about 1% per Oracle Support information.

    To be able to get a overview of how Oracle executes a particular query, install "autotrace". I usually follow the instructions here http://asktom.oracle.com/~tkyte/article1/autotrace.html.

    Make sure, that the Oracle CBO works with adequate statistics

    The Oracle Cost Based optimizer is a piece of software that tries to find the "optimal" execution plan for a given SQL statement. For that it estimates the costs of running a SQL query in a particular way (by default up to 80.000 permutations are being tested in a Oracle 8i). To get an adequate cost estimate, the CBO needs to have adequate statistics. For that Oracle supplies the dbms_stats package.

     

    Useful links

    Previous Month March 2017
    Sun Mon Tue Wed Thu Fri Sat
    26 27 28 1 2 3 4
    5 6 7 8 9 10 11
    12 13 14 15 16 17 18
    19 (1) 20 21 22 23 24 25
    (1) 26 27 28 29 30 31 1

    Popular tags

    17 , 5.9.0 , 5.9.1 , ad_form , ADP , ajax , aolserver , asynchronous , bgdelivery , bootstrap , bugtracker , CentOS , COMET , CSP , CSRF , cvs , debian , emacs , fedora , FreeBSD , hstore , includelets , install , installation , installers , install-ns , javascript , libthread , linux , monitoring
    No registered users in community xowiki
    in last 30 minutes
    Contributors

    OpenACS.org