one Oracle Unit Test Package--PLUTO

pluto-test-framework (http://code.google.com/p/pluto-test-framework/)

The framework is written in PL/SQL and is based on the Oracle object system. The inner-workings of the system are based largely on the JUnit design, with a few PL/SQL'isms added in. The system is built to be easily extended and has hooks to interface with common test runners like Perl's Test::Harness.

PLUTO is lightweight, easy to install, and is free and open for everyone to use. Please feel free to give it a spin and comment back with any feedback that you might have.

Why can not Universal Installer create Listener Service

Problem: I tried to install a oracle server on remote computer via Windows Remote desktop connection. The remote computer is Windows 2003 std server. After fail to install Oracle 10g, I did try to install 11g. But the results were the same. --Every thing were OK except Oracle listener service missing. And i could not run the tnslsnr utility to configure.
Causing: Via Windows Remote desktop connection. It causes all listener operation to fail including create Listener service and manage listener service.

use PLSQL in SQLPLUS

正常情况下, SqlPlus是一个行命令工具, 你只能在其中运行标准的SQL语句或者是SqlPlus特有的命令, 比如print或desc. 但有两种方法, 可以使得SqlPlus进入PL-SQL模式(可以执行诸如dbms_out.output_line()等PL-SQL函数).

方法1:在SqlPlus的提示符下输入begin并回车, 接下来几行输入你的代码, 然后输入end并回车, 最后一行输入/
方法2:在SqlPlus的提示符下输入declare 然后回车, 然后声明变量(可选项), 接下来输入Begin并回车, 接下来几行输入你的代码, 然后输入end并回车, 最后一行输入/

格式如下:
[declare]

[variable1 type;]



--some statements;



/


示例如下:

SQL> DECLARE

v_dname VARCHAR2(14);

BEGIN

v_dname := 'HR';

dbms_output.put_line(v_dname);

END;

/

call stored procedure that return one result set using sqlplus Toad SqlTools

http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/
PLSQL Developer调用和测试一个存储过程非常简单, 找到一个存储过程后, 在快捷菜单中选择Test菜单项目, 输入存储过程的参数, 就可以了.
1. 在SQLPlus中调用这个过程的方法
用SQLPlus来调用/测试一个自定义的存储过程, 相对比较麻烦, 不像用PLSQL Developer方便. 尤其是如果该存储过程返回一个记录集, 我用SQLPLUS一直没有调用成功过. 最主要的原因是: 原来在SQLPlus中不知道怎样使用bind variable. 下面给出一个例子.
假如在PKG_1包中有 PROC_1存储过程, 它接受一个Input参数, 然后返回一个记录集.

PROCEDURE PROC_1(IN_SRVY_ID IN VARCHAR , OUT_TABLE OUT SYS_REFCURSOR )
IS
BEGIN
OPEN OUT_TABLE FOR
SELECT * FROM SRVY WHERE SRVY.SRVY_ID=IN_SRVY_ID;
END PROC_1; 

在SQLPlus中调用这个过程的方法, 然后打印出结果集

SQL> VARIABLE CURSOR_1 REFCURSOR

SQL > VARIABLE IN_SRVY_D VARCHAR2 ( 5 )

SQL > EXEC :IN_SRVY_D := 'SU07'

SQL > EXEC PKG_1.PROC_1(:IN_SRVY_D, :CURSOR_1)

SQL >PRINT :CURSOR_1

http://www.indiastudychannel.com/resources/8519-SQL-SQL-Plus-Interview-Questions-with-Answers-Set.aspx

http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a66736/ch35.htm

2. 在Toad或SQLTools就不像SQLPLus那么容易了, 主要是在于打印记录集比较复杂

--假设emp表中有三个字段分别是 eid, ename, sal. -- select eid, ename, sal from emp; 
create table emp
(
 ename VARCHAR2 ( 5 ) not null ,
 eid NUMBER ( 6 ) not null ,
 sal NUMBER ( 6 )
);

create or replace package PKG_2
IS
 TYPE c_return_table IS REF CURSOR ;
 PROCEDURE SP_GET_EMP_INFO(IN_EMP_ID IN VARCHAR , OUT_TABLE OUT c_return_table);
END PKG_2;


create or replace package BODY PKG_2
IS
 PROCEDURE SP_GET_EMP_INFO(IN_EMP_ID IN VARCHAR , OUT_TABLE OUT c_return_table)
 BEGIN
   OPEN OUT_TABLE FOR
     SELECT eid, ename, sal FROM EMP WHERE eid= IN_EMP_ID;
 END SP_GET_EMP_INFO ;
END PKG_2;


--***************************示例说明*************************************************
--在PLSQL Developer中,调用和显示一个返回记录集的存储过程非常简单(因为它提供了Test Procedure功能),
--但对于其他工具, 却没有这样的功能, 你必须自己写代码来调用, 然后显示
--***********************************************************************************

DECLARE
 IN_EMP_ID emp.eid% TYPE;
 OUT_C_RETURN_TABLE PKG_2.C_RETURN_TABLE;

 --如果从一个Cursor中整条整条地取数据, 则下面的类型必须和Cursor中每个字段的类型都匹配, 这包括类型必须匹配和字段个数必须一样.
 TYPE REC_EMP IS RECORD(   
   ename VARCHAR2(5),   
   eid NUMBER(6),   
   sal NUMBER(6)   
   );

 EMP_ROW REC_EMP;

 --如果Cursor的字段和emp的字段刚好一样, 你不必先定义一个Record类型, 直接声明EMP_ROW
 --EMP_ROW emp%ROWTYPE ;
BEGIN
 IN_EMP_ID := 10;

 --STEP1: OPEN CURSOR BY EXECUTING STORED PROCEDURE
 PKG_2.SP_GET_EMP_INFO(IN_EMP_ID, OUT_C_RETURN_TABLE);

 --STEP1: OR OPEN CURSOR BY SELECT STATEMENT
 /*  
 OPEN C_EMP FOR SELECT * FROM EMP where eid=IN_EMP_ID; 
 */

 LOOP 
   --STEP2: FETCH EVERY ROW FROM CURSOR  
   FETCH OUT_C_RETURN_TABLE
     INTO EMP_ROW; 
   EXIT WHEN OUT_C_RETURN_TABLE% NOTFOUND; 
   --PRINT EVERY ROW  
   DBMS_OUTPUT.PUT_LINE(EMP_ROW.eid || ' - ' || EMP_ROW.ename); 
 END LOOP;

 --STEP3: CLOSE CURSOR
 CLOSE OUT_C_RETURN_TABLE;

 DBMS_OUTPUT.PUT_LINE('EXECUTION OVER.');
END;

Introduction to Oracle Trace Utility

Introduction to Oracle Trace Utility and Understanding The Fundamental Performance Equation
copied from http://tonguc.wordpress.com/2006/12/30/introduction-to-oracle-trace-utulity-and-understanding-the-fundamental-performance-equation/

-- it is better to make a new connection before you start a trace each time, if not information
-- will be appended if any trace has already been started
-- config section, please refer the http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html
-- acronym meanings, please refer http://www.gplivna.eu/papers/otrace.htm

conn hr/hr

-- Set these initialization parameters for your trace session to guarantee the integrity of the trace file

alter session set max_dump_file_size=unlimited;

ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set "_rowsource_execution_statistics" = true
exec dbms_system.ksdddt -- prints out wall clock value

-- show parameters
-- in order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = 'tracefilename' ;

-- start tracing from this session

--specified Events number and level, please refer http://www.psoug.org/reference/trace_tkprof.html
Alter session set SQL_Trace = true ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

-- 或者你想trace某个特定的session, 可以先查的这个sessionID, serial#, 然后调用语句DBMS_System.Set_Sql_Trace_In_Session()来开启trace

--select sid,serial# from v$session where audsid= userenv('SESSIONID');
--EXEC DBMS_System.Set_Sql_Trace_In_Session(sid, serial#, true )

-- also these can be used for tracing
-- execute DBMS_SESSION.SET_SQL_TRACE ({true|false});
-- execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id, serial_id, {true|false});
-- execute DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, ");
-- for more information; http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html
--for more information; http://www.gplivna.eu/papers/oracle_trace.htm
-- Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;

-- stop tracing
Alter session set SQL_Trace = false ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

-- Than go to USER_DUMP_DEST to pick up the trace file. A query that gives your trace file name generated from your session
-- in windows without the tracefile_identifier would be as follows
--
-- select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace_file_is_here
-- from v$process a, v$session b, v$parameter c, v$instance d
-- where a.addr = b.paddr
-- and b.audsid = userenv('sessionid')
-- and c.name = 'user_dump_dest' ;
--
-- Format the trace file with TKPROF and interpret the output.
-- $ tkprof tracefile.trc output.txt [set the options as you like]
-- tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_2220_bind.trc D:\temp\xe_ora_2220_bind.txt explain=hr/hr sys=yes waits=yes sort=prscpu 

This will create a formatted file named trace.txt from the data captured in the .trc file. You can then review the trace.txt file to see the SQL statements, execution plans and counts for statement parses, executes and fetches.
Syntax of TKPROF

TKPROF is run from the operating system prompt. The syntax is:

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

TKProf Usage:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm
http://www.psoug.org/reference/trace_tkprof.html

Oracle tuning 3rd party tools

1. SQL Monitor

http://www.toadsoft.com/downld.html

SQL Monitor is a utility that monitors Oracle, MySQL, and DB2 Client DLL calls, allowing you to capture all SQL calls a program sends to the database via the client DLLs. The Freeware version will allow you to monitor your Toad program.

Statement Tracer for Oracle

http://www.aboves.com/products/statement_tracer_for_oracle/

oraperf

http://oraperf.sourceforge.net/

Oracle 11g desupport isqlplus and sqlplusw

Copied from http://bloggernitin.blogspot.com/2008/01/oracle-11g-web-sql-client.html

The following is a list of components that were part of Oracle Database 10g release 2 (10.2), and are not available for installation with Oracle Database 11g:
• iSQL*Plus/sqlplusw
• Oracle Workflow
• Oracle Data Mining Scoring Engine
• Oracle Enterprise Manager Java console

Oracle recommend migration path:

sqlplusw==>SQL Developer or APEX(Application Express) or sqlplus command line

isqlplus==>OEM web interface

To get a web interface for typing in SQL and getting the results, you can use the Oracle Enterprise Manager (OEM) web interface, accessible at: https://localhost:1158/em (assuming you've got Oracle installed on your computer).

To log into the OEM web interface, you need to have the SELECT ANY DICTIONARY privilege (or you can use the SYSTEM account :-P ). When you're logged in, click on the Data Movement 'tab' and click the SQL Worksheet link (under the related links section). You'll now have the iSQLPlus-like interface for executing SQL queries.

Oracle recommend migration path:

sqlplusw==>SQL Developer or APEX(Application Express) or sqlplus command line

isqlplus==>OEM web interface

Use bind variable in SqlPlus and PL-SQL

Look through the whole article: http://www.rittmanmead.com/2004/03/24/bind-variables-explained/
http://www.akadia.com/services/ora_bind_variables.html

Bind Variables in SQL*Plus.

In SQL*Plus you can use bind variables as follows:

因为变量是专门为PLSQL语言设计的, 如果要在SqlPlus中使用, 那么绑定变量的写法有些特别. 除了声明绑定变量之外, 在其他地方使用绑定变量, 必须在绑定变量之前加上:, 告诉SqlPlus, 这是一个绑定变量. 为绑定变量赋值, 需要使用Exec 语句, 比如exec :deptno:=10 . 调用函数或存储过程也需要使用exec.

SQL>variable deptno number --You can replace variable with var

SQL> exec :deptno := 10 --注意这是为绑定变量赋值的正确写法, 必须加exec

SQL>select * from emp where deptno = :deptno;

What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement)

Bind Variables in PL/SQL

Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the
issues to do with bind variables, to the point where most code that you write already uses bind variables without you
knowing. Take, for example, the following bit of PL/SQL:

下面是一个存储过程, 在存储过程中,你使用的各种变量(包括formal argument和local variable)都是绑定变量.

create or replace procedure dsal(p_empno in number)
as
begin
 update emp set sal=sal*2 
  where empno = p_empno;
 commit;
end dsal;

Now you might be thinking that you've got to replace the p_empno with a bind variable.
However, the good news is that every reference to a PL/SQL variable is in fact a bind
variable.

Dynamic SQL
在PLSQL中, 你需要注意怎样使用绑定变量的场合, 只有在PLSQL语句块中包含了Dynamic SQL.
In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.
Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:
create or replace procedure dsal(p_empno in number)

as
begin
  execute immediate
   'update emp set sal = sal*2 where empno = '||p_empno;
commit;
end;

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set
sal = sal*2 where empno = :x' using p_empno;
commit;
end;

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.

Oracle default port list

copied from: http://www.red-database-security.com/whitepaper/oracle_default_ports.html

Oracle default port list

The following table contains Oracle default ports for different products like Oracle Database or Oracle Application Server.
Changing the default ports can help to stop simple attacks but not real portscans. In the world of Oracle it is very often not possible to change the default port because the port is hardcoded.

At least for the Oracle database (except iasdb) it's is recommended to change the TNS listener port from 1521/1526 to something else.

The IANA default port number can be found here: http://www.iana.org/assignments/port-numbers

Service	Port	Product	How to change
Oracle HTTP Server listen port / Oracle HTTP Server port	80	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle Internet Directory(non-SSL)	389	Oracle Application Server
Oracle HTTP Server SSL port	443	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle Internet Directory(SSL)	636	Oracle Application Server
Oracle Net Listener / Enterprise Manager Repository port	1521	Oracle Application Server / Oracle Database	Edit listener.ora and restart listener
Oracle Net Listener	1526	Oracle Database	Edit listener.ora and restart listener
Oracle Names	1575	Oracle Database	Edit names.ora and restart names server
Oracle Connection Manager (CMAN)	1630	Oracle Connection Manager	Edit cman.ora and restart Connection Manager
Oracle JDBC for Rdb Thin Server	1701	Oracle Rdb
Oracle Intelligent Agent	1748	Oracle Application Server	snmp_rw.ora
Oracle Intelligent Agent	1754	Oracle Application Server	snmp_rw.ora
Oracle Intelligent Agent	1808	Oracle Application Server	snmp_rw.ora
Oracle Intelligent Agent	1809	Oracle Application Server	snmp_rw.ora
Enterprise Manager Servlet port SSL	1810	Oracle Enterprise Manager
Oracle Connection Manager Admin (CMAN)	1830	Oracle Connection Manager (CMAN)	Edit cman.ora and restart Connection Manager
Enterprise ManagerAgent port	1831	Oracle Enterprise Manager
Enterprise Manager RMI port	1850	Oracle Enterprise Manager
Oracle XMLDB FTP Port	2100	Oracle Database	change dbms_xdb.cfg_update
Oracle GIOP IIOP	2481	Oracle Database	Edit listener.ora/init.ora and restart listener/database
Oracle GIOP IIOP for SSL	2482	Oracle Database	Edit listener.ora/init.ora and restart listener/database
Oracle OC4J RMI	3201	Oracle Application Server
Oracle OC4J AJP	3301	Oracle Application Server
Enterprise Manager Reporting port	3339	Oracle Application Server	Edit oem_webstage/oem.conf and restart OHS
Oracle OC4J IIOP	3401	Oracle Application Server
Oracle OC4J IIOPS1	3501	Oracle Application Server
Oracle OC4J IIOPS2	3601	Oracle Application Server
Oracle OC4J JMS	3701	Oracle Application Server
Oracle9iAS Web Cache Admin port	4000	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle9iAS Web Cache Invalidation port	4001	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle9iAS Web Cache Statistics port	4002	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle Internet Directory(SSL)	4031	Oracle Application Server
Oracle Internet Directory(non-SSL)	4032	Oracle Application Server
OracleAS Certificate Authority (OCA) - Server Authentication	4400	Oracle Application Server
OracleAS Certificate Authority (OCA) - Mutual Authentication	4401	Oracle Application Server
Oracle HTTP Server SSL port	4443	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle9iAS Web Cache HTTP Listen(SSL) port	4444	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle TimesTen	4662	Oracle TimesTen
Oracle TimesTen	4758	Oracle TimesTen
Oracle TimesTen	4759	Oracle TimesTen
Oracle TimesTen	4761	Oracle TimesTen
Oracle TimesTen	4764	Oracle TimesTen
Oracle TimesTen	4766	Oracle TimesTen
Oracle TimesTen	4767	Oracle TimesTen
Oracle Enterprise Manager Web Console	5500	Oracle Enterprise Manager Web
iSQLPlus 10g	5560	Oracle i*SQLPlus
iSQLPlus 10g	5580	Oracle i*SQLPlus RMI Port
Oracle Notification Service request port	6003	Oracle Application Server
Oracle Notification Service local port	6100	Oracle Application Server
Oracle Notification Service remote port	6200	Oracle Application Server
Oracle9iAS Clickstream Collector Agent	6668	Oracle Application Server
Java Object Cache port	7000	Oracle Application Server
DCM Java Object Cache port	7100	Oracle Application Server
Oracle HTTP Server Diagnostic Port	7200	Oracle Application Server
Oracle HTTP Server Port Tunneling	7501	Oracle Application Server
Oracle HTTP Server listen port / Oracle HTTP Server port	7777	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle9iAS Web Cache HTTP Listen(non-SSL) port	7779	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle HTTP Server Jserv port	8007	Oracle Application Server
Oracle XMLDB HTTP port	8080	Oracle Database	change dbms_xdb.cfg_update
OC4J Forms / Reports Instance	8888	Oracle Developer Suite
OC4J Forms / Reports Instance	8889	Oracle Developer Suite
Oracle Forms Server 6 / 6i	9000	Oracle Application Server
Oracle SOAP Server	9998	Oracle Application Server
OS Agent	14000	Oracle Application Server
Oracle Times Ten	15000	Oracle Times Ten
Oracle Times Ten	15002	Oracle Times Ten
Oracle Times Ten	15004	Oracle Times Ten
Log Loader	44000	Oracle Enterprise Manager

oracle and SqlServer comparision

Migrating Oracle Databases to SQL Server 2000

http://www.akadia.com/services/sqlsrv2ora.html

Bristle Software SQL Tips

http://bristle.com/Tips/SQL.htm

psoug group

http://psoug.org/reference/sqlserver.html

Cost and envirnment comparsion

http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

return resultset from stored procedure

如果要在procedure中返回一个记录集, 有两种方法, 一种是使用ref cursor, 另一种方法是使用pipelined function, 这里只讲述ref cursor.

Oracle8i, 如果你的存储过程需要返回一个记录集, 那么必须要将这个存储过程放在一个package中, 因为你必须首先定义一个ref cursor类型, 而这个类型只能放在package中定义, 对你而言, 没有其他地方可以定义类型. 记录集类型可以是强类型的, 也可以定义成非强类型的.

create or replace package sprs_temp_obj.PKG_SPRS_SMPLU_H_IND
IS
TYPE c_return_table IS REF CURSOR; --这是一个非强类型记录集类型

TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE; --这里定义了一个强类型的记录类型

END PKG_SPRS_SMPLU_H_IND;

定义之后, 就可以在PKG_SPRS_SMPLU_H_IND package的body中使用这两个记录集类型了.

在Oracle9i, 系统已经有一个SYS_REFCURSOR类型, 所以你的存储过程不必一定要放在某个package里了.

procedure proce1( emp_refcur out SYS_REFCURSOR) -- 这定义了一个非强类型的记录集
is
begin
  Open emp_refcur For
    select * from emp;
end proce1;

那么非强类型的Ref cursor 和sys_refcursor的区别是什么呢?
A REF CURSOR that does not specify the return type such as SYS_REFCURSOR. Meaning the SYS_REFCURSOR can be opened for a dynamic SQL query, where as simple REF CURSOR can not be opened for a query dynamically built at execution time.

query and kill oracle connection session

List all sessions:

     SQL> SELECT s.sid, s.serial#, s.osuser, s.program FROM v$session s; 

Find current session

     SQL> select sid from v$session where audsid= userenv('SESSIONID'); 

Kill one session:

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. If the marked session persists for some time you may consider killing the process at the operating system level, as explained below. Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.
It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

I will focus on Oracle Advanced topics

1. Indexes and Storage Types

2. Transactions and Locks

http://www.wisc.edu/drmt/oratips/sess004.html

3. Oracle and XML Integration

4. Oracle Database Extensions for .NET

5. Debugging PL/SQL
6. Tuning PL/SQL Applications

http://www.wisc.edu/drmt/oratips/sess006.html
7. Tracing PL/SQL Execution

Use Oracle Explain Plan to Tune Your Queries

The first article is very clear.

http://www.evolt.org/article/Use_Oracle_s_Explain_Plan_to_Tune_Your_Queries/17/2986/

http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php#cpu_costing

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm

How to find the worst sql statements in one oracle application

3rd party tool

http://www.wise-oracle.com/



http://www.confio.com/English/Products/Ignite_for_Oracle.php


http://www.confio.com/download/Ignite6_4_63Trial.zip

Oracle Tools

Oracle enterprise management diagnostics pack (not free)

Oracle DBA worst pratice

http://www.dba-oracle.com/t_worst_practices.htm

Oracle SQL top sessions

http://www.dba-oracle.com/oracle10g_tuning/t_sql_top_sessions.htm

How to import very large oracle dump?

How to import very large oracle dump?

Firstly, Make sure you have enough free disk space.

Steps:

第一步. If the system tablespace has not quite large free, you should grow tablespace manually, or set system table extend size automatically.

ALTER TABLESPACE system

AUTOEXTEND ON --turn on the auto extend

NEXT 512K

MAXSIZE 250M;

第二步. Create one bigfile tablespace

--To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...). Oracle Database automatically creates a locally managed tablespace with automatic segment space management.

CREATE BIGFILE TABLESPACE BigTableSpace

DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

Some userful Firefox addons for Oracle

In the following site, there some useful firefox plugins.

http://awads.net/wp/oracle-stuff/oracle-firefox-extensions/
Categories:
Oracle Docs extension
Oracle New extension

http://www.oracle-base.com/misc/Firefox.php
Categories:
Oracle Search Plugins
Oracle Error Search Plugins
Oracle Documentation Search Plugin
English Dictionary plugin

How to choose the right primary key

之前, 我并不知道Oracle是否能创建Compound primary key, 因为我选择Primary Key的原则是尽量用sequence, 或者其他一些选择性很强的字段. 所以我没有在意其他情况.

我在review script时候, 看到下面这个表, 它的结构和key是这样创建的.

create table RMR_USBLTY_VALUES(

SRVY_ID VARCHAR2(5) not null,

SRVY_PHASE_NMBR VARCHAR2(2) not null,

OPERAND_NMBR NUMBER(2) not null,

SMPLU_ID NUMBER(5) not null,

ARBSU_ID VARCHAR2(6) not null,

CNTY_TYPE_CODE VARCHAR2(5) not null,

CMPD_DEMO_ID VARCHAR2(5) not null,

TOTAL_SMPL_CNT NUMBER(6)

);

-- Create/Recreate primary, unique and foreign key constraints

alter table RMR_USBLTY_VALUES

add constraint RMR_USBLTY_VALUES_PK primary key (SRVY_ID, SRVY_PHASE_NMBR, OPERAND_NMBR, ARBSU_ID, CMPD_DEMO_ID, SMPLU_ID, CNTY_TYPE_CODE)

粗体字field都被作为复合主键了, 第一眼, 我就认为这不是一个好的设计, 我猜想, 原设计者认为只有将几个粗体字field组合在一起, 才能在该表中唯一区分每个记录, 所以将这些字段组合起来作为复合主键, 这是十足的为了有个主键而建主键的, 是完全错误的. 理由是: 每次更新与索引相关的字段, oracle将不得不重新计算索引. 上例中, 主键中有这么多字段, 更新字段的几率是比较大的, 也就是说, 随便更新其中一个字段, 就要计算一次索引.

那么什么样的字段可以作为主键呢 ?

先看看, 做主键的条件是什么? (article: Candidate keys: Choosing the right primary key)

1. The candidate key must be unique within its domain (the entity it represents, and beyond, if you also intend to access external entities).

2. The candidate key can not hold NULL values (NULL is not zero. Zero is a number. NULL is 'nonexistent value').

3. The candidate key can never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity.

知道了作为主键应该具备哪些条件之后, 那么到底哪些字段适合作为主键呢? (article: Database Design Guidelines and Rules of Thumb )

通常我们可选择的主键有: You have three choices for defining the primary key for most tables: a 'natural' key, a system generated or 'internal' key, and (for smaller lookup tables) a mnemonic code.

最适合的应该是 a system generated field

Rule of thumb: for any table over 100 records, use a system-generated number as the primary key.

One Oracle course stucture

http://www.objectinnovations.com/CourseOutlines/443.html

exception in plsql

PL sql的exception必须是放在Begin和end块中, 而且每个begin-end 块中只能有一个exception. 这并不表示一个存储过程只能有一个exception.

通常的写法是:

BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;
   ...
   WHEN OTHERS THEN  -- handles all other errors
      ROLLBACK;
END;  -- exception handlers and block end here

一个存储过程中包含多个exception handler的写法是:

BEGIN
   DELETE FROM stats WHERE symbol = 'XYZ';
   BEGIN  ---------- sub-block begins
      SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
         WHERE symbol = 'XYZ';
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         pe_ratio := 0;
   END;  ---------- sub-block ends

   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
   WHEN OTHERS THEN
      ...
END;

我们也可以主动的抛出一个异常, 语法和Pascal出奇的一致. 下面代码是先定义了一个past_due异常类型, 然后在代码中主动抛出一个past_due异常.

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due:

DECLARE

   past_due EXCEPTION;

begin

...

if ... then

   RAISE past_due;  -- this is not handled

end if;

,,

end;

classic orelly oracle book

Orelly Oracle Series

http://www.unix.org.ua/orelly/oracle/

Orelly books

http://www.unix.org.ua/orelly

(copied) Oracle: a suitable career for a young person?

Keywords: DBA career, Oracle

A google blog, I need to read carefully.

http://radiofreetooting.blogspot.com/2005/10/oracle-suitable-career-for-young.html

Oracle video and audio resource

Keywords: Oracle, video, audio, resource, training, tutorials

Oracle Podcast Center
http://www.oracle.com/podcasts/index.html

Oracle Technology Network TechCasts
http://feeds.feedburner.com/OTN_TechCasts

Oracle Database Podcasts
http://feeds.feedburner.com/OracleDatabaseInsider

Oracle Knowledge Base Podcast
http://oracle.ittoolbox.com/research/podcasts/

Simplifying Configuration Complexities http://oukc.oracle.com/static05/opn/oracle9i_database/47193//103107_47193_source/index.htm

Optimizing through understanding
http://oukc.oracle.com/static05/opn/oracle9i_database/022607_1000/022607_1000_source/022607_1000/index.htm

RAC, ASM and Clusterware Technical Webcast Series: RAC for Beginners http://oukc.oracle.com/static05/opn/oracle9i_database/30388//072006_30388/index.htm

Oracle video and audio resource

Keywords: Oracle, video, audio, resource, training, tutorials

Oracle Podcast Center
http://www.oracle.com/podcasts/index.html

Oracle Technology Network TechCasts
http://feeds.feedburner.com/OTN_TechCasts

Oracle Database Podcasts
http://feeds.feedburner.com/OracleDatabaseInsider

Oracle Knowledge Base Podcast
http://oracle.ittoolbox.com/research/podcasts/

Simplifying Configuration Complexities http://oukc.oracle.com/static05/opn/oracle9i_database/47193//103107_47193_source/index.htm

Optimizing through understanding
http://oukc.oracle.com/static05/opn/oracle9i_database/022607_1000/022607_1000_source/022607_1000/index.htm

RAC, ASM and Clusterware Technical Webcast Series: RAC for Beginners http://oukc.oracle.com/static05/opn/oracle9i_database/30388//072006_30388/index.htm