Home » SQL & PL/SQL » SQL & PL/SQL » How to Find Last DML on a table
How to Find Last DML on a table [message #347269] Thu, 11 September 2008 03:58 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All,

I am trying run a script based on the data from a table which is in Server.Is it possible to find the Last DML on a table ??..and other details such as "ML statement" executed on that table ?..
This table is shared by many people.

Thanks in advance
Re: How to Find Last DML on a table [message #347275 is a reply to message #347269] Thu, 11 September 2008 04:16 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
You can try the following query.I hope this will help you.

SELECT versions_starttime, versions_endtime, versions_xid, 
versions_operation, *column_names*
FROM *table_name* versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_STARTTIME


This is 10g Flashback Versions Query.
Which oracle version you are using?

[Updated on: Thu, 11 September 2008 04:18]

Report message to a moderator

Re: How to Find Last DML on a table [message #347287 is a reply to message #347275] Thu, 11 September 2008 04:32 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Can You give pla the exact query ??..My table name is Ravi_test5,
with columns I,M,N

SQL> 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
Re: How to Find Last DML on a table [message #347293 is a reply to message #347269] Thu, 11 September 2008 04:38 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Try this one.

SELECT versions_starttime, versions_endtime, versions_xid, 
versions_operation, I,M,N
FROM  Ravi_test5 versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_STARTTIME

Re: How to Find Last DML on a table [message #347297 is a reply to message #347293] Thu, 11 September 2008 04:43 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Ora, I am getting the follwoing Error

SQL> SELECT versions_starttime, versions_endtime, versions_xid, 
  2  versions_operation, I,M,N
  3  FROM  Ravi_test5 versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
  4  ORDER BY VERSIONS_STARTTIME
  5  /
FROM  Ravi_test5 versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
      *
ERROR at line 3:
ORA-30051: VERSIONS clause not allowed here
Re: How to Find Last DML on a table [message #347298 is a reply to message #347293] Thu, 11 September 2008 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hopefully you are there to convert *X* to their values otherwise OP will be lost. Twisted Evil

Regards
Michel
Re: How to Find Last DML on a table [message #347302 is a reply to message #347298] Thu, 11 September 2008 04:49 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Michel,

Can u pls share the query.I am not getting what to do ?..
Re: How to Find Last DML on a table [message #347303 is a reply to message #347269] Thu, 11 September 2008 04:51 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
CREATE TABLE Ravi_test5(I NUMBER,M NUMBER,N NUMBER)

INSERT INTO Ravi_test5 VALUES(1,2,3);
/
INSERT INTO Ravi_test5 VALUES(4,5,6);
/
UPDATE Ravi_test5 
SET    N= 1000
WHERE n = 6;
/

SELECT versions_starttime, versions_endtime, versions_xid, 
versions_operation, I,M,N
FROM  Ravi_test5 versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_STARTTIME

9/11/2008 11:51:32. AM	9/11/2008 11:52:20. AM	120007009C110000	I	4.00	5.00	6.00
9/11/2008 11:51:32. AM		                120007009C110000	I	1.00	2.00	3.00
9/11/2008 11:52:20. AM		                100000009E110000	U	4.00	5.00	1,000.00

[Updated on: Thu, 11 September 2008 04:53]

Report message to a moderator

Re: How to Find Last DML on a table [message #347309 is a reply to message #347303] Thu, 11 September 2008 04:56 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Ora Sad

I am getting the Follwoing O/P

SQL> SELECT versions_starttime, versions_endtime, versions_xid, 
  2  versions_operation, I,M,N
  3  FROM  Ravi_test5 versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
  4  ORDER BY VERSIONS_STARTTIME;

no rows selected
Re: How to Find Last DML on a table [message #347310 is a reply to message #347269] Thu, 11 September 2008 04:57 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html
Re: How to Find Last DML on a table [message #347313 is a reply to message #347309] Thu, 11 September 2008 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Flashback transaction query (this feature) relies on undo data.
If the dml is too old, it is no more in the undo segments and so can't be seen.

In addition, only committed dml are available.

Regards
Michel

[Edit: Fix english error]

[Updated on: Thu, 11 September 2008 05:09]

Report message to a moderator

Re: How to Find Last DML on a table [message #347315 is a reply to message #347313] Thu, 11 September 2008 05:04 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Thank You very much Ora,Michel for your very quick responce.

Now i got the o/p

i forgot to do Commit after DML.

Re: How to Find Last DML on a table [message #347332 is a reply to message #347315] Thu, 11 September 2008 05:33 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Ora/Michel,

Is it possible in Oracle 8i to do the same ??..if not how can i do ??
Re: How to Find Last DML on a table [message #347336 is a reply to message #347332] Thu, 11 September 2008 05:38 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
I believe Flashback was introduced in oracle 10g.
You are struck if you don't a last_updated column if it is 8i
Re: How to Find Last DML on a table [message #347344 is a reply to message #347336] Thu, 11 September 2008 05:55 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

what do you mean by "You are struck if you don't a last_updated column if it is 8i " this ???

SO you mean it is not possible to get the o/p required?
Re: How to Find Last DML on a table [message #347346 is a reply to message #347332] Thu, 11 September 2008 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Flashback query was introduced in 10g.
In 8i, you have to use Log Miner.

Regards
Michel
Re: How to Find Last DML on a table [message #347354 is a reply to message #347346] Thu, 11 September 2008 06:14 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
In oracle 9i, use of audit or logminer will work.


Regards,
Oli
Previous Topic: trying to find solution!
Next Topic: Column Formatting (merged)
Goto Forum:
  


Current Time: Thu Dec 08 14:11:49 CST 2016

Total time taken to generate the page: 0.08993 seconds