This is just demonstration how to compile invalid  package body with minor errors without fixing code.You should always write correct code which will compile.Big what if you cannot see code ( wrapped or not your code and protected ) but you need to compile  that package .

Over the years I noticed that most package compilation errors are due to mismatch of specification and definition in the body. Read more of this post

Security is a big topic these days so it’s time to play with new Oracle 11g features.

Apperently encrypted tablespaces has few advantages over TDE (  table level encryption ) .Could not find detail info how all works but got this from google:

“Transparent encryption/decryption takes place during disk input/output
(I/O) and not for every logical access to the data. This leads to improved
performance.”
The encrypted data is protected during operations like JOIN and SORT. This
means that the data is safe when it is moved to temporary tablespaces. Data
in undo and redo logs is also protected.

and decided to play some demo:

First you need hex convertor:

Make lifes easier.Here is one:

http://www.paulschou.com/tools/xlate/

and more info about dissasembling redo log.

http://www.orafaq.com/papers/redolog.pdf

Old, but good paper  written by Graham Thornton .

 Now my demo:

SQL> create table secret (username varchar2(20),password varchar2(20)) tablespace users ;

Table created.

SQL> insert into secret values (‘MYSELF’,'SECUREPASSWORD’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from secret;

USERNAME             PASSWORD
——————– ——————–
MYSELF               SECUREPASSWORD

If  I look at datafile

strings /oracle/demo/data/users01.dbf|grep -i SECUREPASSWORD
SECUREPASSWORD

of course our data will be there exposed. Read more of this post

Quick way to find dynamically modified parameters.

Again old friend oradebug.Command oradebug dump modified_parameters will dump all dynamically altered parameters  (parameteres modified using alter system or alter session command).

Example:

oradebugh setmypid

SQL> alter session set cursor_sharing=force;

oradebug dump modified_parameters 1

oradebug tracefile_name
/oracle/admin/diag/rdbms/test/trace/test_ora_194.trc

host vi /oracle/admin/diag/rdbms/test/test_ora_194.trc

DYNAMICALLY MODIFIED PARAMETERS:
  _memory_broker_stat_interval= 600
  service_names            = test.world
  local_listener           = LISTENER_TEST
  cursor_sharing           = FORCE
  result_cache_max_size    = 10485760
  result_cache_max_result  = 6
  diagnostic_dest          = /ora-main/app/oracle/admin

high_version counts caused by unsafe binds?   Oracle event 10046 can be used to determine if binds are unsafe. 

From the trace file  part that represent binds:

BINDS #3:
 Bind#0
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0300 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aaaac0d8cf0  bln=22  avl=02  flg=09
  value=100

First field of interest oacdty represent Oracle internal data type.

Query to get list of internal oracle data types: Read more of this post

The sql query was using a lot of CPU’s resources.Research shows that cursor has high version_count. It is not being shared because of bind mismatch.

So for troubleshooting I have used  CURSORTRACE to trace cursor and get more information why cursor is not being shared. 

Some basic informations and simple test case :

Command to start tracing:

alter system set events ‘immediate trace name cursortrace level 577, address hash_value’;

Read more of this post