I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program.
1. Created the audit table with similar number of records.
2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.
I am unable to figure out the proper way to implement the point 2 above in oracle database.
create table tab_tab(col1 number, col2 number); insert into tab_tab values(1, 1); insert into tab_tab values(2, 2); insert into tab_tab values(3, null); [code]......
My question is why the table content didn't look like: col1 col2 ---------- 1 2 2 2 3 2 4 3 5 4
I thought that when oracle will update 1st, 2nd & 3rd rows then the sub query will get a count equal to 2, but the time when it goes to update the 4th & 5th row the sub query should get back a count equal to 3 & 4 resp. BUT that is not what exactly is happening!!
I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.
I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.
We can audit a particular table alone, I would like to audit one table, to find all different kinds of queries fired (Including select, insert and update) over a period of 2 months.
We need to audit what data has changed from Vendor_B table when compared to Vendor_A table.
There is two tables:
a) VENDOR_A b) VENDOR_B
VENDOR_A and VENDOR_B table does not share the same structure but have 1 column in common - the ID and may or may not having changed Coordinates.
We need to audit what coordinates has changed from Vendor_B data when compared to Vendor A and get the report back in the format of:
<pre> VENDOR_A VENDOR_B ID Change_date LONGITUDE LATITUDE LONGITUDE LATITUDE 873 1/02/2013 -33.46711315 151.3195056 -33.46211316 151.3245057 694 3/02/2013 -33.46721315 151.3198056 -33.46214312 151.3265053
</pre> --------------------------------------------------------------------------------------------------------------------------------------------------- --Found a sample query from Ask Tom but I am not sure how I can apply them to get the structure above. --------------------------------------------------------------------------------------------------------------------------------------------------- <pre> create table base_tab (pk number, column1 number, column2 varchar2(30), column3 date); Create table refresh_tab (pk number, column1 number, column2 varchar2(30), column3 date); Create table diff (pk number, column1 number, column2 varchar2(30), column3 date, base_tab number, refresh_tab number, action varchar2(30));
[Code] .....
--clean out the differences table truncate table diff;
-------------------------------- -- build the difference data ------------------------------------ insert into diff select pk,column1,column2, column3, count(src1) base_tab, count(src2) refresh_tab, null action from ( select a.*,
I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.
DROP TABLE emp; CREATE TABLE emp AS (SELECT * FROM scott.emp); CREATE TABLE audit_table
I want to audit dml on few table for non application users.can I omit only application users from audit so that whenever new user is created we need not to add audit for user.do i have to create audit logon trigger for this which check first the application user names from table and if logged user is not application user auditing will start for it.
I'm trying to create a trigger that will come into play after the user has inserted a 0 for the booking evaluation, i need the booking id sent to the audit table. This is what I have tried so far:
CREATE OR REPLACE TRIGGER zero_evaluation AFTER INSERT OR UPDATE ON booking FOR EACH ROW WHEN (NEW.Evaluation=0) BEGIN INSERT INTO audit (audit_id, booking_id, Reason) VALUES (audit_id_seq.NEXTVAL,:NEW.booking_id, 'Contact customer for 0 evaluation'); END;
I get the following: Line 2 PL/SQL: SQL Statement ignored Line 3 PL/SQL: ORA-02289: sequence does not exist
This is my SQL statement: CREATE TABLE audit (audit_id numeric(5) not null, booking_id numeric(5) not null,
I am just making a audit table as well. i have learnt the basics from here URL.....
My problem is that after inserting into audit table if i issue commit command then the table + unsaved data present on the form is also saved.What i want is that i issue a command which save only inserted record in audit table, and should NOT save data present on the data entry form. (which will be saved later by other method/button).
I support to get a handle on statistics collectionn in their data warehouses. It seems developers have created several ANALYZE TABLE jobs but the code for these is not stored as PLSQL in the database and thus it is problematic for statistics collection. Even if we collect stats that way we want, these jobs kick in and overlay the statistics we collect every day.
Is there a way to AUDIT ANALYZE TABLE? I can't find it anywhere.
Is there a way to globally turn of ANALYZE TABLE in a 9i database?
AVDF current version 12.1 not support External/SAN storage. my question is, if customer get a huge number of Audit log and DBFW event records, then how max size can Audi Vault server support for online data (not archive data)? and can I use a Hardware server with multiple HDDs for AV Server?
I am using oracle developer 10g. I want to know the status of the printer where i want to print. If the running report is printing or in queue then a record is to be inserted into a table as audit-trail of printing. Idon't want to do it manually.
You have a stock_amt value in one table and there is a procedure that updates and substracts from this stock_amt.
lets say in a store a have a stock amount of 50 items and this procedure, for each sale is subtracting from this value and it is not allowed to go below zero. The process, beside the update, on this column (set stock_amt = stock_amt - x) is doing a lot of other updates on other tables and it total it takes like 0.5 seconds. Everything is fine till I want to execute this procedure by 50 users in parallel.
The initial implementation, to avoid some dead locks and we put a lock on that column (stock_amt) but there where to much waits. we cannot hold that lock for 0.5 seconds.
What will be the best approach for this? For this stock amt problem, maybe the solution can be a trade like: do not update that column every time but once in a while, by another process or by a materialize view logic.
but what if my column is a critical value like a Prepay balance or bank balance and it needs to be updated in near real time. What will you do?
Update employee e set e.dept_id = (select d.dept_id from dept d
[Code].....
The above is not the exact code which I am executing but an exact replica of the logic implied in my code.
Now, when i display the value of 'rows_updated' it returns a value greater than 0,i.e 3 but it should ideally return 0 since there are no records matching for the condition: (select d.dept_id from dept d where e.dept_name = d.dept_name)
So, I executed the statement: select count(*) from employee e where emp_id = 1234 and exists (select 1 from emp_his ee where e.emp_id = ee.emp_id) and the result was 3 which is the same value returned by %rowcount.
why this is happening as I am getting incorrect values in %rowcount for the number of rows updated.
SQL> show user USER is "ANDREY" SQL> SQL> SQL> SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data: SQL> SQL> insert into a(key1 , key2) values (1 , 1); 1 row created. SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a set key3 = 'inactive' where key2 = ( select max(key2) from a a2 where a2.key1=a.key1 );
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1 2 IS 3 4 5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1; PL/SQL procedure successfully completed. SQL> select * from a; KEY1 KEY2 KEY3 ---------- ---------- ---------- 1 1 active 1 5 incative 2 24 active 2 21 active
This problem went away for me in 4.2, but I am limited to 4.1 in production. The problem is this: I have three editable columns in my Tabular Form
1) I make a change to column 3 and press submit. The values in columns 1 & 2 (that were not changed) are overwritten with null during the update (submit). Column 3 is saved correctly.
I deleted my tabular form and re-created it thinking that I may have trashed something. But it appears that is the way it works. How do I prevent the unchanged editable columns from being overwritten with nulls?
I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.
The value doesn't always change but the scripts will still attempt to perform an update.
The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.
I need some detail information regarding the CPM Patches released by oracle quarterly.
Is it compulsory to set the CPM patch when it is being realized. How to set or apply this patch. (either by runInstaller or OPatch utility i am not sure about this)
What is the difference between the interim & CPM patches.
How to update a form fields which taking a searched value from another table .
I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.
When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.
If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.
create or replace PROCEDURE TESTPERFORMANCE ( o_statuscode OUT NUMBER, o_statusdescription OUT VARCHAR2, starttime out timestamp, time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
I facing the below issue while enable the audit on table.
SQL> audit delete on test; audit delete on test * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01400: cannot insert NULL into ("SYS"."AUDIT_DDL"."DICT_OBJ_TYPE") ORA-06512: at line 2
=== Related Information === SQL> select version from v$instance;
VERSION ----------------- 11.2.0.2.0
SQL> show parameter audit;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /orasw/product/11.2.0 /rdbms/audit audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB
SQL> SELECT TABLESPACE_NAME,owner from dba_tables where table_name='AUD$';
