FIXED_DATE

There are so many little known parameters in oracle that sometimes you are taken by surprise. Today I found out about FIXED_DATE parameter and I did not know about it till now. Did you? May be...

This parameter is used to set a constant date for SYSDATE pseudo column. Once set, SYSDATE would always return the constant date. However, the problem is that it's not modifiable at session level which means it may not be really useful.

How to set FIXED_DATE :-

ALTER SYSTEM SET FIXED_DATE='2008-02-02-00:00:00';

SQL> select sysdate from dual;

SYSDATE
---------
02-FEB-08

Note: The format can be as shown above or the oracle default date format.

How to unset :-

ALTER SYSTEM SET FIXED_DATE=NONE;

SQL> select sysdate from dual;

SYSDATE
---------
27-FEB-08

BUT, setting this parameter does not effect SYSTIMESTAMP. It continues to show current timestamp.

SQL> ALTER SYSTEM SET FIXED_DATE='2008-02-02-00:00:00';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
02-FEB-08

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-FEB-08 09.01.07.669041 AM +05:30

Since this is a very old parameter (I think it was introduced in Oracle 8), my guess is SYSTIMESTAMP has been left out as its relatively new.

Using FGA to view/retrieve bind variables used in queries

I was responding to a question on Oracle Forums on retrieving bind variables and thought I should post this on the blog as well.

Of many things, fine grained auditing techniques can used to view/retrieve values supplied to bind variables used in a query. I found it useful when user complains that queries are taking longer time in certain conditions and normally you would rely on application developers to provide you with the values supplied to the bind variables.

I have demonstrated an example below to retrieve bind variable values using FGA :-

1. First apply audit policy on the table/s which are involved in SQL e.g. in this example its the "DEPT" table.

begin
dbms_FGA.add_policy (
object_schema => 'SCOTT',
object_name => 'DEPT',
policy_name => 'DEPT_Aud',
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'UPDATE, DELETE, INSERT, SELECT' );
end;
/

2. Run your SQL with bind variables e.g. in this example it's an INSERT into DEPT table.

var a number
var b varchar2(20)
var c varchar2(20)

begin
:a:=90;
:b:='NewDept';
:c:='NewLoc';
end;
/

INSERT INTO DEPT VALUES(:a,:b,:c);

3. Query DBA_FGA_AUDIT_TRAIL view to get the SQL and bind variables

select object_name, sql_text, sql_bind from dba_fga_audit_trail;

OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------

DEPT
INSERT INTO DEPT VALUES(:a,:b,:c)
#1(2):90 #2(7):NewDept #3(6):NewLoc

So, happy auditing BUT do remember to turn off the auditing when your work is over.

begin
DBMS_FGA.DROP_POLICY (
object_schema => 'SCOTT',
object_name => 'DEPT',
policy_name => 'DEPT_Aud');
end;
/

Compare table structure in oracle

I found an interesting script that I used sometime back to compare the structure of 2 tables. Thought I should preserve and blog it for the benefit of all.

--- SCRIPT START ---

Rem script name - compare_tables.sql
Rem Table structure comparison script
Rem Can compare tables across schema's provided the user has privilege
Rem Provide table name in the format SCHEMA_NAME.TABLE_NAME

set verify off
set heading off
set feedback off
set line 100

column owner format a20
column column_name format a20
column data_type format a20


accept table1 prompt 'Table 1: '
accept table2 prompt 'Table 2: '

prompt
prompt columns having same name but difference in datatype or length:
prompt -------------------------------------------------------------------------

column data_precision fold_after

select
a.column_name, a.data_type, a.data_length, a.data_scale,a.data_precision,
b.column_name, b.data_type, b.data_length, b.data_scale,b.data_precision
from
all_tab_columns a, all_tab_columns b
where
a.table_name = upper(substr('&table1',instr('&table1','.')+1)) and
a.owner = upper(substr('&table1',1,instr('&table1','.')-1)) and
b.table_name = upper(substr('&table2',instr('&table2','.')+1)) and
b.owner = upper(substr('&table2',1,instr('&table2','.')-1)) and
a.column_name = b.column_name and
(
a.data_type <> b.data_type or
a.data_length <> b.data_length or
a.data_scale <> b.data_scale or
a.data_precision <> b.data_precision
);

prompt columns present in &table1 but not in &table2
prompt ----------------------------------------------

select
column_name
from
all_tab_columns
where
table_name = upper(substr('&table1',instr('&table1','.')+1))
and owner = upper(substr('&table1',1,instr('&table1','.')-1))
minus
select
column_name --, data_type, data_length, data_scale, data_precision
from
all_tab_columns
where
table_name = upper(substr('&table2',instr('&table2','.')+1))
and owner = upper(substr('&table2',1,instr('&table2','.')-1));

prompt columns present in &table2 but not in &table1
prompt ----------------------------------------------

select
column_name --, data_type, data_length, data_scale, data_precision
from
all_tab_columns
where
table_name = upper(substr('&table2',instr('&table2','.')+1))
and owner = upper(substr('&table2',1,instr('&table2','.')-1))
minus
select
column_name
from
all_tab_columns
where
table_name = upper(substr('&table1',instr('&table1','.')+1))
and owner = upper(substr('&table1',1,instr('&table1','.')-1));

--- SCRIPT END --

Example using the script

CREATE TABLE DEPT_1
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);

create table dept_2 as select deptno,dname from dept;

alter table dept_2 modify dname VARCHAR2(40);

@compare_tables.sql

Table 1: SIPRAS.DEPT_1
Table 2: SIPRAS.DEPT_2

columns having same name but difference in datatype or length:
------------------------------------------------------------------------

SIPRAS DNAME VARCHAR2 20
SIPRAS DNAME VARCHAR2 40

columns present in SIPRAS.DEPT_1 but not in SIPRAS.DEPT_2
-------------------------------------------------------------------------

LOC
columns present in SIPRAS.DEPT_2 but not in SIPRAS.DEPT_1
--------------------------------------------------------------------------

Performance tuning Seminar by Craig Shallahamer

I attended a 2 day seminar on "Reactive Performance Management" organized by Oracle University last week and the speaker was well-known Oracle guru Craig Shallahamer of OraPub.

Craig covered latching mechanism, SGA (Buffer and Shared Pool) management in detail. Though he did not get into in depth details but coverage was enough to refresh the memory.

Craig presented his methodology in tracking performance problem and identify potential tuning areas. He presented a model wherein he tries to identify bottlenecks at 3 different layers namely Oracle, Operating System and Application. He focused on using the wait events to diagnose and come up with solutions for each of the 3 layers. He emphasized the need to present the findings in such a manner that management (non-technical) people can understand and appreciate your work.

I had very high expectations from the seminar but unfortunately they were not met. At times, questions remained unanswered. However Craig was very honest in his responses and did not try to mislead. On the whole it was a refresher for me. All of us have an approach but the seminar enabled us to look at it more holistically. I specially liked his teaching method and honesty in his responses when he did not have an answer. Personally I felt his explanation on latching in a very easy to understand method was very good.