| Purging Data From table! [message #676688] |
Thu, 04 July 2019 01:39  |
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 #676750 is a reply to message #676742] |
Thu, 11 July 2019 07:43  |
Solomon Yakobson
Messages: 3211
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 |
John Watson
Messages: 8803
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ramya29p wrote on Thu, 11 July 2019 02:39Hi 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 |
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 |
John Watson
Messages: 8803
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 |
Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ramya29p wrote on Fri, 12 July 2019 07:11I 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 |
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 |
John Watson
Messages: 8803
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 |
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 |
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 #677162 is a reply to message #677161] |
Tue, 27 August 2019 05:41 |
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 #677203 is a reply to message #677177] |
Fri, 30 August 2019 02:01 |
 |
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
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
