I would have expected a mention of Interval Partitioning in response to this question.

The solution you have provided is not useful in my case as our tables are important ones in our application and it is risky to drop them.Also they have sys indexes created on it.
In my solution insert operation taking long time which we need to find solution for it.I have tried insert with append hint but it is also not working as direct path loading will not work with LOB columns.Could you please suggest any better solution ?

Sorry for not highlighted before that we can't use partition here on that table as we don't have partition key.We are using another table to take reference of 3 years of data.

Can we use dbms_parallel_execute here in step 4 in my solution for better performance ? What could be best and fast way to insert data in LOB columns ?

alter session enable parallel dml;
insert /*+ parallel */ into ...
  select /*+ parallel */ ...

Thanks I will try your suggestions and let you know.

I am facing performance issue now when I do CTAS (step 1) as sample code below :

create table t1_backup nologging parallel as
select /*+ parallel(4) */ * from t1 where id in (
select id from t2 where daterange between date1 and date2);

Volume of select data - 95 million with LOB columns

Time to complete - 100 minutes

Is there any way to improve the performance ?

exec DBMS_monitor.session_trace_enable ( null, null, true, true );

create table ...
  select ...

exec DBMS_monitor.session_trace_disable;

I never thought I'd come out of the woodwork after so long!
Create a synonym with the same name as the original table, point it to the original table then use this to flip between your two tables. This way you'll have table independence and can use many methods to move your data around. Are your sys$ indexes on your lob and blob storage or constraints columns? As a tip, always name all your own objects.

Also, check your PQ statement. A PQ pipeline is best maintained end to end through parallelism of the same degree. For example, your IN clause has has no PQ statement so not sure it will paralellise. Also consider reforming your selection via a join rather than the inlist iterator.

Thanks for your above suggestion.I am really getting some knowledge here.
Sad part is I have very limited access to our database environment so can't trace session.
I didn't get clear understanding how synonym will work here ?

Apart from that I am facing performance issue when inserting data from backup table to main table as per steps 4
in my solution via parallel hint as sample code below :

insert /*+ parallel(4) */ into t1 select /*+ parallel(4) */ from t1_backup;
commit;

It is taking around 16 hours to insert data of volume 80 million.

Please provide some solution to better the performance ?

create synonym tab for main_table;

create or replace synonym tab for backup_table;

A little correction - 16 hours to complete steps 2,3 and 4.
I made changes to truncate table like below -
Truncate table t1 reuse storage;
It has any impact on performance ?

Please find below approximated time taken during each steps in my solution :

Step 1 : 1 sec
Step 2 : 100 mins
Step 3 : 1 sec
Step 4 : 8 hrs 30 mins ( via Bulk collect limit 50000)
Step 5 : 30 mins

We can't swap between main table and backup table as I mentioned earlier main table is used
everywhere and someplaces we don't have access.Also can't take risk to hamper production stability.
Also we have sys indexes on LOB columns on main table.

Please advise how can I improve performance if we have heavy table with lob columns.

create table t1 as 
  select level c1, 'T1' c2 from dual
  connect by level <= 8;
  
create table t2 as 
  select c1, 'T2' c2 from t1
  where  c1 <= 4;

create or replace procedure p as
begin
  for rws in ( 
    select * from t1
  ) loop
    dbms_output.put_line ( rws.c2 || ' ' || rws.c1 );
  end loop;
end p;
/

exec p;

T1 1
T1 2
T1 3
T1 4
T1 5
T1 6
T1 7
T1 8

rename t1 to told;
rename t2 to t1;

exec p;

T2 1
T2 2
T2 3
T2 4

insert into main
  select * from backup

We have 1 billion data to be inserted into main table from backup table.
If we do insert..select then it will consume more undo and temp segment as we are inserting
into table with lob columns.This is my understanding.
Please let me know if my understanding is wrong ?

HI ,

What about using below method .

create table t (
c1 int
);

insert into t values ( 1 );
insert into t values ( 2 );
commit;

select * from t22;

alter table t
move including rows where c1 > 1; --which rows you want put it in where clause

select * from t;

I am getting tablespace issue : ORA-30036: unable to extend segment by 4 in undo tablespace when doing insert..select directly.As I mentioned we are inserting billion data.

create table tmain as 
  select level c1 from dual
  connect by level <= 100;
  
create table tbackup ( c1 ) 
partition by list ( c1 ) ( 
  partition pdef values ( default ) 
) as 
  select * from tmain
  where  c1 <= 10;
  
select count(*) from tmain;

COUNT(*)   
        100 

alter table tbackup 
  exchange partition pdef 
  with table tmain;
  
select count(*) from tmain;

COUNT(*)   
         10 

Sorry to come back after some days.Partition is disabled in our database so we can't use partitioing here.
SO I don't have much but to stick with my plan as below things didn't work for me because of LOB columns :
1) Append hint
2) Parallel hint
3) Nologging on table level

In my solution still bulk insert taking around 12 hours to insert 90 million data.
I am doing like below :

BEGIN
OPEN c1 for select * from main_table_backup ;
LOOP
FETCH....LIMIT 50000;
FORALL....
INSERT INTO main_table .....
COMMIT;
EXIT WHEN c1%notfound;
END LOOP;
END;

Could you please guide here to improve ?

exec dbms_monitor.session_trace_enable(waits=>true);

BEGIN
OPEN c1 for select * from main_table_backup where rownum <= 1000000;  --ie, a subset
LOOP
  FETCH....LIMIT 50000;
  FORALL....
    INSERT INTO main_table .....
  COMMIT;
  EXIT WHEN c1%notfound;
END LOOP;
END;
/

We are also facing ORA-01555 : snapshot too old issue here.

open cursor C ...
loop
  fetch from C;
  do_something_that_takes_a_long_time;
end loop;

open cursor C ...
loop
  fetch from C;
  do_something_that_takes_a_long_time;

  if ora-1555 then
     close cursor;
     open cursor;
  end if;
end loop;

We are not making any changes in backup table from anywhere as we just created from main table in step 1 from my solution.Then why ORA-01555 issue is coming while doing bulk insert ? Also we are running database on noarchivelog mode.

Yes agree my solution is not so good.
But how we will swich tables as we have lots of constraints and indexes include sys indexes on LOB columns. How DBMS_REDEFINITION package will help me ?
Is there any other way to do it ?

All LOB columns have a SYS index. It's a byproduct of the create of the table with a LOB. There's nothing you need to convert or move over for a SYS named index on a lob column.

Your other constraints and indexes can be created with the CTAS by adding them inline in the create table portion of the statement. It's a little tedious perhops. Or they can be created after with a little more risk but much greater speed on the CTAS.

Finally, I love how you are all worried about the integrity of this database and what Chris and Connor are proposing is far too risky, but then you mention your DB is in noarchivelog mode!! I truly laughed out loud when I saw that.

We have kept database in noarchivelog mode to increase the performance as this is one time job.In case of any issues we will restore our backup. Anything to worry about ?

we also can't change any constraints or indexes names. So we can't created with CTAS.

If I do your steps with little change will it work :

1. Create backup table for 3 years (via CTAS)
2. Drop the main table
3. Create same constraints/indexes on backup table
4. Rename the backup table to the main table

Do you thing any issues here ?

Hi Tom,

I need your suggestion, I have a task where I need to purge data which are more than 5 years in db.We are using Oracle 12c and In my application tables have complex parent child relationship like table C is dependent on table B and table B is dependent on table A and we have composite primary keys as well.Actually my task is to purge
5 year old data. Could you please suggest how can I proceed to achieve this task in Oracle 12c.

Regards,
Nidhika

We have parent table which has list partition and have multiple child tables all child tables have reference partition, so we are neither able to enable on delete case on child tables nor we are able to drop constraint, so my challenge is how can I drop partition in parent table.

Could you please give best way to resolve this iisue

Hi Tom

Thanks for your help.

Our Oracle DB version is 12.2.0.1.0.
There are 2 big tables with Parent-child relationship. There is NO "on delete cascade".
Both have "INTERVAL RANGE" partitioning on RC_DT (date type) column.

My task is to purge them to store only last 2 years of data and both above tables have to be retained.

Table P - rows 161397436
Table C - rows 395170380


Planned steps :
1. Create 2 new partitioned tables P_new and C_new with 2 years of data.
2. RENAME P & C to P_history & C_history respectively.
3. RENAME P_new & C_new to P & C respectively.
4. Create a scheduler job to keep purging data from P_new & C_new periodically to ensure only 2 years data is retained in new tables.


Here is the progress till now in non-production region:

1) I created P_new table via INSERT. It took 11 hrs. I did not use CTAS as I thought to keep INTERVAL RANGE partitioning in place so that data may keep on moving in respective partitions.

Upon reading your posts above I think I might have used CTAS and then created partitions & indexes/constraints. Not sure how much time it would have saved.

Anyway, table P_new is in place now.

2) Then I tried INSERT for populating C_new from C but it quickly failed due to failing foreign key (FK) constraint.
I disabled the FK and tried again but INSERT failed with snapshot too old error after running for 30 hrs.

So now I am pondering the following options.

Kindly provide your input:

1. Keep FK disabled for C_new, INSERT partition-wise data from C into C_new for 2 yrs partitions.
Then enable the FK with " EXCEPTIONS INTO MY_EXCEPTIONS;" .

then follow remaining of planned steps. But a caveat here is the ongoing transactions in the tables, which we will have to stop by taking a downtime, anyway.

2. Or If above does not work, make copies of P & C via expdp/impdp as _history tables.
Perform deletes on main tables P & C intermittently to remove data older than 2 years. Not deleting in single shot, but in chunks, may be spread over days.

As there is no on delete cascade, I will have to ensure that no children exists before deleting from parent table.

3. Or a workable & good method as per your advice.

Please help.

create table t ( x ) 
  partition by range ( x ) 
  interval ( 10 ) (
    partition p0 values less than ( 1 )
  )
as 
  select level x from dual
  connect by level <= 100;
  
select partition_name, high_value 
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME    HIGH_VALUE   
P0                1             
SYS_P1269         11            
SYS_P1270         21            
SYS_P1271         31            
SYS_P1272         41            
SYS_P1273         51            
SYS_P1274         61            
SYS_P1275         71            
SYS_P1276         81            
SYS_P1277         91            
SYS_P1278         101

Thanks Tom.
Will definitely try this.

But can you please also tell the definite way to take care of referential integrity relationship in this case.

Thanks

alter table ... 
  add constraint ...
  novalidate;

alter table ... 
  validate constraint ...;

Hi Tom

In reference with my above requirement, I tested these steps on Oracle DB version 12.2.0.1.0. and observed some behaviour which I seek your help to understand:

Basically, I did following things:

1. Export 2 tables (this will export data , indexes, and constraints PK & FK )
2. Rename the constraints so that while importing there is no name conflict
3. Import 2 tables (this will import data , indexes, and constraints PK & FK ) . Here I found something unexpected with "Referenced Constraint name" in dba_constraints table.

Here is sequence of steps for this test:

drop table HR.child;
drop table HR.child_history;
drop table HR.parent;
drop table HR.parent_history;

CREATE TABLE HR.parent
( "GEN_KEY" VARCHAR2(12 BYTE) NOT NULL ENABLE,
RECEIPT_DATE date NOT NULL ENABLE,
CONSTRAINT PARENT_PK_NEW PRIMARY KEY ("GEN_KEY") ENABLE )

PARTITION BY RANGE ("RECEIPT_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION PARENT_DEFAULT VALUES LESS THAN (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

CREATE TABLE HR.child
( "GEN_KEY" VARCHAR2(12 BYTE) NOT NULL ENABLE,
"PARENT_GEN_KEY" VARCHAR2(12 BYTE) NOT NULL ENABLE,
RECEIPT_DATE date NOT NULL ENABLE,
CONSTRAINT CHILD_PK_NEW PRIMARY KEY (GEN_KEY)ENABLE ,
CONSTRAINT CHILD_FK_NEW FOREIGN KEY (PARENT_GEN_KEY)
REFERENCES HR.parent (GEN_KEY) ENABLE)
PARTITION BY RANGE ("RECEIPT_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION CHILD_DEFAULT VALUES LESS THAN (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

insert into HR.parent values ('1',to_date('31-AUG-2014','DD-MON-YYYY') );
insert into HR.parent values ('2',to_date('31-AUG-2018','DD-MON-YYYY') );
insert into HR.parent values ('3',to_date('31-AUG-2019','DD-MON-YYYY') );
insert into HR.parent values ('4',to_date('31-AUG-2020','DD-MON-YYYY') );
insert into HR.parent values ('5',to_date('31-DEC-2020','DD-MON-YYYY') );
insert into HR.parent values ('6',to_date('30-DEC-2020','DD-MON-YYYY') );
commit;

insert into HR.CHILD values ('1','3',to_date('31-AUG-2014','DD-MON-YYYY') );
insert into HR.CHILD values ('2','3',to_date('31-AUG-2018','DD-MON-YYYY') );
insert into HR.CHILD values ('3','5',to_date('31-AUG-2019','DD-MON-YYYY') );
insert into HR.CHILD values ('4','5',to_date('31-AUG-2020','DD-MON-YYYY') );
insert into HR.CHILD values ('5','6',to_date('31-DEC-2020','DD-MON-YYYY') );
insert into HR.CHILD values ('6','6',to_date('30-DEC-2020','DD-MON-YYYY') );
commit;

So after this, "select * from dba_indexes where table_name in ('PARENT','CHILD') order by table_name;" will show me following 2 indexes:

CHILD_PK_NEW
PARENT_PK_NEW

Constraints are as follows: "select * from dba_constraints where table_name in ('PARENT','CHILD') order by table_name;" :

SYS_C007436
CHILD_FK_NEW (references PARENT_PK_NEW)
SYS_C007438
CHILD_PK_NEW
SYS_C007437
SYS_C007433
PARENT_PK_NEW
SYS_C007434


Now , I rename the Indexes & Constraints :

alter index CHILD_PK_NEW rename to CHILD_PK_NEW_1;
alter index PARENT_PK_NEW rename to PARENT_PK_NEW_1;

alter table CHILD rename constraint CHILD_PK_NEW to CHILD_PK_NEW_1;
alter table CHILD rename constraint CHILD_FK_NEW to CHILD_FK_NEW_1;
alter table PARENT rename constraint PARENT_PK_NEW to PARENT_PK_NEW_1;

So my Indexes & constraints are renamed now with _1 appended to them.


Now the import, remapping PARENT & CHILD tables as PARENT_HISTORY & CHILD_HISTORY respectively :

impdp \"/ as sysdba\" directory=test_dir dumpfile=my_oe_code.dmp REMAP_TABLE=HR.PARENT:PARENT_HISTORY,HR.CHILD:CHILD_HISTORY


While checking indexes on my new tables 'PARENT_HISTORY','CHILD_HISTORY' :
"select * from dba_indexes where table_name in ('PARENT_HISTORY','CHILD_HISTORY') order by table_name;"
gave me EXPECTED result:

CHILD_PK_NEW
PARENT_PK_NEW

But, I got something UNEXPECTED while checking constraints:
select * from dba_constraints where table_name in ('PARENT_HISTORY','CHILD_HISTORY') order by table_name;

CHILD_PK_NEW
SYS_C007456
SYS_C007457
CHILD_FK_NEW (to my surprise this is referring to PARENT_PK_NEW_1 as per R_CONSTRAINT_NAME column , I was expecting PARENT_PK_NEW)
SYS_C007455
PARENT_PK_NEW
SYS_C007459
SYS_C007458

Due to this incorrect referencing, I am able to delete records from PARENT_HISTORY with no referential constraint violation error.

(I understand that another method is to get tables imported first & creating the constraints afterwards, but I am trying to achieve similar result via above method)

Can you please advise what I may be missing here to understand w.r.t. value in R_CONSTRAINT_NAME column ?

Thanks Chris.

Above I gave sample tables/data, but actually the parent & child tables are very large in my case and INSERT had taken forever before erring out with snapshot too old error.

I will use the alternate way in this case as suggested above.

Thanks

Hi all,

Thanks for your help & guidance.

This post is in response to my original one posted on "January 07, 2020 - 10:42 am UTC".

Just sharing the way the given requirement was met.

Requirement : Purge 2 large partitioned tables, with foreign key relationship, to contain only last 2 years of data.

I had tried CTAS & INSERT methods, but given sheer size of data, they got timed out or gave snapshot too old error.

Lets call the tables P (parent) & C (child) with around 15 years of data. Lets call P_NEW & C_NEW the tables with last 2 years of data from P & C respectively.

1) P_NEW required data from all partitions of P based upon a WHERE clause. So I created a par file with QUERY clause & ran expdp. This took 3 hrs & produced 1 dmp file.

2) C_NEW just required last 2 year partitions worth of data, so I exported it partition wise. There were 26 partitions to consider. I ran 4 simultaneous expdp commands (with expdp for step 1 already running), dividing partitions in each export (6,6,6,8) with parallel=4.
This took 3.15 hrs (overlapping) and produced 4 dmp files.

3) Rename Primary, Unique, check etc constraints & indexes of P & C.

4) Import dmp file from Step 1 in P_NEW table with parallel=4, constraints=y indexes=y transform=disable_archive_logging:y. Took 35 mins.

5) Import all dmp files from Step 2 sequentially with parallel=6, indexes=y constraints=y EXCLUDE=REF_CONSTRAINT transform=disable_archive_logging:y.
Except 1st import in this step, remaining 3 imports had additional table_exists_action=append parameter.
Please note that here I excluded REF constraint for successful import.
Total time taken 1 hr 45 mins.

6) After this, I had P_NEW and C_NEW ready with data and indexes & all constraints , except REF constraint.

7) Create a FOREIGN KEY constraint on C_NEW referencing P_NEW in with NOVALIDATE.
Create an EXCEPTIONS table & then alter C_NEW ENABLE VALIDATE CONSTRAINT EXCEPTIONS INTO EXCEPTIONS for Ref constraint.
This took 50 mins. The REF constraint was in DISABLED NOVALIDATE mode after this.

8) Now to ENABLE VALIDATE this REF constraint was tricky.

I tried BULK insert but it was failing as given that for every key in P_NEW, there were multiple records in C_NEW, every other attempted insert on P_NEW for same key failed due to primary key constraint violation error on P_NEW.

So I did it in a loop, the failing every other attempted insert on P_NEW from P for same key was sent to EXCEPTION section & loop continued with next key insert.
This took around 10 hrs (there may be some efficient way for this that I might be missing here).

So total work of creating a REF key constraint took approx 11 hrs.

9) At the end, I had P_NEW & C_NEW tables which had VALIDATED foreign key relationship and these were renamed to P & C quickly and requirement was met.

This was done in TEST region. Next will be doing in PROD region and as data is loaded only once a week, so they can tolerate the hours taken by above work.

Thanks