Home » Other » Training & Certification » syntax issue
syntax issue [message #310911] Wed, 02 April 2008 16:28 Go to next message
dmkanz
Messages: 9
Registered: March 2008
Junior Member
CREATE TABLE ch07ee_rangehash
(employee_id NUMBER (10),
job_title VARCHAR2 (45),
first_name VARCHAR2 (40),
last_name VARCHAR2 (40),
phone_number VARCHAR2 (20))
PARTITION BY RANGE (employee_id)
SUBPARTITION BY HASH (job_title)
(SUBPARTITIONS 4 STORE IN
(users, users, users, users)
PARTITION employ_P1 VALUES LESS THAN (25000),
PARTITION employ_P2 VALUES LESS THAN (50000),
PARTITION employ_P3 VALUES LESS THAN (75000),
PARTITION employ_p4 VALUES LESS THAN (100000));

(SUBPARTITIONS 4 STORE IN
*
ERROR at line 9:
ORA-14004: missing PARTITION keyword

I am getting an error when I try to create a composite range-hash partition table. Not sure what part of the syntax is wrong? I am thinking it is (SUBPARTITIONS 4 STORE IN (users, users, users, users). I want to have each of the partition belong to the users tablespace.

I hope this makes sense.

Thanks


DMKanz

[Updated on: Wed, 02 April 2008 16:30]

Report message to a moderator

Re: syntax issue [message #311021 is a reply to message #310911] Thu, 03 April 2008 04:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Parenthesis misplaced:
SQL> CREATE TABLE ch07ee_rangehash
  2  (employee_id NUMBER (10),
  3  job_title VARCHAR2 (45),
  4  first_name VARCHAR2 (40),
  5  last_name VARCHAR2 (40),
  6  phone_number VARCHAR2 (20))
  7  PARTITION BY RANGE (employee_id)
  8  SUBPARTITION BY HASH (job_title)
  9  (SUBPARTITIONS 4 STORE IN
 10  (users, users, users, users)
 11  PARTITION employ_P1 VALUES LESS THAN (25000),
 12  PARTITION employ_P2 VALUES LESS THAN (50000),
 13  PARTITION employ_P3 VALUES LESS THAN (75000),
 14  PARTITION employ_p4 VALUES LESS THAN (100000));
(SUBPARTITIONS 4 STORE IN
 *
ERROR at line 9:
ORA-14004: missing PARTITION keyword

SQL> CREATE TABLE ch07ee_rangehash
  2  (employee_id NUMBER (10),
  3  job_title VARCHAR2 (45),
  4  first_name VARCHAR2 (40),
  5  last_name VARCHAR2 (40),
  6  phone_number VARCHAR2 (20))
  7  PARTITION BY RANGE (employee_id)
  8  SUBPARTITION BY HASH (job_title)
  9  SUBPARTITIONS 4 STORE IN (ts_d01,ts_d01,ts_d01,ts_d01)
 10  (PARTITION employ_P1 VALUES LESS THAN (25000),
 11  PARTITION employ_P2 VALUES LESS THAN (50000),
 12  PARTITION employ_P3 VALUES LESS THAN (75000),
 13  PARTITION employ_p4 VALUES LESS THAN (100000));

Table created.

Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: TRIGGER FOR UPDATING ROW IF EXISTS AND INSERT NEW ROW IF ALREADY EXIST
Next Topic: In 10g Which of the following statements is not always true?
Goto Forum:
  


Current Time: Sat Dec 03 04:09:07 CST 2016

Total time taken to generate the page: 0.08121 seconds