SQL & PL/SQL :: How To Delete Data Without Disable Constraints
Feb 14, 2013
I want to delete data from say 100 tables without disable the constraints.
Tables having foreign key.. which is giving error if I select table randomly to delete.
I want to know the query which will give me the sequence of all tables based upon dependencies, so that If i delete the data on that sequence, it wont give me child record exists error.
how to disable and enable all constraints pertaining to one table at once. I want to disable them at once and also enable them at once. I make use of TOAD.
I want to create a store procedure to copy data from a source tables(which may not have any constraints defined) to a table which has primary key, foreign key and unique key constraints.
Any records which are rejected due to these constraints not being satisfied need to go another table.
Once the initial data load is done, these procedures need to be automated(through cron) to do the future incremental uploads in the same manner.
I have some tables (below), but I'm having trouble entering multiple lines of data.Here are the tables I have created.
Quote: CUSTOMER table SQL> create table customer 2 (customer_no char(6) not null, 3 name varchar2(30) not null, 4 address varchar2(50) not null, 5 credit_limit number(6,2), 6 constraint customer_customer_no_pk primary key (customer_no));
Table created. SALESMAN table SQL> create table salesman 2 (salesman_id char(8), 3 name varchar2(20) not null, 4 address varchar2(50) not null, 5 emaill_address varchar2(30), 6 constraint salesman_salesman_id_pk primary key (salesman_id));
Table created. ITEM table SQL> create table item 2 (ISBN char(13) not null, 3 title varchar2(30) not null, 4 price number(4,2) not null, 5 constraint item_ISBN_pk primary key (ISBN));
Table created. INVOICE table SQL> create table invoice 2 (invoice_no char(1) not null, 3 invoice_date date not null, 4 salesman_id char(8),
[code]...
Table created. DELIVERY table SQL> create table delivery 2 (invoice_no char(1) not null, 3 ISBN char(13) not null,
[code]...
Table created. I can enter data into the customer, salesman and item table without any problems. However, I need to enter multiple lines of data using the same invoice_no (which is 1). The data is generally the same, except for 2 things need changing but I keep getting the following error...
With 11gr2, by default, on STARTUP, the standby database is open -> in READ ONLY mode -> with Intended State: APPLY-ON
so the ACTIVE DATAGUARD option is in use ....
is there a way to deactivate REAL TIME QUERY permanently, so whether on STARTUP or STARTUP MOUNT, the standby stay only mounted with Intended State: APPLY-ON
The only way i found is to do the following :
SQL> startup DGMGRL> edit database 'PHNXENT' set state='APPLY-OFF'; then SQL> startup mount DGMGRL> edit database 'PHNXENT' set state='APPLY-ON';
I am trying to disable mouse navigation on data block or text item,. What i want is to my text items in specified block be keyboard navigable only.
The problem is that i don't have that option on text_items or data block (my conclusion: probably because it is not possible to change mouse navigate option for text item or data block).
I have also tried in WHEN-NEW-BLOCK-INSTANCE trigger
From 28/AUG/2013 to 30/AUG/2013 records has to be deleted becuase thease records are two weeks old.
From 31/AUG/2013 to 06/SEP/2013 records belongs to one week and from 07/SEP/2013 to 13/SEP/2013 belongs to another week.Today is 12/SEP/2013 so from 06/SEP/2013 to 12/SEP/2013 should be existed in the table.Once date changed to 13/SEP/2013 then from 31/AUG/2013 to 06/SEP/2013 records has to be deleted.
I am posting table and insert scripts.
CREATE TABLE process_data(p_id NUMBER,CREATED_DT DATE); INSERT INTO process_data VALUES(TO_DATE('28/AUG/2013','DD/MON/YYYY'); INSERT INTO process_data VALUES(TO_DATE('29/AUG/2013','DD/MON/YYYY'); INSERT INTO process_data VALUES(TO_DATE('30/AUG/2013','DD/MON/YYYY'); INSERT INTO process_data VALUES(TO_DATE('31/AUG/2013','DD/MON/YYYY'); INSERT INTO process_data VALUES(TO_DATE('01/SEP/2013','DD/MON/YYYY');
I have a table with around 650,000,000 rows and we need to delete about 60,000,000 rows at the end every month and same amount of rows accumulate throughout the month. The deletion usually takes overnight to delete. We are using 10r2 in IBM AIX. The procedure we are using to delete is:
declare ln_count number:=0; begin for i in (select rowid from table1 where some_id<2012090000) loop delete from table1
[code]...
When this procedure is started I mostly see that the session is busy in user i/o wait for db sequencial file read. Will using cursor instead will give better results.
I need to create a stored procedure in Oracle 9i which will automatically delete data one by one from a particular table and then by means of same procedure insert record one by one in same table.
am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate
e.g
Jan 1 - 30 deletes 2 - 30 keeps data for 1st Feb 1 - 28 deletes 2 - 28 keeps data for 1st Mar Apr Current sysdate May
We are planning to setup a data guard (Maximum performance configuration ) between two Oracle 9i databases on two different servers.
The archive logs on the primary servers are deleted via a RMAN job bases on a policy , just wondering how I should delete the archive logs that are shipped to the standby.
Is putting a cron job on the standby to delete archive logs that are say 2 days old the proper approach or is there a built in data guard option that would some how allow archive logs that are no longer needed or are two days old deleted automatically.
i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.
I want to delete the records which are less than or equal to this date
MIN(TRUNC(sale_week_date)-(1/(24*60*60)))
For that I have created the following procedure.
CREATE OR REPLACE PROCEDURE DELETE_DATA_WEEKISMIN_DATE DATE;SELECT MIN(TRUNC(sale_week_date)-(1/(24*60*60))) INTO MIN_DATE FROM sales_week; DBMS_OUTPUT.PUT_LINE(MIN_DATE); DELETE FROM sales WHERE sale_date <= MIN_DATEAND sales_creation_date <= MIN_DATEAND sales_update_date <= MIN_DATE; END;
If declare as MIN_DATE Date;
it's not taking time. If declare as MIN_DATE Timestamp; It is taking milli seconds also. 18-OCT-13 11.59.59.000000 PM SALE_DATE ,SALES_CREATION_DATE and SALES_UPDATE_DATE in this format 18-OCT-2013 11.59.59 AM. How to delete the records which are less than or equal to this date MIN(TRUNC(sale_week_date)-(1/(24*60*60))) ..
CREATE TABLE "TEST_JET" ("K1" NUMBER, "K2" NUMBER, "K3" NUMBER, "K4" VARCHAR2(1)) ; REM INSERTING into TEST_JET Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'I'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,3,5,'I'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T'); [code]....
based on the above result set , for a particular group ,only that op will be retained which comes out in the query . say for example , we have got 1,2,3,'D' for group 1,2,3
now since we have got the D Operation from the above query , i don't need the other two rows .i.e. (1,2,3,'I'); (1,2,3,'U');
what is the best way to delete the data we don't want retaining the rows we want ,using a single sql statement . Also , for the result set row 7,7,7,T I first need to delete the group containing T operation, and insert two new rows .i.e. 7,7,7,D and 7,7,7,I .
I have a table which contains 8,21,177 amount of data totally.Now I am trying to delete around 4,84,000 of data from this table by using just one filter i.e. my query is something like below
DELETE /*+ parallel(resource,4) */ FROM resource where created_by = 'MIGN'
This is going to delete 4,84,000 rows of data . But my current issue is this is taking lots of time to delete the data . To be precise , its almost taking 25 hours to delete this data..The created_by column is indexed .
Execution Plan ---------------------------------------------------------- Plan hash value: 2389236532
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------
On next month i will have plan to delete history data on my Primary database, but i still want to keep them in logical standby as the data warehouse. So, how can i skip delete statement from primary database apply to logical standby.
Oracle SQL apply only support for Skip DML but not for keep delete statement
(on 10g and 11g)SQL>exec dbms_logstdby.skip(statement => 'DELETE',schema_name => 'SCOTT', object_name => 'DEPT');BEGIN dbms_logstdby.skip(statement => 'DELETE',schema_name => 'SCOTT', object_name => 'DEPT'); END; *ERROR at line 1:ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'SKIP'ORA-06550: line 1, column 7:PL/SQL:
We have two Windows 2008 VMs with Oracle 11G configured as primary and standby.This configuration has been defined by a partner who is no longer working for us.We have broken the correct application of the redo log on the standby when we have not correctly restored some snapshots on those two VMs.I would like to create a new Oracle server that will become the new standby. For that, I need too :
- Display the full dataguard configuration to reproduce it on the new server - Stop (delete ?) the configuration on the primary server.
We also have the current problem that RMAN refuses to delete old archivelog since they have not been applied to the standby.
I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.