Solved

How to find new rows inserted in a oracle table between perticular time frame and how to delete them?

Posted on 2009-05-12
14
879 Views
Last Modified: 2013-12-19
By mistake some1 has inserted 5000 rows in one oracle table, As it was by mistake they are looking to find out those rows to delete them.

As they inserted those rows by running some utility, we dont know what rows are inserted and how many rows inserted. We just know the timeframe when utility was ran on the system and approximately 5000 rows were inserted.

Oracle 9i,
Windows Server 2000

Any quick help will be appreciated.
0
Comment
Question by:MOTODBA
  • 5
  • 4
  • 2
  • +2
14 Comments
 

Author Comment

by:MOTODBA
ID: 24371466
hi
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24371494
do you have auditting?

either oracle built in (check dba_audit_trail) or triggers that write a log (file, table or some other place)

if not, you will have to resort to log miner

or worst case,  do a point in time recovery to immediately before you know when the data was loaded and then redo all the work since that time except the mistaken data load.
0
 

Author Comment

by:MOTODBA
ID: 24371544
can you please guide me how to use log miner? i studied on oracle website but i didnt get nething.

can we do point in time revovery of that perticular table?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24374479
here's a quick example of using log miner

Metalink doc 111886.1  has more complete descriptions.

walking through a full mining task is beyond the scope of this forum
as is a recovery.  Your dba should be able to do a recovery though.
execute dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora', dictionary_location =>'c:\some\path');

execute dbms_logmnr.add_logfile(logfilename => 'c:\your\archive\log\directory\some_log_file.log', options => dbms_logmnr.NEW);

execute dbms_logmnr.add_logfile(logfilename => 'c:\your\archive\log\directory\some_log_file2.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.start_logmnr(dictfilename => 'c:\some\path\dictionary.ora');

execute dbms_logmnr.start_logmnr(dictfilename => 'c:\some\path\dictionary.ora');
 

-- creating a copy isn't strictly necessary but makes subsequent queries faster over querying the v$ view

create table junk_logminer as select * from v$logmnr_contents;  

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24375108
Or you could use the ORA_ROWSCN pseudocolumn:
 

SELECT e.*, scn_to_timestamp (ORA_ROWSCN)

  FROM employees e

 WHERE scn_to_timestamp (ORA_ROWSCN)

       BETWEEN TO_TIMESTAMP ('20090101','YYYYMMDD')

           AND TO_TIMESTAMP ('20090430235959','YYYYMMDDHH24MISS');

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24375453
oh duh!!!

much easier. I jumped right to the hard solutions and completely forgot about the most obvious answer.

nice!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24375546
note,  any row modifications that were committed during the same time window as the inserts will show up with the ora_rowscn  method.

It's still a good idea to try, but make sure you do the select and check the data before deleting so you make sure you're only getting rid of the rows you want.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Franck Pachot
ID: 24376198
Hi,
Note as well that if the table is not with rowdependencies then ora_rowscn will give you all rows that are in a block where some updated were committed during that period.
So be careful to not delete too much rows !
Regards,
Franck.
0
 

Author Comment

by:MOTODBA
ID: 24378747
when I am running this query i i am geting this error:

ora-08181 specified number is not a valid system change number

ora-06512: at sys.scn_to_timestamp, line 1
0
 
LVL 18

Expert Comment

by:sventhan
ID: 24378776
ORA_ROWSCN is a 10g feature. If you're in 9i it will not work.
0
 

Author Comment

by:MOTODBA
ID: 24378930
i am having problem in oracle 9i, what should i do? how to figure what rows inserted in that table at perticular time. I checked v$sql and v$sql area but i didnt find what values were inserted because it was inserted through some utility. its not showing sqls. pls help.
0
 
LVL 18

Accepted Solution

by:
sventhan earned 500 total points
ID: 24379032
If you have flash back enabled you could try this..
http://www.oracle-base.com/articles/9i/FlashbackQuery.php
 
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24379161

Otherwise your option is to follow sdstuber's suggestion and use logminer.
-- Or --
Recover database on another server to a point in time before the offending job was executed and compare the tables.
0
 

Author Closing Comment

by:MOTODBA
ID: 31580840
Excellent , it worked great! thanks a bunch.... 1000 point:)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now