OraFAQ Forum: SQL & PL/SQL » Split Partition with Online / Update global indexes clause

Home » SQL & PL/SQL » SQL & PL/SQL » Split Partition with Online / Update global indexes clause (11.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Split Partition with Online / Update global indexes clause [message #682207]

Fri, 09 October 2020 10:51

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Hi,

I am trying to split partition

with this statement, indexes become UNUSABLE, I have to rebuild them.but parition is splitted correctly
ALTER TABLE TRANSACTIONS_LOG_TESTING
  SPLIT PARTITION TRANSLOG_MAX AT (TO_DATE('01-JAN-2018 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION TRANSLOG_DEC_2017,
        PARTITION TRANSLOG_MAX);

This statement takes forever as it's trying to update/rebuild indexes i guess and on a huge table/large partition 
table around 300 GB and this huge partition about 150 gb takes forever.
ALTER TABLE TRANSACTIONS_LOG_TESTING
  SPLIT PARTITION TRANSLOG_MAX AT (TO_DATE('01-JAN-2018 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION TRANSLOG_DEC_2017,
        PARTITION TRANSLOG_MAX) UPDATE GLOBAL INDXES;

with this clause with ONLINE option so that i don't have to rebuild indexes, i am getting this error
ALTER TABLE TRANSACTIONS_LOG_TESTING
  SPLIT PARTITION TRANSLOG_MAX AT (TO_DATE('01-JAN-2018 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION TRANSLOG_DEC_2017,
        PARTITION TRANSLOG_MAX) ONLINE;
ORA-14126: only a <parallel clause> may follow description(s) of resulting partitions

Please advise how can i do this without having to rebuild indexes at the end.

Thanks

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682208 is a reply to message #682207]

Fri, 09 October 2020 11:37

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Two questions -
First, what is wrong with using the UPDATE INDEXES clause? It may take a while, is that a problem?
Second, is your attempted use of an ONLINE clause documented? What are you trying to do with it?

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682209 is a reply to message #682207]

Fri, 09 October 2020 11:55

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In your version you cannot split partition with ONLINE option.
Only MOVE can be done with ONLINE option.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2131218

[Updated on: Fri, 09 October 2020 11:56]

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682210 is a reply to message #682208]

Fri, 09 October 2020 12:23

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Thanks John, michel

Yes John, with Update index option it's the time that need to be considered we really don't know how much time will take for 350 GB table, in lower Env for 50 gb it took more than 10 mins then i cancelled it.
2nd point michel mentioned i don't have ONLINE option in 11.2.

Thanks

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682308 is a reply to message #682207]

Thu, 15 October 2020 12:41

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

trying to convert a non paritioned table into a partition table in 12c, getting below error,

 ALTER TABLE TRANSACTIONS_ARCHIVE MODIFY
  PARTITION BY RANGE (trans_dt) (
    PARTITION RPTARCH_JUN_2020 VALUES LESS THAN (TO_DATE('01-JUL-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_JUL_2020 VALUES LESS THAN (TO_DATE('01-AUG-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_AUG_2020 VALUES LESS THAN (TO_DATE('01-SEP-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_SEP_2020 VALUES LESS THAN (TO_DATE('01-OCT-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_OCT_2020 VALUES LESS THAN (TO_DATE('01-NOV-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_NOV_2020 VALUES LESS THAN (TO_DATE('01-DEC-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_DEC_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_JAN_2021 VALUES LESS THAN (TO_DATE('01-FEB-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_FEB_2021 VALUES LESS THAN (TO_DATE('01-MAR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_MAR_2021 VALUES LESS THAN (TO_DATE('01-APR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    ) ONLINE;

[Error] Execution (2: 13): ORA-14006: invalid partition name

what is wrong with partition Name please advise.

[Updated on: Thu, 15 October 2020 13:05]

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682309 is a reply to message #682308]

Thu, 15 October 2020 13:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Post SQL & results showing existing partitions for table TRANSACTIONS_ARCHIVE

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682310 is a reply to message #682308]

Thu, 15 October 2020 13:08

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Last partition definition ends with a ",", it should not.
Maybe it is this simple thing but of course, it should be far better you use SQL*Plus and copy and paste your session.
In addition, SQL*Plus points to the error.

[Updated on: Thu, 15 October 2020 13:08]

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682311 is a reply to message #682310]

Thu, 15 October 2020 13:37

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

CREATE TABLE TRANSACTIONS_ARCHIVE (
  id           NUMBER,
  description  VARCHAR2(50),
  archive_dt DATE,
  CONSTRAINT trns_pk PRIMARY KEY (id)
);

ALTER TABLE TRANSACTIONS_ARCHIVE MODIFY
  PARTITION BY RANGE (archive_dt) (
    PARTITION RPTARCH_JUN_2020 VALUES LESS THAN (TO_DATE('01-JUL-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_JUL_2020 VALUES LESS THAN (TO_DATE('01-AUG-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_AUG_2020 VALUES LESS THAN (TO_DATE('01-SEP-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_SEP_2020 VALUES LESS THAN (TO_DATE('01-OCT-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_OCT_2020 VALUES LESS THAN (TO_DATE('01-NOV-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_NOV_2020 VALUES LESS THAN (TO_DATE('01-DEC-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_DEC_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_JAN_2021 VALUES LESS THAN (TO_DATE('01-FEB-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_FEB_2021 VALUES LESS THAN (TO_DATE('01-MAR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_MAR_2021 VALUES LESS THAN (TO_DATE('01-APR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
    ) ONLINE;

Table created.
>> ALTER TABLE TRANSACTIONS_ARCHIVE MODIFY
  PARTITION BY RANGE (archive_dt) (
    PARTITION RPTARCH_JUN_2020 VALUES LESS THAN (TO_DATE('01-JUL-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_JUL_2020 VALUES LESS THAN (TO_DATE('01-AUG-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_AUG_2020 VALUES LESS THAN (TO_DATE('01-SEP-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_SEP_2020 VALUES LESS THAN (TO_DATE('01-OCT-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_OCT_2020 VALUES LESS THAN (TO_DATE('01-NOV-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_NOV_2020 VALUES LESS THAN (TO_DATE('01-DEC-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_DEC_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_JAN_2021 VALUES LESS THAN (TO_DATE('01-FEB-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_FEB_2021 VALUES LESS THAN (TO_DATE('01-MAR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
    PARTITION RPTARCH_MAR_2021 VALUES LESS THAN (TO_DATE('01-APR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
    ) ONLINE
Error at line 8
ORA-14006: invalid partition name

Script Terminated on line 8.

[Updated on: Thu, 15 October 2020 13:39]

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682314 is a reply to message #682311]

Thu, 15 October 2020 14:51

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Any suggestion, what's wrong with partition name Error Please advise.

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682315 is a reply to message #682311]

Thu, 15 October 2020 14:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the result of:

select * from v$version;

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682316 is a reply to message #682315]

Thu, 15 October 2020 15:07

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682317 is a reply to message #682316]

Thu, 15 October 2020 15:11

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This works only from 12.2.

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682319 is a reply to message #682317]

Thu, 15 October 2020 15:44

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Thanks Michel

so what should be my approach to partition this table in 12.1

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682364 is a reply to message #682319]

Mon, 19 October 2020 15:53

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

can I use a Create Table as select * option and partition it.
any clue or example Please.

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682366 is a reply to message #682364]

Tue, 20 October 2020 00:25

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes you can, it is as simple as:

SQL> CREATE TABLE TRANSACTIONS_ARCHIVE_new (
  2    id,
  3    description,
  4    archive_dt,
  5    CONSTRAINT TRANSACTIONS_ARCHIVE_new_pk PRIMARY KEY (id)
  6    )
  7    PARTITION BY RANGE (archive_dt) (
  8      PARTITION RPTARCH_JUN_2020 VALUES LESS THAN (TO_DATE('01-JUL-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
  9      PARTITION RPTARCH_JUL_2020 VALUES LESS THAN (TO_DATE('01-AUG-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 10      PARTITION RPTARCH_AUG_2020 VALUES LESS THAN (TO_DATE('01-SEP-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 11      PARTITION RPTARCH_SEP_2020 VALUES LESS THAN (TO_DATE('01-OCT-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 12      PARTITION RPTARCH_OCT_2020 VALUES LESS THAN (TO_DATE('01-NOV-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 13      PARTITION RPTARCH_NOV_2020 VALUES LESS THAN (TO_DATE('01-DEC-2020 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 14      PARTITION RPTARCH_DEC_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 15      PARTITION RPTARCH_JAN_2021 VALUES LESS THAN (TO_DATE('01-FEB-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 16      PARTITION RPTARCH_FEB_2021 VALUES LESS THAN (TO_DATE('01-MAR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')),
 17      PARTITION RPTARCH_MAR_2021 VALUES LESS THAN (TO_DATE('01-APR-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
 18      )
 19  as select id, description, archive_dt from TRANSACTIONS_ARCHIVE
 20  /

Table created.

You the have to create the other indexes and grant the privileges you have granted on the original table.
In the end, rename/drop the old table and rename the new one and possibly recompile the dependent objects (PL/SQL ones, views...).

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682407 is a reply to message #682366]

Thu, 22 October 2020 09:53

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Thanks Michel

Please advice best practice/approach i should be using for this scenario.

I am on 12.1 Version, so can't use ONLINE option for splitting the partitions, as a result of that after
splitting the huge 300 GB partition, indexes are in UNUSABLE state, i need to rebuild the indexes .

Please advice, should i use Alter index <index_name> rebuild with parallel of 16 , but rebuilding such a huge table index will have other effect like
undo/temp, and maintain 2 copy of index till rebuild is done ?

or should i drop and re-create the indexes, this activity will be for 2 tables of 350 GB and 250 GB Each table size.

Please suggest.

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682413 is a reply to message #682407]

Thu, 22 October 2020 11:11

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The answer(s) depends on the priority and availability of the following parameters (non exhaustive list):

Availability of the data: is downtime possible? For how long?
Available space: is double space for indexes possible?
Available disk bandwidth: to determine possible parallelism; on how much disks are spread the indexes (and tables)? what is the concurrent load (for the same of other instances and applications, back to first point)?
Available CPU: to determine possible parallelism; what is the concurrent load...

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682415 is a reply to message #682413]

Thu, 22 October 2020 12:53

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Thanks

1. 4-6 hrs downtime acceptable
2 Double space for index not possible, we have to do 1 table/index at a time so that space is not filled up and i guess we will have log generation issue also correct if we go with index rebuild option
3. this will be done during Maitenance, no others apps/users will be active on DB, complete DB resources can be used for this, no other load.
4. it's a 40 Core CPU for each node of a RAC Cluster.

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682416 is a reply to message #682415]

Thu, 22 October 2020 13:31

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you can't double the index size then you can't use rebuild and have to go to drop and recreate.
Note that your previous option (CTAS) means you can double the space used by the tables.
Also note that if your indexes are local only the 2 last partitions of the indexes are unusable after the split, you only have to rebuild these ones.

Can you (in addition to modify tables) recreate the indexes in the acceptable down time? This is the question you have to answer.
Can you do several down time sessions with 1 table per session or are all tables related and must be consistent and so modified in one session? This is another question to answer.

Note that disk bandwidth is a more important (but not independent) parameter than number of CPU for parallelism.
If you can use 16 CPU but have only one disk, the only thing you will do is saturated the disk controller queue and your CPU will wait for IO completion.

Report message to a moderator

Re: Split Partition with Online / Update global indexes clause [message #682417 is a reply to message #682416]

Thu, 22 October 2020 13:40

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

thanks for quick suggestions.

CTAS is for a different issue where i will be partitioning a non-partitioned table.

this index rebuild issue is arising after i need to split a huge partition which somehow has many years of data (past 4 yrs ) as all data went into MAXVALUE partition,90% of 380 GB data is in this 1 prtition, i am splitting this partition into 4 partitions for 1 year each and then monthly partitions for 2020 and future ones..

I got 16 Disk in DATA disk group.

I like the suggestion of doing this into 2 windows for 1 table each with either rebuild index if space there Else with drop and re-create.

Thanks Again

Report message to a moderator

Previous Topic:	To_char date with and without format
Next Topic:	Get Distinct Data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 26 10:27:50 CDT 2025