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
886 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle collections 15 53
Create file system directory from Oracle 10g 4 46
oracle forms question 9 39
Dbms_job.change procedure 16 34
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

732 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