[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding  multiple partition to a oracle table

Posted on 2012-08-22
5
Medium Priority
?
706 Views
Last Modified: 2012-08-23
Hello Experts,

I need an urgent help on adding partition to a table.
Here is my code:

 
 CREATE TABLE TEST 
    (
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 8)) VIRTUAL NOT NULL,
    col3 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 6)) VIRTUAL NOT NULL
    )
    PARTITION BY RANGE(COL2)(PARTITION P20120822 VALUES LESS THAN (20120823)),
    PARTITION BY RANGE(col3)(PARTITION P201206 VALUES LESS THAN (20120823)),
    ;

Open in new window


But I get the error by for two partition cannot be done , so what would be the best solution to get the range partition on two columns ?
0
Comment
Question by:Swadhin Ray
  • 3
  • 2
5 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38321973
You can only partition a table by a single item.

You might need to use sub-partitions on the second value if you need that many partitions.

http://ora-14027.ora-code.com/
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38321981
can we do a sub partition on the range for two different columns ?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38322010
You partition on one range for col2 then you should be able to subpartition by col3 or col3 then col2.

Check out:
Composite Partitioned Table - By Range And Range

http://psoug.org/reference/partitions.html
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38322485
I am getting syntax error for the below code .
I am doing something really wrong on this:


CREATE TABLE TEST_T
  (
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 8)) VIRTUAL NOT NULL,
    col3 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 6)) VIRTUAL NOT NULL
  )
  PARTITION BY RANGE
  (
    COL2
  )
  SUBPARTITION BY RANGE
  (
    COL3
  )
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION P201206 VALUES LESS THAN (20120823)
  )
  PARTITION P20120822 VALUES LESS THAN
  (
    20120823
  ) ;

Open in new window

0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38322751
What are you wanting to test here?

First this is not valid even without partitioning:  SUBSTR(TRIM(TIMESTAMP), 1, 6)

Try just creating the table:

CREATE TABLE TEST_T
  (
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 8)) VIRTUAL NOT NULL,
    col3 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 6)) VIRTUAL NOT NULL
  );

Open in new window


If you really mean SYSTIMESTAMP, you should use TO_CHAR with it to force the character output.

>>I am getting syntax error for the below code .

The error message would help.

I'm not where I can test partitioning right now.  I only have access to XE until tomorrow.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month19 days, 10 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question