Jump to content
Vivi

Table Partitioning in oracle 11g

Recommended Posts

Hello,

I got a task to partition the existing table in an Oracle database 11g,I want to convert a non partitioned table to partition table based on range partition.So please share your valuable tips and suggestions.

Share this post


Link to post
Share on other sites

Hi Vivi, and welcome to the forum!

If you are in release 12.2 or higher, you can use “alter table” syntax to move the rows into partitions.

But since you are on 11g, you need to reorganize the table into partitions using dbms_redefinition.  Here is how it works:

http://www.dba-oracle.com/t_dbms_redefinition.htm

Here is an example of the full procedure.  Please read carefully:

http://www.dba-oracle.com/t_dbms_redefinition_example.htm

As you see, it’s relatively straightforward, but remember that you can achieve the same result with expdp and impdp.

http://dba-oracle.com/t_oracle_expdp_tips.htm

Of course, you can also use CTAS, provided that you have enough disk space to hold two copies of the table:

http://www.remote-dba.net/t_tuning_reorganizing_tables.htm

Hope this helps . . . 

Share this post


Link to post
Share on other sites

Hello sir,

here i am creating a new table as Table 1,please check the query and please give your suggestions.I am also getting an error while executing this

and this is the error that got while executing the following query.

 

Error starting at line : 1 in command -
create table new table
AS
(SELECT * from Table 1)
PARTITION BY RANGE (TXN_DATE)
  ( PARTITION test_p1 VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy'))
    TABLESPACE testpartition
 , PARTITION test_p2 VALUES LESS THAN (TO_DATE('01-APR-2017','dd-MON-yyyy'))
    TABLESPACE testpartition1
 , PARTITION test_p3 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy'))
    TABLESPACE testpartition2
 , PARTITION test_p4 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy'))
    TABLESPACE testpartition3
 , PARTITION test_p5 VALUES LESS THAN (TO_DATE('01-OCT-2018','dd-MON-yyyy'))
    TABLESPACE testpartition4
)
Error at Command Line : 4 Column : 1
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

query as follows : 

 

create table  new table
AS
(SELECT * from Table 1 )
PARTITION BY RANGE (TXN_DATE)
  ( PARTITION test_p1 VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy'))
    TABLESPACE testpartition
 , PARTITION test_p2 VALUES LESS THAN (TO_DATE('01-APR-2017','dd-MON-yyyy'))
    TABLESPACE testpartition1
 , PARTITION test_p3 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy'))
    TABLESPACE testpartition2
 , PARTITION test_p4 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy'))
    TABLESPACE testpartition3
 , PARTITION test_p5 VALUES LESS THAN (TO_DATE('01-OCT-2018','dd-MON-yyyy'))
    TABLESPACE testpartition4
);

Share this post


Link to post
Share on other sites

Hi Vivi, 

Start with a small sub-set of the statement in your development database.

Then, continue to add partition syntax until it works!

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×