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
887 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
14 Comments
 

Author Comment

by:MOTODBA
ID: 24371466
hi
0
 
LVL 74

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 74

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 74

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 74

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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

690 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