Home » SQL & PL/SQL » SQL & PL/SQL » How to take History for particular table? (merged)
How to take History for particular table? (merged) [message #264651] Tue, 04 September 2007 02:13 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Dear all,

We are using Oracle 9i. In the database table Cane_Data is used for insert daily cane data. Daily we are updating and chaning data as per user requirements. But daily should updated.
In that situation some wrong data's are updated in that table. Because our database is Network based. Whoever having login can access through the Forms(D2K). No restrictions. So our Manager wants history of that table, that means whenever datas's are updated and from which machine and which login. It is possbile to find out? Please assist me. How we can know? If any queries please send me.

Thanks & Regards
Manoharan Pichaipillai


[mod-edit] font & color removed.

[Updated on: Tue, 04 September 2007 08:00] by Moderator

Report message to a moderator

How to take history for particular table? [message #264652 is a reply to message #264651] Tue, 04 September 2007 02:16 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Dear all,

We are using Oracle 9i. In the database table Cane_Data is used for insert daily cane data. Daily we are updating and chaning data as per user requirements. But daily should updated.
In that situation some wrong data's are updated in that table. Because our database is Network based. Whoever having login can access through the Forms(D2K). No restrictions. So our Manager wants history of that table, that means whenever datas's are updated and from which machine and which login. It is possbile to find out? Please assist me. How we can know? If any queries please send me.

Thanks & Regards
Manoharan Pichaipillai

[mod-edit] font & color removed.

[Updated on: Tue, 04 September 2007 08:01] by Moderator

Report message to a moderator

Re: How to take history for particular table? [message #264657 is a reply to message #264652] Tue, 04 September 2007 02:25 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

you can create a trigger or enable audit for DML's.


regards,
Re: How to take History for particular table? [message #264658 is a reply to message #264651] Tue, 04 September 2007 02:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Flahback versions Query.
Re: How to take history for particular table? [message #264661 is a reply to message #264652] Tue, 04 September 2007 02:30 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Never do Duplicate thread. You can also create a history table and use a trigger to insert rows into it along with flashback version query.
Re: How to take History for particular table? [message #264662 is a reply to message #264658] Tue, 04 September 2007 02:33 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Arju,

I could not understand. Please can you explain how? I dont want recall. i need whenever data's updated and who's updated? i am awit for your reply.
Re: How to take History for particular table? [message #264666 is a reply to message #264662] Tue, 04 September 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/print_hit_summary?search_string=Flashback+version+Query

Regards
Michel
Re: How to take History for particular table? [message #264668 is a reply to message #264666] Tue, 04 September 2007 02:43 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
All,

There is no trigger previously for inserting old data to another table? that is problem.I thing flashback query is using for Roll back/recall the data's. but i need updating informtion. Anything ideas from you?
Re: How to take History for particular table? (merged) [message #264671 is a reply to message #264651] Tue, 04 September 2007 02:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Same answer.
Re: How to take History for particular table? (merged) [message #264672 is a reply to message #264651] Tue, 04 September 2007 02:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

By the way flashback query and flashback versions query is not same.
Re: How to take History for particular table? (merged) [message #264673 is a reply to message #264651] Tue, 04 September 2007 02:49 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Through flahback query it's possible? how?
Re: How to take History for particular table? (merged) [message #264676 is a reply to message #264673] Tue, 04 September 2007 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/print_hit_summary?search_string=flashback+query

Regards
Michel
Re: How to take History for particular table? (merged) [message #264677 is a reply to message #264673] Tue, 04 September 2007 03:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want to be able to access that data at any time, flashback is NOT the way to go.
What you want is known as auditing. If you search this site or Google for it, it will return a lot of hits. Start reading those and see if it helps you.
Re: How to take history for particular table? [message #264692 is a reply to message #264652] Tue, 04 September 2007 03:48 Go to previous messageGo to next message
huje
Messages: 7
Registered: May 2007
Location: kolkata
Junior Member

spmano1983 wrote on Tue, 04 September 2007 02:16
,

hi,
i suggest you to change the structure of the table or you can add a table which will store all logging information(like form_nm,log_date,log_time,ip or use etc).but it can only applicable for new data. now you write a database trigger which will provide all data for that table.



[Updated on: Tue, 04 September 2007 03:49]

Report message to a moderator

Re: How to take History for particular table? (merged) [message #264711 is a reply to message #264651] Tue, 04 September 2007 05:04 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Mano,

I am not sure this is the correct answer. Please make it Example.

Test this query

select SYSDATE,SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV', 'HOST') from dual;



First you have to create table like this

Name                           Null     Type 
------------------------------ -------- ------------
STAMP                                   DATE  
USERNAME                                VARCHAR2(30)
OSUSER                                  VARCHAR2(30)
MACHINE                                 VARCHAR2(30)
TERMINAL                                VARCHAR2(30)
OPERATION                               VARCHAR2(30)
OBJTYPE                                 VARCHAR2(30)
OBJNAME                                 VARCHAR2(30)




Create Tigger like this

create or replace trigger triger_name
AFTER update
      ON Tab_name
begin

  INSERT INTO dll_audit VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME
        );
END triger_name; 


I am not 100% sure please check it.

Reds
Thangam.

[Updated on: Tue, 04 September 2007 05:34]

Report message to a moderator

Re: How to take History for particular table? (merged) [message #264964 is a reply to message #264651] Wed, 05 September 2007 02:22 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Durai,

Thanks for your gold timings. Atually your code is pre planned. If we created like your codings, this automatically will store to that table, then we can take history. but what i am telling, my table was already updated. but that database dont have like that trigger. Can we take updated record history information from database? I am also not sure this is possible in oracle. If you knows convey me. anyhow i knew some information from your above triggers

thanks
Re: How to take History for particular table? (merged) [message #264968 is a reply to message #264964] Wed, 05 September 2007 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the links I posted.

Regards
Michel
Re: How to take History for particular table? (merged) [message #264975 is a reply to message #264651] Wed, 05 September 2007 03:03 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
your link not matched little bit also to my problem
Re: How to take History for particular table? (merged) [message #264985 is a reply to message #264651] Wed, 05 September 2007 03:32 Go to previous messageGo to next message
guarav.sirsa
Messages: 8
Registered: September 2007
Location: Silvassa
Junior Member
This is very simple thing what you are asking about.Have to get terminal id in table.and to revert can get seprate backup table.which will be updated on regural basis itself.
Re: How to take History for particular table? (merged) [message #264995 is a reply to message #264651] Wed, 05 September 2007 03:49 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi sirsa,

what you coming to tell? can you explain me? can we get history for already updated table? please awaiting for your reply.

[Updated on: Wed, 05 September 2007 07:49] by Moderator

Report message to a moderator

Re: How to take History for particular table? (merged) [message #264996 is a reply to message #264985] Wed, 05 September 2007 03:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's what I got so far from this thread:
1. someone has changed the content of a table.
2. you want to find out what was changed by whom.
3. you have no audit or history tables.
4. you want to keep track of changes in the future.

If that is the case, you could use flashback to get the data prior to the update and compare it with the actual data. But there are a few things you have to keep in mind: flashback consumes space so it is very well possible that it is limited in time or space by your DBA. And you won't get user information from it. But if I'm missing something, don't hesitate to correct me.

For the future, to prevent this situation from happening again, I'd follow Frank's advise: use AUDIT. Triggers can be disabled.

MHE
Re: How to take History for particular table? (merged) [message #265000 is a reply to message #264651] Wed, 05 September 2007 04:06 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Maaher,

Really i like your smart. You have understood 100% me. I will check with flashback and convey you what's happened. But really i need details about AUDIT. can you explain me?

Thanks
Re: How to take History for particular table? (merged) [message #265001 is a reply to message #265000] Wed, 05 September 2007 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I will check with flashback and convey you what's happened

In this case, follow the links I posted!
Quote:
But really i need details about AUDIT

Just put AUDIT in the search field of Oracle documentation.

Regards
Michel
Re: How to take History for particular table? (merged) [message #265008 is a reply to message #264651] Wed, 05 September 2007 04:22 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Michel,

Where is Documentation search?
Re: How to take History for particular table? (merged) [message #265018 is a reply to message #265008] Wed, 05 September 2007 04:39 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

the very first post in this forum has the link to the Oracle documentation.you may also refer this link :

http://www.oracle-base.com/articles/10g/Auditing_10gR2.php


regards,
Re: How to take History for particular table? (merged) [message #265022 is a reply to message #265018] Wed, 05 September 2007 04:55 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
hi dhananjay,

thanks your info. really great use of AUDIT. Now i came to one level. thanks all.

Re: How to take History for particular table? (merged) [message #265062 is a reply to message #265008] Wed, 05 September 2007 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where is Documentation search?

At least, in the url I posted.
Didn't you see the "search_string=...".

Regards
Michel
Re: How to take History for particular table? (merged) [message #267805 is a reply to message #265000] Sat, 15 September 2007 01:23 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

Set initialization parameter Audit_trail to DB_EXTENDED in spfile scope. Connect sql with sys:
AUDIT <INSERT, UPDATE, DELETE>
ON <USER.TABLE> --or user
BY ACCESS
WHENEVER NOT SUCCESSFUL;

after few transactions
COL OBJ_NAME FORMAT 'A15'
COL ACTION_NAME FORMAT 'A10'
COL TERMINAL FORMAT 'A10'
COL SQL_TEXT FORMAT 'A30'
SELECT TO_CHAR(TIMESTAMP, 'DD-MM-YY HH:MM:SS'), TERMINAL, ACTION_NAME, OBJ_NAME, SQL_TEXT
FROM DBA_AUDIT_TRAIL;

In my understandings, you were asking the above. If not, sorry
Re: How to take History for particular table? (merged) [message #267808 is a reply to message #267805] Sat, 15 September 2007 01:33 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, I forget that TERMINAL was in the audit trail but just a look at the view shows it at 4th column.

Regards
Michel

[Updated on: Sat, 15 September 2007 01:33]

Report message to a moderator

Previous Topic: what is the size of date datatype?
Next Topic: seeking help for desired output
Goto Forum:
  


Current Time: Mon Dec 05 14:51:52 CST 2016

Total time taken to generate the page: 0.12034 seconds