SQL & PL/SQL :: Delete All Records From A Recordset In One Go?
Jul 3, 2010
I am selecting around 10000 records in a recordset using an sql query.I need to delete these 10000 records from 10 different tables as each of the table have these records.I can use a FOR Loop for each record one by one to delete these 10K records from each tabel but i was wondering if i can delete there 10K in one go so that the program would Loop Only once rather thatn 10K times.
Mine Delete code looks like:
if x.first is not null then
for i in x.first..x.last loop
delete tbl_A where trade_id = x(i).trade_id;
delete tbl_B where trade_id = x(i).trade_id;
[code]...
Where x is the recordset having around 10K records.Is there any way i can run the loop only once rather than one by one for 10K times.
Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10. The final result that I wish to have is 10 or less than 10 rows of records.
I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.
After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.
The problem is: how can I not use a temporary table. I've search through online about using nest tables. but somehow I just could not get it working.
how to replace the temp table with something else? or is there other approach that I can use to compare the row columns with other rows.
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.
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
Sqty is sold qty , rqty is return qty , sdate is sold date , vno is billno .
When we enter sdate , this will fetch the products sold on the given date , and total sold qty .
If some products are returned which are entered in rqty , then this should delete all the records of the product on that sdate , and these products as spread along several billno's .
My aim is to delete duplicates out of above data, with the below condtions.
1) first record with value 30 and then with value 0. 2) if there are 3 duplicate records ex: ID is 1 and Group KK, then i have to delete both 30 & 0 qty records. 3) If there are more than 3 duplicate records ex: ID is 3 and Group is AA, the i have to delete all the records with qty value either 30 or 0 and.
I have written a query like below.
SELECT id, unit, RANK () OVER (PARTITION BY id, unit ORDER BY id, unit) num FROM temp;
with the above query, i am unable to mark this dynamic duplications.
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexDelete ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA DRG-50857: oracle error in drexrdml ORA-01031: insufficient privileges [code]....
I am using one script to delete the records from a table, its taking 1hr to delete.
declare cursor c1 is select ownerid,ownertype from nightly_metric_projects ; v1 c1%rowtype; open c1; loop fetch c1 into v1; exit when c1%notfound; DELETE FROM DGT_ITEMEFFORTDATA WHERE OWNERTYPE = c1.OWNERTYPE AND OWNERID = c1.OWNERID; end loop; close c1; commit;
nightly_metric_projects--1200 records DGT_ITEMEFFORTDATA--13200000
I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.
how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.
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))) ..
DELETE FROM sre_t WHERE TO_CHAR(end_dt,'yyyy')<'2000' or TO_CHAR(start_dt)<'yyyy')<'2000';
It's executing for 15 to 20 minutes after that i got the error "session timed out"..The table is having four crore records.The delete statement is deleting 12,00000 records.
/* Step2 Create MATERIALIZED VIEW Refresh after Every 20 Minutes */ CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST START WITH SYSDATE NEXT SYSDATE+20/ (24*60) AS SELECT * FROM ABC WHERE TMSTP > SYSDATE-1;
Scenario:Intially data extracted will be "Greater than SYSDATE-1 = 18/08/2009 11:27:39 " - 76 Millions of Records After 20 Min of FAST Refresh, it will add 2500 Records
i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?
ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh. Is there any alternative way to achieve the Nature of COMPLETE REFRESH in Materialized View?
iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?
iv)What is the machnism for populating the logs for MVIEWS?Is it overhead for the system and better than a Trigger based approach?
Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.In my project every table has an associated MLOG$ table for it.
Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesn't free much space.If this is oracle internal table is it advisable to delete from MLOG$ tables too.
My oracle database is mounted on the Linux server.If I delete bulk records in tables from which path I can see how much space is freed.
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE: CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT ( CALENDAR_KEY INTEGER NOT NULL, DAY_TIME_KEY INTEGER NOT NULL, SITE_KEY NUMBER NOT NULL, RESERVATION_AGENT_KEY INTEGER NOT NULL, LOSS_CODE VARCHAR2(30) NOT NULL, PROP_ID VARCHAR2(5) NOT NULL, [code].....
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
We have a bunch of tables with FKs but we do not have the 'on delete cascade' option on these FKs. If I want to delete records in the database do I have to write delete statements for each table starting with the lowest child or is there a cascade option I can use with the delete statement?
what is the error in this procedure.I am getting error where s.msg_id = Tbl_Repo_Salo (i.msg_id));
Create or replace Procedure Proc_Stg_Clear(P_Err_Code OUT NOCOPY number, P_err_msg OUT NOCOPY varchar2) as v_count number; Type Repo_Salo is table of Tbl_Tml_msg_Salo_Stg%ROWTYPE; Type Repo_Smb is table of Tbl_Tml_msg_SMB_Stg%ROWTYPE;
i have one question that why oracle not set high water mark when we delete data from a table and commit it, on the other hand it set in case of truncate.both of these statement release physical structure(in case of delete after commiting)
i have a problem that i have created a table named PROJECT and structure is like- CREATE TABLE PROJECT ( id VARCHAR2(250 BYTE) NOT NULL, NAME VARCHAR2(250 BYTE) NOT NULL, COMPID NUMBER NOT NULL,
[code]...
and this table is created in some number of different database then i want to delete the not null constraint of "id" column and there is 3 not null constraint in my table and i didnot gave name at the time of creation, so we cant delete it by its name in the script because there will be different name in another database for the same constraint. i want to make a script to delete the not null constraint because it is the part of my script.
how should i get proper information for deleting the constraint. and i used "user_constraints" but it shows same information for the all three "not null" constraint.
this is my trigger..SQL> create or replace trigger drop_trigger
2 before drop on database 3 declare 4 PRAGMA AUTONOMOUS_TRANSACTION; 5 BEGIN 6 INSERT INTO ddl_log 7 SELECT ora_sysevent,ora_dict_obj_type, ora_dict_obj_owner, 8 ora_dict_obj_name,USER,SYSDATE,to_char(sysdate,'hh24:mi:ss') 9 FROM dual; 10 commit; 11 raise_application_error(-20090,'you can not drop'); 12 end; 13 /
DÚclencheur crÚÚ. when a user enter a "drop table" for exemple, the trigger is run but we have this error message
SQL> drop table fg; drop table fg * ERREUR Ó la ligne 1 : ORA-00604: une erreur s'est produite au niveau SQL rÚcursif 1 ORA-20090: you can not drop ORA-06512: Ó ligne 9
My Question is, since this 3 indexes has two columns "Bus_unit and Md_code", which index can i delete. Will they effect on database performance, if i delete one of them?
From plan I would expect the delete command to be processed very quickly, however it does huge amounts of db block gets. I assume this is because of deleting the row from other structures (indexes, foreign keys in tables etc.) How can I find out which object scan caused the db block gets ?
SQL> delete from messages2 where id = 11004240718; 1 row deleted. Elapsed: 00:00:03.79 Execution Plan ---------------------------------------------------------- Plan hash value: 140582024
