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.*,
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.
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.
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.
In one of the databases, we have created more temp tables as on commit preserve rows. but i want to change the on commit preserve rows to on commit delete rows as per application requirements.I have searched the google but i didnt find any alter scripts for it.?
create table units (id number primary key , agent number, constraint agnent_fk foreign key (agent) references agent_lookup (id) )create table agent_lookup (id number primary key , agent_name varchar2 (40))INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (1, 'X1'); INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (2, 'X2'); INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (3, 'X3');INSERT INTO UNITS (ID, AGENT) VALUES (100, 1); INSERT INTO UNITS (ID, AGENT) VALUES (101, 2); INSERT INTO UNITS (ID, AGENT) VALUES (102, 3);I want to make units table uses user_lookup table instead of agent_lookup table; where user_lookup contains the same names that exist on agent_lookup, but with different IDs.
create table user_lookup ( id number primary key , user_name varchar2 (40))INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (10, 'X1'); INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (20, 'X2'); INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (30, 'X3');So, I need to update the table units and SET the value of Agent to the ID column of the user_lookup table .
I thought of this, but sure it will not work:
update units ut set ut.agent = (select u.id from user_lookup u where agent_lookup.agent_name = u.user_name ) where ut.agent = agent_lookup.id
I wanted to export a table "emp_production" from Production database then import it as "emp_datawarehouse" in Data warehouse database.Both tables has same structure. I have granted IMPORT FULL DATABASE & EXPORT FULL DATABASE privileges to both schema
I am new to PL/SQL, worked mostly on SQL server, I have to change the table name dynamically based on the parameter.and used a ref_cursor to display the results in a report. when I execute it throws me an error.
create or replace procedure test1 ( p_eod_date IN VARCHAR2, p_link IN NUMBER, c_rec IN OUT SYS_REFCURSOR) [code]....
I have a table called cust_file, his table consists of a lot of columns (one of these columns called cus_tax) and have a lot of data,I use oracle 11g, I want to change the default value of the column cus_tax to be equal 1, I wrote
ALTER TABLE cust_file MODIFY(cus_tax DEFAULT 1); table alteredbut
after I inserted new data to test the operation, I found that the new record has a value
= null for the column cus_taxthen
I tested using the following query select
data_default from all_tab_columns where table_name='CUST_FILE' and column_name='CUS_TAX'; no rows selected...
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$';
