PL/SQL :: Audit Trails For Non-updatable View?
Oct 23, 2012
Database :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
I have a requirement to develop audit trails for non-updatable view.In order to do this I created an audit trail table and row level triggers on view's underlying tables.When I update any column value from front-end oracle form then both triggers (on both tables) fire and log audit information for all columns(for both tables) into generic audit table. so far it's good but there is small difference in time interval between the triggers firing and seeing time stamp difference for both tables audit data.
sample audit data:
changed_by changed_on changed_type table_name column_name old_val new_val
myself 10/23/2012 10:15:*48* AM U TABLE1 COLUMN1 X Y
myself 10/23/2012 10:15:*48* AM U TABLE1 COLUMN2 C D
myself 10/23/2012 10:15:*49* AM U TABLE2 COLUMN5 A B
myself 10/23/2012 10:15:*49* AM U TABLE2 COLUMN6 F G
My requirement is to see all columns data from both tables in audit table with same time stamp and we will query the audit table using time stamp to show up old and new values of the non-updatable view on particular time.How can I make two triggers log the same time stamp on audit table ?.
View 1 Replies
ADVERTISEMENT
Apr 24, 2013
DBA's are recently migrated oracle 10g to oracle 11g, we got some problem in views after it has been migrated to oracle 11g.
A view which is there in oracle 10g is updatable and the same view currently is not updatable in oracle 11g after migration
view is like
CREATE OR REPLACE FORCE VIEW t3_v
(A1,A2,A3)
AS
select aa.a, aa.b, bb.c
form t1_v aa, t2_v bb
[Code]...
t3_v is updatable in oracle 10g, but after migration to oracle 11g, t3_v is not updatable in oracle 11g,
we have checked underlying views t1_v and t2_v both are insertable, updatable and deletable in both oracle 10g and oracle 11g.
we checked constraints also in oracle 11g, everything is same as in oracle 10g.
View 4 Replies
View Related
Sep 12, 2013
I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies
View Related
Sep 4, 2009
i need to set up a central server with all the master tables and two other local database which will hold the updatable materialized view of the master table...the databases must be synchronized with central server..and user will work on the materialized view database...
View 10 Replies
View Related
Aug 18, 2013
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?
View 0 Replies
View Related
Jul 18, 2011
how to change form in updatable mode?
View 1 Replies
View Related
Mar 28, 2013
I have a form which utilizes 2 canvases that the user can toggle between. There is a database column that I would like to have appear on both and be updatable from either place. In my search here first, I found where I could set up a non-database item and copy to it at the point of Post-Query...and that comes close, but I need both columns to not just reflect the db column but be able to update it.
I am about to try using a second trigger to move things from a non-DB column to a DB-column next, but just wondered if there is a better way. When I first compiled with the designer the duplicate column I set up as a DB column also. It only gave me warnings (that I could have lived with) but the ultimate compile my system does outside of the designer calls it an error.
View 7 Replies
View Related
Jan 8, 2013
I have master and detail table , in detail table i want to build tabular form manual , i want to insert , delete and update in detail manual tabular.
tables i use :
CREATE TABLE "DISTRB_PLAN"
( "PLAN_ID" NUMBER NOT NULL ENABLE,
"PLAN_NAME" VARCHAR2(200),
"PLAN_DESC" VARCHAR2(2000),
"PLAN_NOTES" VARCHAR2(2000),
CONSTRAINT "DISTRB_PLAN_PK" PRIMARY KEY ("PLAN_ID") ENABLE
[code]....
View 6 Replies
View Related
Aug 20, 2009
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.
DROP MATERIALIZED VIEW LOG ON ABC;
DROP MATERIALIZED VIEW MV_ABC;
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh
View 6 Replies
View Related
Oct 11, 2013
navigate from interactive report to different forms..
View 10 Replies
View Related
Apr 17, 2013
Oracle 10, Apex 4.0.
Working on this query:
SELECT waarde1,waarde2,
APEX_ITEM.POPUP_FROM_QUERY (3,waarde3,'select select ((waarde1-0.1)+(level*0.1)) d, ((waarde1-0.1)+(level*0.1)) r from (select * from lov_test where waarde1 = c001) connect by level <= (((waarde2-waarde1) *10)+waarde1)') waarde3
FROM lov_test
ORDER BY 1
The idee is to get a popup or dropdown box for "waarde3" in witch the selectable values are waarde1 to waarde2 rising with 0.1 at a time.
And this in a multi row updatable report.Example
waarde1 waarde 2 waarde3
(possible values in the dropdown box)
1 4 1-1.1-1.2 ... 3.8-3.9-4.0
3 7 3-3.1-3.2 ... 6.8-6.9-7.0
The error I get is:Error in init lov: ORA-00936: Ontbrekende uitdrukking. p_lov:select select ((waarde1-0.1)+(level*0.1)) d, ((waarde1-0.1)+(level*0.1)) r from (select * from lov_test where waarde1 = c001) connect by level <= (((waarde2-waarde1) *10)+waarde1)wwv_flow_security.g_security_group_id:1264429985836387wwv_flow_security.g_curr_flow_security_group_id:1264429985836387 Unable to initialize query. For every row in the table lov_test.
View 5 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Apr 20, 2012
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.
View 4 Replies
View Related
Dec 11, 2012
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
View 5 Replies
View Related
Oct 8, 2010
is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies
View Related
Apr 25, 2012
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
View 6 Replies
View Related
May 22, 2011
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.
View 3 Replies
View Related
Jan 6, 2012
I need to list all AUDIT command issued.I'm using the DBA_STMT_AUDIT_OPTS view, but it doesn't list all of them.
For example if a run this command:audit create session by my_user;
the DBA_STMT_AUDIT_OPTS will list it. But if I run this command:audit select on my_user.my_table;
The DBA_STMT_AUDIT_OPTS list only the first.
Why the DBA_STMT_AUDIT_OPTS doesn't list the second audit command?How can I track all the AUDIT commands issued?
View 4 Replies
View Related
Mar 8, 2011
how to set up alerts on specific audit log results without using Audit Vault?
View 1 Replies
View Related
Jul 5, 2012
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$';
TABLESPACE_NAME OWNER
------------------------------ ------------------------------
AUDIT_DATA SYS
View 4 Replies
View Related
Oct 14, 2008
To write a audit script, that will analyze SQL usage and the performance of both individual statements and the overall memory utilization.
View 1 Replies
View Related
Oct 7, 2011
I want to make Audit Trail on every DML operation users does on record. I have many more tables but I mention some example tables.
My Tables are
Invoice Table
Ref_Number char(12), -- Auto Generated
Invoice_No char(12),
Date Date,
Date_Created Date,
Created_By char(50),
Modify_By char(50),
Date_Modified date,
Amount number;
Receipt Header Table
Receipt_No Char (12),--Auto Generated
Date Date,
Date_Created Date,
Created_By char(50),
Modify_By char(50)
Date_Modified date;
Receipt Detail Table
Receipt_No Char (12),
Serial_No,
Doc_Number Char(12),
Date_Created Date,
Created_By char(50),
Modify_By char(50),
Date_Modified date;
View 5 Replies
View Related
May 31, 2012
is it possible to disable audit logging (audit_file)? (Ora 11)
View 27 Replies
View Related
Dec 17, 2011
i enabled auditing by setting
audit_trail=db,extended in spfile,and restart the database and after that i issued command to audit as below :
audit select on emp by access;
but how to see audit log information(ie from which table),i checked dba_audit_trail but it doesnot have any record.
View 4 Replies
View Related
Sep 28, 2011
In our database without enabling audit file, we are getting audit file.find the audit parameter output
SQL> show parameter aud
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /app/oracle10g/admin/ytr/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
View 1 Replies
View Related
Oct 4, 2012
We got an apps that queries an EMP table , using select * from EMP where fname like '%SCOTT%' and Lname like '%TIGER%';
How do I list all the hits being encountered by the query within the day? Is it available in the flashback query or archived logs or anywhere in the database?
View 10 Replies
View Related
Feb 27, 2013
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.*,
[Code]....
View 8 Replies
View Related
Aug 27, 2013
1.) Whatever statics/data should we gather to compare the database performance before and after enable the audit on database level?
2.) How can we enable audit on database level?
3.)what is pros and crons of enable audit database level?
View 1 Replies
View Related
Feb 19, 2013
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
[Code].....
View 10 Replies
View Related
Jun 7, 2011
I am trying to setup logon/logoff auditing for our databases which reside in 9i and 10G on sun solaris servers. I am asked to turn on auditing sending the audit data to syslog! How exactly do you do that?
View 3 Replies
View Related
