Home » RDBMS Server » Server Administration » Save point Information. (oracle 10g windows XP)
Save point Information. [message #337647] Thu, 31 July 2008 08:42 Go to next message
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 #337650 is a reply to message #337647] Thu, 31 July 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
No.

Regards
Michel
Re: Save point Information. [message #337761 is a reply to message #337650] Fri, 01 August 2008 01:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #338903 is a reply to message #338887] Wed, 06 August 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can dump the internal structure but this does not mean it is usable for a program, it is just useful for Oracle debugging.

Regards
Michel
Re: Save point Information. [message #338910 is a reply to message #338903] Wed, 06 August 2008 04:27 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

I couldn't get you Michel, Can you please throw some light on that?
Re: Save point Information. [message #338930 is a reply to message #338910] Wed, 06 August 2008 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The savepoint list exists in an Oracle internal structure that you may dump but you can hardly use it in a program (actually I don't know how you could use it).

Regards
Michel
Re: Save point Information. [message #338933 is a reply to message #338930] Wed, 06 August 2008 05:01 Go to previous messageGo to next message
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 #338946 is a reply to message #338933] Wed, 06 August 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I read that oracle stores this savepoint information in some internal tables.

Yes and no.
tables in the meaning of programming language table not in dbms tables and so you can't access it I already said that.

Quote:
How can i dump that internal savepoint structure?

alter session set events 'immediate trace name savepoints level 1'


Regards
Michel
Re: Save point Information. [message #338948 is a reply to message #338946] Wed, 06 August 2008 05:22 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Smile Thank you michel..

I will try this.

Regards,
Dipali..
Re: Save point Information. [message #338956 is a reply to message #338946] Wed, 06 August 2008 05:38 Go to previous messageGo to next message
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 #338961 is a reply to message #338956] Wed, 06 August 2008 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-03113 = bug, so apply patchset. You will have no support from Oracle on this unsupported statement.

Regards
Michel
Re: Save point Information. [message #338998 is a reply to message #338961] Wed, 06 August 2008 07:25 Go to previous messageGo to next message
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..
Re: Save point Information. [message #339003 is a reply to message #338998] Wed, 06 August 2008 08:16 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hey, got the answer...

After firing that "alter session" statement, the trace file is being refreshed. Smile

Thanks once again ,Michel.
-Dipali..
Re: Save point Information. [message #339005 is a reply to message #339003] Wed, 06 August 2008 08:32 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A good practice when you use trace is to disconnect then Oracle flushes its buffer and close the file.

Regards
Michel
Previous Topic: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded)
Next Topic: Shrink DB
Goto Forum:
  


Current Time: Mon Dec 05 07:14:19 CST 2016

Total time taken to generate the page: 0.08830 seconds