Home » SQL & PL/SQL » SQL & PL/SQL » Purging Data From table!
Purging Data From table! [message #676688] Thu, 04 July 2019 01:39 Go to next message
ramya29p
Messages: 138
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
I have a requirement to clear the logs older than 130 days from the table, because we have been allocated less tablespace size. The challenge is, the logs are keeps on writing into this table by the microservice. So whenever I execute the Delete statement it is taking more time.

I am using below method to deleting the data.
Begin
EXECUTE IMMEDIATE 'delete from log_table where inserted_ts <= systimestamp - 130' ; 
EXECUTE IMMEDIATE 'commit'; 
EXECUTE IMMEDIATE 'alter table log_table enable row movement'; 
EXECUTE IMMEDIATE  'alter table log_table shrink space'; 
EXECUTE IMMEDIATE 'alter table log_table disable row movement';
End;

Could anyone tell me the best way to delete the data.
Re: Purging Data From table! [message #676689 is a reply to message #676688] Thu, 04 July 2019 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why a PL/SQL block and EXECUTE IMMEDIATE?
Directly execute the statements in SQL*Plus (or whatever is your SQL tool).

Quote:
Could anyone tell me the best way to delete the data.
If you want to still insert when you purge then DELETE is the only way.

You could also partition your table (for instance per month or quarter) and then drop obsolete partitions.

Re: Purging Data From table! [message #676690 is a reply to message #676689] Thu, 04 July 2019 03:32 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
How big is the table?
how much data are you deleting?
Re: Purging Data From table! [message #676735 is a reply to message #676688] Wed, 10 July 2019 07:20 Go to previous messageGo to next message
EdStevens
Messages: 1083
Registered: September 2013
Senior Member
In addition to the other questions, why are you doing the ALTER TABLE SHRINK SPACE? Just delete the rows. Period. The space vacated by the deleted rows will be reused by the ongoing inserts. Trying to shrink the table to reclaim space just introduces more overhead at the time of the SHRINK, and still more overhead when future INSERTs end up requiring the re-allocation of extents.
Re: Purging Data From table! [message #676738 is a reply to message #676735] Wed, 10 July 2019 18:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
EdStevens wrote on Wed, 10 July 2019 08:20
The space vacated by the deleted rows will be reused by the ongoing inserts.
Depends. It could be INSERT APPEND.

SY.
Re: Purging Data From table! [message #676739 is a reply to message #676688] Wed, 10 July 2019 18:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
Is your company licensed for partitioning? If so, interval partition table on 1 day interval and simply drop older that 130 day partitions.

SY.
Re: Purging Data From table! [message #676740 is a reply to message #676739] Wed, 10 July 2019 20:39 Go to previous messageGo to next message
ramya29p
Messages: 138
Registered: November 2007
Location: Chennai
Senior Member
Hi all, thanks for the suggestions.

Actually my table had been created without partitioning.
How to create partition on existing table??
Re: Purging Data From table! [message #676741 is a reply to message #676740] Wed, 10 July 2019 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
ramya29p wrote on Wed, 10 July 2019 18:39
Hi all, thanks for the suggestions.

Actually my table had been created without partitioning.
How to create partition on existing table??

NOT possible.
Re: Purging Data From table! [message #676742 is a reply to message #676740] Thu, 11 July 2019 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DBMS_REDEFINITION.

Re: Purging Data From table! [message #676750 is a reply to message #676742] Thu, 11 July 2019 07:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
I wouldn't use DBMS_REDEFINITION on large table especially if data is inserted on high rate if there is enough space to hold 260 days worth of data and some down time is possible. Then we can:

1. rename log_table table and its indexes.
2. create log_table table as interval partitioned with 1 day partitioning interval and 2 permanent partitions: p0 with inserted_ts less than TIMESTAMP '2019-1-1 00:00:00.00' and p1 with inserted_ts less than tomorrow.
3. exchange partition p1 with renamed log_table (takes split second).
4. rebuild unusable indexes (if there are any global indexes).
5 drop renamed log_table.

After partitioning log_table will have partition p1 holding 130 days of through today and starting tomorrow, each day data will reside in a separate partition. Now we can create purge job that will check each partition HIGH_VALUE (excluding always empty partition p0 we have to keep since interval partitioned table must have one permanent partition) and drop partition if HIGH_VALUE is more than 130 days old. So after 130 days (that's why I said we need space for 260 days worth of data) purge job will drop partition p1 and we will regain all that extra space and then, assuming purge runs daily, oldest interval partition will be dropped each day.

SY.
Re: Purging Data From table! [message #676752 is a reply to message #676740] Fri, 12 July 2019 05:13 Go to previous messageGo to next message
John Watson
Messages: 7956
Registered: January 2010
Location: Global Village
Senior Member
ramya29p wrote on Thu, 11 July 2019 02:39
Hi all, thanks for the suggestions.

Actually my table had been created without partitioning.
How to create partition on existing table??
For example,
orclx>
orclx> select partitioned from user_tables where table_name='EMP';

PAR
---
NO

orclx> alter table emp modify partition by range (hiredate) (partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')),partition p2 values less than (maxvalue)) online;

Table altered.

orclx> select partitioned from user_tables where table_name='EMP';

PAR
---
YES

orclx>
Re: Purging Data From table! [message #676753 is a reply to message #676752] Fri, 12 July 2019 06:11 Go to previous messageGo to next message
ramya29p
Messages: 138
Registered: November 2007
Location: Chennai
Senior Member
I am unable to create partition by
alter table emp modify partition by range (hiredate) (partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')),partition p2 values less than (maxvalue)) online;

Getting Error as ora-14006 invalid partition name.

Also i tried creating a new table with Partition as mentioned below. But it is not working. Getting Error as ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
create table emp(id number,name varchar2(150),hiredate TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL) partition by range(hiredate) (partition P_old values less than (sysdate-170),partition p2 values less than (maxvalue))

I want to create a partition which contains the value less than 170 days in P_old. So that i can drop this partition.

Could anyone please give suggestion.


Re: Purging Data From table! [message #676754 is a reply to message #676753] Fri, 12 July 2019 06:19 Go to previous messageGo to next message
John Watson
Messages: 7956
Registered: January 2010
Location: Global Village
Senior Member
You might want to (a) read the documentation and (b) read the error message. You have given it thisQuote:
values less than (sysdate-170)
which is notQuote:
one of: string, datetime or interval literal, number, or MAXVALUE
Re: Purging Data From table! [message #676755 is a reply to message #676753] Fri, 12 July 2019 07:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
ramya29p wrote on Fri, 12 July 2019 07:11
I am unable to create partition by

Getting Error as ora-14006 invalid partition name.


Which tells me you are on Oracle 12.1 or older. Option to partition existing non-partitioned table is only available starting 12.2. For older version you'd need to use something like I described in my previous reply or use DBMS_REDEFINITION as Michel suggested.

SY.
Re: Purging Data From table! [message #676773 is a reply to message #676755] Mon, 15 July 2019 04:29 Go to previous messageGo to next message
ramya29p
Messages: 138
Registered: November 2007
Location: Chennai
Senior Member
I tried creating a new table with Partition as mentioned below. But it is not working. Getting Error as ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
create table emp(id number,name varchar2(150),hiredate TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL) 
partition by range(hiredate) (partition P_old values less than (sysdate-170),partition p2 values less than (maxvalue))
Re: Purging Data From table! [message #676774 is a reply to message #676773] Mon, 15 July 2019 04:33 Go to previous messageGo to next message
John Watson
Messages: 7956
Registered: January 2010
Location: Global Village
Senior Member
This is looking a bit like trolling: Being deliberately stupid in an attempt to make people angry. However, I shall be generous and assume that it is not deliberate.

As I have already told you, and as the ora-14019 tells you, your partition boundary must be string, a datetime or interval literal, number, or MAXVALUE. You are giving it (sysdate-170), which is none of the above.
Re: Purging Data From table! [message #676808 is a reply to message #676774] Thu, 18 July 2019 12:23 Go to previous message
Bill B
Messages: 1870
Registered: December 2004
Senior Member
ramya29p,
What version of the database are you using? It makes a big difference on how you make the partition

For example if you were running Oracle 18 you could alter a table to be partitioned and have it automatically add partitions. My example below will alter a table to have a partition on the create_dt column. Everything before the date of 01/01/2000 will be in the first partition. Any record after that time will be in a partition that will be automatically build when needed. I have the partition made one per month. The ONLINE command will allow the database to add the partitions while the table is in user by other users. The UPDATE INDEX command maintains the indexes while the alter is running .

ALTER TABLE ITEM
MODIFY
PARTITION BY RANGE (CREATE_DT)
INTERVAL( NUMTOYMINTERVAL(1, 'MONTH'))
(  
  PARTITION ITM_P1 VALUES LESS THAN (TO_DATE('01/01/2000','MM/DD/YYYY'))
)
ONLINE
UPDATE INDEXES;

[Updated on: Thu, 18 July 2019 12:43]

Report message to a moderator

Previous Topic: Sending table data in HTML format through PL/SQL
Next Topic: ALTER INDEX To Add the Extra columns
Goto Forum:
  


Current Time: Tue Jul 23 04:23:38 CDT 2019