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'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.
I have 4 implicated table in this problem.
The deletion is done depending of the pool_id given
Table 1 contain the pool_id Table 2 the ticket_id foreign join ticket_pool_id with the pool_id Table 3 ticket_child_id foreign join ticket_id with the ticket_id Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id
Concerned count for each table 1---->1 table 2---->1 200 000 table 3---->6 300 000 table 4---->6 300 000
So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted
Here`s the constraint : -No partintionning -Oracle version 9 -Online all the time so no downtime neither CTAS -We cannot use cascade constraint -The normalization is very important
Here`s what I tried: -Bulk delete -Delete with statement (In and Exists clause) -temp table for each level and 1 level join -procedure and commit each 20k
None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now
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.
Have a table which has 3 columns id,name,time where time is of datatype timestamp and it stores the time when the row was inserted. Need an query which accepts 2 parameters as input Ex: Start_Time,End_Time and all the rows in between the above mentioned timestamps must be deleted.
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls 2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
I was asked in a telephone interview about committing a delete ( a few million rows)in an oracle log table which had a trillion rows in total. He said that delete took 2 days.
Then he asked me if then commit is performed(assuming a huge rollback segments are allocated) how long does it take for that commit .
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 00094 122 153 145 224 245 545 114 544 444 111 555 222 00095 233 123 145 224 205 545 114 444 444 111 555 222 00096 163 123 145 224 215 545 114 551 444 111 555 222 00097 163 0 145 224 215 545 114 551 444 111 555 222 conditions:
where condition: where year = 2007
Table Structure:
create table HR_PAYSLIP ( EMP_NO VARCHAR2(6) not null, YEAR NUMBER(4) not null, MONTH NUMBER(2) not null, BASIC_PAY NUMBER(9,2), )
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY) VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
There are four columns as follows, but I need to delete those rows from the third column only where the second letter of the word appears as vowel. For example, I want to delete the rows having the words, 'Ramu' and 'Ravi' only.
A B C D xx y Ramu xx yu ut Ameer uui rtt iw Ravi iwoow fgsg isd Intel jjuiw
1 select s.reg_no,s.course_code, 2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag 3 from attendance a ,src s 4 where a.semester_code=1 5 and a.semester_year=2013 6 and s.semester_code=1 [code]....
I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1 where t1.campo1 in ( select distinct(tr.campo1) from tabla1 tr, tabla2 t2 where t2.error = 0 tr.campo1 = t2.campo1 and tr.campo2 = t2.campo2
I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?
I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:
SELECT * FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST) WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
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 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.
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.
