in last 10 minutes
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 analyzeNo, not really, but you can do something similar to speed up a table:
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.analyze table ticket_xrefs compute statistics;
Moving one Oracle instance to another serverApparently, 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 autotraceTurning on Autotrace
Hierarchical queries and getting around join problem with CONNECT BYhttp://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 updatesYou 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 updatesDBMS 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.