Home » SQL & PL/SQL » SQL & PL/SQL » Partitoning taking long time (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
Partitoning taking long time [message #683211] Tue, 15 December 2020 09:26 Go to next message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Dear Sir,

We are doing partiton to an existing non partitioned table using below command in UAT, It exeucted almost 10 hours and did not complete. There are 11 indexes also including unique and pk. However the statistics are stale .There are 3 indexes are there but with that 2 more columns are also present in that index. There are around 398101093 records are present.Please suggest what might went wrong. We are doing this in Oracle 12c.

Earlier the error was coming
ORA-01652:Unable to extend the temp segment by 8192 in tablespace TAB_SPC
But now the error is not coming after DBA made auto extension enabled.

Initially the tablespace size is like
used size-560 GB allocated size-720 GB
after that it increases to
used size-820 GB allocated size-840 GB


ALTER TABLE TXN_DET MODIFY
PARTITION BY RANGE (CREATION_DATE)
(
PARTITION TD_PROCESSED VALUES LESS THAN (TO_DATE('20200401','YYYYMMDD')),
PARTITION TD_PENDING VALUES LESS THAN (TO_DATE('20201101','YYYYMMDD'))
) UPDATE INDEXES;

Thank you
Re: Partitoning taking long time [message #683212 is a reply to message #683211] Tue, 15 December 2020 09:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
When you are partitioning existing n0n-partitioned table Oracle creates temporary segments for each partition populates them with data from original table, switches metadata and drops original segments, so you have to have al the minimus as much free space as the size of original table.

SY.
Re: Partitoning taking long time [message #683213 is a reply to message #683212] Tue, 15 December 2020 09:59 Go to previous messageGo to next message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Thank you sir for prompt reply.

Earlier the temp segment error was coming so DBA made auto-extension enabled and added lots of space , so it seems resoved. However it keep on execute around 9 hours before manually cancelled. The table index are stale, is that reason its taking lots of time. What is the excpeted time for completion. I am trying after gathering statistics of the table now.

Re: Partitoning taking long time [message #683224 is a reply to message #683213] Wed, 16 December 2020 08:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
Indexes will be marked unusable, not stale. And it this is normal since data moved physically. You must rebuild indexes after that unless you use indexing clause in ALTER TABLE ... PARTITION...; And obviously partitioning will take long(er) time on a larg(er) table - Oracle has to sort table data on a partitioning column(s) to determine what data goes to what partition and copy data there. In addition this is online partitioning so Oracle has to keep track of all data changes since partitioning started.

SY.

[Updated on: Wed, 16 December 2020 09:38]

Report message to a moderator

Re: Partitoning taking long time [message #683288 is a reply to message #683224] Thu, 24 December 2020 09:47 Go to previous messageGo to next message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Dear Sir,

After getting help from Oracle team now partition get completed without UPDATE INDEXES clause with 16.5 hours. But this much downtime could not accepted in Production. There are 3 indexes based on creation_date(partitioned key), 1 unique ,1 primary key ,5 other indexes. So to reduce the time can all indexes are dropped excluding pk and unique. Is there any impact if we drop and recreate. All activities to be done offline.

Re: Partitoning taking long time [message #683289 is a reply to message #683288] Thu, 24 December 2020 13:04 Go to previous message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
You could try exporting table, dropping it and creating partitioned table without any indexes and constraints. Then import using data only. Create indexes and constraints after import.

SY.
Previous Topic: DBMS_HS_PASSTHROUGH - Column Value Truncated
Next Topic: sending bulk email in Report 12c?
Goto Forum:
  


Current Time: Fri May 14 04:55:15 CDT 2021