Data … as usual

All things about data by Laurent Leturgez

Category Archives: Internals

BBED in Oracle 11g

If you like playing with oracle internals, maybe you frequently use BBED (Block Editor). In Oracle 11g, BBED becomes unavailable but if you search in the ins_rdbms.mk makefile (located in $ORACLE_HOME/rdbms/lib directory), you will see that the entry is still there.

If you want to use it with an Oracle 11g database, you will have to get some files from an old Oracle 10g installation and build the binary in your Oracle 11g home.

The operation is detailed in the differents points above:

1- If you have installed Oracle 10g and 11g on the same server, define your two Oracle Home (otherwise you can use scp to copy files across the network)

export ORA10G_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORA11G_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

2- Copy the librairies shipped with Oracle 10g but unavailable in the Oracle 11g

cp $ORA10G_HOME/rdbms/lib/ssbbded.o $ORA11G_HOME/rdbms/lib
cp $ORA10G_HOME/rdbms/lib/sbbdpt.o $ORA11G_HOME/rdbms/lib

3- Copy the message files shipped with Oracle 10g but unavailable in Oracle 11g

cp $ORA10G_HOME/rdbms/mesg/bbed* $ORA11G_HOME/rdbms/mesg

4- Build the bbed executable

make -f $ORA11G_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORA11G_HOME/bin/bbed $ORA11G_HOME/bin/bbed

5- Have fun …

[oracle@linux1 ~]$ . oraenv
ORACLE_SID = [oracle] ? db112
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@linux1 ~]$ $ORACLE_HOME/bin/bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 9 10:10:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>

Password is : blockedit

Don’t forget to use the library in the same kernel architecture …

Advertisements

Control bug fixes activation in Oracle

When you upgrade oracle database version, you can be faced to a problem of CBO that changes its behaviour.

To do this, Oracle have introduced an undocumented parameter : “_fix_control” (Since 10.2.0.2). With this parameter, you can unable or disable a bug fixe to see, for example, if the CBO behaviour is controlled by a bug fix or not.

Off course, this kind of tests have to be implemented for testing purposes only !

To see what bug fix is implemented on your database, oracle have implemented two views V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL.

In these views, you will find information about the bug number, a short description, and the value of the parameter optimizer_features_enabled in which the bug fix is activated.

SQL> select bugno,value,description,optimizer_feature_enable,is_default from v$system_fix_control;

BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
---------- ---------- ---------------------------------------------------------------- ------------------------- ----------
3834770 1 Lift restriction on unnest subquery with a view 8.0.0 1
3746511 1 do not combine predicates from LNNVL 8.0.0 1
4519016 1 Pick view card from view qb instead of parent qb 9.2.0 1
3118776 1 Check for obj# for named view estimated card 9.2.0.8 1
4488689 1 ignore IS NOT NULL predicate as an index filter 10.2.0.2 1
2194204 0 disable push predicate driven by func. index into partition view 1
2660592 1 do not trigger bitmap plans if no protential domain index driver 8.1.7 1
2320291 1 push into table with RLS 9.2.0 1
2324795 1 add(remove) cluster index for push view 8.1.7 1
4308414 1 outer query must have more than one table unless lateral view 10.1.0.5 1
3499674 0 enable tiny index improvements: consider small indexes as cachhe 1
4569940 1 Use index heuristic for join pred being pushed 10.1.0.5 1
4631959 1 Refine criteria for additional phase in JPPD 10.2.0.2 1
4519340 1 Generate distinct view in SU if candidate for JPPD 10.2.0.2 1
4550003 1 do not consider no sel predicates in join selectivity sanity 10.1.0 1

.../...

In this example, we can see that the bug fix #4488689 is activated in our database (because of the value parameter set to 1).

We can see too that some og the buf fixes are not activated by default (ex: 2194204)

You can have a session view by querying the V$SESSION_FIX_CONTROL.

Now, if you want to test to desactivate a bug fix, you can do it by using the _fix_control undocumented parameter.

To disable a bug fix, you have to specify the bug number completed by ON or OFF.

For example:

-- To deactivate a single bug fix:
SQL> alter session set "_fix_control"='4488689:OFF';
-- To deactivate many bug fixes:
SQL> alter session set "_fix_control"='4488689:OFF','4631959:OFF','4519340:OFF';
-- To activate it, you have to use ON instead of OFF
SQL> alter session set "_fix_control"='4488689:ON';

If we control the V$SESSION_FIX_CONTROL view on these specific bug fixes, we can view that they are desactivated (value column equals 0).

SQL> select SESSION_ID,BUGNO,VALUE,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT
 2 from V$SESSION_FIX_CONTROL
 3 where session_id=sys_context('USERENV','SID') and bugno in (4488689,4631959,4519340);
 SESSION_ID BUGNO VALUE OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
 ---------- ---------- ---------- ------------------------- ----------
 22 4488689 0 10.2.0.2 0
 22 4631959 0 10.2.0.2 0
 22 4519340 0 10.2.0.2 0

 

Disassembling V$ views

If you are instesting by Oracle Internals, maybe you have already tried to use the DBMS_METADATA Package to get the definition code of an Oracle static view :

SQL> set long 5000
SQL> set pages 5000
SQL> select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_DATA_FILES')
---------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "F
ILE_ID", "TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO
", "AUTOEXTENSIBLE", "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BY
TES", "USER_BLOCKS", "ONLINE_STATUS") AS
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
.../...


But if you try to get the definition code of the “V$” dynamic views, you will obtain a strange output which mentioned a call to V$DATAFILE (at the end)

SQL> select dbms_metadata.get_ddl('VIEW','V_$DATAFILE') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_$DATAFILE')
----------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION
_CHANGE#", "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHE
CKPOINT_CHANGE#", "CHECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOV
ERABLE_TIME", "LAST_CHANGE#", "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_
CHANGE#", "ONLINE_TIME", "BYTES", "BLOCKS", "CREATE_BYTES", "BLOCK_SIZ
E", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NAME", "FIRST_NONLOGGE
D_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CREATION_CHAN
GE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "
PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS
select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","ST
ATUS","ENABLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_
CHANGE#","UNRECOVERABLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHAN
GE#","ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS","CREATE_BYTES","B
LOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_OFFSET","AUX_NAME","FIRST_NONLO
GGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","FOREIGN_CREATION_CHAN
GE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHANGE#","PLUG
IN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile

In fact, dynamic performance view definition are stored in the V$FIXED_VIEW_DEFINITION.
As in 10g and 11g, the V$ views are results of GV$ view, if you want to get the definition view of the V$DATAFILE, a better way is to query this view with the GV$DATAFILE definition.

SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='V$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# , STAT
US , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME, UNRECOVERABLE_CHA
NGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME , OFFLINE_CHANGE# ,
ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS , CREATE_BYTES , BLOCK_
SIZE , NAME, PLUGGED_IN, BLOCK1_OFFSET , AUX_NAME , FIRST_NONLOGGED_SC
N, FIRST_NONLOGGED_TIME, FOREIGN_DBID, FOREIGN_CREATION_CHANGE#, FOREI
GN_CREATION_TIME, PLUGGED_READONLY, PLUGIN_CHANGE#, PLUGIN_RESETLOGS_C
HANGE#, PLUGIN_RESETLOGS_TIME from GV$DATAFILE where inst_id = USERENV
('Instance')
SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='GV$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_t
im,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fetsn,fe.ferfn,
decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), d
ecode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')), decode(f
e.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED'
,4,'READ ONLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_da
te(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number
(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Grego
rian'), to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL), to_d
ate(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_numb
er(fe.feofs),to_number(fe.feonc_scn), to_date(fe.feonc_tim,'MM/DD/RR H
H24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fe
csz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb, fn.fnbof, decode(fe.fepax,
0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam), to_number(fh.fhfirstunr
ecscn), to_date(fh.fhfirstunrectime,'
MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fepdi, fe.fefcrs, f
e.fefcrt, decode(fe.fefdb, 1, 'YES', 'NO'), fe.feplus, fe.feprls, fe.
feprlt from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where
((fe.fe
pax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or
((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno
and fnaux.fntyp=4 and fnaux.fnnam is not null and
bitand(fnaux.fnflg, 4) != 4
and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil an
d fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.f
nnam is not null and bitand(fn.fnflg, 4) != 4 order by fe.fenum

Now you know that GV$DATAFILE view is using x$kccfe, x$kccfn, x$kcvfh fixed tables.

Event for crashing an oracle session

Thanks to Grimm for this information relayed by Laurent Schneider.

This is a tricky method for crashing a session without disconnecting the user :

Session 1 :

SQL> begin
2 while 2>1 loop
3 null;
4 end loop;
5 end;
6 /

Session 2 (logged as user SYS or as user who has privilege to execute the packageDBMS_SYSTEM.SET_EV) :

SQL> exec dbms_system.set_ev(sid,serial#,10237,4,'');
PL/SQL procedure successfully completed.

Session 1 :

ERROR at line 1:
ORA-01013: user requested cancel of current operation

The session is not killed, user is still connected –

If you want to perform other queries, administrator have to invalidate this event (exec dbms_system.set_ev(sid,serial#,10237,0,”);) otherwise you will encountered some “ORA-00604: error occurred at recursive SQL level 1”

SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit