Basic Concept of Performance Tuning in Oracle

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

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

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

How to use trigger to check database constant

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.

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

Auditing Table Data using Trigger

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

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

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

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

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

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

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

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

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

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

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

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 finding 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

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)

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

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