PL/SQL :: Client Identifier In Web App For Use In Audit Trigger?
Feb 1, 2013
in my java web app running on Weblogic which uses connection pooling, I am calling an Oracle Stored Proc that sets client identifier to userid using dbms_session.set_identifier
I then retrieve it in triggers attached to db tables to capture before-after image
I must have missed a step because this is what I find (testing locally):
1. I login to my app as USER1 and update some data
2. I check the audit tables and sure enough last_updated_by is set to USER1
3. Without logging out, I start a different browser (Firefox, in step 1 I used Chrome) and login as USER2
4. I now switch to USER1 session in Chrome browser (still logged in as USER1) and update some data
5. This time I see that the audit tables show last_updated_by as USER2 (instead of USER1)
View 11 Replies
ADVERTISEMENT
Jul 19, 2011
I'm trying to create trigger:
CREATE OR REPLACE TRIGGER TBI_ID
BEFORE
INSERT
ON zoo.risk_eval#ctypein
[code]...
Here is error message:
ORA-24344: success with compilation error
2/14 PLS-00201: identifier 'ZOO.RISK_EVAL_CTYPEIN#ID_SEQ' must be declared
2/3 PL/SQL: Statement ignored
View 10 Replies
View Related
Jun 3, 2010
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,
[code]...
View 5 Replies
View Related
Jun 13, 2010
I want to make a trigger , to store values that are affected during any DML(insert,update,delete).
CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE
[code]....
View 8 Replies
View Related
Nov 21, 2012
I'm not able to login to PL/SQL Developer 7.0.3.1123.I'm receiving "ORA-12154: TNS: could not resolve the connect identifier specified" error. Although it lists all TNS entries in Database drop down.It was working fine last week but now I'm not able to login.
View 10 Replies
View Related
Apr 11, 2011
Is there any tool available for audit records from a specific Machine/terminal Only?
View 1 Replies
View Related
Feb 24, 2010
it seems to me that the file tnsnames.ora is not read when i execute sqlplus.whit the command: sqlplus username / password @ servicename i receive the error ORA-12154, while if i pass the whole connection string i can connect without problems
sqlplus username/password@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oradev10)(PORT = 10520))(CONNECT_DATA = (SERVICE_NAME = D10)))
i set the ORACLE_HOME environment variable correctly... what more i have to do? following is my tnsnames.ora
TOTEMPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ttf19.cern.ch)(PORT = 1521))
[code]...
View 6 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
Aug 5, 2010
why my logon trigger always creates 2 records with different timestamp. Also is this the proper way of excepting records to insert?
Script.
CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
If user<> 'DBSNMP' then -- don't want to insert this
if user <> 'SYS' then -- don't want to insert this
[code]....
Result when select.
OS_USERNAME LOGON_TIMESTAMP
----------- ----------------
GAN 2010-08-05 14:27:52
GAN 2010-08-05 14:27:55
View 12 Replies
View Related
Jul 1, 2011
How can "call one trigger of item in trigger of form"
View 5 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
Oct 30, 2011
I am trying to do an update with an anonymous PL/SQL block because it has been absolutely impossible to get by the infamous "Single-row subquery returns multiple rows" Whatever.So this code below work... Obviously, I am updating it to a constant.. OK, duh.. Of course it works, it's easy.... Now, I really need to update it to a value in another table that is in my cursor. I believe that I probably need to declare a secondary cursor.
I'm just getting back in the DBA saddle, so I'm a little rusty..
set serveroutput on;
DECLARE
numrows NUMBER := 0;
total NUMBER := 0;
CURSOR upd_record_cur IS SELECT m.rowid, m.swcm_cycle, t.newcycle, p.aprem_no from toad.fswcmas m,
[code]...
Not so good here
ORA-06550: line 20, column 53:
PL/SQL: ORA-00904: "TOAD"."TTP43425_LOAD"."NEWCYCLE": invalid identifier
ORA-06550: line 20, column 10:
PL/SQL: SQL Statement ignored
Doesn't work...
BEGIN
FOR upd_rec IN upd_record_cur LOOP
update toad.fswcmas sw set sw.swcm_cycle = toad.ttp43425_load.newcycle
WHERE rowid = upd_rec.rowid;
total := total + 1;
View 17 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
Feb 5, 2009
I am attempting to run a query to pull some data to fill a data request. However I keep getting the following error, "ERROR at line 1:ORA-00972: identifier is too long."
select FS_CORRESPONDENCE_CODE||';'||CM_FILER_SEQ||';'||CM_ORGNAME||';'||CM_FILER_CODE||';'||
CM_PARTY_CODE||';'||RC_LNAME||';'||RC_FNAME||';'||RC_ORGNAME||';'||RC_MAILADDR1||';'||
RC_MAILADDR2||';'||RC_CITY||';'||RC_STATE||";'||RC_ZIP||';'||RC_OCCUPATION||';'||
RC_EMPLOYER||';'||RC_AMT||';'||
from TREFCM, trefrc, treffs
WHERE CM_FILER_SEQ = RC_FILER_SEQ
[code]....
View 3 Replies
View Related
Oct 30, 2008
I am trying to execute the below and getting the error:
PLS-00201: identifier 'DBMS.REFRESH' must be declared
The mv is in my schema.. so I am owner. I thought I once read that a dbms.refresh had to be in a block.. so I added the begin and end.. but that didn't resolve it.
I have tried two ways, both resulting in the above error:
execute dbms.refresh('mv_bb_basket');
begin
dbms.refresh('mv_bb_basket');
end;
View 1 Replies
View Related
Jul 14, 2012
SQL> alter system "_allow_level_without_connect_by"=true scope=spfile;
alter system "_allow_level_without_connect_by"=true scope=spfile
*
ERROR at line 1:
ORA-00972: identifier is too long
View 11 Replies
View Related
Oct 18, 2013
I'm having an issue creating a table which references a larger table of mine.It gives me the error:
ORA-00904: "EID": invalid identifier
when issuing the command:
CREATE TABLE Phone (P_num CHAR(7), P_type VARCHAR2(10), PRIMARY KEY(P_num), FOREIGN KEY(EID) REFERENCES Employee ON DELETE CASCADE);
I need the employee id (EID) to be the primary key for my table Employee, as described here:
DESC Employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NOT NULL CHAR(7)
E_NAME VARCHAR2(15)
E_ADDRESS VARCHAR2(30)
And since Phone has its own primary key of Pnum, I find it weird that it won't let me reference Phone back to Employee using EID.
View 12 Replies
View Related
May 22, 2013
When I am running script in oracle 10g ,getting error message as "PLS-00201: identifier 'UTL_SMTP' must be declared".
how to declare identifier 'UTL_SMTP' in oracle 10g or how to give execute access to current user
View 6 Replies
View Related
Aug 3, 2011
I need export data from remote server i.e. in Oracle8i envtt. but in my machine having client oracle10g.
I m using command from local machine :
expdp test/test@test_env_tns dumpfile=abcd_dat.dat logfile='abcd.log
then i m getting error :
UDE-00008: operation generated ORACLE error 6550
ORA-06550: line 1, column 52:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 52:
PL/SQL: Statement ignored
[/code]
Note : I m executing command from my local machine command prompt
View 8 Replies
View Related
Jul 16, 2012
I am using oracle 10g database (10.2.0.1.0), which trouugh an error while i am trying to EXPORT.
View 4 Replies
View Related
