Virtual on Virtual – VPD on virtual column

Quick test to prove that  Virtual Private Database ( VPD ) works on virtual column.

Here is demo.First create test table and load it ( so lazy that I found this on google too )

/** Create table and insert some rows **/
DROP TABLE employees;
CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  bonus      NUMBER(9,2),
  extra1       NUMBER(3),
  extra2       NUMBER(3),
  bonus1     AS (ROUND(bonus*(1+extra1/100),2)),
  bonus2     NUMBER GENERATED ALWAYS AS (ROUND(bonus*(1+extra2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, bonus, extra1, extra2)
VALUES (1, ‘MARKO’, ‘JANKO’, 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, bonus, extra1, extra2)
VALUES (2, ‘JOE’, ‘JANKO’, 200, 10, 20);

COMMIT;

 /** VPD role **/

drop role view_virtual;
create role view_virtual not identified;

Here is my function that will enable users witt VIEW_VIRTUAL role to view data on column BONUS2.

 /** Create function **/

CREATE OR REPLACE FUNCTION secure_virtual (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
 
BEGIN
  IF dbms_session.is_role_enabled(‘VIEW_VIRTUAL’)  then
   return ”;
  
  ELSE 
    return ’1=0′; — don’t show column
  END IF;
END secure_virtual;
/

and finally create policy  Read more of this post

Script to list modified parameters

All started with dba-willage post when someone asked how to list static and dynamic parameter..

Answer was to select  v$parameter table.I made a comment that also oradebug ( love it ) can be used to list dynamically modified parameter ( one of the previous post )  and got the answer that oradebug is useless when ISMODIFIED column or v$parameter is enough..

Since I like internal stuff a posted a query that can do the same but not using oradebug but underluying  x$ tables..  and this will list underscore parameters and session or system modified too…

Demo:

SQL> alter session set “_replace_virtual_columns”=true;

Session altered.

SQL> select
  2    a.ksppinm  name,
  3    b.kspftctxvl  value,
  4    b.kspftctxdf  isdef,
  5    decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  ismod,
  6    decode(bitand(b.kspftctxvf,2),2,’TRUE’,'FALSE’)  isadj
  7  from
  8    sys.x$ksppi  a,
  9    sys.x$ksppcv2  b
 10  where a.inst_id = userenv(‘Instance’) and
 11    b.inst_id = userenv(‘Instance’) and
 12    a.indx+1 = b.kspftctxpn and
 13    decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  <> ‘FALSE’
 14  order by
 15    translate(a.ksppinm, ‘ _’, ‘ ‘)
 16  /

NAME                                          VALUE                                         ISDEF  ISMOD      ISADJ
——————————————— ——————————————— —— ———- —–
cursor_sharing                                exact                                         TRUE   SYSTEM_MOD FALSE
_replace_virtual_columns                      TRUE                                          TRUE   MODIFIED   FALSE

Here is script:
select
  a.ksppinm  name,
  b.kspftctxvl  value,
  b.kspftctxdf  isdef,
  decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  ismod,
  decode(bitand(b.kspftctxvf,2),2,’TRUE’,'FALSE’)  isadj
from
  sys.x$ksppi  a,
  sys.x$ksppcv2  b
where a.inst_id = userenv(‘Instance’) and
  b.inst_id = userenv(‘Instance’) and
  a.indx+1 = b.kspftctxpn and
  decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  <> ‘FALSE’
order by
  translate(a.ksppinm, ‘ _’, ‘ ‘)
/

Run away from GUI DBA.. Script to report waiting sessions for RAC

Found this one in my old DBA folder.. It’s time to use more sqlplus command line than Toad and OEM ( GUI DBA )..

 

set serverout on size 999999
     declare
     begin
     dbms_output.put_line(‘ ‘);
     dbms_output.put_line(‘************* Start report for WAITING sessions with current SQL ***************’);
     for x in (select vs.inst_id, vs.sid || ‘,’ || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> ‘BACKGROUND’
     and vsw.event NOT IN (‘rdbms ipc message’
     ,’smon timer’
     ,’pmon timer’
     ,’SQL*Net message from client’
     ,’lock manager wait for remote message’
     ,’ges remote message’
     ,’gcs remote message’
     ,’gcs for action’
     ,’client message’
     ,’pipe get’
     ,’Null event’
     ,’PX Idle Wait’
     ,’single-task message’
     ,’PX Deq: Execution Msg’
     ,’KXFQ: kxfqdeq – normal deqeue’
     ,’listen endpoint status’
     ,’slave wait’
     ,’wakeup time manager’))
     loop
     begin
     dbms_output.put_line(‘Event WaitState InstID SidSerial LastCallEt SecondsInWait’);
     dbms_output.put_line(‘************************* ******************** ****** *********** ********** *************’);
     dbms_output.put_line(rpad(x.event,25) ||’ ‘|| rpad(x.state,20) ||’ ‘|| lpad(x.inst_id,6) ||’ ‘|| lpad(x.sidser,11) ||’
     ‘|| lpad(x.last_call_et,10) ||’ ‘|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(‘ SQLText ‘);
     dbms_output.put_line(‘****************************************************************’);
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line(‘************** End report for sessions waiting with current SQL ****************’);
     dbms_output.put_line(‘ ‘);
     end;
     /

Output look like: Read more of this post

Poor man Audit Vault

Everyone knows how to enable database auditing .That’s for sure.But to make auditors happy is another story.One of the requirements is that all these data  from the source should be  stored somewhere else.. but not on source ..

If you have plenty of money you can get  Oracle audit wault.But if you don’t   than you make your own audit wault  using Oracle Streams.I called it Poor man audit wault.There is metalink note DML and DDL Auditing Using Streams which explains this into detail.

I run into this presentation many months ago and again today..  What trigger my attention is that  if I correctly remember audit vault use same technology beneath  to offload data and load into vault data warehouse database…

Principle is simple.Streams capture process run of different database .Not on the one where redo is generated.This feature is called Downstream capture.Redo logs are transfered to different database and mined by capture process.All auditing work can be offloaded to downstrem database.

Metalink Note: 316893.1

and also :

https://www.dbguide.net/servlet/com.util.DownloadServlet?file=%2F20060316%2F1142486648994.pdf&filename=DDL+DML%BF%A1+%B4%EB%C7%D1+%B0%A8%BB%E7.pdf 

Apex in 11g database.

 

My pain developer http://www.oraclenerd.com/  keep buging me for some time to install APEX…   We had it installed and it worked fine and sudenly APEX images did not work…

In the past I have installed APEX  using Oracle Application server and configuring dads.conf file etc..

Well with 11g database installing APEX is much easier.In Oracle Database 11g, Oracle Application Express is installed when the database is installed.

Only thing you have to do is to run script  apxconf located under $ORACLE_HOME/apex directory.This script will confugure PL/SQL gateway.

-sh-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Feb 17 14:46:42 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

SQL> @apxconf

      PORT
———-
      8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.
Enter a password for the ADMIN user              [] admin_123
Enter a port for the XDB HTTP listener [      8080]
…changing HTTP Port

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.

…changing password for ADMIN

PL/SQL procedure successfully completed.
Commit complete.

SQL> alter user anonymous account unlock;

User altered.

SQL> exit

You can find Oracle tutorial 

http://www.oracle.com/technology/obe/11gr1_db/install/apexinst/apexinst.htm

No more messing with app server and encrypting password for apex user…

Incorrect Reference Critical Patch Update January 2009

I am in process of applying  January patch for my 11g and 10g database.There is always something that will cause a trouble…

Just found a this note explaining that Oracle posted incorrect references related to CPU patch..

Here is note .

Metalink Note:753345.1  Critical Patch Update January 2009 Known Issues for Oracle Database:

Incorrect CPU Reference

Read it before start playing with this CPU..

OERI – Oracle Error Impact Notes

The OERI notes are helpfull ( where exists ) when troubleshooting Oracle internal erros like ORA-00600 or ORA-07445  .

When I am searching metalink normaly I enter oracle error and argument  like

ORA-00600  [kmgs_parameter_update_timeout_1]

but with addition of word  OERI

ORA-00600  ORA-00600  [kmgs_parameter_update_timeout_1]  OERI 

metalink search will return all notes with Error and Impact information.This is not a case for all errors.Some of them are not documented.

Inside Encrypted tablespace 11g Part2

I have been vey busy these days but today finally I got some time ..

Well few weeks ago I did a post on encrypted tablespace in 11g  and one of the questions posted by Martin Beger ( Sorry I did not have a chance to replay earlier ) was that data are unencrypted in buffer cache.

I did some reading and research ( still working on my case as time permits )  and found that actually that’s a true statement.

So how it works?

1)  When you do select data are moved from storage ( encrypted data ) to buffer cache.

2) Data are DECRYPTED before loading buffer cache

3) Data are in CLEAR TEXT UNENCRYPTED inside buffer cache

Since most of  operations  ( index scans , table access ) are performed on clear text data  which are in buffer cache there is no differences in peformances  between encrypted tablespace ( unencrypted in buffer cache ) and unencrypted data.That’s the trick why encrypted tablespaces has better performances over regular TDE on table ( Have to test regular TDE on table – next workshop ).

Eventually we have to record data back to storage.

DB writer process during checkpoint will encrypted data before writing to disk (  I have proved this in my previous blog).

Now there is interesting point.According to Oracle   DIRECT PATH operations perform encryption inline.When log writer process write log buffer data to redo logs they are ENCRYPTED.If I am not mistaken in my case from previous post data were UNENCRYPTED inside redo log files but I did not use DIRECT PATH. That would be next test.

DDL Audit trigger that sends e-mail

Found this code on google and modified it to send e-mail when DDL change on schema of interest.You can alter it to fit your needs..

First create two tables that will store DDL information:events and sqltext:
DROP TABLE ddl_events;
CREATE TABLE ddl_events
( eventId          NUMBER(10,0),
  eventDate        DATE,
  oraLoginUser     VARCHAR2(30),
  oraDictObjName   VARCHAR2(30),
  oraDictObjOwner  VARCHAR2(30),
  oraDictObjType   VARCHAR2(30),
  oraSysEvent      VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuser           VARCHAR2(30),
  ip_address       VARCHAR2(20));
 
DROP TABLE ddl_events_sql;
CREATE TABLE ddl_events_sql
( eventId          NUMBER(10,0),
  sqlLine          NUMBER(10,0),
  sqlText          VARCHAR2(4000) );
 
Sequence to support events id’s:
DROP SEQUENCE dsq_ddlEvents;
CREATE SEQUENCE dsq_ddlEvents START WITH 1000;
and here is trigger code:

CREATE OR REPLACE TRIGGER dtr_ddlEvents
AFTER DDL ON DATABASE
DECLARE
 
  l_sqlText    ORA_NAME_LIST_T;
 
BEGIN
  IF ORA_DICT_OBJ_OWNER in (‘SCHEMA_NAME’)
  THEN
BEGIN
  utl_mail.send (
  sender => ‘senders_email’,
  recipients => ‘recipient_email’,
  subject => ‘DDL change  has been made in ‘||ORA_DATABASE_NAME||’ database.’,
  message => ‘User ‘||ORA_LOGIN_USER||’ had run ‘||ORA_SYSEVENT|| ‘ on ‘||ORA_DICT_OBJ_TYPE||’ ‘||ORA_DICT_OBJ_OWNER||’.'||ORA_DICT_OBJ_NAME||’ in ‘||ORA_DATABASE_NAME||’ database.’
  );
  END;
  INSERT INTO ddl_events
  ( SELECT dsq_ddlEvents.NEXTVAL,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           SYS_CONTEXT(‘USERENV’,'IP_ADDRESS’)
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT(‘USERENV’,'SESSIONID’ ) = audsid(+) );
 
   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( dsq_ddlEvents.CURRVAL, l, l_sqlText(l) );
  END LOOP;
 END IF;
END;
/

Keep in mind purging recyclebin with objects owned by schema will also send e-mail so you might need to exclude BIN objects.