Oracle Internals

Session Tracing alter session set tracefile_identifier='10046'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever,level 12'; -- Execute the queries or operations to be traced here -- select * from dual; exit; alter session set events '10046 trace name context off'; Tracing a process after it has started The first step is to identify the session to be traced by some means: select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID / SPID is the operating system Process identifier (os pid) PID is the Oracle Process identifier (ora pid) Once the OS process id for the process has been determined then the trace can be initialised as follows: connect / as sysdba oradebug setospid 9834 oradebug unlimit oradebug event 10046 trace name context forever,level 12 oradebug event 10046 trac

Symptoms When accessing a XMLType column(s) or XMLType table in a remote database via database link, an ORA-03001 is generated: ERROR at line 9: ORA-03001: unimplemented feature ORA-02063: preceding line from SOMEDBLINK XMLTYPE over DBLinks are not supported.  This is properly documented in Appendix B of the XDB Developer's Guide: Solution XMLTYPE over DBLinks are not supported, however, in earlier releases, it was not completely blocked by issuing an error like ORA-03001 for certain cases.  For those specific cases that should have been blocked in the old releases, patch.31385623 was created. Therefore, if available, download and apply patch.31385623 and see if it helps in your situation. No other patch will be created to circumvent the restriction on accessing XMLType over dblinks.

 Due to DST  changes some days back we see couple of OEM Agents failed to start with below mentioned errors: oracle@machine[TEST01]> ./emctl start agent Starting agent ......... failed. The agentTZRegion value in /orcl/app/oracle/agent11g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent. If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(<agent_name>, <new_tz_rgn>) to get the value propagated to repository. Consult the log files in: /orcl/app/oracle/agent11g/sysman/log OS level time as changed but it's not changed in Oracle Agent binaries. We have manaully fix it using below menthod: 1. Set the Correct Env variable to TZ parameter oracle@machine[TEST01]> echo $TZ Europe/Moscow export TZ=Etc/GMT-4

On one occasion support teams reached out to us with an strange error ORA-06502: "Numeric Or Value Error: Character String Buffer Too Small" from SQL Using DBMS_LOB.SUBSTR.  They mentioned to get this error only when data is more than 4000 bytes and lesser values it works fine. MOS already have this problem listed under Doc ID 567574.1. Reason of this error: The DBMS_LOB.SUBSTR returns a VARCHAR2 and VARCHAR2 cannot be > 4000 bytes. The data being returned is from a UTF8 database and the Column BIGCOL contains multibyte characters which result in the converted size exceeding 4000 bytes. The following can be used as a work around:     create or replace function my_clob_substr( c in clob) return varchar2     as     v_characters number := 4001;     v_length number := 4001;     begin     while v_length > 4000 loop     v_characters := v_characters-1;     v_length := lengthb(dbms_lob.substr(c,v_characters,1));     end loop;     return dbms_lob.substr(c,v_characters,1);     en

Lately, there comes a need where needed to configure a sysemctl startup script for stop and start of services on Linux servers. Usually, oracle services come with high availability and cluster-based services which provides this similar capability but in cases where we don't have this capability, we as an administrator may need to understand how to perform such operations. Now systemctl services can be created easily but if one wants to package that as an rpm and deploy on the various services we can use the following steps.  RPM stands for Red Hat Package Manager. It was developed by Red Hat and is primarily used on Red Hat-based Linux operating systems. These packages come up with a .rpm extension and use the following format:                     <name>-<version>-<release>.<arch>.rpm Each of these components is critical and will be tied up together as we create the package in the coming section of this blog. To build an RPM we first need to create the follo

 Although i don't have too much experience working with outlines, but these days i am working significantly with SQL Profile. My Favorite blogger Kerry Osborne has written a wonderful blog on this topic which gives me very clear idea of sql profile. Outlines basically locks the profile , but sql profile plays with empirical value of the sql plan which normally known as fudge factor. It's give the flexibility to optimizer to look for alternate plan. But we can say sql profile advantages will fade with time. Hints used in sql profile, these hints are very likely documents so wanted to give as much information as possible: 1. Setting Optimizer Mode : ALL_ROWS 2. Disable hints present in sql : IGNORE_OPTIM_EMBEDDED_HINTS 3.Setting OPTIMIZER_FEATURES_ENABLE to it's default Value i.e active all available feature :OPTIMIZER_FEATURES_ENABLE (default) 4.Adjusting number of rows returned from the table: OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_RO