PL/SQL :: DB Block Gets While Delete?

May 22, 2013

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

[code]....

View 25 Replies


ADVERTISEMENT

Forms :: Delete Detail Block Single Record

Apr 22, 2010

My form consist two datablock cust_mstr,cust_dtls, each customer code consists two or three contact details records, when i delete the single detail record instead of deleting single record it deletes all the details records from cust_detail table.

Here by i attaching my form for your reference pls find the code in delete button

View 12 Replies View Related

PL/SQL :: To Delete Child Records Manually Without Using Oracle Delete Cascade

Oct 9, 2012

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.

View 9 Replies View Related

Forms :: Multi Data Block And Checkbox Field Based On Control Block

Nov 19, 2010

i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field

e.g

item11 item21 item31 chkbox1
item12 item22 item32 chkbox2

Scenario like this :

My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field

when i checked chkbox1 , my cursor goes to item31...not item32

View 12 Replies View Related

Forms :: Distribute Master Block Total Amount In Detail Block Line Wise

May 5, 2012

i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example

Cheque amount in master block = 291

Invoice wise receiveable amount is as follows

Invoice No , receivable amount , Received amount

10, 196 , 0
20 , 95 , 0
30 , 54 , 0

Result should be as follows:

Invoice No , receivable amount , Received amount

10, 196, 196
20 , 95 , 95
30 , 54, 0

Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.

View 2 Replies View Related

Backup & Recovery :: Error / Corrupt Block Relative Dba / 0x01c12a58 (file 7 / Block 76376)

Jan 25, 2011

i have an error with block corruption

Error: Corrupt block relative dba: 0x01c12a58 (file 7, block 76376)

What all the ,methods I can go for if we are working on a production environment with out any down time.

I can go for dbms repair package and restore and recover

View 3 Replies View Related

Forms :: How To Read Values In A Multi Record Control Block Using Pre-insert Trigger (block Level)

Jul 24, 2010

I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.

I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".

View 3 Replies View Related

On Delete Cascade

Mar 27, 2007

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?

View 1 Replies View Related

Way To Bulk Delete

May 16, 2011

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;

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Truncate Vs Delete?

Apr 9, 2010

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)

View 7 Replies View Related

SQL & PL/SQL :: How To Delete Constraint

Apr 26, 2012

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.

View 2 Replies View Related

SQL & PL/SQL :: How To Delete ORA 00604

Sep 30, 2010

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

what can i do to delete the ORA-00604??

View 5 Replies View Related

SQL & PL/SQL :: Delete One Particular Field Value In A Row?

Feb 22, 2010

How to delete one particular field value in a row?

Constraint: There should be no null value or zero instead of deleted value. Other values remain unchanged.

View 14 Replies View Related

SQL & PL/SQL :: Delete An Index

Jun 27, 2010

I have a very huge table. There are many indexes.My focus is on the following indexes:

APT_DEDUCTIBLE A_PT_ORDREGBUS_UNIT
APT_DEDUCTIBLE A_PT_ORDREGMD_CODE
APT_DEDUCTIBLE A_PT_ORDREGPT_CODE

[Code]...

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?

View 4 Replies View Related

PL/SQL :: On Delete Cascade?

Oct 27, 2013

, we have used alter table to change the constraint in on delete cascade. there is two option. what is different between both ofthem. Example:1)Alter table t3 add constraint t2_F foreign key (id) references t2(id,column2,column3) on delete cascade ;or2)Alter table t3 add constraint t2_F foreign key (id) references t2 on delete cascade ; 

know first one that particular column will set to on delete cascade.What is the use of second one in this case we are not using column name in "foreign key (id) references t2 on delete cascade ;" 

View 9 Replies View Related

How To Delete SID Name (oracle) From Computer

Oct 29, 2010

How to delete SID name (oracle) from my computer ?

View 7 Replies View Related

Delete Files With Extension Arc

Sep 15, 2010

I can delete the old archives with extension arc. Today, when I consult V$RECOVERY_FILE_DEST, there are files with today date. Can I delete these files, without danger the database?

View 3 Replies View Related

Error When Attempting To Delete

Sep 6, 2010

I'm attempting to write some SQL to delete a record where it doesn't exist in one of three other tables. My SQL is below:

SELECT COUNT(*) FROM GAZETTEER_ADDRESSES
WHERE NOT EXISTS (
SELECT MEDICALS.*, REVIEWS.*, ADDRESS_HISTORIES.*
FROM MEDICALS INNER JOIN REVIEWS
ON MEDICALS.GADDRE_IDENTIFIER = REVIEWS.GADDRE_IDENTIFIER
JOIN ADDRESS_HISTORIES
ON REVIEWS.GADDRE_IDENTIFIER = ADDRESS_HISTORIES.GADDRE_IDENTIFIER
WHERE GAZETTEER_ADDRESSES.IDENTIFIER = MEDICALS.GADDRE_IDENTIFIER);

When I attempt to run this I receive the following error: ORA-00600: internal error code, arguments: [qcopcovm2], [0], [], [], [], [], [], []

I know it's not generally a good idea to delete records but in this case a large number of cases are incorrect and need to be reinserted and this is the quickest way of doing it.

View 2 Replies View Related

How To Delete Schema (Tablespace)

Jun 15, 2007

i have used toad to create a tablespace say XYZ and user ABC from the system schema.

Now i want to delete this tablespace xyz.

View 1 Replies View Related

Delete Duplicate Rows?

Feb 6, 2011

I having a problem with duplicate rows with a query.

What I have so far is

SELECT D.Student_Id,
E.Latest_Reg_Date,
E.Attendance_Count,

[Code].....

View 1 Replies View Related

SQL & PL/SQL :: Delete Statement With Inner Join?

Mar 14, 2011

I am trying to run following sql query,but it is throwing following error.

SQL> delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CON
NECTION_ID_TEMP=COMPLEMENTS.feature_conn_id
2 WHERE a.current_designation is null and a.current_low is null and a.current
_high is null;

delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CONNECTI
ON_ID_TEMP=COMPLEMENTS.feature_conn_id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

View 4 Replies View Related

SQL & PL/SQL :: How To Delete Rows From Table

Oct 19, 2011

I am using the below proc to delete some records

1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;

client_id count(*)
----------- ---------
16205601785128

2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK
where client_id=1620560178
group by client_id
having count(*) > 40
order by 2 desc
client_id count(*)
----------- ---------
16205601785128

3) select client_id,clnt_notification_pack_tid
-- bulk collect into v_client_id,v_notif_tid
from (select clnt_notification_pack_tid,
client_id,
clnt_notification_pack_typ_tid,
crte_dt,

[code]....

4) Iam using the below proc to delete the rows from table, except the 4 rows returned above

declare
v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type;
tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type;
tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type;
v_client_id t_client_id;
v_notif_tid t_notif_tid;

[code]....

5) After running this procedure, i shud see 5124 records, but i see zero records.

View 23 Replies View Related

SQL & PL/SQL :: Update And Delete Using Loader?

Mar 14, 2012

i have a flat file with fixed positions.Records of this file are having like empid empname deptname typeEx:

100 AAAA ADEPT I
101 BBBB BDEPT I
102 CCCC CDEPT U
103 DDDD DDEPT I
104 EEEE EDEPT D

Here value of type would be like I or U or D I have to load this file into oracle table in such a way that

if type value is I then i have to insert into table
if type value is U then i have to update this record in table
if type value is D then i have to delete this record from table.

I am using oracle10g.

View 3 Replies View Related

Forms :: Delete Or Overwrite FMX On AS

Jul 15, 2013

we are using oracle application server.

i want to delete or overwrite .FMX file while they are in use.

waiting for users to logout or finding out which user is logged in..is too time consuming.

View 6 Replies View Related

SQL & PL/SQL :: Delete OS File In Oracle 8i

May 13, 2010

I am facing a problem while trying to delete an OS file using PL/SQL in Oracle8i v8.1.7.I have tried to use UTL_FILE.fremove but it is not supported in Oracle8i.

how can I delete a file Test.txt from C:Test location?

View 1 Replies View Related

SQL & PL/SQL :: Delete Duplication In A Table

Jul 7, 2011

I want to delete the duplicate rows in a table. there is no column which can be used to differentiate between the rows.

SELECT column_name1
FROM [table]
WHERE column_name2 = cond
GROUP BY column_name1
HAVING COUNT (column_name1) > 1

from the above query i can find the duplication in the table and can delete through it. But what i want one record of each duplication not to be deleted most probably the record added last to the table.

View 13 Replies View Related

SQL & PL/SQL :: Delete Table If Exist

Jan 18, 2013

delete table if exist

How do we write this code in oracle / sqlplus

View 4 Replies View Related

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.

View 5 Replies View Related

SQL & PL/SQL :: Delete Even Rows Of A Table

Oct 25, 2006

How to delete even rows of a table or rather alternate rows of a table.

View 31 Replies View Related

SQL & PL/SQL :: Delete Duplicated Record

May 21, 2010

I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages.

SQL> SELECT * FROM XYZ;

NAME FNAME ADDRESS JOB
-------------- -------------------- -------------------- -----------------------------------------
Bilal Khan Wali your rehman name district abbottabad student
Bilal Khan Wali your rehman name district abbottaba student
Bilal Kh Wali your rehma name district abbotta studen
Bilal K Wali your rehm name district abbotta studen
Bilal Khan Wali your rehman name istrict abbottaba tudent
lal Khan i your rehman name strict abbottaba tudent
[code]....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved