My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • May 2011
    M T W T F S S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Blogs I Follow

Archive for May 4th, 2011

Change In “Wrap” methods Of oracle Versions

Posted by Sriram Sanka on May 4, 2011


As we all know ,

We can wrap the source code using wrapper utility.In the same way Oracle has wrapped some objects in Oracle Database By default.

But  there is some change in its wrapping methods.

For Example..
In Oracle 10g

CREATE OR REPLACE FUNCTION “SYSMAN”.”DECRYPT” wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9000000
1
4
0
15
2 :e:
1FUNCTION:
1DECRYPT:
1CIPHER_TEXT:
1VARCHAR2:
1RETURN:
1RAW_TEXT:
1RAW:
132767:
1DBMS_CRYPTO:
1SRC:
1HEXTORAW:
1TYP:
1ENCRYPT_3DES:
1+:
1CHAIN_CBC:
1PAD_PKCS5:
1KEY:
1GETEMKEY:
1UTL_I18N:
1RAW_TO_CHAR:
1AL32UTF8:
0

0
0
48
2
0 a0 8d 8f a0 b0 3d b4
:2 a0 2c 6a a3 a0 51 a5 1c
81 b0 :3 a0 6b :3 a0 a5 b e
:3 a0 6b 7e :2 a0 6b b4 2e 7e
:2 a0 6b b4 2e e :2 a0 b4 2e
e a5 b d :3 a0 6b a0 6e
a5 b 65 b7 a4 b1 11 68
4f 1d 17 b5
48
2
0 3 7 23 1f 1e 2b 1b
30 34 38 3c 59 44 48 4b
4c 54 43 60 64 68 40 6c
70 74 78 79 7b 7d 81 85
89 8c 8f 93 97 9a 9b a0
a3 a7 ab ae af b4 b6 ba
be bf c4 c6 c7 c9 cd d1
d5 d9 dc e0 e5 e6 e8 ec
ee f2 f4 100 104 106 107 110
48
2
0 1 a 12 21 :2 12 11 b
12 :2 1 7 10 14 13 :2 10 7
5 11 :2 1d 16 1b 24 :2 1b :2 16
1b :2 27 33 34 :2 40 :2 1b 49 4a
:2 56 :2 1b :2 16 :3 1b 16 :2 11 :2 5 c
:2 15 21 2b :2 c 5 :9 1
48
4
0 :7 1 :2 2 :2 1
:7 3 :4 5 :6 6 :11 7
:5 8 :3 5 :9 a :2 4
:7 1
112
4
:3 0 1 :3 0 2
:a 0 43 1 :7 0
5 :2 0 3 4
:3 0 3 :7 0 5
4 :3 0 5 :3 0
4 :3 0 7 9
0 43 2 a
:2 0 14 15 0
9 7 :3 0 8
:2 0 7 d f
:6 0 12 10 0
41 0 6 :6 0
6 :3 0 9 :3 0
2 :3 0 a :3 0
b :3 0 3 :3 0
b 18 1a 17
1b c :3 0 9
:3 0 d :3 0 1e
1f 0 e :2 0
9 :3 0 f :3 0
22 23 0 d
21 25 :3 0 e
:2 0 9 :3 0 10
:3 0 28 29 0
10 27 2b :3 0
1d 2c 11 :3 0
12 :4 0 2f 30
:3 0 2e 31 13
16 33 13 34
0 3f 5 :3 0
13 :3 0 14 :3 0
37 38 0 6
:3 0 15 :4 0 17
39 3c 3d :2 0
3f 1a 42 :3 0
42 1d 42 41
3f 40 :6 0 43
:2 0 2 a 42
46 :3 0 45 43
47 :8 0
1f
4
:3 0 1 3 1
6 1 e 1
c 1 19 2
20 24 2 26
2a 3 1c 2d
32 2 3a 3b
2 35 3e 1
11
1
4
0
46
0
1
14
1
3
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0
3 1 0
c 1 0
2 0 1
0

where as in Oracle 11.2.0 the same function wrapped as

CREATE OR REPLACE FUNCTION “SYSMAN”.”DECRYPT” wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
1d9 16d
b7I22qBmrA5hngZ8PfHmiTb8O+UwgzJpNdxqfC9dbuqKO2FNZvfno/Ns8QsgqwomCzHLyLco
3aSo0eQcxmPI6Euw98YDgtrXDlO37aycscH+csQoFWNIszAHrssarFvJMNlZYLbOiNZIfJ2O
fPguqzMwjjlnm37HxGvpCYbc5c4137hhVayrRctxbcWTikHjZiJR0zVUFPpYrxC9n6AmTSg8
x4InsbDh78p8DHv99jCTDTUFru7JrX2SIDf9MpNiLmnpj8c3MyOhQN8amzSLH1/JFR4AxMS1
MM1SVgqke3o2ohYAkBJWC4PfU//ZEzvyC0TtT+xJsHs8S+BMQbLiMeKs38MrcRD7gl5SzA==

seems its more difficult than 10g.
for Examples see these lines from 10g code.

1FUNCTION:
1DECRYPT:
1CIPHER_TEXT:
1VARCHAR2:
1RETURN:
1RAW_TEXT:
1RAW:
132767:
1DBMS_CRYPTO:
1SRC:
1HEXTORAW:
1TYP:
1ENCRYPT_3DES:
1+:
1CHAIN_CBC:
1PAD_PKCS5:
1KEY:
1GETEMKEY:
1UTL_I18N:
1RAW_TO_CHAR:
1AL32UTF8:

which can give us some clues to re write it as below

create or replace FUNCTION DECRYPT(CIPHER_TEXT IN VARCHAR2)
RETURN VARCHAR2 AS
RAW_TEXT RAW(32767);
BEGIN
IF CIPHER_TEXT IS NULL THEN
RETURN NULL;
END IF;

RAW_TEXT := SYS.DBMS_CRYPTO.DECRYPT(

SRC=>HEXTORAW(CIPHER_TEXT),
TYP=>SYS.DBMS_CRYPTO.ENCRYPT_3DES+SYS.DBMS_CRYPTO.CHAIN_CBC+SYS.DBMS_CRYPTO.PAD_PKCS5,
KEY=>GETEMKEY());

RETURN SYS.UTL_I18N.RAW_TO_CHAR(RAW_TEXT, ‘AL32UTF8‘);
END;

Similarly some other methods.

Hope you Enjoyed it. 😉

Posted in Functions In oracle | 3 Comments »

All about the DUAL

Posted by Sriram Sanka on May 4, 2011


The Magic table DUAL is owned by User SYS as its an dictionary object.

ind> select object_name,owner,object_type
2 from dba_objects
3 where object_name like ‘%DUAL%’;

OBJECT_NAME OWNER OBJECT_TYPE
———— ———————- ——————-
DUAL SYS TABLE
DUAL PUBLIC SYNONYM

2 rows selected.

‘Dual’ is a special table with 1X1 (1 row and 1 column) owned by sys which can be used to know the result I can say :).

ind> desc dual;
Name Null? Type
—————————————– ——– ————-
DUMMY VARCHAR2(1)

ind> desc x$dual
Name Null? Type
—————————————– ——– ———————-
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(1)

In specific cases like whenever Database is not fully up(recovery process),at that situation oracle reads the table dual from a fixed table/view

ind> select * from dual;

D

X

1 row selected.

ind> select * from x$dual;

ADDR INDX INST_ID D
——– ———- ———- –
0366CD54 0 1 X

1 row selected.

Never ever try to play with dual/dictionary objects which will ruin you DB.

Posted in Oracle Server Administration | Leave a Comment »

Oracle Fixed(/Dynamic) views

Posted by Sriram Sanka on May 4, 2011


“Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables.”

Why the term “Fixed”?

Because They are fixed ..One cannot perform any DML , DDL or any operation other than “SELECT“,thats why these views/Tables are known as Fixed.

ind> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

5 rows selected.

ind> alter table x$ksmsp add sriram varchar2(30);
alter table x$ksmsp add sriram varchar2(30)
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views.

Oracle maintains some dynamic performance views which are dynamically created based on the “X$” tables.

Those are basically start with ‘V$’ and ‘GV$’ with one extra column “Instance_id”.

ind> desc v$fixed_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 TYPE                                               VARCHAR2(5)
 TABLE_NUM                                          NUMBER

ind> desc v$fixed_view_definition
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                          VARCHAR2(30)
 VIEW_DEFINITION                                    VARCHAR2(4000)

We can query these two views …to get more Info on these views

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$SESSION'
  3  ;

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$SESSION                      select  SADDR , SID , SERIAL# , AUDSID , PADDR , U
                               SER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKW
                               AIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUS
                               ER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE
                                , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID, SQL_CHILD
                               _NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_S
                               QL_ID, PREV_CHILD_NUMBER , MODULE , MODULE_HASH ,
                               ACTION , ACTION_HASH , CLIENT_INFO , FIXED_TABLE_S
                               EQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAI
                               T_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_
                               ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHO
                               D , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STA
                               TUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATIO
                               N, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLO
                               CKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT
                               ,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,
                               WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, S
                               ECONDS_IN_WAIT,STATE,SERVICE_NAME, SQL_TRACE, SQL_
                               TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where
                                inst_id = USERENV('Instance')

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='GV$SESSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$SESSION                     select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuuds
                               es,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.k
                               susesow, decode(s.ksusetrn,hextoraw('00'),null,s.k
                               susetrn),decode(s.ksqpswat,hextoraw('00'),null,s.k
                               sqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0
                               ,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACH
                               ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.kss
                               patyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'),
                                 s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ks
                               usemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksus
                               eflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?
                               '), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.k
                               susesch, 65535, to_number(null), s.ksusesch),  s.k
                               susepsq, s.ksusepha, s.ksusepsi,  decode(s.ksusepc
                               h, 65535, to_number(null), s.ksusepch),  s.ksuseap
                               p, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli,
                                s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s
                               .ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.
                               ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,
                               'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),de
                               code(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARS
                               E','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksu
                               segrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',de
                               code(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')
                               ),decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(
                               bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),de
                               code(bitand(s.ksusepxopt,32),32,'FORCED',decode(bi
                               tand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),
                               s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,429
                               4967295,'UNKNOWN',  4294967294, 'UNKNOWN',42949672
                               93,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'
                               NOT IN WAIT','VALID'),decode(s.ksuseblocker, 42949
                               67295,to_number(null),4294967294,to_number(null),
                               4294967293,to_number(null), 4294967292,to_number(n
                               ull),4294967291,  to_number(null),bitand(s.ksusebl
                               ocker, 2147418112)/65536),decode(s.ksuseblocker, 4
                               294967295,to_number(null),4294967294,to_number(nul
                               l), 4294967293,to_number(null), 4294967292,to_numb
                               er(null),4294967291,  to_number(null),bitand(s.ksu
                               seblocker, 65535)),s.ksuseseq, s.ksuseopc,e.ksledn
                               am, e.ksledp1, s.ksusep1,s.ksusep1r,e.ksledp2, s.k
                               susep2,s.ksusep2r,e.ksledp3,s.ksusep3,s.ksusep3r,e
                               .ksledclassid,  e.ksledclass#, e.ksledclass, decod
                               e(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksuse
                               tim/10000),0,-1,round(s.ksusetim/10000))), s.ksuse
                               wtm,decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED U
                               NKNOWN TIME',  -1, 'WAITED SHORT TIME',   decode(r
                               ound(s.ksusetim/10000),0,'WAITED SHORT TIME','WAIT
                               ED KNOWN TIME')),s.ksusesvc, decode(bitand(s.ksuse
                               flg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.
                               ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.k
                               suseflg2,128),128,'TRUE','FALSE')from x$ksuse s, x
                               $ksled e where bitand(s.ksspaflg,1)!=0 and bitand(
                               s.ksuseflg,1)!=0 and s.ksuseopc=e.indx

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$FIXED_VIEW_DEFINITION        select  VIEW_NAME , VIEW_DEFINITION from GV$FIXED_
                               VIEW_DEFINITION where inst_id = USERENV('Instance'
                               )

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2   where view_name='GV$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$FIXED_VIEW_DEFINITION       select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i,
                                x$kqfvt t where i.indx = t.indx



Conclusion:
The regular Dynamic views('V$') are created  based on 'Gv$' and these 'Gv$' views are based on 'X$' views
These are permanent tables/views.The X$ tables are generated when ever you instance started.
These tables are accessible to the sys User only.
These 'X$' are not even modifiable By the Super User 'SYS' 
Those are "FIXED"
Never ever try to expertise on these view on your production environment 
which will ruin you DB  complete. ;)

Posted in Oracle Server Administration | 16 Comments »

 
Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Know-Oracle

Oracle Cloud ERP Blog by Team CLT

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

%d bloggers like this: