Tamim DBA

Basic Concept of Performance Tuning in Oracle

2010-10-26T00:15:00.000-07:00

Scope of Performance Tuning
There is four main area for Performance Tuning.
1. SQL Tuning – Responsibility of the Developer
2. Database Tuning – Responsibility of the Database Administrator
3. System Tuning – Responsibility of the System Administrator
4. Network Tuning – Responsibility of the Network / LAN / WAN Administrator.
SQL Tuning
Find the problem to a single SQL
You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.
Analyze the SQL to determine the nature of the problem
Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.
Fix the problem.
Almost every performance problem has a solution; it's just that some are more complex than others. In order of increasing complexity and expense, such fixes include:
• Analyze the underlying table to give Oracle's Cost Based Optimizer the information it needs to resolve the SQL efficiently.
• Add one or more hints to the SQL to encourage or discourage certain execution plans.
• Minor changes to the SQL to encourage or discourage certain execution plans.
• Restructure a poorly designed SQL that cannot be resolved efficiently.
• Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
• Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
• Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.
Database Tuning
For optimum performance an Oracle database should be regularly tuned. Only tune a database after it has been up and running for a little while.
• Tuning the cache hit ratio
• Tuning the library cache
• Tuning the log buffer
• Tuning buffer cache hit ratio
• Tuning sorts
• Tuning rollback segments
• Identifying missing indexes
• Identifying index fragmentation
• Identifying free list contention
• Identify significant reparsing of SQL
• Reducing database fragmentation
• Rebuilding indexes
• Reduce thrashing or poor system performance (or how to un-tune oracle?!)
Operating System Tuning
Tune your operating system according to your operating system documentation. For Windows platforms, the default settings are usually sufficient. However, the Solaris and Linux platforms usually need to be tuned appropriately. The following sections describe issues related to operating system performance:
• Basic OS Tuning Concepts
• Solaris Tuning Parameters
• Linux Tuning Parameters
• HP-UX Tuning Parameters
• Windows Tuning Parameters
• Other Operating System Tuning Information
Network Tuning
Network tuning is the performance optimization and tuning of SQL*Net based on an arbitrary UNP which could be TCP/IP, SPX/IP or DECnet. SQL*Net performance can be maximized by synchronization with tunable parameters of the UNP, for example, buffer size.

SQL*Net transaction performance can be divided into components of connect time and query time, where
Total SQL*Net (Net8) Transaction Time = Connect Time + Query Time

Connect time can be maximized by calibration of tunable parameters of SQL*Net and the UNP when designing and implementing networks.

SQL*Net Performance
For this discussion, SQL*Net performance and tuning analysis is based on two categories:
• SQL*Net performance
• SQL*Net tuning

Some useful Linux Command for DBA

2010-09-05T02:27:00.000-07:00

ls: List files
cp: Copy files
mv: Move and rename files
mkdir: Make a directory
alias: Define command macros
rm: Remove files and directories
more: Page through output
head: Show beginning of file contents
tail: Show end of file contents
df: Display filesystem space usage
du: Display directory disk space usage
cat: Show and concatenate files
grep: Search for patterns in files
chmod: Change permissions of files
chown: Change owner of files
zip: Compress and package files together
gedit: A WYSIWYG text editor
export: Make environment settings global
ps: List running processes
touch: Change file time stamps
id: Show information about the current user
sudo: Execute commands as another user

Standard Measurement Tools
• Top Resource Consumers: top
• System Activity Reporter: sar
• Virtual Memory Statistics: vmstat
• I/O Statistics: iostat
• System Log files: /var/log/messages
Linux Tools
• X-based tools: xosview
• The /proc virtual file system
• Free and used memory: free
Tools for monitoring and tuning CPU include:
• top
• pstree and free
• vmstat
• Syntax: vmstat
• Example : # vmstat 2 5
• mpstat –p All
• sar –u
• Syntax: #sar -B
#sar -R
• Example : #sar -B 2 3
#sar -R 2 3
• xosview
• xload
• System Monitor
Measuring Total Memory
• top
• free
• cat /proc/meminfo
Monitoring and Tuning I/O

• /proc file system
• sar -d
• I/O statistics by device [iostat –d]
Syntax : iostat -d
Eample : #iostat -d 2 2
• I/O activity by partition
iostat –d -p
• vmstat
• xosview

Oracle & Iron Man – Great Combination

2010-08-31T00:12:00.000-07:00

http://www.oracle.com/us/ironman2/index.html

How to use trigger to check database constant

2010-08-04T05:03:00.001-07:00

We can use trigger to check database constant and customize error message. For example we have two tables name Department and Employee. I have written two trigger on Update and Delete on Department table, which will responsible to check referential integrity constraint and customize the error message.

Creating Tables Department and Employee

CREATE TABLE DEPARTMENT

    Department_Id          Number(*,0) Not Null Enable,

    Department_Name        Varchar2(45 Byte) Not Null Enable,

    Department_Description Varchar2(500 Byte),

    Primary Key (Department_Id)

);

CREATE TABLE EMPLOYEE

    Employee_Name        Varchar2(45 Byte) Not Null Enable,

    Employee_Ssn         Varchar2(45 Byte) Not Null Enable,

    Employee_Phone       Varchar2(45 Byte) Not Null Enable,

    Employee_Cellular    Varchar2(45 Byte) Not Null Enable,

    Employee_Description Varchar2(500 Byte),

    Department_Id        Number(*,0) Not Null Enable,

    Primary Key (Employee_Ssn) ,

    Foreign Key (Department_Id) References Department (Department_Id) Enable

);

Insert some data on Department and Employee Table

Insert Into Department (Department_Id, Department_Name, Department_Description)

Values ('1001', 'Tecnical', 'Tecnical Department');

Insert Into Department (Department_Id, Department_Name, Department_Description)

Values ('1002', 'Merketing', 'Merketing Department');

Insert Into EMPLOYEE (Employee_Name, Employee_Ssn, Employee_Phone, Employee_Cellular, Department_Id)

Values ('Tamim', '100001', '880175307713', '880175307713', '1001');

Trigger on Delete of Department

Create Or Replace Trigger Td_Department

  After Delete on Department

  FOR EACH row

    DECLARE numrows INTEGER;

  BEGIN

    SELECT COUNT(*) INTO numrows

    FROM Employee

    WHERE  Employee.Department_ID = :old.Department_ID;

    IF (numrows> 0) THEN

      raise_application_error( -20001, 'Cannot DELETE Department because Employee exists.' );

    END IF;

  END;

Test a Delete SQL Statement on Department Table

  Delete

  From Department

  Where Department_Id = 1001

/*

  SQL Error: ORA-20001: Cannot DELETE Department because Employee exists.

*/

Trigger on Update of Department

Create Or Replace Trigger Tu_Department

  After Update On Department

  For Each Row

    Declare Numrows Integer;

  Begin

    IF (:old.Department_ID <> :new.Department_ID) THEN

      Select Count(*) Into Numrows

      FROM Employee

      Where Employee.Department_Id = :Old.Department_Id;

      IF (numrows > 0) THEN

        raise_application_error( -20005, 'Cannot UPDATE Department because Employee exists.' );

      END IF;

    END IF;

  END;

Test a Update SQL Statement on Department Table

  Update Department

  Set Department_Id = 1003

  Where Department_Id = 1001

/*

  SQL Error: ORA-20005: Cannot UPDATE Department because Employee exists.

*/

How To Block Websites Without Using Any Software.

2010-08-04T03:45:00.000-07:00

Sometimes we want to restrict access to some particular

websitefrom our PC but we don’t know how to do it without using some software
for it.

Here I m sharing with you a method to do it without using any software......

1. Run -> drivers

2. Open The file ..\drivers\etc\ hosts using note pad

3. Under "127.0.0.1 localhost" Add 127.0.0.2 www.yahoo.com

Code:

127.0.0.1                 localhost

127.0.0.2                 www.yahoo.com

127.0.0.2                 yahoo.com

That site will no longer be accessible.

Even if you ping www.yahoo.com from command prompt it will not response from original source.

N.B: Please restart your browser before you perform the test.

Auto Draft

2010-07-31T06:46:00.000-07:00

Auditing Table Data using Trigger

2010-07-26T07:42:00.000-07:00

Create New User in Oracle

CREATE User <UserName> IDENTIFIED BY <Passsword>;

Grant User necessary privileges

GRANT CREATE session TO <username>;

GRANT CREATE TABLE TO <username>;

GRANT CREATE TRIGGER TO <username>;

ALTER USER <username> QUOTA UNLIMITED ON <TablespaceName>

Create a Transactional Table

CREATE TABLE AD_DURATIONS
  (
      Ad_Duration_Cn    Varchar2(10 Byte)     Not Null Enable,
      Ad_Start_Date     Date Not              Null Enable,
      Ad_End_Date       Date Not              Null Enable,
      Modified_By       Varchar2(10 Byte),
      Modified_Date     Date                  Default Sysdate,
      Constraint CJ_AD_DURATIONS_PK Primary Key (AD_DURATION_CN)
  );

Insert value in the AD_DURATIONS Table

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10001', To_Date('26-JUL-10', 'DD-MON-RR'),
                              To_Date('30-JUL-10', 'DD-MON-RR'), 'Tamim');

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10002', To_Date('31-JUL-10', 'DD-MON-RR'),
                              To_Date('05-Aug-10', 'DD-MON-RR'), 'Khan');

Create a Log Table for AD_DURATIONS Data

Here Modified_By, Modified_Date and Action is Audit Column.

CREATE TABLE AD_DURATIONS_LOG
  (
      Ad_Duration_Cn    Varchar2(10 Byte),
      Ad_Start_Date     Date,
      Ad_End_Date       Date,
      Modified_By       Varchar2(20 Byte),
      Modified_Date     Timestamp (6),
      Action            Varchar2(20 Byte)
  );

Create a Trigger on AD_DURATIONS table

Create Or Replace TRIGGER AD_DURATIONS_LOG
AFTER DELETE OR UPDATE ON AD_DURATIONS
FOR EACH ROW
Begin
  If Updating Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date,:Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Update');
  Elsif Deleting Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date, :Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Delete');
  End If;
END;

Update on AD_DURATIONS Table

Update Ad_Durations
Set Ad_End_Date = To_Date('31-JUL-10', 'DD-MON-RR')
Where Ad_Duration_Cn = 10001;

Update from AD_DURATIONS Table

Delete From Ad_Durations
Where Ad_Duration_Cn = 10002;

Now Selecting data from AD_DURATIONS_LOG Table.

Select * From Ad_Durations_Log;

CONTROLFILE AUTO BACKUP

2010-07-11T09:44:00.000-07:00

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN has following default parameters and its default values:

RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFORCL.ORA'; # default

Or

RMAN>  SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

By default CONTROLFILE AUTOBACKUP is OFF. But it is strongly recommended enabling CONTROLFILE AUTOBACKUP ON.

Advantage:

RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

Restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file auto backup.

Control file auto backup can keep track of add a data file, resize, increase/decrease the size of data files or etc.

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up

To perform control file auto backup on issue the following command

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

ORA-19809: limit exceeded for recovery files

2010-06-28T03:51:00.000-07:00

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 28 10:25:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
select * from tab
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Shutdown the database first

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Start the database

SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  1250356 bytes
Variable Size             180358092 bytes
Database Buffers          411041792 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

Problem: The value db_recovery_file_dest_size is not enough for generate archive log

          
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest_size           big integer 2G

Solution: Increase the value of db_recovery_file_dest_size following way.

 
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

System altered.

Now open the database.

SQL> alter database open;

Database altered.

Cancel-Based Recovery

2010-06-22T02:44:00.000-07:00

A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database). The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.

In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.

A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.

Recovery Scenario	Preferred Recovery Method
Some important table is dropped	Oracle Time-based Recovery based Recovery
Some bad data is committed in a table	Oracle Time-based Recovery based Recovery
Lost archive log results in failure of complete recovery	Oracle Cancel-based Recovery
Backup control file does not know anything about the arhivelogs	Oracle Cancel-based Recovery
All unarchived Redo Logs and datafiles are lost	Oracle Cancel-based Recovery
Recovery is needed up to a specific archived log file	Oracle Cancel-based Recovery
Recovery through Resetlogs when media failure occurs before backup completion.	Oracle Change-based Recovery
A Tablespace is dropped	Recovery with a backup control file

1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

sqlplus '/ AS SYSDBA'

2. Start a new instance and mount the database:

STARTUP MOUNT

3. Begin cancel-based recovery by issuing the following command:

RECOVER DATABASE UNTIL CANCEL

If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

CANCEL

Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:

ALTER DATABASE OPEN RESETLOGS;

For More Detail please visit here

SQL Tuning Advisor in Oracle 10g

2010-06-14T06:32:00.000-07:00

In Oracle 10g, the SQL tuning process has been automated by introducing a new manageability feature call Automatic SQL Tuning. This feature is designed to work equally well for OLTP and Data Warehouse workloads. Unlike existing tools, automatic SQL Tuning id preformed in the database server by the Oracle query optimizer itself, running in special mode. When running in this mode, the Oracle query optimizer is referred to us the Automatic Tuning Optimizer.

Create a Table to Test a SQL

Create Table TestEmp
       As Select * from Scott.emp;

Write a SQL that you need to tune.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM TESTEMP
ORDER by SAL;

Execute DBMS_STATS Package for Gather Table Satieties

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(
  3    ownname          => 'TEST', 
  4    tabname          => 'TESTEMP',
  5    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  6    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'
  7    );
  8  END;
  9  /
PL/SQL procedure successfully completed.

How to use dbms_sqltune Package

CREATE OR REPLACE PROCEDURE P_SQL_TUNING (
        p_sql CLOB
  )
AS
  v_task VARCHAR2(30);
BEGIN
  /* Drop the task in case we are re-running... */
  BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'sql_tuning_task');
      EXCEPTION
         WHEN OTHERS THEN -- ORA-13605
            NULL;
  END;
 
  /* Create a SQL Tuning task for our SQL... */
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                   sql_text    => p_sql,
                   time_limit  => 1,
                   scope       => 'COMPREHENSIVE',
                   task_name   => 'sql_tuning_task',
                   description => 'Demo of DBMS_SQLTUNE'
                   );          
  /* Execute the task... */
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task');
  
  /* We want to run this again... */
  ROLLBACK;
END P_SQL_TUNING;

Execute p_Sql_Tuning Procedure

SQL> exec P_SQL_TUNING('SELECT emp.EMPNO, emp.ENAME, emp.JOB,
                        emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO
                        FROM TESTEMP emp,TESTEMP empmgr
                        Where emp.empno=empmgr.mgr
                        ORDER by SAL'
                       );
 
PL/SQL procedure successfully completed.

Tuning Result

SQL> set long 80000
SQL> col recs format a90
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS result
     FROM   DUAL;
 
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_task
Tuning Task Owner                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1
Completion Status                 : COMPLETED
Started at                        : 06/14/2010 12:24:18
Completed at                      : 06/14/2010 12:24:18
Number of Index Findings          : 1
 
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : grfz43h6vdf3d
SQL Text   : SELECT emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE,
             emp.SAL, emp.COMM, emp.DEPTNO FROM TESTEMP emp,TESTEMP empmgr
             Where emp.empno=empmgr.mgr ORDER by SAL
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
 
  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TEST.IDX$$_041C0001 on TEST.TESTEMP('MGR');
 
  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 719480185
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    13 |   533 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |         |    13 |   533 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |         |    13 |   533 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TESTEMP |    13 |    52 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TESTEMP |    14 |   518 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."EMPNO"="EMPMGR"."MGR")
   3 - filter("EMPMGR"."MGR" IS NOT NULL)
 
2- Using New Indices
--------------------
Plan hash value: 2840960280
 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    13 |   533 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |                |    13 |   533 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS      |                |    13 |   533 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TESTEMP        |    14 |   518 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX$$_041C0001 |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."EMPNO"="EMPMGR"."MGR")
       filter("EMPMGR"."MGR" IS NOT NULL)

Clean up

The DBMS_SQLTUNE package provides APIs to remove the objects we created as follows.

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
 
PL/SQL procedure successfully completed.

Note:

The Following error message means user have no permission to user Advisor privilege

ORA-13616: The current user <User Name> has not been granted the ADVISOR privilege.

Solutions:

Grant ADVISOR to <User Name>;

How to Clear Buffer Cash and Shared Pool in Oracle 10g

2010-05-30T05:52:00.000-07:00

Flush buffer cache to clear recently cached data blocks

SQL> alter system flush buffer_cache;
System altered.

Flush shared pool to clear recently created SQL parse and execution plan

SQL> alter system flush shared_pool;
System altered.

What is the Difference between “clear buffer” and “alter system flush buffer_cache”

Clear Buffer SQL* Plus Command which is use to clear the SQL*Plus screen and the screen buffer.

Syntax:
CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING}

SQL> clear buffer
buffer cleared

Example:
SQL> Select empno, ename from Scott.emp Where ename='KING';
     EMPNO ENAME
---------- ----------
      7839 KING
SQL> list;
  1* Select empno, ename from Scott.emp Where ename='KING'
SQL> clear buffer;
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.

But alter system flush buffer_cache use to clear data buffer cash in SGA.

Full Database Backup Script

2010-05-22T04:20:00.000-07:00

rem ######################################################################
rem Filename:   FullDBBackup.sql
rem Purpose:    Generate script to do a simple on-line database backup.
rem Notes:      Adjust the copy_cmnd and copy_dest variables and run from
rem             sqlplus. Uncomment last few lines to do the actual backup.
rem ######################################################################
set serveroutput on
set trimspool on
set line 500
set head off
set feed off
spool backup.cmd
declare
  copy_cmnd constant varchar2(30) := 'cp';            -- Use "ocopy" for NT
  copy_dest constant varchar2(30) := '/u02/backup/';  -- C:\BACKUP\ for NT
  dbname  varchar2(30);
  logmode varchar2(30);
begin
  select name, log_mode
  into   dbname, logmode
  from   sys.v_$database;

  if logmode <> 'ARCHIVELOG' then
    raise_application_error(-20000, 'ERROR: Database must be in ARCHIVELOG mode!!!');
    return;
  end if;
  dbms_output.put_line('spool backup.'||dbname||'.'|| to_char(sysdate, 'ddMonyy')||'.log');
  --Loop through tablespaces
  for c1 in (select tablespace_name ts from sys.dba_tablespaces where CONTENTS <> 'TEMPORARY')
  loop
    dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
    --Loop through tablespaces' data files
    for c2 in (select file_name fil
               from   sys.dba_data_files
               where  tablespace_name = c1.ts)
      loop
        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
      end loop;
      dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
    end loop;
  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
  copy_dest||'control.'||dbname||'.'||
  to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_line('spool off');
end;
/
spool off
set head on
set feed on
set serveroutput off
-- Unremark/uncomment the following line to run the backup script
-- @backup.cmd
-- exit

Grant Select/Execute on Database Objects

2010-05-22T04:01:00.000-07:00

rem ##################################################################
rem Filename:   GrantSelectOnView.sql
rem Purpose:    Grant Select on Tables/View to the role
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################
SELECT 'Grant Select on  '||object_name || ' to '||'<Role/User Name>' || ';'
FROM ALL_OBJECTS
Where object_type = '<VIEW/TABLE>'
  and OWNER = '<USER_NAME>';

rem ##################################################################
rem Filename:   GrantExecuteOnPackage.sql
rem Purpose:    Grant Execute on Package/Function/Procedure to the Role
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################
SELECT 'Grant Execute on  '||object_name || ' to '||'<Role/User Name>' || ';'
FROM ALL_OBJECTS
Where object_type = '<PACKAGE/FUNCTION/PROCEDURE>'
  and OWNER = '<USER_NAME>';

Create Public Synonym

2010-05-22T03:50:00.000-07:00

rem ##################################################################
rem Filename:   CreatePublicSynonym.sql
rem Purpose:    Create Public Synonym for a User Objects
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################

SELECT 'Create or replace public synonym  '||object_name || ' for '
                               || OWNER || '.' || object_name || ';'
FROM ALL_OBJECTS
Where object_type  in ('VIEW','PACKAGE')
and OWNER = '<USER_NAME>';

How delete duplicate rows from a Table

2010-05-22T03:24:00.000-07:00

Creating Table and Insert some sample Data

CREATE TABLE PROC_DATA_LOG
  (
    CN      VARCHAR2(20 BYTE),
    A_DATA  VARCHAR2(400 BYTE)
  );
Select * from PROC_DATA_LOG;

CN                   A_DATA                                          
-------------------- ------------
1                    Test Data 1
1                    Test Data 1
2                    Test Data 2
3                    Test Data 3
3                    Test Data 3
4                    Test Data 4
5                    Test Data 5

SQL to delete the duplicate rows

DELETE FROM PROC_DATA_LOG  
WHERE ROWID NOT IN (
                    SELECT MAX (ROWID)
                    FROM PROC_DATA_LOG
                    GROUP BY CN
                   );

All duplicate row base on CN is deleted

Select * from PROC_DATA_LOG;

CN                   A_DATA                                                   
-------------------- ------------
1                    Test Data 1
2                    Test Data 2
3                    Test Data 3
4                    Test Data 4
5                    Test Data 5

Delete Duplicate Rows using Analytic functions

DELETE FROM PROC_DATA_LOG
WHERE ROWID IN ( 
        SELECT ROWID
        FROM (
              SELECT
              ROW_NUMBER() OVER (PARTITION BY CN ORDER BY CN) rnk
              FROM   PROC_DATA_LOG
             )
        WHERE rnk>1
        );

Parameterized View in Oracle

2010-04-10T06:01:00.000-07:00

There is nothing like parameterize view in Oracle, but you can use parameterize view in oracle following way. There is two possible option, You can use the User Environment variable to manage a session variable (dbms_application_info package), another way is write a package to get and set value parameterize view in the global variable or sys_context, later on a view can use the value from, which called so called parameterized view.

Example:

Creating Table

CREATE TABLE APPLICATIONS (
    APPLICATION_CN             VARCHAR2 (10 BYTE) NOT NULL ENABLE,
    APPLICATION_NAME           VARCHAR2 (20 BYTE) NOT NULL ENABLE,
    APPLICATION_DESCRIPTION    VARCHAR2 (200 BYTE),
    APPLICATION_STATUS         VARCHAR2 (5 BYTE) DEFAULT 'True'
  );

Inserting Data in to the table

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10001','ERP','Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10002','Oracle ERP','Oracle Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10003','Web Base Application','Web Base Application','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10004','Desktop Application','Desktop Application','True');

Commit;

Create a Package

create or replace
PACKAGE TestParam_VW_PKG AS
 PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN            VARCHAR2
  );

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2;
END TestParam_VW_PKG;

Create a Package body for Get and Set operation

 
create or replace
PACKAGE BODY TestParam_VW_PKG AS
  g_Application_CN            VARCHAR2(10);
  PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN          VARCHAR2
  ) AS
  BEGIN
    g_Application_CN := p_Application_CN;
  END;

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_Application_CN;
  END;
END TestParam_VW_PKG;

Create a View base on the Packege

CREATE OR REPLACE FORCE VIEW APPLICATION_VW ("APPLICATION_CN", "APPLICATION_NAME", "APPLICATION_DESCRIPTION", "APPLICATION_STATUS")
AS
  SELECT APPLICATION_CN,
    APPLICATION_NAME,
    APPLICATION_DESCRIPTION,
    APPLICATION_STATUS
  FROM  APPLICATIONS
  WHERE APPLICATION_CN = TestParam_VW_PKG.F_GET_APPLICATION_CN;

How to use the view

SQL> Exec TESTPARAM_VW_PKG.P_SET_APPLICATION_CN('10001');

PL/SQL procedure successfully completed.

SQL> SELECT APPLICATION_CN, APPLICATION_NAME, APPLICATION_DESCRIPTION, APPLICATION_STATUS
FROM APPLICATION_VW ;
APPLICATION_CN    APPLICATION_NAME     APPLICATION_DESCRIPTION        APPLICATION_STATUS
---------------   -------------------- -----------------------------  ------------------
10001             ERP                  Enterprise resource Planning   True

Database system development life cycle

2010-03-30T02:43:00.000-07:00

Database planning is the management activities that allow the stages of the database system development lifecycle to be realized as efficiently and effectively as possible.

System Definition involves identifying the scope and boundaries of the database system, including its major user views. A user view can represent a job role or business application area.

Requirements collection and analysis is the process of collecting and analyzing information about the organization that is to be supported by the database system, and using this information to identify the requirements for the new system.

Requirements collection and analysis is a preliminary stage to database design. The amount of data gathered depends on the nature of the problem and the policies of the organization. Identifying the required functionality for a database system is a critical activity, as systems with inadequate or incomplete functionality will annoy the users, and may lead to rejection or underutilization of the system. However, excessive functionality can also be problematic as it can overcomplicate a system, making it difficult to implement, maintain, use, and learn.

Database design is the process of creating a design that will support the organization’s mission statement and mission objectives for the required database system. This stage includes the logical and physical design of the database.

DBMS selection

The aim of DBMS selection is to select a system that meets the current and future requirements of the organization, balanced against costs that include the purchase of the DBMS product and any additional software/hardware, and the costs associated with changeover and training.

[caption id="attachment_636" align="aligncenter" width="204" caption="Database Design Life Cycle"][/caption]

Application design involves designing the user interface and the application programs that use and process the database. This stage involves two main activities: transaction design and user interface design.

Transaction Design

An action, or series of actions, carried out by a single user or application program that accesses or changes the content of the database.

There are three main types of transactions:

retrieval transactions

update transactions

mixed transactions

User interface design

In addition to designing how the required functionality is to be achieved, we have to design an appropriate user interface for the database system. This interface should present the required information in a user-friendly way.

Prototyping involves building a working model of the database system, which allows the designers or users to visualize and evaluate the system.

There are two prototyping strategies in common use today:

Requirements prototyping and

Evolutionary prototyping.

Requirements prototyping uses a prototype to determine the requirements of a proposed database system and once the requirements are complete the prototype is discarded. While evolutionary prototyping is used for the same purposes, the important difference is that the prototype is not discarded but with further development becomes the working database system.

Implementation is the physical realization of the database and application designs.

Data conversion and loading involves transferring any existing data into the new database and converting any existing applications to run on the new database.

Testing is the process of running the database system with the intent of ﬁnding programming errors.

Operational maintenance is the process of monitoring and maintaining the system following installation.

The process of monitoring and maintaining the database system following installation.

In this stage, the database system now moves into a maintenance stage, which involves the following activities:

Monitoring the performance of the database system. If the performance falls below an acceptable level, the database may need to be tuned or reorganized.

Maintaining and upgrading the database system (when required). New requirements are incorporated into the database system through the preceding stages of the lifecycle.

For More detail please read the book Database Solutions A step-by-step guide to building databases

A Tutorial on Database Design concept please click here.

How to Delete All Objects for a User in Oracle

2010-03-03T05:27:00.000-08:00

If you don't have system level access(Sys or System), and want to clean your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects

Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:


purge recyclebin;

Following Script can run the full System

SET feedback off
SET verify off
SET echo off
PROMPT Number of objects IN the schema:
SELECT COUNT(*) FROM user_objects;
PROMPT Finding objects TO DROP

SET termout OFF
SET pages 80
SET heading OFF
SET linesize 120

PURGE RECYCLEBIN;

SPOOL c:\cleanup_schema.sql
SELECT 'SPOOL c:\cleanup_schema.log' FROM dual;
SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM user_objects ORDER BY object_id DESC;
SELECT 'PURGE RECYCLEBIN;' FROM dual;
SELECT 'PROMPT After cleanup, number of objects remaining in schema' FROM dual;
SELECT 'SELECT COUNT(*) FROM user_objects;' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'EXIT;' FROM dual;

SPOOL OFF

SET termout ON
PROMPT Dropping Objects now ...
-- Execute the sql file created earlier
--@c:\cleanup_schema.sql
EXIT
/

Reff: http://forums.oracle.com/forums/message.jspa?messageID=1057359

Customize SPLIT Function written in Oracle (PLSQL)

2010-02-23T08:12:00.000-08:00

A common task when selecting data from a database is to take a set of values a query returns and format it as a comma delimited list. Another task that's almost as common is the need to do the reverse take a comma delimited list of values in a single string and use it as a table of values.

To serve this purpose I preferred following function to Split string.

Split Function:

create or replace function split(
  p_InputString   varchar2,
  p_Position      number,
  p_Delimiter     varchar2
  )
return varchar2
is
   v_list varchar2(32767) := p_Delimiter || p_InputString;
   v_start_position number;
   v_end_position number;
begin
   v_start_position := instr(v_list, p_Delimiter, 1, p_Position);
   if v_start_position > 0 then
      v_end_position := instr( v_list, p_Delimiter, 1, p_Position + 1);
         if v_end_position = 0 then
            v_end_position := length(v_list) + 1;
         end if;
         return(substr(v_list, v_start_position + 1, v_end_position - v_start_position - 1));
   else
         return NULL;
   end if;
end split;
/
Show Error

Output 1:

select split('Tamim Khan',1,' ') from dual;
SPLIT('TAMIMKHAN',1,'')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------------------
Tamim

Output 2:

select split('Tamim Khan',2,' ') from dual;

SPLIT('TAMIMKHAN',1,'')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------
Khan

Output 3:

select split('Dhanmondi,Dhaka',1,',') from dual;                        
SPLIT('TAMIMKHAN',1,'')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
----------------------------------------------
Dhanmondi

Output 4:

select split('Dhanmondi,Dhaka',2,',') from dual;                        
SPLIT('TAMIMKHAN',1,'')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
----------------------------------------------
Dhaka

Wrapping PL/SQL

2010-02-18T02:27:00.000-08:00

Wrapping PL/SQL source is not so different using this method than it is using the WRAP binary. It is more flexible. Oracle developers can take advantage of many PL/SQL or Java utilities that exist in $ORACLE_HOME/bin or in supplied packages.

These utilities can load, encrypt, tune or debug code objects. This chapter will focus on the utilities that perform these functions, including wrap, dbms_profiler, dbms_debug, loadjava, dropjava and loadpsp.

The first of these utilities to be discussed will be the wrap utility that allows PL/SQL developers to encrypt their code.

Procedure/Function                Description

WRAP                                     Overloaded function that returns the wrapped PL/SQL source code when provided with the original source.

CREATE_WRAPPED             Procedure that wraps the source code provided as input. It’s faster than using WRAP.

Create Country Table

SQL Code:
CREATE TABLE COUNTRIES
  (
    C_NAME  VARCHAR2(15 BYTE)
  );

Insert Data into Country Table

SQL Code:
REM INSERTING into COUNTRIES
Insert into COUNTRIES (C_NAME) values ('Poland');
Insert into COUNTRIES (C_NAME) values ('Germany');
Insert into COUNTRIES (C_NAME) values ('United States');
Insert into COUNTRIES (C_NAME) values ('Portugal');
Insert into COUNTRIES (C_NAME) values ('Czech Republic');
Insert into COUNTRIES (C_NAME) values ('China');
Insert into COUNTRIES (C_NAME) values ('Slovakia');
Insert into COUNTRIES (C_NAME) values ('Slovenia');

The DBMS_DDL package for Dynamic Wrapping

Use the overloaded WRAP function with EXECUTE IMMEDIATE to create the wrapped code, as the following example illustrates:

SQL Code:
DECLARE
  v_Procedure VARCHAR2(32767);
BEGIN
  v_Procedure :=  'create or replace PROCEDURE p_CountryList '
                  || 'AS '
                  || 'cursor crCountryList is '
                  || 'SELECT C_NAME FROM COUNTRIES; '
                  || 'BEGIN '
                  || ' for rc_countryList in crCountryList loop '
                  || '    dbms_output.put_line(rc_countryList.C_NAME);'
                  || ' end loop; '
                  || ' End;'; 
  EXECUTE IMMEDIATE DBMS_DDL.WRAP(v_Procedure);
END;

To see the wrapped procedure, select the text from the USER_SOURCE view.

SQL Code:
SELECT text
FROM user_source WHERE name = 'P_COUNTRYLIST';
 
Output:
TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------------------------------
PROCEDURE p_CountryList wrapped                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
a000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
b2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
c2 d2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
OBXTyU790UHZX1Pz/x6SK6zaQdowg0zwLcsVfC+EOo7QS9JbZQ2c1RiJi1GYkXCXR+Hnl92a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
6yUmVMJdr3L/xdu8xYazffm813KuNLzJ0jnlJ1HKbCYmfWw51ec5hoXDZ2KBtgvDWEsq4RNq                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
3cHIqVKojYPLLqq6zBQLjSwb9gkM4mO6aOBaz2jrVZ+/wI9dqhpqq1XumZC83+s=

Execute the wrapped procedure to verify all works as expected

Set Serveroutput on
execute p_CountryList;

This returns the following result

Poland
Germany
United States
Portugal
Czech Republic
China
Slovakia
Slovenia

CREATE_WRAPPED

DBMS_DDL.CREATE_WRAPPED works in a similar way. The use of EXECUTE IMMEDIATE is not required.

Use SYS.DBMS_DDL.CREATE_WRAPPED (v_procedure)
with replace of EXECUTE IMMEDIATE DBMS_DDL.WRAP(v_Procedure)

PL/SQL Wrap Utility for Encryption

The wrap utility (wrap.exe) provides a way for PL/SQL developers to protect their intellectual property by making their PL/SQL code unreadable.

Instead, the wrap utility takes a readable, ASCII text file as input and converts it to a file containing byte code. The result is that the DBA, developers or anyone with database access cannot view the source code in any readable format.

The command line options for wrap are:

wrap iname=[file] oname=[file]

· iname – The name of the unencrypted PL/SQL file to be used as input (your source file).

· oname – The name of the output file. This file will be encrypted.

For more information http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/c_wrap.htm

How to use Ref Cursor example

2010-02-17T03:03:00.000-08:00

How to use Ref Cursor example

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Declaring a SYS_REFCURSOR Cursor Variable

The following is the syntax for declaring a SYS_REFCURSOR cursor variable:

name SYS_REFCURSOR;

name is an identifier assigned to the cursor variable.

The following is an example of a SYS_REFCURSOR variable declaration.

DECLARE
    rc_emp SYS_REFCURSOR;

Opening a Cursor Variable

Once a cursor variable is declared, it must be opened with an associated SELECT command. The OPEN FOR statement specifies the SELECT command to be used to create the result set.

Syntax:
OPEN name FOR query;

name is the identifier of a previously declared cursor variable. Query is a SELECT command that determines the result set when the statement is executed. The value of the cursor variable after the OPEN FOR statement is executed identifies the result set.

Declaring a User Defined REF CURSOR Type Variable

You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:

Create a referenced cursor TYPE

Declare the actual cursor variable based on that TYPE

The syntax for creating a user defined REF CURSOR type is as follows:

Syntax:
TYPE cursor_type_name IS REF CURSOR [RETURN return_type];

The following is an example of a cursor variable declaration.

DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    rc_emp emp_cur_type;

Closing a Cursor Variable

Unlike static cursors, a cursor variable does not have to be closed before it can be re-opened again. The result set from the previous open will be lost. The example is completed with the addition of the CLOSE statement.

Syntax:
CLOSE cursor_name;

N.B:

Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.

Null cannot be assigned to a cursor variable.

The value of a cursor variable cannot be stored in a database column.

Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.

Permitted Cursor Variable Parameter Modes

Operation	IN	IN OUT	OUT
OPEN	No	Yes	No
FETCH	Yes	Yes	No
CLOSE	Yes	Yes	No

Create Country Table

SQL Code:
CREATE TABLE COUNTRIES
  (
    C_NAME  VARCHAR2(15 BYTE)
  );

Insert Data into Country Table

SQL Code:
REM INSERTING into COUNTRIES
Insert into COUNTRIES (C_NAME) values ('Poland');
Insert into COUNTRIES (C_NAME) values ('Germany');
Insert into COUNTRIES (C_NAME) values ('United States');
Insert into COUNTRIES (C_NAME) values ('Portugal');
Insert into COUNTRIES (C_NAME) values ('Czech Republic');
Insert into COUNTRIES (C_NAME) values ('China');
Insert into COUNTRIES (C_NAME) values ('Slovakia');
Insert into COUNTRIES (C_NAME) values ('Slovenia');

Procedure using ref cursor

create or replace
PROCEDURE p_Get_Country (
    p_CountryName     IN VARCHAR2
    )
  AS
  rc_Country_Search  SYS_REFCURSOR;
  v_query            VARCHAR2(30000) := NULL;
  rows_fetched       number;
  TYPE t_tab IS TABLE OF COUNTRIES%ROWTYPE;
  rec_tab t_tab;
BEGIN
  if (p_CountryName is not null) then
    v_query := 'SELECT C_NAME FROM COUNTRIES 
                where UTL_MATCH.JARO_WINKLER_SIMILARITY
                (C_NAME,'|| '''' || to_char(p_CountryName) || ''''  || ') > 60';
  else
    v_query := 'SELECT C_NAME FROM COUNTRIES';
  end if;

  OPEN  rc_Country_Search FOR v_query;
  FETCH rc_Country_Search BULK COLLECT INTO rec_tab;
  rows_fetched := rc_Country_Search%ROWCOUNT;
  --Print Data to the console
  dbms_output.put_line('--------------------------------');
  FOR i IN 1..rows_fetched LOOP
      dbms_output.put_line('Country Name -> '      ||  rec_tab(i).C_NAME||CHR( 13 ) || CHR( 10 ));
  END LOOP;
  dbms_output.put_line('--------------------------------');
  CLOSE rc_Country_Search;
END p_Get_Country;

Out put 1

Set serveroutput on
DECLARE
  RC_COUNTRY_SEARCH SYS_REFCURSOR;
BEGIN
  P_GET_COUNTRY(
    P_COUNTRYNAME => null
  );
END;
--------------------------------
Country Name -> Poland
Country Name -> Germany
Country Name -> United States
Country Name -> Portugal
Country Name -> Czech Republic
Country Name -> China
Country Name -> Slovakia
Country Name -> Slovenia
--------------------------------

Out put 2

Set serveroutput on
DECLARE
  RC_COUNTRY_SEARCH SYS_REFCURSOR;
BEGIN
  P_GET_COUNTRY(
    P_COUNTRYNAME => 'Slov'
  );
END;
--------------------------------
Country Name -> Slovakia
Country Name -> Slovenia
--------------------------------

Procedure for Ref Cursor population and return through out parameter

create or replace
PROCEDURE p_Get_Country2 (
    p_CountryName     IN VARCHAR2,
    rc_Country_Search  OUT SYS_REFCURSOR
    )
  AS
  v_query         VARCHAR2(30000) := NULL;
  TYPE t_tab IS TABLE OF COUNTRIES%ROWTYPE;
  rec_tab t_tab;
BEGIN
  if (p_CountryName is not null) then
    v_query := 'SELECT C_NAME FROM COUNTRIES
                where UTL_MATCH.JARO_WINKLER_SIMILARITY
                (C_NAME,'|| '''' || to_char(p_CountryName) || ''''  || ') > 60';
  else
    v_query := 'SELECT C_NAME FROM COUNTRIES';
  end if;

  OPEN  rc_Country_Search FOR v_query;

END p_Get_Country2;

Populate Ref cursor variable in C#.Net Console base application using ADO.NET

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace TEST_ODP
{
    class Program
    {
        static void Main(string[] args)
        {
            // create connection
            OracleConnection conn = new OracleConnection("Data Source=orcl;User;Password=test;");
            // create the command for the stored procedure
            OracleCommand cmd = new OracleCommand();

            Console.WriteLine("Please input your searching keyword.");
            string str = Console.ReadLine();

            cmd.Connection = conn;
            cmd.CommandText = "p_Get_Country2";
            cmd.CommandType = CommandType.StoredProcedure;

            // add the parameters for the stored procedure including the REF CURSOR
            // to retrieve the result set
            cmd.Parameters.Add("p_CountryName", OracleType.VarChar).Value = str.ToString();
            cmd.Parameters.Add("rc_Country_Search", OracleType.Cursor).Direction = ParameterDirection.Output;

            // open the connection and create the DataReader
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();

            // output the results and close the connection.
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                    Console.Write(dr[i].ToString() + ";");
                Console.WriteLine();
            }
            conn.Close();
            Console.ReadLine();
        }
    }
}

Manual Cleanup process and Uninstall Oracle for Windows system

2010-02-16T02:13:00.000-08:00

Uninstall all Oracle components using the Oracle Universal Installer (OUI).

Delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key which contains registry entries for all Oracle products by using regedit.

Delete any references to Oracle services/components in the following registry location: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/. Looks for key entries that starts with “Ora” which are obviously related to Oracle.

Reboot the workstation.

Delete the ORACLE_BASE directory. (i.e C:\Oracle)

Delete the C:\Program Files\Oracle directory.

Empty the temp directory.

Empty the recycle bin.

utl_match package for string matching

2010-02-15T07:18:00.000-08:00

There are four functions included in the package utl_match use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source string into the destination string.

Source: $ORACLE_HOME/rdbms/admin/utlmatch.sql

Oracle added the UTL_MATCH package in Version 10gRelease 2 to compare strings. The four functions included in the package use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source into the destination string. The functions are broken down into two categories. The categories are actually the algorithms employed to analyze the strings.

Levenshtein Distance

The Levenshtein Distance (LD) algorithm, commonly called the Edit Distance (ED) algorithm, is the older of the two supported methods. It measures the distance between the source and destination strings. By distance, we’re referring to the number of changes required to turn the source string into the destination string.

Jaro-Winkler

The Jaro-Winkler algorithm is the second category of algorithms used in UTL_ MATCH. These functions take the same two arguments, but instead of simply calculating the number of steps required to change the source string to the destination string, it determines how closely the two strings agree with each other. The algorithm also tries to take into account the possibility of a data entry error when determining similarity.

Procedures/Functions

edit_distance

Returns the number of changes required to turn the source string into the destination string using the Levenshtein Distance algorithm.

function edit_distance returns binary_integer (
 s1    in        varchar2,
 s2    in        varchar2       
);

edit_distance_similarity

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match.

function edit_distance_similarity returns binary_integer (
 s1    in        varchar2,
 s2    in        varchar2       
);

jaro_winkler

Instead of simply calculating the number of steps required to change the source string to the destination string returns similarity based on Jaro-Winkler distance algorithm, determines how closely the two strings agree with each other and tries to take into account the possibility of a data entry error.

function jaro_winkler returns binary_double (
 s1    in       varchar2,
 s2    in       varchar2       
);

jaro_winkler_similarity

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match but tries to take into account possible data entry errors.

function jaro_winkler_similarity returns binary_integer (
 s1    in                 varchar2,
 s2    in                 varchar2       
);

Example 1:

SQL> SELECT UTL_MATCH.EDIT_DISTANCE('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
         1

SQL> SELECT UTL_MATCH.EDIT_DISTANCE_SIMILARITY('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
        88

SQL> SELECT UTL_MATCH.JARO_WINKLER('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
 9.25E-001

SQL> SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY ('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
        92

Example 2:

CREATE TABLE TEST
( 
        test_id        number primary key, 
        test_name      varchar2(20), 
        test_date      date
);  

INSERT INTO TEST values(1, 'Sony CD Player', '20-FEB-2010');
INSERT INTO TEST values(2, 'Sony CD Player', '24-FEB-2010');
INSERT INTO TEST values(3, 'Pioneer DVD Player', '25-FEB-2010');
INSERT INTO TEST values(4, 'Sony CD Player', '25-FEB-2010');
INSERT INTO TEST values(5, 'Bose Speaker', '22-FEB-2010');
INSERT INTO TEST values(6, 'Tascam CD Burner', '25-FEB-2010');
INSERT INTO TEST values(7, 'Nikon digital camera', '22-FEB-2010');
INSERT INTO TEST values(8, 'Canon digital camera', '26-FEB-2010');

Commit;

Select TEST_ID,TEST_NAME,TEST_DATE
FROM TEST
WHERE  UTL_MATCH.JARO_WINKLER_SIMILARITY(test_name,'dogotal') > 60;

TEST_ID                TEST_NAME            TEST_DATE
---------------------- -------------------- -------------------------
7                      Nikon digital camera 22-FEB-00
8                      Canon digital camera 26-FEB-00

Proxy User in Oracle 10g

2010-02-10T01:52:00.000-08:00

First we’ll create two database Schema users(cpdata, mpdata) and One Big Application User (app_user called Proxy User). The app_user will be used by a web application to connect to the database. Usually the application user, in this case cpdata and mpdata, will be authenticated by the web application through app_user.

Create User cpdata and mpdata as a Sys or System user which contain schema objects.

create user cpdata identified by cpdata;
grant create session, create table to cpdata;             
alter user cpdata quota unlimited on users;              

create user mpdata identified by mpdata;
grant create session, create table to mpdata;             
alter user mpdata quota unlimited on users;

Create Table on cpdata and mpdata schema

create table cpdata.test1
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);      

create table mpdata.test2
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);

Create User app_user, for a Proxy User.

create user app_user identified by app_user;
grant create session to app_user;

Grant Proxy Authentication

alter user cpdata grant connect through app_user;
alter user mpdata grant connect through app_user;

Now Connect cpdata and mpdata user using Proxy Authentication and access there schema objects

SQL> connect app_user[cpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
CPDATA

SQL> desc test1;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> connect app_user[mpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
MPDATA

SQL> desc test2;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> select sys_context('USERENV','PROXY_USER') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
-------------------------------------------------------------
APP_USER

More :

http://www.oracle.com/technology/products/ias/toplink/doc/1013/main/_html/dblgcfg008.htm

http://www.oracle.com/technology/tech/java/oc4j/1013/how_to/how-to-ds-proxy/doc/how-to-ds-proxy.html

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