OraFAQ Forum: Performance Tuning » How to partition unpartitioned existing table

Home » RDBMS Server » Performance Tuning » How to partition unpartitioned existing table (12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)

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

How to partition unpartitioned existing table [message #665794]

Wed, 20 September 2017 13:41

jokrasa
Messages: 14
Registered: March 2017

Junior Member

I want to know an easy approach to partitioning an existing table. I think by Range and using a date field will work.

However I see two options on the Oracle site ( see link below )

https://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG00302

Converting a Non-Partitioned Table to a Partitioned Table seems easier than Using Online Redefinition to Partition Collection Tables, just by the steps involved.

However I see here ( see link below )

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533887100346178338

we see

QL> ALTER TABLE a MODIFY
2 PARTITION BY RANGE(x) INTERVAL (10)
3 ( PARTITION P1 VALUES LESS THAN (10),
4 PARTITION P2 VALUES LESS THAN (20),
5 PARTITION P3 VALUES LESS THAN (30)
6 )
7 update indexes
8 (xpa local,
9 xiea global partition by range(y)
10 (partition ip1 values less than (MAXVALUE))
11 ) ;

Table altered.

And all of that works only in 12.2. If you are on 12.1, you need to use DBMS_REDEFINITION . See here for an example

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484

So as I am using..

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

am I only able to use/linited to Online Redefinition to Partition Collection Tables ??

Report message to a moderator

Re: How to partition unpartitioned existing table [message #665795 is a reply to message #665794]

Wed, 20 September 2017 13:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post SQL & results that show the benefit from having a partitioned table over non-partitioned table.

CREATE TABLE PARTITIONED_TABLE...... ;

INSERT INTO PARTITIONED_TABLE SELECT * FROM NON_PARTITIONED_TABLE;

Report message to a moderator

Re: How to partition unpartitioned existing table [message #665797 is a reply to message #665795]

Wed, 20 September 2017 15:07

jokrasa
Messages: 14
Registered: March 2017

Junior Member

tried something like that...

create table collection_audit_staging_copy2 PARTITION BY RANGE (open_date) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))
(PARTITION col_date_min VALUES LESS THAN (TO_DATE('2015-01-01', 'YYYY-MM-DD'))) as select * from collection_audit_staging
Error report -
SQL Error: ORA-01658: unable to create INITIAL extent for segment in tablespace AUDITCOLLECTIONS
01658. 00000 - "unable to create INITIAL extent for segment in tablespace %s"
*Cause: Failed to find sufficient contiguous space to allocate INITIAL
extent for segment being created.
*Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
tablespace or retry with a smaller value for INITIAL

Hmmm what do you think about that ?

Report message to a moderator

Re: How to partition unpartitioned existing table [message #665798 is a reply to message #665797]

Wed, 20 September 2017 15:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Hmmm what do you think about that ?
I think that you don't read & follow directions very well.
I think that you need to CREATE TABLE in a tablespace that has sufficient free space.
Not that I necessarily advocate the following, but individual partitions can each reside in their own tablespace.
I think that you have no basis in reality for creating a partitioned table.
Please quantify what exactly you expect to gain by having a partitioned table.

Report message to a moderator

Previous Topic:	Performace issue
Next Topic:	Statspack snap shot at level 7 is slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 06:50:22 CDT 2025