• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

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

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
MOTODBA
Asked:
MOTODBA
  • 5
  • 4
  • 2
  • +2
1 Solution
 
MOTODBAAuthor Commented:
hi
0
 
sdstuberCommented:
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
 
MOTODBAAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sdstuberCommented:
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
 
MikeOM_DBACommented:
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
 
sdstuberCommented:
oh duh!!!

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

nice!
0
 
sdstuberCommented:
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
 
Franck PachotCommented:
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
 
MOTODBAAuthor Commented:
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
 
sventhanCommented:
ORA_ROWSCN is a 10g feature. If you're in 9i it will not work.
0
 
MOTODBAAuthor Commented:
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
 
sventhanCommented:
If you have flash back enabled you could try this..
http://www.oracle-base.com/articles/9i/FlashbackQuery.php
 
0
 
MikeOM_DBACommented:

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
 
MOTODBAAuthor Commented:
Excellent , it worked great! thanks a bunch.... 1000 point:)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now