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: 146
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: 68625
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: 13917
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: 1376
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: 3269
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: 3269
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: 146
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: 26766
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: 68625
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: 3269
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: 8922
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: 146
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: 8922
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: 3269
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: 146
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: 8922
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 messageGo to next message
Bill B
Messages: 1971
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

Re: Purging Data From table! [message #677159 is a reply to message #676742] Mon, 26 August 2019 04:39 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Quote:

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.

I have tried this approach by creating monthly interval partition.
Table without Partition.
CREATE TABLE log_table
							(
								LOG_ID             NUMBER NOT NULL,   							 
								PACKAGE_NAME       VARCHAR2 (30 CHAR),								 
								INSERTED_TS        TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL,
								 
							)

Table with Partition
CREATE TABLE LOG_NEW
							(
								LOG_ID             NUMBER NOT NULL,   							 
								PACKAGE_NAME       VARCHAR2 (30 CHAR),								 
								INSERTED_TS        TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL,
								 
							)
							PARTITION BY RANGE (INSERTED_TS) 
							INTERVAL(NUMTOYMINTERVAL(1, ''MONTH'')) 
							(  
							   PARTITION p0 VALUES LESS THAN (TO_DATE(''01-6-2019'', ''DD-MM-YYYY'')),
							   PARTITION p1 VALUES LESS THAN (TO_DATE(''01-7-2019'', ''DD-MM-YYYY'')),
							   PARTITION p2 VALUES LESS THAN (TO_DATE(''01-8-2019'', ''DD-MM-YYYY'')),
							   PARTITION p3 VALUES LESS THAN (TO_DATE(''01-9-2019'', ''DD-MM-YYYY'')) 
							) ENABLE ROW MOVEMENT

Exchange Partition

ALTER TABLE LOG_NEW
                                    EXCHANGE PARTITION P2
                                    WITH TABLE log_table
                                    WITHOUT VALIDATION
                                    UPDATE GLOBAL INDEXES		

When i did exchange partition all the data got inserted into one partition P2 [Ex: Apr,jun,jul,Aug Data].
Now i want to split the partitions to drop the older data Apr,jun. But i am getting Error as ORA-14080: partition cannot be split along the specified high bound

ALTER TABLE t_log_bkp
  SPLIT PARTITION P2 AT (TIMESTAMP' 2019-06-15 00:00:00')
  INTO (PARTITION P1,
        PARTITION P2)
  update global indexes




Could anyone please help.

[Updated on: Mon, 26 August 2019 04:55]

Report message to a moderator

Re: Purging Data From table! [message #677160 is a reply to message #677159] Mon, 26 August 2019 06:53 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I ask again, what version of the database are you running. It makes a big difference
Re: Purging Data From table! [message #677161 is a reply to message #677160] Tue, 27 August 2019 04:09 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
I am using Oracle 12c version
Re: Purging Data From table! [message #677162 is a reply to message #677161] Tue, 27 August 2019 05:41 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Then you can partition your table while users are accessing it. The following is directly from an oracle site for 12.2

ALTER TABLE employees_convert MODIFY
PARTITION BY RANGE (employee_id) INTERVAL (100)
( PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (500)
) ONLINE
UPDATE INDEXES
( IDX1_SALARY LOCAL,
IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id)
( PARTITION IP1 VALUES LESS THAN (MAXVALUE))
);

The ONLINE indicates that users can still access the table while it is in the process of being converted. The UPDATE INDEXES tells oracle to maintain the indexes during the conversion and with new data from the end users.
Re: Purging Data From table! [message #677177 is a reply to message #677162] Thu, 29 August 2019 00:54 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi Bill,
Thanks for your reply. This is not working on oracle 12.1
Re: Purging Data From table! [message #677203 is a reply to message #677177] Fri, 30 August 2019 02:01 Go to previous message
oracledev
Messages: 2
Registered: August 2019
Junior Member
Even If it works then need to change code part(insert scripts).

My suggestion

create table bkp_log_table
as
select * from log_table where inserted_ts > systimestamp - 130;

Ensure the comparison operator

truncate table log_table;


insert into log_table
select * from bkp_log_table;

we can use here bulk insert for above query (forall).


Thanks

Previous Topic: Additional Column Indicator for Failed or Passed
Next Topic: How to remove ordinal suffixes after the numbers
Goto Forum:
  


Current Time: Thu Mar 28 12:35:37 CDT 2024