29 June, 2013

A Function executing DML in a View

As promised earlier, here is a simple demonstration of a Function that gets executed when a View is queried and does it's own DML.  (Note : I have to use "pragma autonomous_transaction" for the DML).

The code :

SQL> drop table emp_view_log;

Table dropped.

SQL> drop table employees;

Table dropped.

SQL> 
SQL> create table employees as select * from hr.employees;

Table created.

SQL> 
SQL> create table emp_view_log
  2  (log_dt_tm  date,
  3   session_id  number,
  4   session_user varchar2(30)
  5  )
  6  /

Table created.

SQL> 
SQL> create or replace function log_emp_view_fn
  2  return number
  3  as
  4  pragma autonomous_transaction;
  5  begin
  6  insert into emp_view_log
  7  select sysdate, sys_context('USERENV','SID'), user from dual;
  8  commit;
  9  return null;
 10  end;
 11  /

Function created.

SQL> 
SQL> create or replace view emp_view
  2  as select employee_id, first_name, last_name, log_emp_view_fn
  3  from employees
  4  /

View created.

SQL> 
What I have done is that I have created a table emp_view_log to log all executions of queries against the employees table. I have then created a function log_emp_view_fn to do the actual inserts into this log table. The view emp_view is then "published" to users.
 Users are presented a view that hides the SALARY column from the employees table.  Yet, when they query this view, for every row they query, the log table captures the occurrence.
(Question : Why does the log table capture one row for every row queried from the view ?)

Let's see what happens when I query the emp_view view :

SQL> select * from emp_view where last_name = 'KING';

no rows selected

SQL> select * from emp_view_log;

no rows selected

SQL> select * from emp_view where last_name = 'Abel';

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 LOG_EMP_VIEW_FN
----------- -------------------- ------------------------- ---------------
        174 Ellen                Abel

SQL> select to_char(log_dt_tm,'DD-MON-RR HH24:MI:SS'), session_id, session_user
  2  from emp_view_log;

TO_CHAR(LOG_DT_TM,'DD-MON-R SESSION_ID SESSION_USER
--------------------------- ---------- ------------------------------
29-JUN-13 08:11:28                  37 HEMANT

SQL> 
SQL> 
SQL> rem wait a while ...
SQL> select * from emp_view where employee_id between 100 and 102;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 LOG_EMP_VIEW_FN
----------- -------------------- ------------------------- ---------------
        100 Steven               King
        101 Neena                Kochhar
        102 Lex                  De Haan

SQL> select to_char(log_dt_tm,'DD-MON-RR HH24:MI:SS'), session_id, session_user
  2  from emp_view_log
  3  order by log_dt_tm;

TO_CHAR(LOG_DT_TM,'DD-MON-R SESSION_ID SESSION_USER
--------------------------- ---------- ------------------------------
29-JUN-13 08:11:28                  37 HEMANT
29-JUN-13 08:12:57                  37 HEMANT
29-JUN-13 08:12:57                  37 HEMANT
29-JUN-13 08:12:57                  37 HEMANT

SQL> 
The first query for employee 'KING' (instead of 'King') returned no rows so no rows were logged in the emp_view_log.
The third query was for 3 rows from the emp_view view.  So, emp_view_log created three entries.

.
.
.

25 June, 2013

Upcoming Blog Post : DML when querying a View

My next blog post will be about how to setup your view definition and certain code such that when the view is queried, DML is executed.

What sort of DML might you want to execute when a view is queried ?

.
.
.

20 June, 2013

Getting the ROWIDs present in a Block


Here is a demonstration of getting the ROWIDs present in a block.

Assume that you are interested in Block 172 in File 11.  Maybe this block is being reported in V$SESSION  (ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) and you know it is object 114154.  OR Assume that this block is being reported in a trace file and you want to know the contents of the block.

First I start with a new Table and identify its OBJECT_ID and Blocks potentially holding the rows.  (In your case you might have already identified the Block(s).

SQL> create table new_table_block_rowid (id_col number, data_col varchar2(15));

Table created.

SQL> insert into new_table_block_rowid values (1, 'Row One');

1 row created.

SQL> insert into new_table_block_rowid values (2, 'Row Two');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select file_id, block_id
  2  from dba_extents -- currently the table is a single extent
  3  where segment_name = 'NEW_TABLE_BLOCK_ROWID'
  4  and owner = 'HEMANT'
  5  /

   FILE_ID   BLOCK_ID
---------- ----------
        11        168

SQL> 
SQL> select object_id, data_object_id
  2  from user_objects
  3  where object_name = 'NEW_TABLE_BLOCK_ROWID'
  4  /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    114154         114154

SQL> 
For subsequent verification, I get the ROWIDs.  (If your query is "which ROWIDs exist in Block 'N', you wouldn't know these ROWIDs in advance but I print them here for verification to confirm that I subsequently identfy the correct ROWIDs.)

SQL> select id_col, data_col, rowid
  2  from new_table_block_rowid
  3  order by 1;

    ID_COL DATA_COL        ROWID
---------- --------------- ------------------
         1 Row One         AAAb3qAALAAAACsAAA
         2 Row Two         AAAb3qAALAAAACsAAB

SQL> 
Later, I will confirm that I have the right ROWIDs.

I now use the DBMS_ROWID.CREATE_ROWID function to identify all potential ROWIDs.  Remember that the first row is not necessarily in Block 168 of File 11.

SQL> select dbms_rowid.rowid_create(1,114154,11,target_block,1), target_block
  2  from
  3  (select rownum+168 target_block from dual connect by level < 12)
  4  /

DBMS_ROWID.ROWID_C TARGET_BLOCK
------------------ ------------
AAAb3qAALAAAACpAAB          169
AAAb3qAALAAAACqAAB          170
AAAb3qAALAAAACrAAB          171
AAAb3qAALAAAACsAAB          172
AAAb3qAALAAAACtAAB          173
AAAb3qAALAAAACuAAB          174
AAAb3qAALAAAACvAAB          175
AAAb3qAALAAAACwAAB          176
AAAb3qAALAAAACxAAB          177
AAAb3qAALAAAACyAAB          178
AAAb3qAALAAAACzAAB          179

11 rows selected.

SQL> 
What I have identified is a set of (potential) ROWIDs for (potential) Rows in Blocks 169 to 179.

 Let's assume that I have identified my candidate block as Block 172 and need to identify the ROWIDs in Block 172.

Here's how I identify the ROWIDs in Block 172 :

SQL> select ret_rowid
  2  from
  3  (
  4  select dbms_rowid.rowid_create(1,114154,11,target_block,target_row) ret_rowid
  5  from
  6  (select 172 target_block from dual) a,
  7  (select rownum-1 target_row from dual connect by level < 8) b
  8  )
  9  /

RET_ROWID
------------------
AAAb3qAALAAAACsAAA
AAAb3qAALAAAACsAAB
AAAb3qAALAAAACsAAC
AAAb3qAALAAAACsAAD
AAAb3qAALAAAACsAAE
AAAb3qAALAAAACsAAF
AAAb3qAALAAAACsAAG

7 rows selected.

SQL> 
That gives me 7 (potential) ROWIDs for (potential) Rows in Bock 172 of File 11 owned by Object 114154.

Let's verify which of these ROWIDs contain the rows I want :

SQL> l
  1  select rowid, id_col, data_col from new_table_block_rowid
  2  where
  3  rowid in
  4  (
  5  select ret_rowid
  6  from
  7  (
  8  select dbms_rowid.rowid_create(1,114154,11,target_block,target_row) ret_rowid
  9  from
 10  (select 172 target_block from dual) a,
 11  (select rownum-1 target_row from dual connect by level < 8) b
 12  )
 13  )
 14* order by id_col
SQL> /

ROWID                  ID_COL DATA_COL
------------------ ---------- ---------------
AAAb3qAALAAAACsAAA          1 Row One
AAAb3qAALAAAACsAAB          2 Row Two

SQL> 
Voila !  I have identified the two rows (with their ROWIDs) that are prsent in File 11, Block 172, Object 114154 and have confirmed that only 2 and not 7 or more rows actually exist !


Note : Thanks to Jonathan Lewis for pointing out that the Row Directory actually starts with Zero (0).  Tha is why my query for "target_row" has rownum-1 from dual.

.
.
.

16 June, 2013

DROP A Tablespace After a Backup


What happens if you drop a tablespace after it is backed up ?

Here's the backup :

RMAN> backup as compressed backupset database;

Starting backup at 16-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
channel ORA_DISK_1: starting piece 1 at 16-JUN-13
channel ORA_DISK_1: finished piece 1 at 16-JUN-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_nnndf_TAG20130616T080419_8vvnzn37_.bkp tag=TAG20130616T080419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-JUN-13
channel ORA_DISK_1: finished piece 1 at 16-JUN-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_ncsnf_TAG20130616T080419_8vvo4k8w_.bkp tag=TAG20130616T080419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUN-13

RMAN>

Here's the subsequent DROP TABLESPACE :

SQL> drop tablespace APEX_2614203650434107 including contents and datafiles;

Tablespace dropped.

SQL> 

What happens when I try to LIST the BACKUP of the datafile / tablespace ?

RMAN> list backup of datafile 10;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/16/2013 08:07:44
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 10

RMAN> 
RMAN> list backup of tablespace APEX_2614203650434107;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/16/2013 08:12:12
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "APEX_2614203650434107"

RMAN> 


What does a full LIST BACKUP OF DATABASE show ?
RMAN> list backup of database ;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    799.58M    DISK        00:02:30     16-JUN-13      
        BP Key: 4   Status: AVAILABLE  Compressed: YES  Tag: TAG20130616T080419
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_nnndf_TAG20130616T080419_8vvnzn37_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  10      Full 14093203   16-JUN-13 
  11      Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

RMAN> 

Datafile 10 appears as a NULL entry. It cannot be restored as it no longer belongs to the database.

A RESTORE obviously fails :
RMAN> restore datafile 10;

Starting restore at 16-JUN-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/16/2013 08:15:32
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 10

RMAN> 

So, there you have it.  Once a datafile doesn't belong to the database it cannot be restored.

.
.
.

03 June, 2013

Bug 10013177 running Aggregation on Expression indexed by an FBI

I noticed an interesting Bug on MOS today.  When running an Aggregation against an Expression that has a Function Based Index, results may be truncated.  The bug notes that dumps and internal errors are also possible.

Here's a demo.  (tested in 11.2.0.2)

Start with a table and an FBI
SQL> create table test_10013177 (col_1 number(6,4));

Table created.

SQL> insert into test_10013177 values (12.3456);

1 row created.

SQL> create index index_10013177 on test_10013177(col_1+1);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','TEST_10013177');

PL/SQL procedure successfully completed.

SQL> select col_1+1 from test_10013177;

   COL_1+1
----------
   13.3456

SQL> select max(col_1+1) from test_10013177;

MAX(COL_1+1)
------------
     13.3456

SQL> select /*+ full (t) */ max(col_1+1) from test_10013177 t;

MAX(COL_1+1)
------------
          13

SQL> 
Funnily, the results in the last query are truncated !
.
.
.