Save point Information. [message #337647] |
Thu, 31 July 2008 08:42 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Hi Friends,
We can define multiple savepoints within a trasaction.
Can we get the information about the savepoints defined in current transaction?
Regards,
Dipali...
|
|
|
|
Re: Save point Information. [message #337761 is a reply to message #337650] |
Fri, 01 August 2008 01:14 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Hi Michel,
After declaring multiple savepoints within transaction, we can committ or rollback transaction upto any of those savepoints. Means, oracle is keeping track/information about those savepoints. Then as a DBA, can we not have the information about those savepoints?
Suppose the case is: Some developer has declared multiple savepoints in his trasaction, and he lost the track, which savepoint name he/she had given, and he asks for the help to us, can't we have track of the savepoints declared by him?
Regards,
Dipali..
|
|
|
Re: Save point Information. [message #337766 is a reply to message #337761] |
Fri, 01 August 2008 01:33 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The fact that Oracle knows it does not mean it wants us to know it.
This information is not exposed to the user.
By the way, how can a developer lose which savepoint he gives? He just have to look at his code.
Regards
Michel
[Updated on: Wed, 06 August 2008 04:13] Report message to a moderator
|
|
|
Re: Save point Information. [message #338887 is a reply to message #337766] |
Wed, 06 August 2008 03:26 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Hi Michel,
I came to know that from my senior, somewhere from operating system level, we can trace savepoints..
I am just doing r&d for that..
Will post here the outcomes..
Regards,
Dipali..
|
|
|
|
|
|
Re: Save point Information. [message #338933 is a reply to message #338930] |
Wed, 06 August 2008 05:01 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Michel,
How can i dump that internal savepoint structure?
I read that oracle stores this savepoint information in some internal tables. Can't we get that table information or that table data anyhow..?
Thanks and Regards,
Dipali..
|
|
|
|
|
Re: Save point Information. [message #338956 is a reply to message #338946] |
Wed, 06 August 2008 05:38 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
ohh..
oracle version: Oracle9i Enterprise Edition Release 9.2.0.1.0
15:59:40 SQL> alter session set events 'immediate trace name savepoints level 1';
alter session set events 'immediate trace name savepoints level 1'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Elapsed: 00:00:00.01
15:59:50 SQL>
Regards,
Dipali.
|
|
|
|
Re: Save point Information. [message #338998 is a reply to message #338961] |
Wed, 06 August 2008 07:25 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
hello michel,
I tried that statement on oracle 10g's database' scott schema.
From net, i found that, the information will be recorded in the trace file in user dump directory.
After that, i open new session of "SCOTT" user, created 3 savepoints, and after each savepoints executed some dml statements.
I got the trace file name for this session using following script.
1 select
2 u_dump.value || '/' ||
3 db_name.value || '_ora_' ||
4 v$process.spid ||
5 nvl2(v$process.traceid, '_' || v$process.traceid, null )
6 || '.trc' "Trace File"
7 from
8 v$parameter u_dump
9 cross join v$parameter db_name
10 cross join v$process
11 join v$session
12 on v$process.addr = v$session.paddr
13 where
14 u_dump.name = 'user_dump_dest' and
15 db_name.name = 'db_name' and
16* v$session.audsid=1606078
17:47:05 SQL> /
Trace File
---------------------------------------------------------------------------
/oracle/admin/stag/udump/stag_ora_9684.trc
Elapsed: 00:00:00.07
17:47:11 SQL>
But this trace file was last updated before nearly 45 minutes. I am monitoring this file content right before executing "alter session " statement till now. But it isn't updated yet. So i couldn't get savepoint information.
My question is: when oracle will create/update trace files (in user dump and in background dump folder)?
Thanks and regards,
Dipali..
|
|
|
|
|