|
-
I have partitioned my table from SEPT. 1998 - SEPT. 2002. I did not specify the maxvalue
when I created the partition.
Assuming I accidentally dropped my JAN2001(i.e. XFL_JAN2001) partition. Now I would like to split the partition for XFL_DEC2000 partition in order to add XFL_JAN2001 partition. XFL_DEC2000 contains data.
How do I perform this partition split. Below is what I have in mind. Can any one help?
alter table football
split partition XFL_DEC2000
into (
partition XFL_DEC2000 tablespace DEC2000_TABS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)
-
Originally posted by randy
alter table football
split partition XFL_DEC2000
into (
partition XFL_DEC2000 tablespace DEC2000_TABS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)
Assuming you have partitioned on a date:
Code:
alter table football
split partition XFL_DEC2000
at (to_date('01/01/2001','mm/dd/yyyy')) into (
partition XFL_DEC2000 tablespace DEC2000_TBS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)
I'm also not sure about the parallel clause, you'd have to try it....
Jeff Hunter
-
It did not work.
alter table football
split partition XFL_DEC2000
at (to_date('01/01/2001','mm/dd/yyyy')) into (
partition XFL_DEC2000 tablespace DEC2000_TBS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)
XFL_DEC2000
*
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound
-
Right, right, right. I wasn't even looking at what you were trying to do. You must split the higher partition since the values are "LESS THAN".
Code:
alter table football
split partition XFL_FEB2001
at (to_date('01/01/2001','mm/dd/yyyy')) into (
partition XFL_JAN2001 tablespace JAN2001_TBS,
partition XFL_FEB2001 tablespace FEB2001_TBS)
Jeff Hunter
-
It did not work again.
SQL> alter football
2 split partition XFL_FEB2001
3 at (to_date('01/01/2001','mm/dd/yyyy')) into (
4 partition XFL_JAN2001 tablespace JAN2001_TBS,
5 partition XFL_FEB2001 tablespace FEB2001_TBS);
split partition XFL_FEB2001
*
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound
-
OK, I'll need to see your partitioning and table structure.
desc football
set long 2000
col partition_name for a30
col high_value for a40
select partition_name, high_value
from dba_tab_partitions
where table_name = 'FOOTBALL'
order by partition_name
Jeff Hunter
-
SQL> desc football
Name Null? Type
----------------------------------------- -------- ----------------------------
PLAYER_LAST_NAME NOT NULL VARCHAR2(40)
PLAYER_FIRST_NAME NOT NULL VARCHAR2(40)
AGENT_NAME VARCHAR2(50)
SIGN_DATE NOT NULL DATE
TEAM_NAME NOT NULL VARCHAR2(40)
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
XFL_APR2001 TO_DATE(' 2001-05-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_AUG2000 TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_AUG2001 TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
XFL_DEC2000 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_DEC2001 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_JUN2000 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
N')
XFL_FEB2001 TO_DATE(' 2001-03-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_JUL2000 TO_DATE(' 2000-08-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_JUL2001 TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-M
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_JUN2001 TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_MAR2001 TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
XFL_MAY2001 TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_NOV2000 TO_DATE(' 2000-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_NOV2001 TO_DATE(' 2001-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
XFL_OCT2000 TO_DATE(' 2000-11-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_OCT2001 TO_DATE(' 2001-11-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
XFL_SEP2000 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
N')
XFL_SEP2001 TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
-
Oh, yes, you have to split at 02/01/2001 since the values are less than...
Code:
ops$jeffh@dev901.us> @test1
Table dropped.
Elapsed: 00:00:00.09
Table created.
Elapsed: 00:00:00.07
ops$jeffh@dev901.us> l
1 create table football (
2 f1 varchar2(10),
3 dt date,
4 f2 number(10))
5 partition by range (dt)
6 (
7 partition xfl_dec2000 values less than (to_date('01/01/2001','mm/dd/yyyy')),
8 partition xfl_feb2001 values less than (to_Date('03/01/2001','mm/dd/yyyy')),
9* partition xfl_mar2001 values less than (to_Date('04/01/2001','mm/dd/yyyy')))
ops$jeffh@dev901.us> select partition_name, high_value
2 from user_tab_partitions
3 /
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFL_DEC2000 TO_DATE(' 2001-01-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
XFL_FEB2001 TO_DATE(' 2001-03-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
XFL_MAR2001 TO_DATE(' 2001-04-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
Elapsed: 00:00:00.02
ops$jeffh@dev901.us> alter table football
2 split partition xfl_feb2001 at (to_date('02/01/2001','mm/dd/yyyy'))
3 into (partition xfl_jan2001, partition xfl_feb2001);
Table altered.
Elapsed: 00:00:00.07
ops$jeffh@dev901.us> select partition_name, high_value
2 from user_tab_partitions
3 /
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFL_DEC2000 TO_DATE(' 2001-01-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
XFL_FEB2001 TO_DATE(' 2001-03-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
XFL_MAR2001 TO_DATE(' 2001-04-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFL_JAN2001 TO_DATE(' 2001-02-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
Elapsed: 00:00:00.02
ops$jeffh@dev901.us> spool off
Jeff Hunter
-
Good looking out. That was a great help
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote