Home » RDBMS Server » Server Administration » table partition
table partition [message #318011] |
Mon, 05 May 2008 05:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Hi,
Oracle version : 9.2.0.1 on windows
I have done hash partitioning on table. I can see partitions.
But I am getting message : Only range partitiong is supported in this version.
How to view table partition data??
Data in table is,
1 Seema
2 Meena
& so on
When I tried to do list partition
SQL> create table ctct_dup2(id number, name varchar2(30))
2 PARTITION BY LIST( substr(name,1,1))
3 (PARTITION from_a_to_f VALUES (`A','B','C','D','E','F') ,
4 PARTITION from g_to_l VALUES (`G','H','I','J','K','L') ,
5 PARTITION from m_to_S VALUES (`M','N','O','P','Q','R','S') ,
6 PARTITION from t_to_z VALUES ('T','U','V','W','X','Y','Z')
7 )
8 ENABLE ROW MOVEMENT;
PARTITION BY LIST( substr(name,1,1))
*
ERROR at line 2:
ORA-00907: missing right parenthesis
Please help
[Updated on: Mon, 05 May 2008 05:58] Report message to a moderator
|
|
|
|
Re: table partition [message #318030 is a reply to message #318011] |
Mon, 05 May 2008 06:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> I have done hash partitioning on table. I can see partitions.
> But I am getting message : Only range partitiong is supported in this version.
When do you get this message? When trying to query one partition?
If so, you probably have wrong idea about the reason for hash partitioning. In that case, data shall be (ideally equally) distributed into partitions using hash function - good for parallel inserting - so one partition contains 'random' data connected only by the same result of the (internal) hash function. I see no reason for querying them.
> PARTITION BY LIST( substr(name,1,1))
List partitions can be built on columns, not expressions.
Try RANGE partitioning instead; it should be good if NAME column starts with letters A-Z in uppercase.
|
|
|
|
|
|
|
Re: table partition [message #318268 is a reply to message #318236] |
Tue, 06 May 2008 05:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Thanks to all.
oracle version : 9.2.0.8 on windows
I have done list partitioning of table on different column
partition by list(c_ctp_id)
( partition ctct_analyst values('2308')
partition ctct_others values('2305','2307','2310','2405')
)
.But when I checked in script in toad for that table, it has converted into hash partitions.
Folowing is code at the end of the script
PARTITION BY HASH (C_CTP_ID)
PARTITIONS 2
STORE IN ( AHD1_DATA,AHD1_DATA);
& in partitions tab in toad, it is showing
partition name max value
ctct_analyst 2308
ctct_others 2305,2307,2310,2405
I have created empty partitioned table ctct_060508 as above & now when I try to copy data from original non parttitoned table into it, it is giving following error
SQL> insert into ctct_060508 select * from ctct;
insert into ctct_060508 select * from ctct
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
But none of the key is different than partition key.
SQL> select distinct c_ctp_id from ctct;
C_CTP_ID
----------
2305
2307
2308
2310
2405
Why error is coming??
6 rows selected.
|
|
|
|
Re: table partition [message #318475 is a reply to message #318273] |
Wed, 07 May 2008 00:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Hi ,
Below is the code( done copy & paste from sqlplus)
SQL> CREATE TABLE CTCT_070508(
2 ID NUMBER NOT NULL,
3 PERSID VARCHAR2 (30),
4 DEL NUMBER NOT NULL,
5 ALIAS NUMBER,
6 LAST_MOD NUMBER,
7 C_RESTRICTED NUMBER,
8 C_LAST_NAME VARCHAR2 (30),
9 C_FIRST_NAME VARCHAR2 (30),
10 C_MIDDLE_NAME VARCHAR2 (30),
11 C_USERID VARCHAR2 (85),
12 C_AKA VARCHAR2 (30),
13 C_PUBLIC_PHONE VARCHAR2 (32),
14 C_FAX_PHONE VARCHAR2 (32),
15 C_VOICE_PHONE VARCHAR2 (32),
16 C_BEEPER_PHONE VARCHAR2 (32),
17 C_EMAIL_SERVICE VARCHAR2 (30),
18 C_EMAIL_ADDR VARCHAR2 (120),
19 C_PEMAIL_ADDR VARCHAR2 (120),
20 C_L_ID NUMBER,
21 C_CTP_ID NUMBER,
22 C_ACCTYP_ID NUMBER,
23 C_PREF_ORDER VARCHAR2 (12),
24 C_CM_ID1 NUMBER,
25 C_CM_ID2 NUMBER,
26 C_CM_ID3 NUMBER,
27 C_CM_ID4 NUMBER,
28 C_WS_ID1 VARCHAR2 (30),
29 C_WS_ID2 VARCHAR2 (30),
30 C_WS_ID3 VARCHAR2 (30),
31 C_WS_ID4 VARCHAR2 (30),
32 C_DEPT VARCHAR2 (12),
33 C_EXPENSE VARCHAR2 (12),
34 C_NOTES VARCHAR2 (240),
35 C_ORG_ID NUMBER,
36 C_ADMIN_ORG_ID NUMBER,
37 C_NX_STRING1 VARCHAR2 (40),
38 C_NX_STRING2 VARCHAR2 (40),
39 C_NX_STRING3 VARCHAR2 (40),
40 C_NX_STRING4 VARCHAR2 (40),
41 C_NX_STRING5 VARCHAR2 (40),
42 C_NX_STRING6 VARCHAR2 (40),
43 C_NX_REF_1 NUMBER,
44 C_NX_REF_2 NUMBER,
45 C_NX_REF_3 NUMBER,
46 C_PARENT NUMBER,
47 C_VENDOR NUMBER,
48 C_DOMAIN NUMBER,
49 C_SERVICE_TYPE VARCHAR2 (30),
50 C_TIMEZONE VARCHAR2 (30),
51 C_VAL_REQ NUMBER,
52 C_SCHEDULE VARCHAR2 (30),
53 C_AVAILABLE NUMBER,
54 C_CONTACT_NUM VARCHAR2 (30),
55 C_ADDR1 VARCHAR2 (30),
56 C_ADDR2 VARCHAR2 (30),
57 C_ADDR3 VARCHAR2 (30),
58 C_ADDR4 VARCHAR2 (30),
59 C_ADDR5 VARCHAR2 (30),
60 C_ADDR6 VARCHAR2 (30),
61 C_CITY VARCHAR2 (30),
62 C_STATE NUMBER,
63 C_ZIP VARCHAR2 (14),
64 C_COUNTRY VARCHAR2 (16),
65 C_POSITION NUMBER,
66 Z_C_PASSWD VARCHAR2 (20),
67 PRIMARY KEY ( ID )
68 USING INDEX
69 TABLESPACE AHD1_IDX PCTFREE 10
70 STORAGE ( INITIAL 106496 NEXT 106496 PCTINCREASE 0 ))
71 TABLESPACE AHD1_DATA
72 PCTFREE 30
73 INITRANS 1
74 MAXTRANS 255
75 STORAGE (
76 INITIAL 57344
77 NEXT 106496
78 PCTINCREASE 0
79 MINEXTENTS 1
80 MAXEXTENTS 2147483645
81 )
82 PARTITION BY LIST(C_CTP_ID)
83 (
84 PARTITION PAR1 VALUES (2308),
85 PARTITION PAR2 VALUES (2305,2307,2310,2405)
86 ) ENABLE ROW MOVEMENT;
Table created.
SQL> insert into ctct_070508 select * from ctct;
insert into ctct_070508 select * from ctct
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
SQL> select distinct c_ctp_id from ctct;
C_CTP_ID
----------
2305
2307
2308
2310
2405
6 rows selected.
SQL>
Note : structure of ctct is same as that of ctct_070508.
[Updated on: Wed, 07 May 2008 00:31] Report message to a moderator
|
|
|
Re: table partition [message #318485 is a reply to message #318011] |
Wed, 07 May 2008 00:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
SQL> select distinct c_ctp_id from ctct;
C_CTP_ID
----------
2305
2307
2308
2310
2405
6 rows selected.
SQL> I see 5 values returned, but 6 rows reported.
The last one is apparently NULL value, which does not fit to any partition you created and the error is raised.
|
|
|
|
|
Re: table partition [message #318511 is a reply to message #318503] |
Wed, 07 May 2008 01:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Thanks.
I have done range partitioning as above.
create table tab3(id number,name varchar2(30))
partition by range(name)
(
partition p1 values less than ('G%'),
partition p2 values less than ('Z%')
)
ENABLE ROW MOVEMENT;
How to include values start with Z??
Since range partitioning supports values less than clause.
[Updated on: Wed, 07 May 2008 03:51] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 06 16:37:13 CDT 2024
|