I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
I have the following requirement and we are on R12.1.3 with Oracle 11gR2 database on a Linux R5 machine.
1) Sensitive data such as SSN and credit card needs to masked in the oracle seeded table like per_all_people_f etc.
2) This original data should not be visible either from the database (apps user) or via the application. They should both provide the decrypted data to the users.
3) There are a set of users that need point # 2 overridden. That is, they should be able to see the decrypted data using sql on the tables or via the application (front end).
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
Is there any tool available for audit records from a specific Machine/terminal Only?
View 1 Replies View RelatedIs there a way where I can check users/IPs who connect to the database in a specific time 2 days ago?
View 5 Replies View RelatedI need to trace all sessions created from specific user and specific machine . Got some code from internet and customised .
CREATE OR REPLACE TRIGGER SYS.enable_trace_trigger
AFTER LOGON ON DATABASE
DECLARE
[Code]....
I have yet not created it on db .
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies View Relatedi have IR report page and download column as blob,how can security applied in specific column wise?e.g. Report page have more rows, and applied query in condition.
Select ID,dbms_lob.getlength(Blob_file) Download from Document_master
where Created_by=UPPER(:APP_USER)
OR
(exists (select '' from apex_workspace_group_users awgu
where awgu.user_name =:app_user AND awgu.GROUP_NAME='EMPLOYEE_GROUP'
)
)
Now all the rows with Download column to see EMPLOYEE_GROUP users,but i need control the download column only except Created_by=UPPER(:APP_USER) ,this case how can do the security.
i'm creating a website where i can search between 2 specific dates which user will enter in the format of DD-MM-YYYY. the 2 dates will be inserted into 2 fields which is $input1 and $input2.
extract($_POST);
//to set the date format
//***************************
$input1 = date('DD-MM-YYYY h:i:s');
$input2 = date('DD-MM-YYYY h:i:s');
[code]...
but i get the error message saying...Warning: odbc_exec() [function.odbc-exec]: SQL error: [Oracle][ODBC]Option value changed., SQL state 01S02 in SQLExecDirect in C:phpwwwaaas_bbb.php on line 86...Error in SQL statement
I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?
View 7 Replies View RelatedHow to identify the specific sequence name applied on a table.
View 4 Replies View RelatedIn details table i am having loc is column in which data is like this(Jones,180),(US,host name),(job, company),(id,0122)
output should be it should take only first value from ().
tell the syntax for setting pctfree and pct used for a specific table.
View 4 Replies View RelatedI want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.
For Example:
Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....
Now i want USA and CANADA on top in that order and then other in alphabetized order.
I need to create a table which contains the details of what companies a specific seeker applied to when he logs in into his account.... Also, when an employer logs in, he needs to get the list of seekers who applied to him...give database schema required for such a situation asap.Say, the user has a primary key UID, and each employer has a primary key EID..
View 1 Replies View RelatedOur oracle database has been running great. This past wendesday the application was unable to access a specific table. Sutting down and restarting the database seemed to have fixed the issue. Last night the database has been working since that restart on wednesday morning. Suddenly stopped working. We stopped and restarted the database again this time getting ORA-00600 error as shown below. We are able to make a connection to the database and can even access the tables in question. The application is complaining about the connection to the database. I understand that ORA-00600 is an internal error what I need to know is what the internal error code 549 actually is. I have tried googling to no avail.
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:47:20 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
ORA-00600: internal error code, arguments: [549], [], [], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\tieoduser>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:49:31 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
[code]....
Let's say we have Table - A and we would like to replicate specific row transaction to Table B.
Here are the rows in *Table A*
Time: Lets say 15:00
A1 Just Updated @15:00
A2 Just inserted @15:01
A3
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B2 -
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05
B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
Current Data in Table B (Before Replication) @15:00
A1 (without updates)
A3
B1
B2
B3
B4
B5
B6
Expected rows in Table B (via replication/snapshot/materialized view / or any other method)
*Replication at 15:30*
Table B - Read Only
Expected rows after replication-
A1 -- Newly updated details
A2 -- Newly inserted row
A3
B1 - Daily delete row is expected to be replicated
B2
B3 - Daily delete row is expected to be replicated
***Note row B4 is not expected to be replicated to table B.
Questions:
1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?
can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
I want to insert only specific number of columns into a table by using Bulk collect and Forall.
SQL> create or replace procedure bifa_proc
2 is
3 type etab is table of emp%rowtype index by binary_integer;
4 erec etab;
5 cursor c is select * from emp;
6 begin
7 open c;
[code]...
Warning: Procedure created with compilation errors.
PLS-00382: expression is of wrong type
PLS-00436: implementation restriction: cannot reference fields of
BULK In-BIND table of records
How to insert specific number of columns without declaring multiple table type definitions for each column by using bulk collect and forall.
Can we do a specfic word count in sql. Requirement is there is a string "I am very very poor in sql" the word "very" occurs two times, so the result should be 2.
View 16 Replies View RelatedI have a requirement with source table has duplicates so i need to select a row based upon the below condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
I am using oracle11g. I want to truncate subpartition on specific partion.
I have partition on statewise. Each state partion has 7 day sub partition.
For intance,
Partion TX
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion CA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion IA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Now i want to perform following tasks.
1. Need to truncate TUE sub partiion on TX partition.
2. Need to truncate WED sub partiion on CA partition.
3. Need to truncate SUN sub partiion on IA partition.
How do we do this?
The below statment truncate all TUE partition on all the partitions.
ALTER TABLE TRX_TABLE
TRUNCATE SUBPARTITION TUE;
How do i tuncate specfic sub partition on specific partition?
query only specific date only. example: '06-MAY-2010'.
1.from the statement below, it will display out more than 06-mAY-2010.
2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.
select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE
CURSOR c_type_id_usg
IS
[Code]....
-- get the list of sms type id usg values
OPEN c_type_id_usg;
FETCH c_type_id_usg bulk collect into l_type_ids;
CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310)
then
dbms_output.put_line('I have found an entry ');
else
dbms_output.put_line('I have NOT found an entry ' );
end if;
END;
Say, a table has existing columns of A, B, D, and E.Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.
View 6 Replies View RelatedHow to add a new column to specified position in a existing table.I have using the oracle database 10g.This below code is not working in oracle 10 g
example:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER FIRST:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER AFTER JOB:
I have a requirement with source table has duplicates so i need to select a row based upon the a condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
I'am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit with the following:
db_flashback_retention_target 1440
undo_management AUTO
undo_retention 900
Is it possible to enable flashback only to some specific tables?
For example, we are only interested in the capability of flashback tables scott.emp and scott.dept, no more.
I have 1M Records coming from an External Data source as a Flat File (using ETL). Now I need only Yesterday's data only to load in my Database Table.
this can be done using Bulk Load and Filter.
write the CODE.
Second Part:-
Hint: if I need to update only those records been updated Say the Address1 field is updated. So this records need to update in my Master Customer Table.
If I have many fields in table and any records that are modified (coming to me from External Datasource as a Flat file) how to identify and update that record in my Master Customer Table?
i cant find any oracle specific methods for chunking data -if i had returned rows of numbers, how could i chunk them into ranges?
View 2 Replies View Related