No question at this time
DBA Top 10
1 B. Vroman 15700
2 M. Cadot 10200
3 T. Boles 8350
4 J. Schnackenberg 8300
5 A. Kavsek 7800
6 M. Hidayathullah ... 2500
7 P. Wisse 1200
8 G. Lambregts 1100
9 T. P 1000
10 B. Derous 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48277
Total active users1612
Act. users last 24h4
Act. users last hour0
Registered user hits last week185
Registered user hits last month836
Go up

Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Next thread: MMON Process
Prev thread: RMAN and expired backup sets

Message Score Author Date
Dear Gurus , We are facing issue with our databas...... Sandeep Negi Apr 08, 2018, 12:04
Hello Sandeep,    <i>... are getting deleted <b...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Apr 09, 2018, 07:57
Depending on how adventurous you are I would try t...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tim Boles Apr 09, 2018, 17:28
Hello Tim, thank you for the interesting pointe...... Bruno Vroman Apr 10, 2018, 07:54
Bruno, I guess my mind for some reason switched...... Tim Boles Apr 10, 2018, 11:50
Thank you Everyone!! I checked and found DML ha...... Sandeep Negi Apr 10, 2018, 12:35
You best hope is to use the information from dba_a...... Tim Boles Apr 10, 2018, 12:45
Hello Sandeep, you can audit the logons of APPL...... Bruno Vroman Apr 10, 2018, 13:15

Follow up by mail Click here


Subject: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Sandeep Negi, India
Date: Apr 08, 2018, 12:04, 196 days ago
Os info: Solaris 11.3
Oracle info: 11.2.0.4
Message: Dear Gurus ,
We are facing issue with our database , Application user reported that three columns are getting deleted every time from a table "XXQF_OBJECTIVE_HISTORY".

Please let us know , how I can track the DML happening against that table.

We checked through below commands but cannot track how this deletion actually happening.Is there any way I can check why this alter happening to this table , is any program or any script causing altering to this table.

select * from dba_audit_object where OBJ_NAME like 'XXQF_OBJECTIVE%';
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Bruno Vroman, Belgium
Date: Apr 09, 2018, 07:57, 195 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hello Sandeep,

   ... are getting deleted every time?

How often is this? I suppose that there is a "watching" mechanism (dbms_scheduler job?) that ensures that some tables have the "official" structure...

You might search info about enable_ddl_logging, or if the modification happens often, you might just create a specific trigger to capture info about DDL on this table... Here is an example, but you will probably track more info:

(from a DBA account:)
GRANT SELECT ON v_$session TO xx;

CREATE TABLE xx.track( moment DATE, username VARCHAR2(30), osuser VARCHAR2(30) );
CREATE OR REPLACE TRIGGER xx.touch_xqf_objective_history
AFTER ALTER ON xx.SCHEMA
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF ora_dict_obj_type = 'TABLE'
AND ora_dict_obj_name = 'XXQF_OBJECTIVE_HISTORY'
THEN
INSERT INTO track( moment, username, osuser )
SELECT sysdate, user, osuser
FROM v$session
WHERE audsid = sys_context( 'USERENV', 'SESSIONID' )
AND ROWNUM = 1
AND username IS NOT NULL
;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( sqlerrm );
RAISE;
END touch_wwqf_objective_history;
/
Testing: modify the table, then as XX you can do
SELECT * from track;

MOMENT USERNAME OSUSER
-------------------- --------------- ------------------------------
09-APR-2018 07:52:50 SCOTT brunov
Remark: changes done by "SYS" will not be captured, SYS is a "very special" user...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Tim Boles, United States
Date: Apr 09, 2018, 17:28, 195 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Depending on how adventurous you are I would try to use Flash Back Query to determine this if you know of the change quick enough.

Tim Hall has a fairly straight forward write up on this method.

https://oracle-base.com/articles/10g/flashback-10g

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Bruno Vroman, Belgium
Date: Apr 10, 2018, 07:54, 194 days ago
Message: Hello Tim,

thank you for the interesting pointer to Tim Hall's article.

I don't understand what you want to do with the flashback query (see the old data? I guess that the OP only wants to know "who" is altering the table, and knowing the old data is not the first concern).
Anyway, unfortunately flashback query does not resist "DDL":
CREATE TABLE xx( n1 NUMBER, n2 NUMBER, n3 NUMBER );

INSERT INTO xx VALUES ( 1, 2, 3 );
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
------------ -------------------
13943170831 2018-04-10 07:22:04

ALTER TABLE xx DROP ( n3 );

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
------------ -------------------
13943170850 2018-04-10 07:22:24

SELECT * FROM xx;
N1 N2
---- ----
1 2

SELECT * FROM xx AS OF SCN 13943170831;
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
BTW, if we want to know when the columns have been dropped, last_ddl_time of user_objects is probably OK:
SQL> SELECT o.last_ddl_time FROM user_objects o WHERE o.object_name = 'XX';

LAST_DDL_TIME
--------------------
10-APR-2018 07:22:17
Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Tim Boles, United States
Date: Apr 10, 2018, 11:50, 194 days ago
Message: Bruno,

I guess my mind for some reason switched from dropping the columns to deleting data from the columns. I think I was in the middle of thinking about this article when someone at work had a different issue and then I got side tracked on this topic because we were talking flashback for their case. So I went from dropped columns to deleting data from columns.

Thanks for the excellent examples as always.

Tim

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Sandeep Negi, India
Date: Apr 10, 2018, 12:35, 194 days ago
Message: Thank you Everyone!!

I checked and found DML happened to the table using default application user " APPLPROD " using query select * from dba_audit_object where OBJ_NAME='XXQF_OBJECTIVE_HISTORY';.


I don't know how I can go forward to track the User who does the altering.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Tim Boles, United States
Date: Apr 10, 2018, 12:45, 194 days ago
Message: You best hope is to use the information from dba_audit_object. It has the username, userhost, terminal, os_username.

What other information do you think you need?

If nothing else send out an email to the DBAs and developers on the system and ask if anyone is messing with that table.

Regards
Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Columns Getting Deleted from a table "XXQF_OBJECTIVE_HISTORY"
Author: Bruno Vroman, Belgium
Date: Apr 10, 2018, 13:15, 194 days ago
Message: Hello Sandeep,

you can audit the logons of APPLPROD to find the OSUSER(s) behind the connections (and then talk with him/her/them)

If you want to be a bit aggressive you might also use a trigger to prevent the modification of the table (causing an error "-20001, 'Not allowed, contact Sandeep'")

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here