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].....
is there any query to find number of primary keys present in a table.
View 13 Replies View RelatedInside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.
In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.
how will i take backup including with primary keys?
the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....
This is how the table creation with the keys looks right now...
CREATE TABLE CAMPUS (
CAMPUSID NUMBER(4) NOT NULL,
NAME VARCHAR2(80) NOT NULL,
CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));
[code]...
I used
SELECT USER_CONS_COLUMNS.CONSTRAINT_NAME,
COLUMN_NAME,
CONSTRAINT_TYPE,
[Code]...
to create a parent key table sort of thing, but for some reason there are rows that display multiple times
CONSTRAINT_NAME COLUMN_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME DELETE_RU
-------------------- -------------------- --------------- ------------------------------ ---------
SYS_C00135225 PATIENT_STATUS C
SYS_C00135226 BALANCE C
SYS_C00135227 PATIENT_STATUS C
PK_PATIENT PATIENT_NUMBER C
[Code] ..........
is it something to do with the join command
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 RelatedI 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.
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]....
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.
View 4 Replies View RelatedI'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]...
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?
View 0 Replies View RelatedI 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.
View 1 Replies View Relatedprimary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies View Relatedwe apply partitioning concept on a table which don't have any primary key ?
I just want to add one more field as primary key with some sequence generated values while partitioning ? Is it possible ?
how to add a primary key into the oracle table but how do i add a partial primary key?
View 2 Replies View RelatedIs is required to check the number of rows updated in a table when the primary key of the table is used in the filter criteria of the update statement? As I know,by default it will update only one record. But if it happens to be an important transaction table and only one record is required to be updated, then is it the best practice to use the 'SQL%ROWCOUNT' check in the query, even if the update query is using primary key in filter clause.
Example:Consider Trans table with trans_id as primary key. Then:
Update Trans
set trans_status='pass'
where trans_id=123;
I know this will update only one record. But what is the best practice? Shall I use 'SQL%ROWCOUNT' after this update to double check whether the record is updated or not?
Is it possible to apply primary key on table having some duplicate record?I can do this by deleting duplicate record, But I don't want to delete exisitng data.
View 10 Replies View RelatedI would like to reorganize a table inorder of primary key but I'm not sure if I'm expecting the right thing from dbms_redefinition package.
I am working on oracle 9i 9.2.0.8
I have the following table :
SELECT * from SCOTT.TESTTABLE
ID REF
---------- ----------
1 FF
2 BB
3 CC
4 DD
8 EE
6 ZZ
7 YY
5 GG
when I use the CODEexec dbms_redefinition.start_redef_table('SCOTT', 'TESTTABLE', 'TESTTABLE2', 'id id, ref ref', dbms_redefinition.cons_use_pk);
Would the newly created scott.testtable be created in order of primary key (ID) thus a select * from scott.testtable will give me an ordered result?
When I do the test, the table before and after the redefinition is exactly the same so why use the CONS_USE_PK if it doesn't order the table by primary key?
I have normal tables with hugh Data and would like to increase the performace by following means:
1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.
I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.
I have an employee table which has a primary key and a self referencing foreign key, as shown here
create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Now if i make the primary key composite, as shown below -
create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Oracle is throwing the following error -
ORA-02270: no matching unique or primary key for this column-list
How can this error be fixed without changing the composite primary key?
I've a staging table STG_TABLEA which has a primary key discount_seq_no.
I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.
I am using Oracle 11.2.0.2 version.
I've put together the below code, not sure on next steps...
BEGIN
UPDATE STG_TABLEA
SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
I have to create primary key using local on a partitioned table.
Since the table is huge it has to be done parallel. Following stmt is giving error
alter table XYZ add CONSTRAINT PKN_XYZ PRIMARY KEY (ID,LogDtTm)
USING INDEX LOCAL parallel 25 INITRANS 5 TABLESPACE OLTP_IDX_TS ;
I am trying to create snapshot using the following script but I am getting the error
ERROR at line 22:ORA-12014: table 'CDA_FUNCTION' does not contain a primary key constraint
SQL> CREATE SNAPSHOT MEDA.cda_function
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 40960
[code]....
I check in dba_constraint data dictionary table for the constraints in the table CDA_FUNCTION.it is showing as follows
owner constraint_name constraint_type table_name search_condition status deferrable deferred validated generated
WEDA SYS_C0032310 C CDA_FUNCTION (LONG) ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME
WEDA SYS_
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies View Related