Oracle Illustrated


February 21, 2010  8:53 AM

Migrating from 9i to 11g – Table Creation scripts

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Drop sequence dummy_seq
/

create sequence dummy_seq start with 1
/

create table emp
as
select
dummy_seq.nextval empno,
object_name empname,
object_id sal,
CASE WHEN ROWNUM BETWEEN 1 and 28000 then ‘CLERK’
WHEN ROWNUM BETWEEN 28001 and 30000 then ‘SALESMAN’
WHEN ROWNUM BETWEEN 30001 and 30150 then ‘PRESIDENT’
WHEN ROWNUM BETWEEN 30151 and 35000 then ‘MANAGER’
ELSE ‘ANALYST’ end Job,
round(
dbms_random.value(1000,100000)) comm,
CASE WHEN ROWNUM BETWEEN 1 and 10000 then 320
WHEN ROWNUM BETWEEN 10001 and 13051 then 120
WHEN ROWNUM BETWEEN 13052 and 26001 then 380
WHEN ROWNUM BETWEEN 26002 and 27002 then 630
ELSE 550 end deptno
from all_objects
/

DELETE FROM emp tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY empname ORDER BY empno, empname ) duplicate FROM emp ) qry
WHERE qry.duplicate > 1)

(The above delete is to run the examples selecting from various blocks with gaps)

create unique index emp_idx on emp (empno )
/

exec dbms_stats.gather_table_stats(ownname => ‘SYSTEM’, tabname => ‘emp’, cascade => TRUE)

CREATE TABLE DEPT AS
SELECT distinct deptno ,
CASE WHEN deptno = 380 then ‘ACCOUNTING’
WHEN deptno = 120 then ‘RESEARCH’
WHEN deptno = 550 then ‘SALES’
WHEN deptno = 320 then ‘OPERATIONS’
ELSE ‘IT’ end dname, ‘SINGAPORE’ Loc
FROM emp

create unique index dept_idx on dept (deptno )
/

CREATE TABLE BONUS
AS select empname, job, sal, comm from emp
/

create index bonus_idx1 on bonus (empname, job )
/

create table salgrade as
select distinct job job,
min(sal) losal, max(sal) hisal from bonus
group by job
/

create index sal_idx1 on salgrade (job );

February 21, 2010  8:52 AM

Migrating from 9i to 11g – Miscellaneous features

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

1. DDL wait

Now all Data Definition languages can wait using DDL_LOCK_TIMEOUT() – default value is 0, the maximum value is 100000 (27.77 hours) !!
Lets look at a quick example –
create table test_lock (a number);
insert into test_lock values (1);

another session –
alter session set ddl_lock_timeout=4
create index test_lock_idx on test_lock (a);

SQL> create index test_lock_idx on test_lock (a);
create index test_lock_idx on test_lock (a)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:04.00
SQL>
It will wait for 4 seconds then error out.

**********************************************************************************

2. Non published statistics

Now, we can gather statistics without applying them into the DB actually.
SQL> Exec DBMS_STATS.SET_SCHEMA_PREFS(‘system’, ‘publish’, ‘false’);
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.12
If we want to set it as pending then,
ALTER SESSION SET OPTMIZER_PENDING_STATISTICS=TRUE
Later, we can use PUBLISH_PENDING_STATS procedure in DBMS_STATS package.

**********************************************************************************

3. Stale percentage

Prior to Oracle 10g statistics becomes stale if there is 10% variation in the data. From Oracle 11g (default is 10%) the stale percentage can be set using SET_SCHEMA_PREFS

EXEC DBMS_STATS.SET_SCHEMA_PREFS(‘SYSTEM’, ‘STALE_PERCENT’, ’20’);
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.89

**********************************************************************************

4. SIMPLE_INTEGER

SIMPLE_INTEGER

This new predefined subtype is newly introduced in Oracle 11g. It has a range of –2147483648 .. 2147483648 and does not include NULL values. Where ever PLS_INTEGER was allowd SIMPLE_INTEGER is allowed. The performance of SIMPLE_INTEGER will be more if the PLS_CODE_TYPE is set to NATIVE.

It is estimated to be faster than PLS_INTEGER and also, it eliminates the overhead of overflow checking.

A quick example to check which is better? – PLS_INTEGER or SIMPLE_INTEGER

PLS_INTEGER

DECLARE
n PLS_INTEGER;
BEGIN
FOR i in 1 .. 500000 LOOP
n := n + 1;
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.78

SIMPLE_INTEGER

DECLARE
n SIMPLE_INTEGER := 0;
BEGIN
FOR i in 1 .. 500000 LOOP
n := n + 1;
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.28

**********************************************************************************
5. Flush buffer_cache feature from Oracle 10g

Now, we can flush the buffer cache. Lets take a look at a quick example –
First time –
SQL> set autotrace traceonly statistics
SQL> select count(1) from dept;
Elapsed: 00:00:01.73
Statistics
———————————————————-
217 recursive calls
0 db block gets
37 consistent gets
5 physical reads
Second time –
Elapsed: 00:00:00.09

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
ALTER SYSTEM FLUSH BUFFER_CACHE
SQL> select count(1) from dept;
Elapsed: 00:00:00.04
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads

**********************************************************************************

6. Create and Rebuild index online

Prior to Oracle 11g it is not possible to create / rebuild index online – now, its possible to do so.
CREATE INDEX ON

ONLINE;
ALTER INDEX REBUILD ONLINE

**********************************************************************************

7. NOT Null with Default

Not Null columns with default value are maintained in the dictionary.
Not much of space is consumed
Instant add
Existing columns are not converted.

**********************************************************************************

8. Hierarchal query enhancements

New Features

The new hierarchical query features in Oracle Database 10g are:

New Operator
* CONNECT_BY_ROOT – when applied to a column, returns the value for that column for the root row
New Pseudo columns
* CONNECT_BY_ISCYCLE –
* CONNECT_BY_ISLEAF

CONNECT_BY_ROOT

SELECT EMP_NAME, CONNECT_BY_ISLEAF
FROM emp
START WITH EMPNO = 159
CONNECT BY PRIOR EMP_ID = MGR_ID;

CONNECT_BY_ISCYCLE

SELECT LEVEL, LPAD(‘ ‘,2*(LEVEL – 1)) || EMP_NAME “EMPLOYEE”, EMPNO, MGR_ID
FROM EMP
START WITH EMPNO = 159
CONNECT BY NOCYCLE PRIOR EMPNO = MGR_ID;

CONNECT_BY_ISLEAF

SELECT EMP_NAME, CONNECT_BY_ISLEAF
FROM EMP
START WITH EMPNO = 159
CONNECT BY PRIOR EMPNO = MGR_ID;

**********************************************************************************

9. Insert or Update – Value too large ORA-12899

Till Oracle 9i its not possible to determine if inserted value for a column is too long – from Oracle 10g its possible to determine. – boon !!

SQL> insert into dept values (600, ‘Front Office’, ‘SINGAPORE’);
insert into dept values (600, ‘Front Office’, ‘SINGAPORE’)
*
ERROR at line 1:
ORA-12899: value too large for column
“SYSTEM”.”DEPT”.”DNAME” (actual: 12, maximum: 10)

Cool !!

**********************************************************************************

10. Inlining

Inlining – introduced in Oracle 11g. It replaces the call to to subroutine with a copy of the code. Prior to Oracle 11g every time a subprogram is called – a call to the original procedure is made. But, from Oracle 11g a copy of procedure runs which is faster than the original. “inlined”.

Though this does not happen automatically !! We need to set some parameters for PLSQL to find out automatically which parts of the code should be inlined.

1.PLSQL_OPTIMIZE_LEVEL – set it to a value 2 or 3
2 -> No automatic inlining ; 3 -> automatic inlining
How to set this? => ALTER PROCEDURE COMPILE PLSQL_OPTIMIZE_LEVEL = REUSE SETTINGS;
2.PRAGMA INLINE
This can be done within the PLSQL program. This can be either set to YES or NO depends on whether you want the code to be inlined or not.

It is recommended by Oracle that we should inline small and frequently called programs. Only local subprograms can be inlined. Cursor functions should not be inlined. By inlining huge programs – it could increase the size of the program. Caution to be taken while inlining DETERMINISTIC functions.

Example –
CREATE OR REPLACE PROCEDURE inline_proc
IS
v_value NUMBER;
FUNCTION multiply_1(p1 number, p2 number) RETURN NUMBER as
begin
RETURN p1 * p2;
end multiply_1;
BEGIN
pragma INLINE (inline_proc, ‘YES’);
v_value := multiply_1(5,6) ;
END inline_proc;

Note: To check how the subprogram is inlined we can either use DBMS_HPROF or Debugger in toad.

**********************************************************************************

11. PLSQL Packages

1. DBMS_DATAPUMP

Feature in Oracle 10g.

This feature provides high speed, parallel, bulk data and metadata movement of Oracle database contents. DBMS_DATAPUMP – this new package enables faster data and metadata movement. Oracle recommends to use this data and metadata movements rather than using Export and Import.

2. DBMS_SCHEDULER

This is a new packaged procedure in Oracle 10g

This feature is introduced in Oracle 10g. DBMS_JOB is depricated and is available only for backup compatibility. Oracle recommends not to use DBMS_JOB anymore as it could not exist in the future versions of Oracle.

Advantage of DBMS_SCHEDULER –
1.Oracle procedures, packaged procedure, packaged functions and functions can be executed
2.Binary and shell scripts can be scheduled.

For administering the job scheduling one need to have SCHEUDLER_ADMIN role. With DBMS_JOB initialization parameter is required but, the same is not needed for DBMS_SCHEDULER.

To create job -

BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘CREATE_JOB_TEST’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN NULL; END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=hourly; byminute=0′,
end_date => NULL,
enabled => TRUE,
comments => ‘Job’);
END;
/

To create schedule -

begin
dbms_scheduler.create_schedule
(schedule_name => ”,
start_date=> trunc(sysdate)+18/24,
repeat_interval=> ‘FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;’,
comments=>’Sample job’);

To create program -

begin
dbms_scheduler.create_program
(program_name=> ‘Pgm_create’,
program_type=> ‘STORED_PROCEDURE’,
program_action=> ”,
enabled=>true,
comments=>’Procedure to create program’ );
end;

To set attribute -

begin
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ”,
attribute => ‘start_date’,
value => to_date(‘22.02.2010 18:15′,’dd.mm.yyyy hh24:mi’)
);

To run a job -

begin
dbms_scheduler.run_job(”,TRUE);
end;

We can get information about scheduled jobs in – user_scheduler_jobs
——————————————————————————————————————

3 DBMS_CRYPTO

create or replace Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
IS
V_Key RAW(128) := UTL_RAW.cast_to_raw(‘testkey1′); — Key
l_data RAW(2048) := utl_raw.cast_to_raw(p_data);
l_encrypted RAW(2048);
BEGIN
NULL;
l_encrypted := dbms_crypto.encrypt — Algorithm
( src => l_data,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => V_KEY );

Return l_encrypted;
END encrypt;
/

create or replace Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
IS
V_Key RAW(128) := UTL_RAW.cast_to_raw(‘testkey1′); — Key
l_decrypted RAW(2048);
BEGIN
l_decrypted := dbms_crypto.decrypt — Algorithm
( src => p_data,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => V_KEY );

Return utl_raw.cast_to_varchar2(l_decrypted);
END decrypt;
/

——————————————————————————————————————

4 DBMS_SQLTUNE

Step 1 – Drop the task (if already exist to avoid error while creating)

exec DBMS_SQLTUNE.DROP_TUNING_TASK( task_name => ‘emp_tune’);

ORA-13605: The specified task or object emp_tune does not exist for the current user

Step 2 – Create and execute the task

declare
tuning_Task varchar2(500);
begin

tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => ‘select * from emp e;’,
time_limit => 1,
scope => ‘COMPREHENSIVE’,
task_name => ‘emp_tune’,
description => ‘Demo’
);

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘emp_tune’);
end;
/

Step 3 – Select the report
set pagesize 0
set long 100000000
set longchunksize 1000
set linesize 180
set timing on trim spool on

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘emp_tune’) FROM dual;

RECOMMENDATIONS
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : emp_tune
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/01/2010 18:29:13
Completed at :05/01/201018:29:15

——————————————————————————-
SQL ID : Qwdvfv2yvswx1
SQL Text: SELECT e.*, d.* FROM emp e, dept d WHERE e.deptno = d.deptno
AND NVL(empno, ‘0’) = :empno

——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-

1- Statistics Finding
———————
Table “EMP” and its indices were not analyzed.

Recommendation
————–
Consider collecting optimizer statistics for this table and its indices.

**********************************************************************************

5 DBMS_FGA

BEGIN
DBMS_FGA.add_policy(
object_schema => ‘LUX’,
object_name => ‘EMP’,
policy_name => ‘COMM_AUDIT’,
audit_condition => NULL,
audit_column => ‘COMM’,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’);
END;
/

Other PL/SQL Procedures included in 10g & 11g -

Other important PL/SQL packages included are –

dbms_file
dbms_epg
utl_recomp
utl_dbws utl_i18n utl_lms
dbms_streams_tablespace_adm
dbms_warning
dbms_xmlstore
dbms_stat_funcs
dbms_streams_auth
dbms_streams_messaging
dbms_server_alert
dbms_service
dbms_sqltune
dbms_frequent_itemset
dbms_monitor
dbms_scheduler
dbms_dimension
dbms_file_transfer
dbms_frequent_itemset
dbms_advanced_rewrite
dbms_advisor
dbms_datapump

**********************************************************************************

12. Performance features

This topic is out and out based on the other topics. Performance features in Oracle 11g could be improved by using the following techniques –

A. Inlining instead of calling the sub-program over and again
B. Native Compilation for not too many sql related processes as it is pretty simple to set native compilation
C. Faster Triggers
D. Fast Dual
E. Result Caching
F. Adaptive Cursor Sharing
G. DBMS_SQLTUNE help
H. Optimizer and stats collection enhancements
I. DML Error logging vs. normal error handling
J. Merge with Delete (in place of separate Merge and Delete)

No new feature comes without a cost – always better to test the new feature before applying the same !!

**********************************************************************************

13. PLW-06009 Warning

New PLW warning is available now. This warning means that the OTHERS handler would exit without executing some form of RAISE or a call to the standard RAISE_APPLICATION_ERROR procedure. Best pl/sql practices suggest that the OTHERS handler must always pass an exception upward.

CREATE OR REPLACE PROCEDURE samp_num
IS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN null;
END samp_num;
/

Lets try to enable warnings and see what happens –

ALTER PROCEDURE samp_num COMPILE PLSQL_warnings = ‘enable:all’ REUSE SETTINGS;

SP2-0805: Procedure altered with compilation warnings

If we make it as OTHERS NULL then, it is marked as warning.

SQL> SHOW ERRORS
Errors for PROCEDURE SAMP_NUMM:

LINE/COL ERROR
——– —————————————————————
6/10 PLW-06009: procedure “P” OTHERS handler does not end in RAISE
or RAISE_APPLICATION_ERROR

**********************************************************************************

14. RBO obsoleted

Rule Based Optimizer – RBO is obsolete from Oracle 10g. We can still set the optimizer mode to RULE but just that no new features are implemented / will be implemented from Oracle 10g and it is depricated. The default optimizer mode is set to ALL_ROWS (Cost based optimizer).

Oracle 9i

Oracle 9i
SQL> show parameter optim

NAME TYPE VALUE
———————————— ———– ——————————
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

NAME TYPE VALUE
———————————— ———– ——————————
plsql_optimize_level integer 2
SQL>

SQL> select * from emp;

40541 rows selected.

Elapsed: 00:00:08.42

Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id | Operation | Name |
———————————-
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
———————————-

Oracle 11g
SQL> show parameter optim

NAME TYPE VALUE
———————————— ———– ——————————
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

NAME TYPE VALUE
———————————— ———– ——————————
plsql_optimize_level integer 2
SQL>

SQL> select * from emp;

40541 rows selected.

Elapsed: 00:00:08.42

Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id | Operation | Name |
———————————-
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
———————————-

Note
—–
– rule based optimizer used (consider using cbo)

**********************************************************************************

15. Secure file LOBs

Secure File LOBs (known as “FastFiles” in the beta releases) is an Oracle 11g database feature that provides faster access to unstructured data stored in LOB columns.
Write access to Secure Files is faster than a standard Linux file system, while read access is about the same. In addition, it features data compression, encryption and data deduplication.

Benefits

LOB compression
LOB encryption
Deduplication (detect duplicate LOB data and only store one copy)
Faster access to LOB data

CREATE TABLE secu_files (a NUMBER, l_clob CLOB)
lob (l_clob) STORE AS SECUREFILE secfile_segment_name
(TABLESPACE users
RETENTION AUTO
CACHE LOGGING
STORAGE (MAXEXTENTS 5))

Table created.

Deduplication – Quick overview –

It enables the storage of physical image for duplicate data and reduces space consumption. There are no negative impact on the read operations. This is part of the advanced compression option in 11g.

**********************************************************************************

16. XML Enhancements

Storing XML in Oracle is there for quite sometime now. We can store the XML data in the database either in unstructured or CLOB format. With Oracle 11g we are allowed to store in binary format also.

Improvements in Oracle 10g –

DBMS_XMLGEN now supports hierarchal queries
Import or export of XML data becomes very much possible

Improvements in Oracle 11g –

Binary format – Binary XML stores it after parsing the designed specifically for XML.

Create or replace directory xml_dir as ‘F:\app\Luxananda’

Create table test_binary_xml OF XMLTYPE XMLTYPE STORE AS BINARY XML
/
INSERT into test_binary_xml VALUES (XMLTYPE(BFILENAME, ‘xml_dir’, ‘sample_doc.xml’)))

XML Index – New universal index can be created on Binary and LOB based XMLType. All the possible paths are indexed by default.

**********************************************************************************

17. Autotrace / Tkprof

1 Autotrace

Autotrace is a handy utility that shows the “actual” execution statistics and explain plan of a query.

SET AUTOTRACE ON – Enables autotrace and displays the EXPLAIN PLAN of the query and displays the STATISTICS.
Here it actually executes the query
SET AUTOTRACE ON EXPLAIN – Displays the EXPLAIN PLAN and the results.
SET AUTOTRACE ON STATISTICS – Displays the STATISTICS and the results.
SET AUTOTRACE TRACEONLY – Does not display the results. Displays the EXPLAIN PLAN and STATISTICS. Executes the query.
SET AUTOTRACE TRACEONLY EXPLAIN – Displays only the EXPLAIN PLAN; Does not execute the query.
SET AUTOTRACE TRACEONLY STATISTICS – Executes the query and displays the statistics only.
SET AUTOTRACE OFF – Switches off autotrace.

Explain plan vs autotrace:

Explain plan ON – Shows what the database will do if the query is executed – it is an estimate.
Autotrace (SET AUTOTRACE …) – Displays the actual – what the database has actually done by firing the query – actual time taken, number of rows hit, consistent gets etc.,

Autotrace should always be the first tool – Just after writing the query its good to run the query in autotrace on full volume environment to see how much time, resources, rows etc., are retrieved by executing the query. Note – while running the query we tend to supply literals, but the actual program might have bind variable. We cannot say that the performance of a query with literals and bind variables would be exactly the same.

So, if your query has bind variable it is always good to trace like this so that you can have the explain plan same as you will get when the process runs.

set autotrace on

variable :var_name
exec :var_name :=

select * from

WHERE condition = :var_name;

ORACLE 9i
SQL> set autotrace traceonly

SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM scott.EMP A) QRY whe
re qry.row_Asc between 1 and 11;

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2175649969

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 1582 | 8 |
|* 1 | VIEW | | 14 | 1582 | 8 |
| 2 | WINDOW SORT | | 14 | 518 | 8 |
| 3 | WINDOW SORT | | 14 | 518 | 8 |
| 4 | COUNT | | | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 2 |
—————————————————————————-

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

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

Note
—–
– cpu costing is off (consider enabling it)

Statistics
———————————————————-
124 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
1360 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed

ORACLE 11g
SQL> set autotrace traceonly

SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM EMP A) QRY where qry.row_Asc between 1 and 11;

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2175649969

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 14 | 1582 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 14 | 1582 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
——————————————————————————

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

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

Statistics
———————————————————-
419 recursive calls
0 db block gets
79 consistent gets
8 physical reads
0 redo size
1360 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
11 rows processed

——————————————————————————————————————

DBMS_XPLAN

DBMS_XPLAN is used to query the execution plan. It is now more improved with Oracle 10g and 11g. Internally it queries the PLAN_TABLE. Best part about this package is – filter and warning.

Oracle 9i

DELETE FROM PLAN_TABLE

SQL> EXPLAIN PLAN for
2 SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC)
ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM scott.EMP A) QRY whe
re qry.row_Asc between 1 and 11;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 2175649969

————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
————————————————————–
| 0 | SELECT STATEMENT | | 14 | 1582 | 8 |
|* 1 | VIEW | | 14 | 1582 | 8 |
| 2 | WINDOW SORT | | 14 | 518 | 8 |
| 3 | WINDOW SORT | | 14 | 518 | 8 |
| 4 | COUNT | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 2 |

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

21 rows selected.

Oracle 11g

DELETE FROM PLAN_TABLE

EXPLAIN PLAN for
SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM scott.EMP A) QRY where qry.row_Asc between 1 and 11;

SELECT * FROM TABLE(dbms_xplan.display)

SQL>
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 2175649969

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 14 | 1582 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 14 | 1582 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

17 rows selected.


February 21, 2010  8:46 AM

Migrating from 9i to 11g – Wrap Utility

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Usually WRAP utility enables to hide the PL/SQL code. From Oracle 10g R2 we can use DBMS_DDL package for this. There are 3 WRAP sub-programs available in DBMS_DDL (function overloading).

A) The source string is passed as a normal string

FUNCTION WRAP RETURNS VARCHAR2
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DDL VARCHAR2 IN

B) The source string is passed as a associative array

FUNCTION WRAP RETURNS TABLE OF VARCHAR2(256)
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DDL TABLE OF VARCHAR2(256) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN

FUNCTION WRAP RETURNS TABLE OF VARCHAR2(32767)
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DDL TABLE OF VARCHAR2(32767) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN

In Oracle 9i

Oracle 9i
Wrap utility (available in 11g also)

create or replace procedure dummy2 as
begin
null;
end;
/

wrap iname=c:/dummy.sql oname=dummy3.sql

OUTPUT

create or replace procedure dummy2 wrapped

a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abc
d
abcd
7
25
59
Lo2gJ8C3lCuqm2DIriMeCiyorkkwg5nnm7+fMr2ywFxaPtfXOyEJuHQruMAy/tJepZmBCC0r
uIHH
Lcmmpv/qnj4=

Oracle 11g

Oracle 11g
A) Normal string

DBMS_DDL

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_source VARCHAR2(32767);
l_wrap VARCHAR2(32767);
BEGIN
l_source := ‘create or replace procedure dummy2 as ‘ ||
‘BEGIN ‘ ||
‘NULL; ‘ ||
‘END;';

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source);
DBMS_OUTPUT.put_line(l_wrap);
END;
/

OUTPUT

create or replace procedure dummy2 wrapped

a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abc
d
abcd
7
25
59
Lo2gJ8C3lCuqm2DIriMeCiyorkkwg5nnm7+fMr2ywFxaPtfXOyEJuHQruMAy/tJepZmBCC0r
uIHH
Lcmmpv/qnj4=

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.82

B) Associative array (for both the functions that uses associative array)

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrap DBMS_SQL.VARCHAR2A;
BEGIN
l_source (1) := ‘create or replace procedure dummy2 as ‘;
l_source(2) := ‘ BEGIN NULL; END;';

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source, lb=> 1, ub => l_source.count);
FOR i IN 1 .. l_wrap.count LOOP
DBMS_OUTPUT.put_line(l_wrap(i));
END LOOP;
END;
/

OUTPUT :

create or replace procedure dummy2 wrapped

a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abc
d
abcd
7
26
59
11TTBymVupEgy2K5qZjc3v3hktYwg5nnm7+fMr2ywFxaPtfXOyEJuHQrabjAMv7SXqWZgQgt
K7iB
xy3JpqY8t56h

PL/SQL procedure successfully completed.


February 21, 2010  8:44 AM

Migrating from 9i to 11g – Virtual column & partitioning

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

1 Virtual Columns

Virtual column is a new feature in Oracle 11g – this allows derivation from a function / expression. The values are not stored within the table – they are just displayed only when calculated. It mainly gives advantage in disk space utilization – as it does not get stored in the database and it can be indexed. We can gather optimizer statistics and histograms on virtual columns.

Prior to Oracle 11g we need to either use a trigger / view to archive a functionality like this. While creating virtual columns we can either include a datatype or let the database determine the data type based on the expression. The keywords “generated always” and “virtual” are optional. A column expression can refer to a user-defined DETERMINISTIC functions. While writing insert scripts on these tables its mandatory to specify column names.

They can be indexed, partitioned, constraints and foreign keys can be created. However, virtual columns cannot be used for IOT, external tables, objects, clusters or temporary tables. These columns only work within the specified table, you cannot reference the columns within other tables.

virtual Column partitioning

A new level of partitioning is introduced with Oracle 11g – virtual column based partitioning. Virtual column will be the partition key for virtual column based partitioning. Prior to 11g it was possible to create only on database columns (physical). We need to create a normal column and make the trigger to update it then partition the same.

How could we have possibly achieved this in Oracle 9i

Oracle 9i

Oracle 9i
create table emp3
(
empno number,
depno number,
exp_years number);
/

CREATE OR REPLACE VIEW emp3_view as
select empno, depno, exp_years,
case
when exp_years = 10 then ‘Super Senior’
else ‘Ultra Senior’
end expi_level from emp3

insert into emp3 (empno, depno, exp_years) values
(1, 101, 2)
/

insert into emp3 (empno, depno, exp_years) values
(2, 102, 6)
/

insert into emp3 (empno, depno, exp_years) values
(2, 102, 15)
/

SQL> select * from emp3_view;

EMPNO DEPNO EXP_YEARS EXPI_LEVEL
———- ———- ———- ————
1 101 2 Junior
2 102 6 Senior
2 102 15 Super Senior

Elapsed: 00:00:00.04

Statistics
———————————————————-
75 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
677 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

**********************************************************************************

Oracle 11g
create table emp2
(
empno number,
depno number,
exp_years number,
expi_level varchar2(20) generated always as
(
case
when exp_years = 10 then ‘Super Senior’
else ‘Ultra Senior’
end
) virtual
);

insert into emp2 (empno, depno, exp_years) values
(1, 101, 2)
/

insert into emp2 (empno, depno, exp_years) values
(2, 102, 6)
/

insert into emp2 (empno, depno, exp_years) values
(2, 102, 15)
/

SQL> select * from emp2;

EMPNO DEPNO EXP_YEARS EXPI_LEVEL
———- ———- ———- ————
1 101 2 Junior
2 102 6 Senior
2 102 15 Super Senior

Elapsed: 00:00:00.04

Statistics
———————————————————-
102 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
677 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

**********************************************************************************

Virtual Indexes

Creating index on virtual column

CREATE INDEX emp2_idx on emp2 (expi_level )

Execution Plan
———————————————————-
Plan hash value: 927509100

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 51 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP2_IDX | 1 | | 1 (0)| 00:00:01 |
—————————————————————————————-

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

2 – access(“EXPI_LEVEL”=’Junior’)

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
603 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

**********************************************************************************

Virtual column partitioning

Virtual columns as we know are expression based – they can be partitioned even though they are stored as meta data only. Prior to 11g it is restricted only to actual (physical) columns – but from 11g even virtual columns could be partitioned.

Creating partition on virtual column

create table emp2
(
empno number,
depno number,
exp_years number,
expi_level varchar2(20) generated always as
(
case
when exp_years = 10 then ‘Super Senior’
else ‘Ultra Senior’
end
) virtual
) PARTITION BY LIST (EXPI_LEVEL)
(partition l1 values (‘Junior’, ‘Senior’) tablespace SYSTEM,
partition l2 values (‘Super Senior’, ‘Ultra Senior’) tablespace USERS,
partition l3 values (default) tablespace users)

Table Created

drop table emp2


February 21, 2010  8:41 AM

Migrating from 9i to 11g – Trigger Enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Oracle trigger enhancements are quite exciting !!

1 DML Trigger – faster
With Oracle 11g DML triggers are much faster when the ROW-LEVLE triggers perform operations on other tables. Documentation suggest that it offers noticable performance improvements in several cases.

Scripts for testing trigger enhancements-

Create table emp_audit (username varchar2(200), operation varchar2(100), sys_time date)
/

CREATE OR REPLACE TRIGGER EMP_TRIG2
BEFORE INSERT OR UPDATE ON emp
referencing new as new old as old
for each row
begin
INSERT INTO emp_audit VALUES (user, ‘INSERT’, sysdate);
end;
/

Oracle 9i
SQL> update emp set comm = comm *1.5;

40541 rows updated.

Elapsed: 00:00:10.28

Oracle 11g
SQL> update emp set comm = comm *1.5;

40541 rows updated.

Elapsed: 00:00:08.17

**********************************************************************************
2 Trigger – Compound

Usually when we perform bulk Insert / Update / Merge into a table and if there is a ROW LEVEL trigger on the table that performs DML operations into another table then, for each and every row trigger would be fired. This makes the very concept of bulk DML operations as record based operation internally. This problem can now be well handled using the compound triggers.

Another use of compound trigger is the triggering timing is well managed. Sometimes we may need to have after insert, before update, after each row, before statement etc., kind of different triggers and in most of the cases we are not sure that which trigger is executed first. But, with this compound triggers we can have all the triggers created in a single trigger in the sequence which we want them to be executed.

create table test_compound_trig (a number, b number)
/

create table test_compound_audit (a number, b number, c date)
/

1. Quick demo to show in what order the trigger is executed

CREATE OR REPLACE TRIGGER trig_test_timing FOR INSERT OR UPDATE OF a
ON test_compound_trig
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line(‘Now before each row is executed’);
END BEFORE EACH ROW;
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line(‘Now the before statement is executed’);
END BEFORE STATEMENT;
AFTER STATEMENT IS
BEGIN
dbms_output.put_line(‘Now the after statement is executed’);
END AFTER STATEMENT;
AFTER EACH ROW IS
BEGIN
dbms_output.put_line(‘Now after each row is executed’);
END AFTER EACH ROW;
END trig_test_timing;

SQL> insert into test_compound_trig values (1,2);

SQL> insert into test_compound_trig select
2 rownum, rownum+1 from dual connect by level insert into test_compound_trig select
rownum, rownum+1 from dual connect by level < = 20;

Fires AN AFTER STATEMENT trigger

The above represents – for inserting 20 rows a STATEMENT LEVEL trigger is executed only once – this is an existing case that we already know. What is so new about ORACLE 11G COMPOUND TRIGGERS – say, on a table if we have a TRIGGER AFTER EACH ROW that inserts into an audit table – we can make it as compound trigger with BEFORE EACH ROW + STATEMENT LEVEL trigger and make the insert statement execute only once.

drop trigger only_statement_level;

drop trigger before_insert_trig;

If there are multiple triggers on the table – ROW LEVEL & STATEMENT LEVEL and if ROW LEVEL performs DML operations on tables then we can assign the values to collection in ROW LEVEL and perform the actual DML operations in STATEMENT level trigger.

Oracle 9i
create table test_compound_trig (a number, b number)
/

create table test_compound_audit (a number, b number, c date)
/

set serveroutput on size unlimited

CREATE OR REPLACE TRIGGER before_insert_trig
AFTER INSERT OR UPDATE ON test_compound_trig
FOR EACH ROW
BEGIN
INSERT INTO test_compound_audit values (1, 2, sysdate);
dbms_output.put_line(‘Fires for each row!!’);
END;
/

SQL> insert into test_compound_trig select
rownum, rownum+1 from dual connect by level select count(1) from test_compound_audit;

COUNT(1)
———-
20

Oracle 11g

1. PERFORMANCE OF DML operations inside TRIGGERS + Use of SEQUENCE directly inside the block

create table test_compound_trig (a number, b number)
/

create table test_compound_audit (a number, b number, c date)
/

set serveroutput on size unlimited

CREATE OR REPLACE TRIGGER trig_compound FOR INSERT OR UPDATE OF a
ON test_compound_trig
COMPOUND TRIGGER
type tp_audit is table of test_compound_audit%rowtype index by pls_integer;
tp_audit_tab tp_audit;
counter number := 0;
BEFORE EACH ROW IS
BEGIN
counter := counter + 1;
tp_audit_tab(counter).a := dummy_seq.nextval;
tp_audit_tab(counter).b := dummy_seq.currval;
tp_audit_tab(counter).c := sysdate;
dbms_output.put_line(‘Now before each row is executed’);
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
dbms_output.put_line(‘Now the after statement is executed’);
FORALL i IN tp_audit_tab.first .. tp_audit_tab.last
INSERT INTO test_compound_audit values tp_audit_tab(i);
END AFTER STATEMENT;
END ;
/

SQL> insert into test_compound_trig select
2 rownum, rownum+1 from dual connect by level select count(1) from test_compound_audit;

COUNT(1)
———-
20

** This is particularly useful when we perform real BULK INSERTS !!
**********************************************************************************

3 Trigger – Follows clause

2. Controlling the order of execution of trigger

CREATE OR REPLACE TRIGGER foll1_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed FIRST’);
END;
/

CREATE OR REPLACE TRIGGER foll2_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed NEXT’);
END;
/

SQL> insert into test_compound_trig select
rownum, rownum+1 from dual ;
foll1_trig – executed NEXT
foll1_trig – executed FIRST

1 row created.

CREATE OR REPLACE TRIGGER foll1_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed FIRST’);
END;
/

CREATE OR REPLACE TRIGGER foll2_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
FOLLOWS foll1_trig
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed NEXT’);
END;
/

SQL> insert into test_compound_trig select
2 rownum, rownum+1 from dual ;
foll1_trig – executed FIRST
foll1_trig – executed NEXT

**********************************************************************************

4 Trigger – Enable / Disable

3. Creating disabled / enabled triggers

Main use of creating ENABLED / DISABLED trigger – Sometimes, we write a complicated trigger – and may like to test it before enabling it – so we can possibly create a disable trigger first then once we know its functionally correct we can enable the same.

drop trigger foll2_trig

drop trigger foll1_trig

CREATE OR REPLACE TRIGGER enable_trig
BEFORE INSERT ON test_compound_trig FOR EACH ROW
ENABLE
BEGIN
DBMS_OUTPUT.put_line(‘ENABLE TRIGGER’);
END;
/

CREATE OR REPLACE TRIGGER disable
BEFORE INSERT ON test_compound_trig FOR EACH ROW
DISABLE
BEGIN
DBMS_OUTPUT.put_line(‘DISABLE TRIGGER’);
END;
/

SQL> drop table test_compound_trig;

SQL> drop table test_compound_audit;


February 21, 2010  8:37 AM

Migrating from 9i to 11g – Sequences

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Prior to Oracle 11g – sequence can be accessed only from a SQL statement. From now on references to sequence is allowed without SQL statement. Oracle recomends to use this method instead of using it in the query. – Time saving !!

Oracle 9i

DECLARE
v_seq_new NUMBER;
v_seq_old NUMBER;
BEGIN
SELECT dummy_seq.nextval, dummy_seq.currval INTO v_seq_new, v_seq_old FROM dual;
END;
/

Elapsed: 00:00:00.57

Oracle 11g
Also, these sequences can be displayed in the trace messages also !!

CREATE OR REPLACE FUNCTION samp_debug (in_number NUMBER) RETURN NUMBER
AS
out_number NUMBER;
BEGIN
out_number := dummy_seq.nextval * in_number;
dbms_output.put_line(‘The value of in_number is ‘ || in_number || ‘ sequence is ‘ || dummy_seq.currval || ‘ out number is ‘ || out_number);
return out_number;
END samp_debug;
/

Test :

SQL> set serveroutput on
SQL> SELECT samp_debug(5) from dual;

SAMP_DEBUG(5)
————-
337810

The value of in_number is 5 sequence is 67562 out number is 337810

DECLARE
v_seq_new NUMBER;
v_seq_old NUMBER;
BEGIN
v_seq_new := dummy_seq.nextval;
v_seq_old := dummy_seq.currval ;
END;
/

Elapsed: 00:00:00.06


February 21, 2010  8:35 AM

Migrating from 9i to 11g – SQL*PLUS Enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Support to BLOB

Prior to Oracle 11g it is not possible to see the column of datatype BLOB.

Example –
create table test_blob (a blob);

insert into test_blob values (‘65454545454′);

1 row created

SQL> select * from test_blob;

A
———————————————————–
065454545454

Append, Create and Replace – spool file

Prior to Oracle 10g it is possible only to create a spool file – cannot append or replace. This is particularly helpful when we spool the data inside shell scripts and use the same spool file to append the data.

Example –
spool F:\app\Luxananda\oradata\lux\spool_file1 create

select * from dual;

spool off

open spool_file1

SQL>
SQL> select * from dual;

D

X

SQL> spool off

spool F:\app\Luxananda\oradata\lux\spool_file1 append

select 1 from dual;

spool off

open spool_file1

SQL>
SQL> select * from dual;

D

X

SQL> spool off
SQL>
SQL> select 1 from dual;

1
———-
1

SQL> spool off

spool F:\app\Luxananda\oradata\lux\spool_file1 replace

select ‘a’ from dual;

spool off

open spool_file1

SQL> select ‘a’ from dual;



a

SQL> spool off

Glogin, Login and Predefined Variables

GLOGIN.sql and LOGIN.SQL will be run after each successful attempt connection to SQL*PLUS. This is quite useful as the current sessions information is set using login.sql.

show recyclebin table name

We discussed about RECYCLEBIN in the earlier section and saw the SHOW RECYCLEBIN command – this displays the contents in the table.

It is also possible to display the contents in the recyclebin for a specific table.

Quick example –

create table a (a1 number);

create table b (a1 number);

drop table a;

drop table b

show recyclebin

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-

A BIN$9rSstjwnQO6P74YgQV+1vQ==$0 TABLE 2010-02-20:11:21:27

B BIN$Yu7KhbxlSAOkWS4u0QxPuQ==$0 TABLE 2010-02-20:11:21:31

show recyclebin a

SQL> show recyclebin a
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
A BIN$9rSstjwnQO6P74YgQV+1vQ==$0 TABLE 2010-02-20:11:21:27

DESC command

Now the DESC command compiles invalid object before displaying the output – if it cannot be re-compiled then, ORA-24372 is returned.

Quick example –

create table a (a number)

CREATE OR REPLACE PROCEDURE test_desc AS
b1 number;
BEGIN
SELECT a into b1 from a where rownum = 1;
END test_desc;
/

drop table a

SQL> select object_name, status from all_objects where object_name = ‘TEST_DESC';

OBJECT_NAME STATUS
—————————— ——-
TEST_DESC INVALID

create table a (a number)

SQL> select object_name, status from all_objects where object_name = ‘TEST_DESC';

OBJECT_NAME STATUS
—————————— ——-
TEST_DESC INVALID

Now lets describle TEST_DESC procedure –

SQL> desc test_desc
PROCEDURE test_desc

Now, lets query again !!

SQL> select object_name, status from all_objects where object_name = ‘TEST_DESC';

OBJECT_NAME STATUS
—————————— ——-
TEST_DESC VALID

Whitespace

Now we can have whitespace in sql*plus.
Example –
Spool “spool file” create

Set serveroutput

This command is used to control write to DBMS_OUTPUT.PUT_LINE. Now, from Oracle 11g the length of the text is increased from 255 to 32,767 bytes. The default is set to UNLIMITED.

dbms_output.put_line in Function

Prior to Oracle 10g if we have DBMS_OUTPUT.PUT_LINE inside a function it would not return any value. From Oracle 10g we can have this inside a function call and the SELECT statement displays the text – extremely powerful utility for debugging I bet !

Lets take a look at a quick example –

CREATE OR REPLACE FUNCTION samp_debug (in_number NUMBER) RETURN NUMBER
AS
out_number NUMBER;
BEGIN
out_number := dummy_seq.nextval * in_number;
dbms_output.put_line(‘The value of in_number is ‘ || in_number || ‘ sequence is ‘ || dummy_seq.currval || ‘ out number is ‘ || out_number);
return out_number;
END samp_debug;
/

Test :

SQL> set serveroutput on
SQL> SELECT samp_debug(5) from dual;

SAMP_DEBUG(5)
————-
337810

The value of in_number is 5 sequence is 67562 out number is 337810


February 21, 2010  8:33 AM

Migrating from 9i to 11g – Read only tables

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Feature in Oracle 11g.

Prior to Oracle 11g – grant READ-ONLY permissions to a table can be done at GRANT level. Now, it is possible to make the table read-only at definition level itself. ALTER TABLE

READ ONLY – alters the table to read only. It is also possible to change the READ ONLY table to READ WRITE using the ALTER table script.

Read only tables can
select, manage indexes, constraints etc., row movement
dropping unused column
drop table

Read only tables cannot
insert, delete or update
truncate
select for update
drop column / setting column to unused
flashback, partitionedonline redefinition

How could we achieve this in ORACLE 9i?

CREATE TABLE test_readonly
(a NUMBER)

create or replace public synonym test_readonly for system.test_readonly;

GRANT SELECT ON test_readonly TO SYSTEM;

Issuing select statement / for update : (from owner – SYSTEM & SCOTT)

SQL> select * from test_readonly;

A
———-
1

Elapsed: 00:00:00.10

SQL> select * from test_readonly for update;

A
———-
1

Elapsed: 00:00:00.00

** owner is SYSTEM
INSERT / UPDATE statement: (from SCOTT – not the owner)

SQL> insert into test_readonly values (2);
insert into test_readonly values (2)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Elapsed: 00:00:00.28
SQL> update test_readonly set a = 5;
update test_readonly set a = 5
*
ERROR at line 1:
ORA-01031: insufficient privileges

Elapsed: 00:00:00.01

Drop / Rename table:

SQL> drop table test_readonly;
drop table test_readonly

Table dropped

Elapsed: 00:00:00.00

SQL> rename test_readonly to test1_readonly;
rename test_readonly to test1_readonly
*
ERROR at line 1:
ORA-04043: object TEST_READONLY does not exist

**But all the above can be done frorm owner – SYSTEM user
Row movement etc., :

SQL> alter table test_Readonly enable row movement;

Table altered.

Elapsed: 00:00:00.06
Drop column / Drop unsed columns / Set it to unused:

SQL> alter table test_readonly add ( b number);

Table altered.

Elapsed: 00:00:00.50
SQL>

SQL> alter table test_readonly set unused column b;

Table altered.

Elapsed: 00:00:01.48
SQL>

SQL> alter table test_readonly drop unused columns;

Table altered.

Elapsed: 00:00:02.01
Delete / Truncate: (from SCOTT)

SQL> delete from test_readonly where a = 1;
delete from test_readonly where a = 1
*
ERROR at line 1:
ORA-01031: insufficient privileges

Elapsed: 00:00:00.00

SQL> truncate table test_readonly;
truncate table test_readonly
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.00
Flashback on table:

SQL> FLASHBACK TABLE test_readonly
2 TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ minute);

Flashback complete.

Elapsed: 00:00:03.71
SQL>
DML operations from table owner:

SQL> CREATE TABLE test_readonly
(a NUMBER);

Table created.

Elapsed: 00:00:01.54

create or replace public synonym test_readonly for system.test_readonly;

SQL> GRANT SELECT ON test_readonly TO SCOTT;

Grant succeeded.

Elapsed: 00:00:00.23
SQL> insert into test_readonly values (1);

1 row created.

Elapsed: 00:00:00.17
SQL>

**********************************************************************************

The same in Oracle 11g – Simple

CREATE TABLE test_readonly
(a NUMBER)

create or replace public synonym test_readonly for system.test_readonly;

ALTER TABLE test_readonly READ ONLY;

Can be modified to writable table – ALTER TABLE test_readonly READ WRITE;
Issuing select statement / for update:

SQL> select * from test_readonly;

no rows selected

Elapsed: 00:00:00.04

SQL> select * from test_readonly for update;
select * from test_readonly for update
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.04
SQL>
INSERT / UPDATE statement:

SQL> insert into test_readonly values (2);
insert into test_readonly values (2)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”


Elapsed: 00:00:00.00

Drop / Rename table:

SQL> drop table test_readonly;
drop table test_readonly

Table dropped
Row movement etc., :

SQL> alter table test_Readonly enable row movement;

Table altered.

Elapsed: 00:00:00.09
Drop column / Drop unsed columns / Set it to unused:

SQL> alter table test_readonly add ( b number);
alter table test_readonly add ( b number)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.00
SQL> alter table test_readonly set unused column b;
alter table test_readonly set unused column b
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.04

Delete / Truncate:

SQL> delete from test_readonly where a = 1;
delete from test_readonly where a = 1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.01
SQL> truncate table test_readonly;
truncate table test_readonly
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.06
SQL>

Flashback on table:

SQL> FLASHBACK TABLE test_readonly
2 TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ minute);
FLASHBACK TABLE test_readonly
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:01.48
DML operations from table owner:

Cannot modify read only tables.

**********************************************************************************


February 21, 2010  8:28 AM

Migrating from 9i to 11g – Result caching

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

The new Result caching feature in Oracle 11g enables brilliant memory utilization efficiently. By caching SQL / PLSQL – re-execution directly fetches from the cache instead of re-executing the query / function again. Widely there are two types of result cache
PL/SQL result cache
SQL result cache

V$RESULT_CACHE related view(s) shows the results of cache SQL / PL/SQL functions

RESULT_CACHE is a new SGA component from Oracle 11g. Usually, results are cached to the SGA and based on the LRU algorithm they will be replaced with the latest results as memory allocated for this is usually a small part. From Oracle 11g RESULE_CACHE_MAX_SIZE parameter can be used to set the cache memory. There is also a new PLSQL package DBMS_RESULT_CACHE that provides various procedures.

1 SQL Result caching

SQL Result cache – The results of FREQUENTLY used SQL queries can be cached in the memory. So that the SQL query results are cached and on re-executing the same it gets the results from the memory and does not run again – and by experience we know fetching from memory is usually faster than executing the query all over again!!
Note : In Oracle RAC environment each NODE has its own result cache and that cannot be shared with another instance in the RAC.

* If the result_Cache_mode parameter is MANUAL – then, results would be cached only if we include the RESULT_CACHE Hint
* If the result_Cache_mode parameter is FORCE – then, results would be cached automatically for all the queries
* If the result_cache_mode is set to AUTO then the results caching would be decided by the CBO

Is there a way in Oracle 9i?
Actually – Not possible

Though for FULL TABLE SCANS CACHE hint is available which will cache the data into memory. But again this is based on LRU algorithm – this does not have a separate memory :result_cache like in 11g.

Oracle 11g

RESULT_CACHE_MODE = MANUAL

Must give result_Cache hint

SQL> select /*+ result_cache */ empno from emp;

Elapsed: 00:00:00.48

Execution Plan
———————————————————-
Plan hash value: 179099197

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 56 | 1(0)| 00:00:01 |
| 1 | RESULT CACHE | 9tgtkj1bvaz1c2rdc9bxr6jcju | | | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 56 | 1(0)| 00:00:01 |
———————————————————————————————–

Result Cache Information (identified by operation id):
——————————————————
1 – column-count=1; dependencies=(SCOTT.EMP); attributes=(ordered); name=”select /*+ RESULT_CACHE */ empno from emp ”

Note: I tried to use RESULT_CACHE from my SYSTEM user – it did not work. Then I logged in as SCOTT to see whether it works – it worked !! – net net we cannot use result cache from SYS / SYSTEM / SYSDBA.

RESULT_CACHE_MODE = FORCE

RESULT_CACHE_MODE = FORCE

Results are cached by default- internally RESULT_CACHE hint is used – check the result cache information section

select empno from emp

Elapsed: 00:00:00.48

Execution Plan
———————————————————-
Plan hash value: 179099197
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 56 | 1(0)| 00:00:01 |
| 1 | RESULT CACHE | 9tgtkj1bvaz1c2rdc9bxr6jcju | | | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 56 | 1(0)| 00:00:01 |
———————————————————————————————–

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=1; dependencies=(SCOTT.EMP); attributes=(ordered); name=”select /*+ RESULT_CACHE */ empno from emp ”

To view the details based on result cache id check V$RESULT_CACHE_OBJECTS –

select status,name,namespace
from v$result_cache_objects where
cache_id=’9tgtkj1bvaz1c2rdc9bxr6jcju';

STATUS NAME NAMES
———————————————————————————————
Published select /*+ RESULT_CACHE */ empno from emp SQL
RESULT_CACHE_MODE = ‘AUTO’

the CBO would determine when the results should be cached based on the following factors – frequency of query execution, cost, data changing factors etc.,

select empno from emp

Execution Plan
———————————————————-
Plan hash value: 179099197

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |

Result cache information

Report before caching

SQL> set serveroutput on
SQL> set autotrace off
SQL> exec dbms_Result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 768K bytes (768 blocks)
Maximum Result Size = 38K bytes (38 blocks)
[Memory]
Total Memory = 5140 bytes [0.004% of the Shared Pool]
… Fixed Memory = 5140 bytes [0.004% of the Shared Pool]
… Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

Notes: Since we have not cached any SQL so far – there is no CACHE Information.

Report after caching

SQL> exec dbms_Result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 103536 bytes [0.080% of the Shared Pool]
… Fixed Memory = 5140 bytes [0.004% of the Shared Pool]
… Dynamic Memory = 98396 bytes [0.076% of the Shared Pool]
……. Overhead = 65628 bytes
……. Cache Memory = 32K bytes (32 blocks)
……….. Unused Memory = 24 blocks
……….. Used Memory = 8 blocks
…………… Dependencies = 1 blocks (1 count)
…………… Results = 7 blocks
………………. SQL = 7 blocks (7 count)

PL/SQL procedure successfully completed.

Notes: The above shows that SQL query results are cached – total cache memory used 32k out of 2M allocated.

Note: the above is just to show RESULT_CACHE output only. Definitely it is not a great idea to do a result cache on full table !!

RESULT CACHE FOR BIND VARIABLES

SQL> variable empno number;
SQL> exec :empno := 7369

SQL> select /*+ result_cache */ * from emp where empno = :empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM D
EPTNO
———- ———- ——— ———- ——— ———- ———- —–
—–
7369 SMITH CLERK 7902 17-DEC-80 800
20

Elapsed: 00:00:00.71

Execution Plan
———————————————————-
Plan hash value: 2949544139

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3
7 | 1 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8d01c2ttvh1h99u09ynz53jnys | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–

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

3 – access(“EMPNO”=TO_NUMBER(:EMPNO))

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=8; dependencies=(SCOTT.EMP); attributes=(single-row); parame
ters=(:EMPNO); name=”select /*+ result_cache */ * from emp where empno = :empno”

Second time –

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 2949544139

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8d01c2ttvh1h99u09ynz53jnys | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
3 – access(“EMPNO”=TO_NUMBER(:EMPNO))

Result Cache Information (identified by operation id):
——————————————————
1 – column-count=8; dependencies=(SCOTT.EMP); attributes=(single-row); parameters=(:EMPNO); name=”select /*+ result_cache */ * from emp where empno = :empno”

** second time with the same value

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 2949544139

———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8d01c2ttvh1h99u09ynz53jnys | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–

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

3 – access(“EMPNO”=TO_NUMBER(:EMPNO))

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=8; dependencies=(SCOTT.EMP); attributes=(single-row); parame
ters=(:EMPNO); name=”select /*+ result_cache */ * from emp where empno = :empno”

With another value

SQL> variable empno number;
SQL> exec :empno := 7499

….

Query on V$RESULT_CACHE_OBJECTS shows 2 hits to EMP table. Note, there were 3 queries executed – two queries with same bind variable value but third with a different bind variable value – this leads to a cache miss.

select status,name,namespace
from v$result_cache_objects where
cache_id=’8d01c2ttvh1h99u09ynz53jnys’

STATUS NAME
NAMES
——— ——————————————————————-
———————————————————- —–
Published select /*+ result_cache */ * from emp where empno = :empno
SQL
Published select /*+ result_cache */ * from emp where empno = :empno
SQL

RESULT CACHE – TEMPORARY TABLE

create global temporary table temp_test (a number) on commit preserve rows
/

Elapsed: 00:00:00.01
SQL> set autotrace on

SQL> select /*+ result_cache */ * from temp_Test;

A
———-
1

Elapsed: 00:00:00.07

Execution Plan
———————————————————-
Plan hash value: 322435456

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEMP_TEST | 1 | 13 | 2 (0)| 00:00:01 |
——————————————————————————-

Note
—–
– dynamic sampling used for this statement

SQL>

Note – Cannot cache TEMPORARY table results.

Which query needs to be cached using RESULT_CACHE – My take on this – Any frequent particular STATIC lookups can be cached in the memory. Definitely it wouldn’t be a good idea to cache millions or even 100,000 of rows into the memory. Also its better to use result_cache on queries that have aggregations, that behalves like materialized views etc., It is not designed for – huge number of concurrent users or readonly lookup tables – client result cache is for this. With SQL result caching we can cache normal queries, sub-queries and inline views. DBMS_RESULT_CACHE.FLUSH removes all the cached information from result_cache area in SGA. The result cache would keep growing until it reaches the maximum cache size We need to manually purge the result cache if it fills up fully.
Other important procedure in DBMS_RESULT_CACHE is status (shows the status of the result cache).
Some of the V$views are –
** V$RESULT_CACHE_STATISTICS
** V$RESULT_CACHE_OBJECTS
** V$5RESULT_CACHE_DEPENDENCY
** V$RESULT_CACHE_MEMORY
Result cache can cache the results for the following
** Bind variables. If the bind variable value differs then, it would lead to cache miss
** If there is any pending transaction to the query that we fire then it will not be cached into the memory
** will not cache flashback queries
** not eligible for caching – non-deterministic functions; currval or nextval in sequence; sql inbuilt functions such as sysdate or curr date; temporary table and dictionary tables.

**********************************************************************************

Function Result caching

Oracle 9i
Not possible – May be we can create functions inside a package and pin it in memory. – but even then results cannot be cached !!

Oracle 11g

create or replace function check_dept_details (dept_no number) return number
result_cache
is
check_avail number;
begin
select 1 into check_avail from dept
where deptno = dept_no;
return dept_no;
end;
/

SELECT check_dept_details(380) from dept where dname = ‘ACCOUNTING’

SQL> SELECT check_dept_details(380) from dept where dname = ‘ACCOUNTING';

CHECK_DEPT_DETAILS(380)
———————-
380

Elapsed: 00:00:00.04
SQL> /

CHECK_DEPT_DETAILS(380)
———————-
380
Elapsed: 00:00:00.00

insert into dept values (600, ‘FO’, ‘SINGAPORE’);

SQL> SELECT check_dept_details(600) from dept where dname = ‘ACCOUNTING';

CHECK_DEPT_DETAILS(600)
———————–
600

Elapsed: 00:00:00.01

Result cache is invalidated for new inserts

SQL> SELECT check_dept_details(120) from dept where dname = ‘ACCOUNTING';

CHECK_DEPT_DETAILS(120)
———————–
120

Elapsed: 00:00:00.00

** The above is just for example only

create or replace function check_dept_details (dept_no number) return number
result_cache relies on(dept)
is
check_avail number;
begin
select 1 into check_avail from dept
where deptno = dept_no;
return dept_no;
end;
/

** Note – Relies on clause is not working in Oracle 11g R1


February 21, 2010  8:21 AM

Migrating from 9i to 11g – Regular expression

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

From Oracle 10g can use regular expressions – some of

1 REGEXP_SUBSTR

REGEXP_SUBSTR searches for a regular expression pattern in a given text – this returns a matching substring.
select regexp_substr(‘Employee id is : 420147321′, ‘[0-9]{9}’, 5) from dual;
REGEXP_SU
———
420147321
Elapsed: 00:00:00.03

From Oracle 11g – nth sub-expression in REGEXP_SUBSTR can be accessed.
Example to access nth sub-expression- say from the text luxvttctdmand – eliminate lux & and and return the rest.
SELECT
REGEXP_SUBSTR
(‘luxvttctdmand’, — source
‘lux(.*)and’, — regular expression pattern
1, — position (to start the search)
1, — number of occurances
‘i’, — match option (case insensitive / non case sensitive)
1) — subexpression
FROM dual;
REGEXP_
——-
vttctdm

*********************************************************************************

2 REGEXP_INSTR

REGEXP_INSTR searches for a regular expression pattern in a given text – this returns the position as output.
select regexp_instr(‘Employee id is : 420147321′, ‘[0-9]{9}’) from dual
REGEXP_INSTR(‘EMPLOYEEIDIS:420147321′,'[0-9]{9}’)
————————————————-
18
Elapsed: 00:00:00.06
From Oracle 11g – nth sub-expression in REGEXP_INSTR can be accessed.
Example to access nth sub-expression- say from the text luxvttlasfluxoiwljljljkasdfctdmand.
SELECT
REGEXP_INSTR
(‘luxvttlasfluxoiwljljljkasdfctdmand’,
‘(lux (vtt) (and))’,
1,
1, 0,
‘i’,
1) inst
FROM dual;

*********************************************************************************

3 REGEXP_COUNT

REGEXP_COUNT is like normal count function – but accepts regular expression characters.
select regexp_count(‘Employee id is : 420147321′, ‘[0-9]{9}’) from dual
REGEXP_COUNT(‘EMPLOYEEIDIS:420147321′,'[0-9]{9}’)
————————————————-
1
Elapsed: 00:00:00.09
From Oracle 11g it returns the number of times a pattern appears in a string-
Example-
select regexp_count
(‘luxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxandluxand’, ‘lux’) cnt from dual
CNT
———-
24

*********************************************************************************

4 REGEXP_LIKE

This function is similar to like but it does a regular expression matching. It can be used in the where clause and can be used in PL/SQL IF condition.

Example –
select 1 from dual where regexp_like(‘Employee id is : 420 CDF 321′, ‘[0-9]{3} [A-Z]{3} [0-9]{3}’)

1
———-
1

Elapsed: 00:00:00.07

*********************************************************************************

5 REGEXP_REPLACE
REGEXP_REPLACE enables to search the string based on patters and display the results.
Example 1 – Continuous text
select regexp_replace(‘Employee id is : 420147321′, ‘[0-9]{9}’, ‘*********’) from dual

REGEXP_REPLACE(‘EMPLOYEEID
————————–
Employee id is : *********

Elapsed: 00:00:00.36

Example 2 – Text with space

select regexp_replace(‘Employee id is : 420 CDF 321′, ‘[0-9]{3} [A-Z]{3} [0-9]{3}’, ‘*** *** ***’) from dual;

REGEXP_REPLACE(‘EMPLOYEEIDIS
—————————-
Employee id is : *** *** ***

Elapsed: 00:00:00.03


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: