SQL & PL/SQL :: Query To Delete Two Weeks Old Data?
Sep 12, 2013
I want to delete the records grater than two weeks old.From Saturday to Friday we are considering as Week.
P_ID CREATED_DT
105 28/AUG/2013
106 29/AUG/2013
107 30/AUG/2013
108 31/AUG/2013
109 01/SEP/2013
110 02/SEP/2013
[code]....
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');
[code]....
View 3 Replies
ADVERTISEMENT
May 10, 2012
can we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
View 1 Replies
View Related
Dec 14, 2011
I need to divide the given time period into weeks from Monday to Sunday .There should not be overlapping of two months, for a week.Every month should start from First day of that month to next Sunday .Same thing can be done by following PL/SQL block . let me know if there is any simple way by using query instead of block .
declare
pid_from_date date := '01-JAN-11';
pid_to_date date := '31-dec-11';
ln_number number := 0;
ld_from_date date;
ld_to_date date;
begin
[Code]....
View 5 Replies
View Related
Aug 27, 2010
wanna to make matrix report retrieve months year and the number of weeks from dual table it supposes to the weeks number is 52 or 53 week months and weeks on the rows please find attach pic it show what i need to do for exmple
month name: January February March April May June July August September Nov October December
no of week : 1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-20-21-22....... TO 52 week
View 5 Replies
View Related
Jun 10, 2010
select SQL_CALC_FOUND_ROWS navl.xydata.Lat,
navl.xydata.Longi,
MIN(navl.xydata.GpsTime) as mn,
MAX(navl.xydata.GpsTime) as mx,
timediff(MAX(navl.xydata.GpsTime) ,MIN(navl.xydata.GpsTime) ) as idle,
[code]......
i want to delete WHERE mn = mx .
View 4 Replies
View Related
Jul 24, 2012
how to insert data in a table by deleting previous entered data and only inserting current data like:
CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);
[code]....
I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:
INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);
it must show aaa,8 and aaa,9 bt not the previous values.
NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.
View 4 Replies
View Related
Nov 5, 2012
I am using oracle 10.2.0.3 and i am receiving very slow response time for the below query and sometimes resulting in a deadlock throwing ora-60 error.
DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE (TRGT.URI) NOT IN (
SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI,
SUBSTR( SERVICEACCESSNAME,1,INSTR( SERVICEACCESSNAME ,'_')-1) AS
FROM_URI, SUBSTR( SERVICEACCESSNAME,INSTR( SERVICEACCESSNAME
,'_')+1,LENGTH(SERVICEACCESSNAME)) AS TO_URI FROM
TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) FROMTOURI,
[code]...
View 1 Replies
View Related
Mar 22, 2011
I have one table that have many records. For the maintenance purpose I like to delete old record based on Customer No.-That is Mobile NO.If each Customer have more than 300 records, I like to delete by everyday batch process.can't figure out how to apply each Customer No.(Specific Column), I could sort (order by few column - SAVE_DT or SMS_ARV_CLC) how to write this kind of query? I try rownum but no more progress.
Here is my table
CREATE TABLE TM_060_SMS_TEST
(
SMS_SEQ VARCHAR2(18 BYTE),
SMS_RCV_CLC VARCHAR2(14 BYTE),
CUST_NUM VARCHAR2(12 BYTE),
[code]...
View 12 Replies
View Related
Mar 24, 2011
I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?
delete from
TM_060_SFS_TEST
WHERE
rowid in (
SELECT
[code]......
View 1 Replies
View Related
Aug 16, 2012
I have data some thing like this.
NO NAME LOC SAL
------------------------------------------------------------------
1 A HYD 100
2 B BGL 200
1 A HYD 200
1 A HYD 150
I want to delete duplicate records (group by no,name,loc) but only max(qty) record should be retained. So I need output like this.
NO NAME LOC SAL
------------------------------------------------------------------
1 A HYD 200
2 B BGL 200
View 3 Replies
View Related
Apr 5, 2013
How Can I delete the returned two rows?
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]....
View 6 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Apr 5, 2011
want to write Procedure to return weeks and days from given set of days.
let suppose we have 72 days to return weeks and days then return should be 7 weeks and 2 days.Can i use date function Or ?
View 5 Replies
View Related
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.
View 8 Replies
View Related
Jan 9, 2013
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.
View 13 Replies
View Related
Oct 28, 2008
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.
View 6 Replies
View Related
Apr 20, 2009
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
View 3 Replies
View Related
May 26, 2012
I haved drop a datafile on Primary,and the archived log has transported to Standby,but the datafile is still exist in Standby.
Primary
alter tablespace tps_hxl
drop datafile '/u02/app/oracl/oradata/hxl10.dbf';
View 7 Replies
View Related
Jun 18, 2011
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.
View 1 Replies
View Related
Apr 13, 2012
how to delete a data which is related to many no of child table and according to setnull and casecad by using subprogram?
View 6 Replies
View Related
Feb 3, 2013
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.
View 17 Replies
View Related
Oct 28, 2013
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))) ..
View 9 Replies
View Related
Nov 19, 2010
how i can chk & delete duplicate rows from a table
View 3 Replies
View Related
Mar 18, 2013
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 .
View 14 Replies
View Related
Nov 10, 2012
I am using oracle 11g database.
unforunatly i delete the data from main table. and i operated alter stmt.
now how do i retrieve the data..??
View 5 Replies
View Related
Apr 27, 2012
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
|
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 499 | 20459 | 39 (0)| 00:00:
01 |
| 1 | DELETE | RESOURCE | | | |
[code]....
View 26 Replies
View Related
Oct 11, 2013
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:
View 15 Replies
View Related
Apr 10, 2013
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.
View 3 Replies
View Related
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
Feb 17, 2011
I have a database "BD001" for example, inside got 90 schemes, each one has two tablespaces, see below script I use to create:
CREATE TABLESPACE TS_F_ORA9_027_DAT NOLOGGING DATAFILE
'E: DataBases ORACLE 92 DAT TS_F_ORA9_027_DAT.ora' SIZE 5M REUSE ON AUTOEXTEND
NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
AUTO;
[code]....
I have the dmp this scheme "F_ORA9_027" but have no way to restore it onceI can not connect to the service.
View 4 Replies
View Related
