Trace The Value In A Table

May 9, 2013

i have 3 tables

T1
node_id

T2
node_id, object_type

T3
node_id, object_type

my requirement is T1 node_id exists in which table(T2 or T3). If T1 node_id exists in T2 then return T2 object_type else return T3 object_type

sample data
==========
create table T1
(
node_id number(4)
);

create table T2
(
node_id number(4),
object_type varchar2(15)
);

create table T3
(
node_id number(4),
object_type varchar2(15)
);

begin
insert into T1 values(10);
insert into T1 values(20);
insert into T1 values(30);
insert into T1 values(40);
insert into T1 values(50);
insert into T1 values(60);
insert into T1 values(70);
insert into T1 values(80);

insert into T2 values(10, 'Concept');
insert into T2 values(20, 'Concept');
insert into T2 values(110, 'Concept');
insert into T2 values(118, 'Concept');
insert into T2 values(136, 'Concept');
insert into T2 values(128, 'Concept');
insert into T2 values(80, 'Concept');

insert into T3 values(10, 'Grouping');
insert into T3 values(120,'Grouping');
insert into T3 values(130,'Grouping');
insert into T3 values(140,'Grouping');
insert into T3 values(150,'Grouping');
insert into T3 values(160,'Grouping');
insert into T3 values(70,'Grouping');
insert into T3 values(180,'Grouping');
insert into T3 values(260,'Grouping');
insert into T3 values(270,'Grouping');
insert into T3 values(280,'Grouping');
end;
/

View 2 Replies


ADVERTISEMENT

Performance Tuning :: Script To Load Trace Files Errors To A Table?

Jul 13, 2013

I need to create a script which can fetch the error related details from Trace files regularly (may be twice a day ) to a custom table in DB.

View 3 Replies View Related

Sql Trace Session

Aug 28, 2012

i read some articals of how to trace oracle sessions and i have some question

i use database 10g

trace vs event ??? different between them ????

how to trace individual statement i hope to give me this point in ex: due to i can't get the sql_id ,how to know the sql_is of specify statement??

View 6 Replies View Related

Getting Error In Trace File

Jul 2, 2013

I am getting below mentioned error in alertlog file very frequently.

ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 747
ORA-06512: at "BEE_CODE_05252013.SS_INDEX_JOB_PKG", line 496
Errors in file /beprddb/diag/rdbms/beprddb/beprddb/trace/beprddb_ora_10581.trc:
Errors in file /beprddb/diag/rdbms/beprddb/beprddb/trace/beprddb_ora_10581.trc:
Errors in file /beprddb/diag/rdbms/beprddb/beprddb/trace/beprddb_ora_10581.trc:
Tue Jul 02 15:31:15 2013
[code]....

View 1 Replies View Related

PL/SQL :: Interpreting Trace File

Jul 24, 2012

i am using 10.2.0.4.0 version of oracle.I am having trace file info as below, for one of the query. So how should i interpret the trace file? What is the issue in the query, and the scope of improvement in the query? I have removed the query and its plans from the trace file, i have only posted the wait sections.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.14       0.13          0          0          1           0
Execute      1      6.63     162.12      33540      72921        383           0
Fetch    17272    178.89    1933.95     274835    3147603         20      259063
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    17274    185.66    2096.21     308375    3220524        404      259063
[code]....

View 18 Replies View Related

SQL & PL/SQL :: Procedure Called To Trace

Jul 12, 2012

I have a procedure in the live environment , which operate in 2 mode.

1 regular load at 10 min
2 nightly

The code is same except they set the last loaded time for both in 2 dfferent rows, so that next time run will get incremental data.

I can trace this procedure on mode 1 that is is scheduled but unable to find how the 2nd one is getting trigger every night. there is no jobs corresponding to that. Is there any way i can find which method is trigger the procedure during night.

View 5 Replies View Related

Opening Permissions To Trace Files?

Nov 15, 2010

un-documented parameter _trace_files_public / I want to set this to true so my app team can review trace files. better way to proceed to open read permissions for non oracle users.

View 2 Replies View Related

Server Utilities :: Trace DDL SQL That Comes From Application

Oct 14, 2012

I wanted to trace DDLs that comes from application and did this .

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 1727, serial_num => 14319,waits => FALSE);
<waited till session completed all operation(ie,drop indexes)>
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 1727, serial_num => 14319);

Now i looked at tkprof output, I dont see any single DDL . Is this not the way to trace DDLs.

View 3 Replies View Related

Performance Tuning :: New Trace File

Aug 11, 2011

I need to take the database trace of each page of my web application. I am giving the following commands.

BEGIN
dbms_monitor.session_trace_enable(session_id=>122, serial_num=>NULL, waits=>true, binds=>true);
END;

Accessing the web application. Once it renders completely, I am executing the following command.

BEGIN
dbms_monitor.session_trace_disable(session_id=>122);
END;

In the user_dump_dest folder, I am expecting to see a new trace whenever I execute these commands. But the same trace file is getting updated. How do I make oracle to create a new trace for each iteration. I am using Oracle 11g Release on CentOS 5.x

View 3 Replies View Related

Trace File Errors Interpretation

Apr 18, 2013

We are running Oracle 11.2.0.3 on a Windows 2008 R2 Server with an app server on the same. Our app has a search function that has recently started timing out on us. Sometimes intermittently but often now. I've ran a trace in our dev environment, below code, in an attempt to track down the issue but I'm finding very little useful information in relation to the errors I see in the trace file below. My experience in deciphering trace code is nill so how to interpret these errors.

*** 2013-04-18 14:27:23.353
*** SESSION ID:(3093.63097) 2013-04-18 14:27:23.353
*** CLIENT ID:() 2013-04-18 14:27:23.353
*** SERVICE NAME:(SYS$USERS) 2013-04-18 14:27:23.353
*** MODULE NAME:(w3pw.exe) 2013-04-18 14:27:23.353
*** ACTION NAME:() 2013-04-18 14:27:23.353
[code]....

View 3 Replies View Related

Trace All The Session Generated From Schema?

Jan 25, 2013

Customer is running a Job from a Schema which is generating multiple sessions . I want the all the sessions to be traced from that particular Schema .

View 4 Replies View Related

Generate Trace File With Required Name

Jul 26, 2013

As a sys user i want to trace a user session. so i am using 

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE); 

to trace. but this trace file is mixing with another trace files in udump. did some Google and found that we can use

ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION".

but this will generate trace file for sys user account not for the desired user session. how we can define trace file name for desired user session trace. 

View 2 Replies View Related

RAC 10g Error Found In Trace File?

Nov 19, 2011

after apply patch 10.2.0.4 for 2 10gr2 node i tried to start asm on 2 node but i found that the 1st node (master node)
can not started but the second node is ok and this error found in trace file for lmon.trc

kjxgmpoll: terminate the CGS reconfig.
Error: KGXGN polling error (15)
error 29702 detected in background process
ORA-29702: error occurred in Cluster Group Service operation
ksuitm: waiting up to [5] seconds before killing DIAG

View 3 Replies View Related

SQL & PL/SQL :: Trace Time To Refresh MV For Last Few Runs?

Mar 10, 2010

I want to trace the time it takes to refresh the MV for the last few runs. How can i find that? The MV runs every 7 hours..for the last few times it runs, I would like to trace the time it took to complete.

View 1 Replies View Related

Fetch Phase In A Trace File?

Mar 11, 2013

Oracle version: 11.2.0.3.0 Enterprise Edition
OS - IBM/AIX RISC System/6000

I am trying to generate a trace file from a piece of code executed by java server. What I asked the java developer to do is to place this block immediately after establishing a connection:

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''M1''';
  dbms_monitor.session_trace_enable(waits => FALSE, binds => TRUE);
END;And at the end of the logical java block of code:

BEGIN
  dbms_monitor.session_trace_disable;
END;

What I want to know is how many rows the java server fetches after executing one particular select statement, because they complain about receiving less in count rows from the select statement than expecting. For example, if I execute the same sql query in sqlplus session, then I fetch let's say 1000 rows. When the same query is executed from java side, the fetched rows are less in count, let's say 500.And because I doubt it, I wanted to trace to see what actually is executed and how.the excerpt of the trace file I see exactly the same query which I execute myself in a sqplus session.is no fine-grained control on the udnerlying tables in the query.

And my question is, how to interpret the FETCH phase of the cursor (for the select statement)?example, if I see one FETCH for this cursor, does this mean that the java server has fetched only one row?If I see 100 FETCHes, does this mean they fetched 100 rows from the cursor?

Here is a short excerpt from the trace file (don't crucify me for the query and the obvious denormalized design of the tables, this is not invented by me):

PARSING IN CURSOR #4573587152 len=667 dep=0 uid=737 oct=3 lid=737 tim=17685516462413 hv=954980718 ad='70000006d3e4940' sqlid='69pm96nwfrqbf'
select /* ordered */ o.id, nvl(o.par_id, -1) as par_id, o.NAME_GER, o.NAME_ENG, o.NAME_ESP, o.NAME_ITL,o.NAME_FRA, decode(lo.lflag, 'Y', 'L', 'N') as leaf_or_node, lo.distance + 1 as "LEVEL",  to_char(o.beg_date,

[code]...

View 5 Replies View Related

Swap Information Shows Zero In Trace File

Nov 8, 2010

I got the below message in trace file. What does the line "swap info: free = 0.00M alloc = 0.00M total = 0.00M" trying to say?

I have

RAM=1.5G
Swap=3.5G

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle
System name:Linux
Node name:linuxdev
Release:2.6.5-7.97-default
Version:#1 Fri Jul 2 14:21:59 UTC 2004

[Code]....

View 1 Replies View Related

How To Trace Refresh Time Of Materialized View

Jun 4, 2011

I have created a fast refresh materialized view.now i wanted to know my materialized view how long processing for refresh.which table stores this information with syntax.

View 2 Replies View Related

Invisible Index Getting Accessed In Trace File 11g

Jun 16, 2011

I am working on tuning the performance of one of the concurrent request in our 11i ERP System having database 11.1.0.7

I had enabled oradebug trace for the request and generated tkprof out of it. For below query which is taking time , I found that , in the trace generated , wait event is "db file sequential read" on an PO_LINES_N10 index but in the generated tkprof , for the same below query , the full table scan for PO_LINES_ALL is happening , as that table is 600 MB in size.

Below is the query ,
===============
UPDATE PO_LINES_ALL A
SET A.VENDOR_PRODUCT_NUM = (SELECT SUPPLIER_ITEM FROM APPS.IRPO_IN_BPAUPDATE_TMP C WHERE BATCH_ID = :B1 AND PROCESSED_FLAG = 'P' AND ACTION = 'UPDATE' AND C.LINE_ID =A.PO_LINE_ID AND ROWNUM = 1 AND SUPPLIER_ITEM IS NOT NULL),
LAST_UPDATE_DATE = SYSDATE
===============

Index PO_LINES_N10 is on the column LAST_UPDATE_DATE , logically for such query , index should not have got used as that indexed column is not in select / where clause.

Also, why there is discrepancy between tkprof and trace generated for the same query .

So , I decided to INVISIBLE the index PO_LINES_N10 but still that index is getting accessed in the trace file .

I have also checked the below parameter , which is false so optimizer should not make use of invisible indexes during query execution.

SQL> show parameter invisible

NAME TYPE VALUE
----------- optimizer_use_invisible_indexes boolean FALSE

View 5 Replies View Related

SQL & PL/SQL :: Trace Privileges Granted And Revoked History?

Nov 20, 2012

I have written a windows service which grants and revokes based on request. How to trace in oracle data dictionary if those privilages were granted and at what time to whom and by whom. I mean is there any table which can be queried for past privilages granted to users.

View 2 Replies View Related

Performance Tuning :: Regarding Trace File And TKprof?

Apr 10, 2013

i want to know about trace file and TKPROF. any example.

View 1 Replies View Related

SQL & PL/SQL :: Find And Trace Custom View / Procedure?

May 24, 2013

I know that this query had been executed again the db, but the person is no longer here. Last run was about 3~4 months ago.

SELECT Subject_ID,
TO_CHAR (completed_date, 'mm/dd/yyyy'), status
FROM ADMINDBG_USER.adbt_master
...

[ADMINDBG_USER.adbt_master] is not a part of [dba_tables]. [ADMINDBG_USER.adbt_master] must be a view/procedure/a sort.
I have no luck to find what view/procedure/syntax is populating the data onto [ADMINDBG_USER.adbt_master].

View 7 Replies View Related

SQL & PL/SQL :: Trace Sessions From Specific User And Machine?

Sep 10, 2012

I need to trace all sessions created from specific user and specific machine . Got some code from internet and customised .

CREATE OR REPLACE TRIGGER SYS.enable_trace_trigger
AFTER LOGON ON DATABASE
DECLARE

[Code]....

I have yet not created it on db .

View 3 Replies View Related

Networking And Gateways :: TRACE LEVEL SERVER Off?

Apr 6, 2012

I am having 11.2.0.1.0 database with windows2k3 OS. For ORA-609 error i have started server side tracing by adding TRACE_LEVEL_SERVER=16,TRACE_LEVEL_DIRECOTRY=D:TRACE,and DIAG_ADR_ENABLED=off in sqlnet.ora file and i reload the listener.

Now i want to stop tracing so i have tried TRACE_LEVEL_SERVER=off but it wouldnot work. After that i remove TRACE_LEVEL_SERVER=16,TRACE_LEVEL_DIRECOTRY=D:TRACE from sqlnet.ora file and reload the listener but trace file generation is going on D:TRACE. stop server side tracing.

View 2 Replies View Related

Security :: Trace Or Audit User Activity

Dec 9, 2011

i want to trace user activities, I want to generate the file with SQL statement generated by particular user. I tried to look in to sys.AUD$ but all i get is logging logout and locations but no SQL Text.

View 7 Replies View Related

Trace Particular Object (procedure / Package) On Database?

Dec 13, 2012

How do I trace an particular object (procedure/package) on the database. my database version is Oracle 11g(11.2.0.2)

View 2 Replies View Related

Trace Particular One User SQL Activity In Oracle 10G Database?

Jan 27, 2013

I want to trace particular one user SQL activity in Oracle 10G database.

Note - Not only one session / Not for all Database activity

View 7 Replies View Related

Turning Off Trace On Specific Deadlocks Events?

Jun 17, 2013

Can I disable tracing just on deadlocks events on 11gR2?One of our applications provoke several deadlocks. I've warned the developers team several times, but it's taking time to solve it. However, these deadlocks generate trace files of several sizes (from 300M to 3G ..)The Deadlock Graph on them are very useful but the PROCESS STATE section are too long, and until the developers team fix the problem they are just filling up my user dump dest.  Can I disable them or disable the PROCESS STATE section from them?

View 2 Replies View Related

Row Lock Contention - Trace File Shared

Nov 19, 2012

I am having enq: TX - row lock contention in top wait event. it is occurring between 10pm - 2am.

We are having sqlloader job running every one hour(conventional path). But for the specific period of time i am getting "Global Enqueue Services Deadlock detected". Between 10-5. I analyzed related trace file it is make me little confusion.I found there are four insert query culprit for this locking. out of four sql , tow of them are ran by same SID, other two insert ran by same id. I got confused because how same sid locking them self. trace file below. during this period oracle maintenance window is active.

Trace file:

*** 2012-10-09 03:40:31.135
user session for deadlock lock 0x15365e060
sid: 1104 ser: 22256 audsid: 8797820 user: 49/iurth flags: 0x45
pid: 71 O/S info: user: oracle, term: UNKNOWN, ospid: 8601
image: oracle@sgh0909
client details:
[code]....

View 3 Replies View Related

Moving Trace Files To Archive Location?

Aug 20, 2012

I am using Oracle RAC 11gR2.I am planing to move my old trace files to an archive location.

I have the below questions concerning the users and groups used by Oracle RAC when moving the trace files.It is related to the permissions but it is about oracle behavior.

The trace files of database (oracle user) are created as below:

284 -rw-r-----    1 oracle   asmadmin     283571 Aug 20 18:08 PPRD1_mmon_13893716.trc
   4 -rw-r-----    1 oracle   asmadmin        125 Aug 20 18:10 PPRD1_gcr0_11730998.trm
   4 -rw-r-----    1 oracle   asmadmin       2833 Aug 20 18:10 PPRD1_gcr0_11730998.trc
580 -rw-r-----    1 oracle   asmadmin     588489 Aug 20 18:12 PPRD1_lmhb_15859792.trm

[code]....

when moving the files using the oracle user it will be created as oracle:oinstall and not oracle:asmadmin since asmadmin is not a group of the oracle user.

1. How oracle created my trace and log files with permission oracle:asmadmin?

2. How should I move these files? I moved it using the Oracle user and it was created as oracle:oinstall with Operating system error "unable to duplicate owner and mode after move"

View 5 Replies View Related

Trace Events (19027) Generated In Oracle

Aug 31, 2013

DB version: 11.2.0.3

I have enabled the below trace event before running query on my session. 

ALTER SESSION SET events = '19027 trace name context forever, level 0x1000';ALTER SESSION SET tracefile_identifier = store_trace;

Then I ran my query, and upon the finishing of the query, I ran the below to disable trace. 

ALTER SESSION SET events = '19027 trace name context off, level 0x1000'; 

Now I checked back on the trace folder in my Oracle DB directory structure, but cannot find any file created by using the above set tracefile_identifier. I had previously run the query with "sql_trace" ON with tracefile_identifier, and successfully got the tracefile generated by using the identifier. 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved