Oracle 10g - Purge Recyclebin

denis@test10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$4Pq19seDSl6+wC0qPEIl8g==$0 TABLE
BIN$5Axx4CZGRuSCF/IRkjHSAQ==$0 TABLE
BIN$ChfXT4unSY+zQgw7Voq1uQ==$0 TABLE
BIN$5LE/p9ZnRXaEWOMM9P02Ow==$0 TABLE
BIN$dl4rs24DSku72JGFBT3CVA==$0 TABLE
BIN$/DNAvyGPQxyRQm2mymxJ8Q==$0 TABLE
BIN$eoUDn9iZQZu8008avWIZTw==$0 TABLE
BIN$s/TtyOpCScOyQF4ryjTqoQ==$0 TABLE
BIN$Bg8JK9uhTYSuYKgj8RAHEQ==$0 TABLE
BIN$pQRYjS68TIWaxHVzVJPm3w==$0 TABLE
BIN$jiQonp+AR4qiyJrUtXuS1g==$0 TABLE
BIN$llYoG9Z/TPuHAqtp2lZl+Q==$0 TABLE
BIN$621+M0RgSOekCDcAcAUZ1w==$0 TABLE
BIN$O9Bb4JGdS2Ws/IHpXMvybw==$0 TABLE
BIN$oSZCxPp1SXWBseQhvo7XTw==$0 TABLE
BIN$LHMtbGiDSPyM7p0jFGzsxA==$0 TABLE
BIN$rhxtKwpeTFCUfajaRKGlew==$0 TABLE
T                              TABLE
BIN$ZC5/LPvySYW4O/iF+yz/6g==$0 TABLE
BIN$9s/ipMyTQ+yRrO3lpB0L/w==$0 TABLE
BIN$LfTIv03ES+eN1r3twcQD2g==$0 TABLE
BIN$1gapbvADSTKwC+vCkqa0xA==$0 TABLE
BIN$GYBu155XRl6HLLepitUvBQ==$0 TABLE
BIN$L87e8DnqSPWL8l4CQbPdpA==$0 TABLE
BIN$fofZqfT0QK2oKEkGy9y3Ag==$0 TABLE
BIN$mJbc7OqwQ4mvD+exbZMH3Q==$0 TABLE
BIN$M24sVrWOTWuf1W3Fn/qglA==$0 TABLE

27 rows selected.

denis@test10g>
denis@test10g>
denis@test10g> purge recyclebin;

Recyclebin purged.

denis@test10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

denis@test10g>

Using Regular Expressions with Oracle Database - two examples

Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

denis@test10g> ho ls

denis@test10g> @regexp_like.sql
denis@test10g> rem regexp_like.sql
denis@test10g> rem   - familar with REGEXP_LIKE
denis@test10g> rem   using regular expression to enfore data integrity
denis@test10g> 
denis@test10g> drop table t;

Table dropped.

denis@test10g> create table t
  2  (
  3    l_name varchar2(30),
  4    p_number varchar2(30)
  5    constraint p_number_format
  6       check ( REGEXP_LIKE(p_number, '^\(\d{3}\) \d{3}-\d{4}$' ) )
  7  );

Table created.

denis@test10g> 
denis@test10g> -- first two statements use valid phone number format
denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '(650) 555-5555'   );

1 row created.

denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '(215) 555-3427'   );

1 row created.

denis@test10g> -- remaining statements generate check contraint errors
denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '650 555-5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '650 555 5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '650-555-5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '(650)555-5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( ' (650) 555-5555'  );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> /
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> 
denis@test10g> ho ls

denis@test10g> @back_references.sql
denis@test10g> rem back_references.sql
denis@test10g> rem
denis@test10g> rem  Using Back Referencs to Reposition Characters
denis@test10g> rem  -- back references store matched subexpressions in a temporary buffer,
denis@test10g> rem    thereby enabling you to reposition characters
denis@test10g> rem
denis@test10g> rem  Explanation of the Regular Expression Elements in the Example
denis@test10g> rem
denis@test10g> rem   ^    Matches the beginning of the string.
denis@test10g> rem   $    Matches the end of the string.
denis@test10g> rem  (\S+)  Matches one or more non-space characters. The parentheses are not escaped so
denis@test10g> rem       they function as a grouping expression.
denis@test10g> rem  \s    Matches a whitespace character.
denis@test10g> rem  \1    Substitutes the first subexpression, that is, the first group of parentheses
denis@test10g> rem       in the matching pattern.
denis@test10g> rem  \2    Substitutes the second subexpression.
denis@test10g> rem  \3    Substitutes the third subexpression.
denis@test10g> rem   ,    Inserts a comma character.
denis@test10g> 
denis@test10g> 
denis@test10g> create table famous_people
  2  ( names varchar2(30)) ;

Table created.

denis@test10g> 
denis@test10g> -- populate table with data
denis@test10g> INSERT INTO famous_people
  2    VALUES ('John Quincy Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES ('Harry S. Truman');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES ('John Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES (' John Quincy Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES ('John_Quincy_Adams');

1 row created.

denis@test10g> COMMIT;

Commit complete.

denis@test10g> 
denis@test10g> 
denis@test10g> col "names" format a40
denis@test10g> col "names after regexp" format a40
denis@test10g> 
denis@test10g> SELECT names "names",
  2    REGEXP_REPLACE(names,
  3          '^(\S+)\s(\S+)\s(\S+)$',
  4          '\3, \1 \2')
  5    AS "names after regexp"
  6  FROM famous_people;

names                                    names after regexp
---------------------------------------- ----------------------------------------
John Quincy Adams                        Adams, John Quincy
Harry S. Truman                          Truman, Harry S.
John Adams                               John Adams
 John Quincy Adams                        John Quincy Adams
John_Quincy_Adams                        John_Quincy_Adams

denis@test10g> 
denis@test10g> drop table famous_people;

Table dropped.

denis@test10g> 
denis@test10g> spool off

Representing Conditional Expressions as Data - Testcase

Representing Conditional Expressions as Data - Testcase

I am working on night shift. To keep me awake, I decided to enhance my pl/sql knowledge during the shift if I have some free time. I build this test case for the conditional expressions when reading the Oracle application developer guide document.

At high level, I have a table called trader:

NAME                 EMAIL                          INTEREST
-------------------- ------------------------------ --------------------------------------------------
name1                vishu@abc.com                  symbol = 'ABC' and price > 31
name2                denis@abc.com                  symbol = 'ABC' and price < 31
name3                Nadeem@abc.com                 symbol = 'ABC' and price = 31
name4                Sam@abc.com                    symbol = 'ABC' and price <> 5.2
name5                John@abc.com                   symbol = 'ABC' and price < 31 and change = 5.2
name6                John@abc.com                   symbol = 'ABC' and price < 31 and change < 5.2
name7                denis@abc.com                  symbol = 'CCC' and price < 31
name8                denis@abc.com                  symbol = 'AAA' and price < 31
name9                vishu@abc.com                  symbol = 'ABC' and price > 25
name10               denis@abc.com                  symbol = 'ABC' and price < 25
name11               Nadeem@abc.com                 symbol = 'ABC' and price = 25
name12               Sam@abc.com                    symbol = 'ABC' and price <> 5.2
name13               John@abc.com                   symbol = 'ABC' and price > 25 and change = 5.2
name14               John@abc.com                   symbol = 'ABC' and price > 25 and change < 5.2
name15               denis@abc.com                  symbol = 'CCC' and price < 25
name16               denis@abc.com                  symbol = 'AAA' and price > 25

Then I issue a query and the output is as follows:

denis@test10g> select name, email
  2  from traders
  3  where evaluate ( interest,
  4          'symbol=> ''ABC'',
  5           price=>31,
  6           change=>5.2'
  7        ) = 1;

NAME                 EMAIL
-------------------- ------------------------------
name3                Nadeem@abc.com
name9                vishu@abc.com
name13               John@abc.com

I don't understand fully why the output looks like that, but at this time I decided to move on, leaving it to sometime later to figure it out

The following gives the whole output of the test case:

denis@test10g> @cond_expr
denis@test10g> rem script: cond_expr.sql
denis@test10g> rem
denis@test10g> rem Representing Conditional Expressions as Data
denis@test10g> rem
denis@test10g> rem    The Oracle Expression Filter feature enables you to store
denis@test10g> rem  conditional expressions as data in the database. The Expression Filter
denis@test10g> rem  provides a mechanism that you can use to place a constraint on a VARCHAR2
denis@test10g> rem  column to ensure that the values stored are valid SQL WHERE clause
denis@test10g> rem  expressions. This mechanism also identifies the set of attributes
denis@test10g> rem  that are legal to reference in the conditional expressions.
denis@test10g> rem
denis@test10g> rem  Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm
denis@test10g> rem
denis@test10g>
denis@test10g>
denis@test10g> -- (1) create a table traders holds data for a stock trading accont holder:
denis@test10g> create table traders
  2  ( name  varchar2(50),
  3    email varchar2(50),
  4    interest varchar2(50)
  5  );

Table created.

denis@test10g>
denis@test10g> -- (2)  create the user-defined datatype ticker with attributes for trading
denis@test10g> -- symbol, limit price, and amount of change in the stock price
denis@test10g>
denis@test10g> create or replace type ticker
  2  as object
  3  ( symbol varchar2(20),
  4   price number,
  5   change number
  6  );
  7  /

Type created.

denis@test10g> -- (3) create an attribute set ticker based on the ticker datatype
denis@test10g>
denis@test10g> begin
  2    dbms_expfil.create_attribute_set ( attr_set => 'ticker',
  3         from_type => 'YES');
  4  end;
  5  /

PL/SQL procedure successfully completed.

denis@test10g>
denis@test10g> -- (4)
denis@test10g> -- Associate the attribute set with the expression set stored in the
denis@test10g> -- database column trader.interest
denis@test10g> -- The following code places a constraint on the interest column that
denis@test10g> -- ensures the column stores valid conditional expressions
denis@test10g>
denis@test10g> begin
  2    dbms_expfil.assign_attribute_set (attr_set => 'ticker',
  3        expr_tab => 'traders',
  4        expr_col => 'interest');
  5  end;
  6  /

PL/SQL procedure successfully completed.

denis@test10g>
denis@test10g>
denis@test10g> -- (5) Populate the table with trader names, email addresses and conditional
denis@test10g> --     expressions that represents a stock the trader is interested in at
denis@test10g> --     a particular price:
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name1', 'vishu@abc.com', 'symbol = ''ABC'' and price > 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name2', 'denis@abc.com', 'symbol = ''ABC'' and price < 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name3', 'Nadeem@abc.com', 'symbol = ''ABC'' and price = 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name4', 'Sam@abc.com', 'symbol = ''ABC'' and price <> 5.2');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name5', 'John@abc.com', 'symbol = ''ABC'' and price < 31 and change = 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name6', 'John@abc.com', 'symbol = ''ABC'' and price < 31 and change < 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name7', 'denis@abc.com', 'symbol = ''CCC'' and price < 31');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name8', 'denis@abc.com', 'symbol = ''AAA'' and price < 31');

1 row created.

denis@test10g>
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name9', 'vishu@abc.com', 'symbol = ''ABC'' and price > 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name10', 'denis@abc.com', 'symbol = ''ABC'' and price < 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name11', 'Nadeem@abc.com', 'symbol = ''ABC'' and price = 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name12', 'Sam@abc.com', 'symbol = ''ABC'' and price <> 5.2');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name13', 'John@abc.com', 'symbol = ''ABC'' and price > 25 and change = 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name14', 'John@abc.com', 'symbol = ''ABC'' and price > 25 and change < 5.2 ');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name15', 'denis@abc.com', 'symbol = ''CCC'' and price < 25');

1 row created.

denis@test10g>
denis@test10g> insert into traders (name, email, interest)
  2    values ('name16', 'denis@abc.com', 'symbol = ''AAA'' and price > 25');

1 row created.

denis@test10g>
denis@test10g> commit;

Commit complete.

denis@test10g>
denis@test10g> -- (6) Use the EVALUATE operator to identify teh conditional expressions
denis@test10g> set linesize 120
denis@test10g> col name format a20
denis@test10g> col email format a30
denis@test10g>
denis@test10g>
denis@test10g> select * from traders;

NAME                 EMAIL                          INTEREST
-------------------- ------------------------------ --------------------------------------------------
name1                vishu@abc.com                  symbol = 'ABC' and price > 31
name2                denis@abc.com                  symbol = 'ABC' and price < 31
name3                Nadeem@abc.com                 symbol = 'ABC' and price = 31
name4                Sam@abc.com                    symbol = 'ABC' and price <> 5.2
name5                John@abc.com                   symbol = 'ABC' and price < 31 and change = 5.2
name6                John@abc.com                   symbol = 'ABC' and price < 31 and change < 5.2
name7                denis@abc.com                  symbol = 'CCC' and price < 31
name8                denis@abc.com                  symbol = 'AAA' and price < 31
name9                vishu@abc.com                  symbol = 'ABC' and price > 25
name10               denis@abc.com                  symbol = 'ABC' and price < 25
name11               Nadeem@abc.com                 symbol = 'ABC' and price = 25
name12               Sam@abc.com                    symbol = 'ABC' and price <> 5.2
name13               John@abc.com                   symbol = 'ABC' and price > 25 and change = 5.2
name14               John@abc.com                   symbol = 'ABC' and price > 25 and change < 5.2
name15               denis@abc.com                  symbol = 'CCC' and price < 25
name16               denis@abc.com                  symbol = 'AAA' and price > 25

16 rows selected.

denis@test10g>
denis@test10g> select name, email
  2  from traders
  3  where evaluate ( interest,
  4          'symbol=> ''ABC'',
  5           price=>31,
  6           change=>5.2'
  7        ) = 1;

NAME                 EMAIL
-------------------- ------------------------------
name3                Nadeem@abc.com
name9                vishu@abc.com
name13               John@abc.com

denis@test10g>
denis@test10g>
denis@test10g> -- clean up the test code:
denis@test10g> drop table traders;

Table dropped.

denis@test10g>
denis@test10g> begin
  2    dbms_expfil.drop_attribute_set ( attr_set => 'ticker');
  3  end;
  4  /

PL/SQL procedure successfully completed.

denis@test10g> spool off

Library Cache Pin for over 600 seconds

During my on-call shift today, I recieved the alerts for a database: Library Cache Pin for over 600 seconds

Checked the database, found the following:

===> check session wait: sid=852 wait for library cache pin


SID USERNAME EVENT P1 P2 WAIT_TIME STATE
------ --------------- -------------------- ---------- ---------- ---------- -------------------

852 MTZUSG12CON library cache pin 1.3835E+19 1.3835E+19 0 WAITING


===> current running sql:

SQL_TEXT SID USER_NAME
---------------------------------------- ------ ------------
update AC_PROCESSING_ACCOUNTING set FIL 852 MTZUSG12CON
E_STATUS='CN' where (((FPFC_NXT_PGM_NAME
=:b0 and FILE_STATUS='RD') and FPFC_NXT_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)


From Google, found Burleson has a tip for this topic. click this.

The following script is published by Mark Bobak for locating the source of library cache pin waits.

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      62, 'EVALUATION CONTEXT',
                     'UNDEFINED') object_type,
       lob.KGLNAOBJ object_name,
       pn.KGLPNMOD lock_mode_held,
       pn.KGLPNREQ lock_mode_requested,
       ses.sid,
       ses.serial#,
       ses.username
  FROM
       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw
  WHERE
   pn.KGLPNUSE = ses.saddr and
   pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event = 'library cache pin'
order by lock_mode_held desc
/

I run it as sysdba and the output is:

OBJECT_TYPE
------------------
OBJECT_NAME
----------------------------------------------------------------------------------------------------------------------                                       --
LOCK_MODE_HELD LOCK_MODE_REQUESTED    SID    SERIAL# USERNAME
-------------- ------------------- ------ ---------- ---------------
NEXT OBJECT
update AC_PROCESSING_ACCOUNTING  set FILE_STATUS='CN' where (((FPFC_NXT_PGM_NAME=:b0 and FILE_STATUS='RD') and FPFC_NX                                       T_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)
             3                   0    985      28668 MTZUSG08CON

NEXT OBJECT
update AC_PROCESSING_ACCOUNTING  set FILE_STATUS='CN' where (((FPFC_NXT_PGM_NAME=:b0 and FILE_STATUS='RD') and FPFC_NX                                       T_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)
             0                   2    852      14208 MTZUSG12CON

After a while, the wait envet went away by itself.

Understanding Read-Only Transaction

In the Oracle document, it stats:
"A read-only transaction does not acquire any additional data locks to provide transaction-level read consistency. The multi-version consistency model used for statement-level read consistency is used to provide transaction-level read consistency; all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins. Because no data locks are acquired, other transactions can query and update data being queried concurrently by a read-only transaction."

To help understand the concept of read-only transaction, I did a small test:

In the session 1, I created a table and keep updating the column value:

test@MYDB> create table t ( a int);

Table created.

test@MYDB> insert into t values(1);

1 row created.

test@MYDB> commit;

Commit complete.

test@MYDB> select * from t;

         A
----------
         1

test@MYDB> update t set a=2;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=3;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=4;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=5;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB>

In the session 2, after update the value to 3 in session 1, I started a read-only transaction as following:

test@MYDB> set transaction read only;

Transaction set.

And after each update in session 1, I query the table t, notice I always got value 3, this demonstrated the point:
"all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins"

test@MYDB> select * from t;

         A
----------
         3

test@MYDB> select * from t;

         A
----------
         3

test@MYDB> select * from t;

         A
----------
         3

Then in session 2, I issue the commmit, this end of the read only transaction mode, and I query table t again, got the value 5 as expected

test@MYDB> commit;

Commit complete.

test@MYDB> select * from t;

         A
----------
         5