Oracle function REGEXP_INSTR

 

 

                  1             2              3                  4               5                  6                    7
REGEXP_INSTR(<source_string>, <pattern>[[, <start_position>][, <occurrence>][, <return_option>][, <match_parameter>][, <sub_expression>]])


5. return_option - The default value of the return_option is 0, which returns the starting position of the pattern.
                 A value of 1 returns the starting position of the next character following the match




SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL;

first character in the fourth subexpression, which is '78':



REGEXP_INSTR( '1234567890' , '(123)(4(56)(78))' ,   1 ,   1  ,  0  ,  'i' ,   4   )
              -----------    ------------------    --    --     --    --     --
                  1                2                3     4     5      6      7



1. source_string is a character expression that serves as the search value.Pattern is the regular expression. 
   It is usually a text literal .It can contain up to 512 bytes.

2. Pattern is the regular expression. It is usually a text literal .It can contain up to 512 bytes.
   For more about pattern:

3. position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1,
   meaning that Oracle begins the search at the first character of source_string.


4. occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for.
   The default is 1, meaning that Oracle searches for the first occurrence of pattern.


5. return_option lets you specify what Oracle should return in relation to the occurrence:
   If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.
   If you specify 1, then Oracle returns the position of the character following the occurrence.


6. match_parameter is a text literal that lets you change the default matching behavior of the function. 
   You can specify one or more of the following values for match_parameter:

   i: to match case insensitively
   c: to match case sensitively
   n: to make the dot (.) match new lines as well
   m: to make ^ and $ match beginning and end of a line in a multiline string
   x: to ignore white spaces.


7. subexpr: REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern.



Anchoring Characters

^ Anchor the expression to the start of a line
$ Anchor the expression to the end of a line


[[:digit:]]{5} ---- any five digit

 


Posix Characters




Character Class      Description


[:digit:]             Only the digits 0 to 9

[:alnum:]             Any alphanumeric character 0 to 9 OR A to Z or a to z.

[:alpha:]             Any alpha character A to Z or a to z.

[:blank:]             Space and TAB characters only.

[:xdigit:]            Hexadecimal notation 0-9, A-F, a-f.

[:punct:]             Punctuation symbols 
                      ------------------------------------------------------------
                      % . , " ' ? ! : # $ & ( ) * ; + - /  = @ [ ] \ ^ _ { } | ~
                      ------------------------------------------------------------

[:print:]             Any printable character.

[:space:]             Any whitespace characters (space, tab, NL, FF, VT, CR). 
                      Many system abbreviate as \s.

[:graph:]             Exclude whitespace (SPACE, TAB). Many system abbreviate as \W.

[:upper:]             Any alpha character A to Z.

[:lower:]             Any alpha character a to z.

[:cntrl:]             Control Characters NL CR LF TAB VT FF NUL SOH STX 
                      EXT EOT ENQ ACK SO SI DLE DC1 DC2 DC3 DC4 NAK SYN 
                      ETB CAN EM SUB ESC IS1 IS2 IS3 IS4 DEL.
 





Quantifier Characters control the number of times a character 
or string is found in a search.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class      Description
*       Match 0 or more times
?       Match 0 or 1 time
+       Match 1 or more times
{m}     Match exactly m times
{m,}    Match at least m times
{m, n}  Match at least m times but no more than n times
\n      Cause the previous expression to be repeated n times
 


Character Class Description

|   Separates alternates, often used with grouping operator ()
( )   Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
[char]   Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes,
          and the ^ and - metacharacters



More Special Characters: 
    \t          tab                   (HT, TAB)
    \n          newline               (LF, NL)
    \r          return                (CR)
    \f          form feed             (FF)
    \a          alarm (bell)          (BEL)
    \e          escape (think troff)  (ESC)
    \033        octal char (think of a PDP-11)
    \x1B        hex char
    \c[         control char
    \l          lowercase next char (think vi)
    \u          uppercase next char (think vi)
    \L          lowercase till \E (think vi)
    \U          uppercase till \E (think vi)
    \E          end case modification (think vi)
    \Q          quote (disable) pattern metacharacters till \E
 
Even More Special Characters:
    \w  Match a "word" character (alphanumeric plus "_")
    \W  Match a non-word character
    \s  Match a whitespace character
    \S  Match a non-whitespace character
    \d  Match a digit character
    \D  Match a non-digit character
    \b  Match a word boundary
    \B  Match a non-(word boundary)
    \A  Match only at beginning of string
    \Z  Match only at end of string, or before newline at the end
    \z  Match only at end of string
    \G  Match only where previous m//g left off (works only with /g)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~






                                            3 ---- refer to 3rd No above 
                                            --
 SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;

     COUNT
----------
         3
                                                3 ---- refer to 3rd No above 
                                               --
SQL> SELECT REGEXP_COUNT('123123123123', '123', 2, 'i') COUNT FROM DUAL;


     COUNT
----------
         3




                                              *
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) "REGEXP_INSTR" FROM DUAL; --- * this will search pattern No 2 i.e. 4

REGEXP_INSTR
------------
           4
                                                **
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) "REGEXP_INSTR" FROM DUAL; ---** this will search pattern No 3 i.e. 56


REGEXP_INSTR
------------
           5


                                               **
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL; ---** this will search pattern No 3 i.e. 78

   
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
------------
           7



SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 5) "REGEXP_INSTR" FROM DUAL;  --- will search pattern No 5 in this '(123)(4(56)(78))' so no pattern in this case


REGEXP_INSTR
------------
           0




it will search the o which is having 3 letter after o




SQL> SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual;

    RESULT
----------
         0

SQL> SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf olf', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual;

    RESULT
----------
         0

SQL> SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf olfw', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual;


    RESULT
----------
        52




search for blank sapce for 4th occurance 

 SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA','[ ]+', 1, 4) "REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
------------
          28



find letter after 3rd occurance of | 

select REGEXP_SUBSTR('One|Two|Three|Four|Five','[^|]+', 1, 3) from dual ;

REGEX
-----
Three



 

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$')   AS REGEXP_INSTR   FROM dual
  

REGEXP_INSTR
------------
          45

Oracle 11g Enabling ARCHIVELOG Mode

ARCHIVELOG Mode on

 

 

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     5
Current log sequence           7


SQL> !mkdir /u01/app/oracle/Archice/vihaan
mkdir: cannot create directory `/u01/app/oracle/Archice/vihaan': No such file or directory

SQL> !mkdir -p /u01/app/oracle/Archice/vihaan

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/Archice/vihaan' ;

System altered.

SQL>  archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/Archice/vihaan
Oldest online log sequence     5
Current log sequence           7



SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/Archice/vihaan' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> startup mount;
ORACLE instance started.

Total System Global Area  941600768 bytes
Fixed Size                  1348860 bytes
Variable Size             608176900 bytes
Database Buffers          327155712 bytes
Redo Buffers                4919296 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open ;

Database altered.

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.



SQL> !ls -ltr  /u01/app/oracle/Archice/vihaan
total 11284

-rw-r----- 1 oracle oinstall     1024 Jan 20 15:35 1_8_769768273.dbf
-rw-r----- 1 oracle oinstall 29083648 Jan 20 15:35 1_7_769768273.dbf

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/Archice/vihaan
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9



SQL> show parameter dest


log_archive_dest                     string      /u01/app/oracle/Archice/vihaan

Oracle 11g REGEXP_COUNT

 

 

 

REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])
                                                                      ^
  --------------------------------------------------------------------|    
-- match parameter:
'c' = case sensitive
'i' = case insensitive search
'm' = treats the source string as multiple lines
'n' = allows the period (.) wild character to match newline
'x' = ignore whitespace characters



Total how many 123 in this string '123123123123' !!!!!!!! (in total string )



SQL> SELECT REGEXP_COUNT('123123123123', '123') COUNT FROM DUAL;

     COUNT
----------
         4


Total how many 123 in this string '123123123123' from 4th place


                        |<-----------------|
SELECT REGEXP_COUNT('123123123123', '123', 4) COUNT FROM DUAL;
                        ^
                        |--- this is 4th place , from this place how many 123                  

     COUNT
----------
         3


Total how many 123 in this string '123123123123' from 5th place

                               |<---------------|
SQL>  SELECT REGEXP_COUNT('123123123123', '123',5) COUNT FROM DUAL;
                               ^
                               |--- this is 5th place , from this place how many 123                             


     COUNT
----------
         2



source string at the third character for case 'i' specifies case-insensitive matching.


SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL; 

     COUNT
----------
         3




SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT    FROM DUAL; ---------'(12)3' ='123' 
 
REGEXP_COUNT
------------
           5




CREATE TABLE test (testcol VARCHAR2(50));

INSERT INTO test VALUES ('abcde');
INSERT INTO test VALUES ('12345');
INSERT INTO test VALUES ('1a4A5');
INSERT INTO test VALUES ('12a45');
INSERT INTO test VALUES ('12aBC');
INSERT INTO test VALUES ('12abc');
INSERT INTO test VALUES ('12ab5');
INSERT INTO test VALUES ('12aa5');
INSERT INTO test VALUES ('12AB5');
INSERT INTO test VALUES ('ABCDE');
INSERT INTO test VALUES ('123-5');
INSERT INTO test VALUES ('12.45');
INSERT INTO test VALUES ('1a4b5');
INSERT INTO test VALUES ('1 3 5');
INSERT INTO test VALUES ('1  45');
INSERT INTO test VALUES ('1   5');
INSERT INTO test VALUES ('a  b  c  d');
INSERT INTO test VALUES ('a b  c   d    e');
INSERT INTO test VALUES ('a              e');
INSERT INTO test VALUES ('Steven');
INSERT INTO test VALUES ('Stephen');
INSERT INTO test VALUES ('111.222.3333');
INSERT INTO test VALUES ('222.333.4444');
INSERT INTO test VALUES ('333.444.5555');
INSERT INTO test VALUES ('abcdefabcdefabcxyz');
commit ;




set pagesize 100
select * from test ;



TESTCOL
--------------------------------------------------
abcde
12345
1a4A5
12a45
12aBC
12abc
12ab5
12aa5
12AB5
ABCDE
123-5
12.45
1a4b5
1 3 5
1  45
1   5
a  b  c  d
a b  c   d    e
a              e
Steven
Stephen
111.222.3333
222.333.4444
333.444.5555
abcdefabcdefabcxyz

25 rows selected.



search for 2a start from 1 and  i for ignore case 

SELECT REGEXP_COUNT(testcol, '2a', 1, 'i') RESULT FROM test;



    RESULT
----------
         0
         0
         0
         1
         1
         1
         1
         1
         1
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0

25 rows selected.



search for "e" start from 1 and  i for ignore case 

SQL> SELECT REGEXP_COUNT(testcol, 'e', 1, 'i') RESULT FROM test;

    RESULT
----------
         1
         0
         0
         0
         0
         0
         0
         0
         0
         1
         0
         0
         0
         0
         0
         0
         0
         1
         1
         2
         2
         0
         0
         0
         2

25 rows selected.

Oracle Database Corruption Issues (Doc ID 1088018.1)

Oracle Database Corruption Issues (Doc ID 1088018.1)

Oracle block corruptions check via DBV

DBV untility - to check oracle Database Corruption

 

DB Verify
DB_VERIFY (dbv)
Oracle dbvverify Utility



col file_name format a20
select substr(name,instr(name,'/',-1)+1) file_name from v$datafile

FILE_NAME
--------------------
system01.dbf
sysaux01.dbf
users_compress.dbf
users01.dbf
example01.dbf
anujtest.dbf
tsapexf01.dbf
tsapexu01.dbf
test.dbf
rman.dbf
drop.dbf
anuj_perfstat.dbf
undotbR.dbf







oracle@apt-amd-02:~> cat dbv.sh

# !/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/11.2
export PATH=${ORACLE_HOME}/bin:$PATH
sqlplus -s "/ as sysdba" <<EOF
set echo off feedback off verify off pages 0 termout off linesize 150
spool dbv-${ORACLE_SID}.sh
select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0) ||' logfile=/tmp/'||substr(name,instr(name,'/',-1)+1)||'.log'
from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF




this script will create

-rw-r--r-- 1 oracle oinstall     1963 2012-01-19 09:41 dbv-orcl.sh




sh dbv.sh
dbv file=/opt/app/oracle/oradata/orcl/system01.dbf blocksize=8192 feedback=11136 logfile=/tmp/system01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/sysaux01.dbf blocksize=8192 feedback=10624 logfile=/tmp/sysaux01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/users_compress.dbf blocksize=8192 feedback=640 logfile=/tmp/users_compress.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/users01.dbf blocksize=8192 feedback=8688 logfile=/tmp/users01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/example01.dbf blocksize=8192 feedback=1280 logfile=/tmp/example01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/anujtest.dbf blocksize=8192 feedback=128 logfile=/tmp/anujtest.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/tsapexf01.dbf blocksize=8192 feedback=640 logfile=/tmp/tsapexf01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/tsapexu01.dbf blocksize=8192 feedback=1408 logfile=/tmp/tsapexu01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/test.dbf blocksize=8192 feedback=256 logfile=/tmp/test.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/rman.dbf blocksize=8192 feedback=640 logfile=/tmp/rman.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/drop.dbf blocksize=8192 feedback=26 logfile=/tmp/drop.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf blocksize=8192 feedback=12800 logfile=/tmp/anuj_perfstat.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/undotbR.dbf blocksize=8192 feedback=6400 logfile=/tmp/undotbR.dbf.log




dbv help=y

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Jan 19 09:48:16 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)           

refer this link for file name

http://anuj-singh.blogspot.com/2012/01/oracle-how-to-get-only-file-name-with.html

Oracle How to get only file name ( with out path )

Oracle file name

Oracle How to get only file name ( with out path )

Oracle file name

 select substr(name,instr(name,'/',-1)+1) file_name from v$datafile;

FILE_NAME
--------------------
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
example01.dbf

set linesize 200
col file_name format a15
col name format a70

select name, substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;

NAME                                                                   FILE_NAME
---------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/vihaan/system01.dbf                            system01
/u01/app/oracle/oradata/vihaan/sysaux01.dbf                            sysaux01
/u01/app/oracle/oradata/vihaan/undotbs01.dbf                           undotbs01
/u01/app/oracle/oradata/vihaan/users01.dbf                             users01
/u01/app/oracle/oradata/vihaan/example01.dbf                           example01

set linesize 200
col file_name format a15
col name format a70
col path for a50
select name,substr(name, 1, instr(name, '/',-1)) path , substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;

NAME                                                                   PATH                                               FILE_NAME
---------------------------------------------------------------------- -------------------------------------------------- ---------------
/u01/app/oradata/Orclsex/system01.dbf                                  /u01/app/oradata/Orclsex/                          system01
/u01/app/oradata/Orclsex/sysaux01.dbf                                  /u01/app/oradata/Orclsex/                          sysaux01
/u01/app/oradata/Orclsex/undotbs01.dbf                                 /u01/app/oradata/Orclsex/                          undotbs01
/u01/app/oradata/Orclsex/users01.dbf                                   /u01/app/oradata/Orclsex/                          users01

set linesize 200
col file_name format a50
col name format a70
col path for a50
select FILE_NAME,substr(FILE_NAME, 1, instr(FILE_NAME, '/',-1)) path , substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1, instr(substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1),'.')-1 ) file_name from dba_data_files
where TABLESPACE_NAME='USERS' ;

FILE_NAME                                          PATH                                               FILE_NAME
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
/u01/app/oradata/Orclsex/users01.dbf               /u01/app/oradata/Orclsex/                          users01
/u01/app/oradata/Orclsex/users02.dbf               /u01/app/oradata/Orclsex/                          users02
/u01/app/oradata/Orclsex/users03.dbf               /u01/app/oradata/Orclsex/                          users03
/u01/app/oradata/Orclsex/users04.dbf               /u01/app/oradata/Orclsex/                          users04
/u01/app/oradata/Orclsex/users05.dbf               /u01/app/oradata/Orclsex/                          users05
/u01/app/oradata/Orclsex/users06.dbf               /u01/app/oradata/Orclsex/                          users06
/u01/app/oradata/Orclsex/users07.dbf               /u01/app/oradata/Orclsex/                          users07
/u01/app/oradata/Orclsex/users08.dbf               /u01/app/oradata/Orclsex/                          users08
/u01/app/oradata/Orclsex/users09.dbf               /u01/app/oradata/Orclsex/                          users09
/u01/app/oradata/Orclsex/users10.dbf               /u01/app/oradata/Orclsex/                          users10
/u01/app/oradata/Orclsex/users11.dbf               /u01/app/oradata/Orclsex/                          users11
/u01/app/oradata/Orclsex/users12.dbf               /u01/app/oradata/Orclsex/                          users12
/u01/app/oradata/Orclsex/users13.dbf               /u01/app/oradata/Orclsex/                          users13

13 rows selected.

====

SELECT    'set newname for datafile '

       || file#

       || ' to ''/dumps/UGARB/'

       || substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'

       || ''';'

  FROM v$datafile;

Monitor Oracle user memory wise

set linesize 300
col "User" format a10
col "Machine" format a10
col "Statement" format a60
col MODULE format a30
col sid_serial format a15
SELECT SUBSTR(USERNAME,1,10) "User",
v$session.SID||','||v$session.serial# sid_serial,
SUBSTR(machine,1,10) "Machine",
sharable_mem, persistent_mem,
runtime_mem, executions,
-- v$sql.module,
SUBSTR(v$sql.sql_text,1,60) "Statement"
FROM v$session, v$sql, v$open_cursor
WHERE v$open_cursor.saddr = v$session.saddr
AND v$open_cursor.address = v$sql.address
and rownum < 50
ORDER BY SUBSTR(USERNAME,1,10), SUBSTR(machine,1,10);

User       SID_SERIAL      Machine    SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS Statement
---------- --------------- ---------- ------------ -------------- ----------- ---------- ------------------------------------------------------------
SYS        125,9           oel5u4            13870           3080        2424        261 select timestamp, flags from fixed_obj$ where obj#=:1
SYS        125,9           oel5u4            38694          12400       11380          1 SELECT SUBSTR(USERNAME,1,10) "User", v$session.SID||','||v$s
SYS        125,9           oel5u4            18053           5784        4232        310 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
SYS        125,9           oel5u4            13912          11868       11168          1 select oper, nclsrs, clpcstr from appqossys.wlm_classifier_p
SYS        125,9           oel5u4            18053           5684        4132          4 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
           133,1           oel5u4            26242           6244        5480         57 select size_for_estimate,                      size_factor *
           131,1           oel5u4            18034           4960        4276          4 insert into smon_scn_time (thread, time_mp, time_dp, scn, sc
           133,1           oel5u4            13995           3276        2512         57 select PGA_TARGET_FOR_ESTIMATE s,          PGA_TARGET_FACTOR
           10,7            oel5u4            13938           6752        6032          3 select lobtsn, lobrdba, lobobjid from x$lobstat   where lobs
           16,8            oel5u4            18113           4764        3396         14 select count(*) from sys.job$ where next_date < :1 and (fiel
           133,1           oel5u4            17966           8352        7820          5 delete /*+ CACHE_CB("ALERT_QT") */ from "SYS"."ALERT_QT" whe
           15,1            oel5u4            22099           7744        7068          6 select o.obj#, t.flags from tab$ t, obj$ o, user$ u where  u
           133,1           oel5u4            18028           7752        6968          7 select time_mp, scn, num_mappings, tim_scn_map from smon_scn
           133,1           oel5u4            13968           2576        1812         57 select shared_pool_size_for_estimate s,          shared_pool
           15,1            oel5u4            18053           5684        4132          4 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
           15,1            oel5u4            18053           5784        4232        310 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
           16,8            oel5u4            13901           3476        2692          8 select userid, name, subname, flags    from sys.scheduler$_l
           16,8            oel5u4            13901           3476        2692          1 select userid, name, subname, flags    from sys.scheduler$_l
           15,1            oel5u4            17991          13872       13340          5 update "SYS"."AQ$_ALERT_QT_H" set transaction_id = NULL wher
           15,1            oel5u4            13891           4200        3224        606 select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$
           7,1             oel5u4            22317           7352        5608        443 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
           133,1           oel5u4            18143           1072         484          1 BEGIN  dbms_ha_alerts_prvt.clear_instance_resources(   :dbdo
           15,1            oel5u4            18095           6224        4952       1574 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
           7,1             oel5u4            18095           6224        4952       1574 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
           133,1           oel5u4            38685          19172       17236          5 select  tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.
           15,1            oel5u4            34906          15312       12588          6 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
           133,1           oel5u4            17976           4632        4060          0 select TIME_WAITED_MICRO from V$SYSTEM_EVENT  where event =
           15,1            oel5u4            13968           4500        3716          8 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
           15,1            oel5u4            13968           4500        3716        318 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
           133,1           oel5u4             9772           2868        2296          8 select max(FA#) from SYS_FBA_FA
           16,8            oel5u4            22334           5284        3812        310 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1
           15,1            oel5u4            22154          11820       10588        314 select owner#,name,namespace,remoteowner,linkname,p_timestam
           15,1            oel5u4            22154          11820       10588         13 select owner#,name,namespace,remoteowner,linkname,p_timestam
           15,1            oel5u4            22154          11820       10588          1 select owner#,name,namespace,remoteowner,linkname,p_timestam
           133,1           oel5u4            22587           6760        6040          7 select /*+ FIRST_ROWS(1) */ x.C1, x.C2, x.C3 from  (select a
           15,1            oel5u4            13914           4312        3400        653 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
           7,1             oel5u4            13914           4312        3400        653 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
           133,1           oel5u4            42824          27356       26316          5 insert into "SYS"."ALERT_QT"  (q_name, msgid, corrid, priori
           15,1            oel5u4            13887           3784        3000          4 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
           15,1            oel5u4            13887           3796        3012       1347 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
           133,1           oel5u4            14068           4920        3624          1 select CONNECTION_POOL_NAME, STATUS, MINSIZE, MAXSIZE,
           15,1            oel5u4            43138          17848       14932        407 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
           7,1             oel5u4            43138          17848       14932        407 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
           140,5           oel5u4            18096            992         444          1  begin      dbms_aqadm_sys.remove_all_nondurablesub(:1, :2);
           133,1           oel5u4            18231           7580        6136          2 select subscriber_id, name, address, protocol, subscriber_ty
           140,5           oel5u4            13916           2736        2164          2 select 1 from sys.aq$_subscriber_table where rownum < 2 and
           16,8            oel5u4            44090          13900       13136         56 select next_run_date, obj#, run_job, sch_job from (select de
           133,1           oel5u4            13978           2660        1896         57 select streams_pool_size_for_estimate s,           streams_p
           15,1            oel5u4            22317           7352        5608        443 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d

49 rows selected.