Very helpful
A reader, January 02, 2003 - 4:38 pm UTC
If the partition is not the last one in a table. This way could save a lot of time on drop and recreate.
rollback
A reader, December 22, 2003 - 10:39 am UTC
Hi
Does split partition use Rollback Segment?
I have been trying to find out myself but my tables arent big enough and the operation is very fast so I cant check v$transaction. Do you know a way to find out if my session has used rollback segment after committing a transaction or DDL?
In fact... do you know what DDL uses RBS? Cant find anything in the documentation
Cheer
December 22, 2003 - 10:52 am UTC
it uses rollback to protect the DDL (the data dictionary) but the direct path operation on the data segment itself won't be recorded in the UNDO (just the DDL changes)
Now, indexes -- if they are maintained during this operation, the will generate UNDO.
All DDL uses UNDO.
The operation being performed may not.
for example:
create table T as select * from billion_row_table;
that'll generate UNDO to protect the data dictionary -- but the table T itself will not contribute to UNDO.
split
A reader, December 22, 2003 - 10:58 am UTC
Hi
A further question, if we split partitions are we working with temporary segments then once the operation finishes these temp segments become the new partitions? I mean we should need temp segments to provide query and ddl protection right? In case the instance fails
Same applies to CTAS, do we need twice segment space when we run a CTAS on a big table?
December 22, 2003 - 11:10 am UTC
yup.
you do not need 2x the space however, the temp segments are created in the target tablespace and converted to permanent segments at the end of the operation.
Sidenote about split
Skip, December 22, 2003 - 12:46 pm UTC
Just make sure you specify the tablespace when using split. It will not split the tablespace and leave both partitions in the original tablespace if you do not specify. It will put the new partitions in your default tablespace.
Combine Partitions
David, December 22, 2003 - 4:36 pm UTC
Tom,
Is it possible to combine partitions? We have a large OLTP/Warehouse application, and we have had to reduce the size of our partitions in order to keep our load rates up. Ideally, we would need one partition per day for data warehousing purposes. However, we have had to break that down into 16 partitions in order keep the load rates up. It would be nice if we could combine the 16 partitions into one after the inserts are finished. All of the queries span all 16 partitions. We insert approximately 400 million records per day into the table, and must maintain 10,000 records inserted per second during the busy hours.
December 22, 2003 - 5:37 pm UTC
that would sort of defeat the purpose of having 16 to load would it not?
(yes we can, but it would take a ton of work to put them all together)
why did you feel the need for the 16 partitions -- in the first place. what problem did they solve?
RE: Combine Partitions
David, December 22, 2003 - 6:04 pm UTC
Tom,
Thanks for the reply. I did not think that it would be practical.
We went with 16 partitions in order to increase our load performance. We found that as the size of the partition increased, specifically the local indexes, the load rates dropped off significantly to a point that the loads would not keep up with the rate that records were coming in.
December 22, 2003 - 6:50 pm UTC
did you determine a root cause?
perhaps it could have been as easy as "multiple freelists is what we need to remove contention"
RE:Combine Partitions
David, December 22, 2003 - 7:03 pm UTC
We played around with freelist, but that did not seem to be a factor. We are not updating or deleting, just inserting records and dropping partitions. The only thing that we could see in the statspack report was a large number of sequential reads. There were no significant waits. We sent that stuff off to Oracle, but their response was just to not do so many inserts.
What was the result of combining the partitions?
Bob Maggio, December 23, 2003 - 8:53 am UTC
We are looking are removing a partition structure, because it was inadequatly planned and is causing some complexity. So instead of combining partitions, we decided to remove them completely from the table. i.e. create new_table as select * from old_partitioned_table and rename all. The tables contain BLOB fields. Is there any issues with doing this? Will the BLOBs affect anything before or after the partitioning?
December 23, 2003 - 11:38 am UTC
the CTAS will take care of coping the blobs as well.
you can use parallel/nologging as well to help speed it up if you like.
Will the local indexes be automatically created after splitting?
Jo Sat, May 05, 2004 - 5:14 pm UTC
Tom
If before splitting the partition/subpartition already has "local" indexes, after splitting will they be created automatically? I query the USER_IND_SUBPARTITIONS after the split, but cannot find indexes for the new splitted partition. How can I make sure that the new partition has the local indexes too?
May 05, 2004 - 7:54 pm UTC
yes you can see them and yes it just does it.
ops$tkyte@ORA9IR2> CREATE TABLE part
2 ( dt date )
3 PARTITION BY RANGE( dt )
4 ( PARTITION p_1_qtr VALUES
5 LESS THAN( to_date('04-01-2000','mm-dd-yyyy') ),
6 PARTITION p_2_qtr VALUES
7 LESS THAN( to_date('07-01-2000','mm-dd-yyyy') ),
8 PARTITION p_3_qtr VALUES
9 LESS THAN( to_date('10-01-2000','mm-dd-yyyy') ),
10 PARTITION p_4_qtr VALUES
11 LESS THAN( to_date('01-01-2001','mm-dd-yyyy') )
12 )
13 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index part_idx on part(dt) LOCAL;
Index created.
ops$tkyte@ORA9IR2> select partition_name from user_ind_partitions where index_name = 'PART_IDX';
PARTITION_NAME
------------------------------
P_1_QTR
P_2_QTR
P_3_QTR
P_4_QTR
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table part
2 split partition p_1_qtr
3 at ( to_date( '02-01-2000', 'mm-dd-yyyy' ) )
4 into ( partition p1_mth, partition p_1_qtr );
Table altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select partition_name from user_ind_partitions where index_name = 'PART_IDX';
PARTITION_NAME
------------------------------
P1_MTH
P_1_QTR
P_2_QTR
P_3_QTR
P_4_QTR
What about tablespace?
A reader, June 22, 2004 - 5:20 am UTC
Tom,
The index gets created OK. But what about their tablespace? i've partitions in tablespace 'X' and the local indexes in tablespace 'Y'.
and after I split the partition, the local index for those 2 partitions gets created in the 'X' tablespace & not 'Y'
Cheers !
June 22, 2004 - 8:41 am UTC
give example (small, concise)
Here you go!
A reader, June 22, 2004 - 12:54 pm UTC
CREATE TABLE PART
(
PK_KEY NUMBER(15) NOT NULL,
PK_ID NUMBER(1) NOT NULL
)
TABLESPACE USERS PARTITION BY RANGE (PK_ID)
(
PARTITION PK_ID_PRD1 VALUES LESS THAN (1)
LOGGING
TABLESPACE ACTIVITY_TAB_TS_1,
PARTITION PK_ID_PRD2 VALUES LESS THAN (2)
LOGGING
TABLESPACE ACTIVITY_TAB_TS_1,
PARTITION PK_ID_PRD3 VALUES LESS THAN (3)
LOGGING
TABLESPACE ACTIVITY_TAB_TS_1,
PARTITION PK_ID_PRD4 VALUES LESS THAN (4)
LOGGING
TABLESPACE ACTIVITY_TAB_TS_1,
PARTITION PK_ID_PRD_MAXVALUE VALUES LESS THAN (MAXVALUE)
LOGGING
TABLESPACE ACTIVITY_TAB_TS_1
)
NOCACHE
NOPARALLEL
ENABLE ROW MOVEMENT;
CREATE INDEX PART_IX ON PART(PK_ID)
LOCAL (PARTITION PK_ID_PRD1 TABLESPACE ACTIVITY_IND_TS_1,
PARTITION PK_ID_PRD2 TABLESPACE ACTIVITY_IND_TS_1,
PARTITION PK_ID_PRD3 TABLESPACE ACTIVITY_IND_TS_1,
PARTITION PK_ID_PRD4 TABLESPACE ACTIVITY_IND_TS_1,
PARTITION PK_ID_PRD_MAXVALUE TABLESPACE ACTIVITY_IND_TS_1)
PARALLEL
NOLOGGING;
select unique tablespace_name from user_tab_partitions; -- ACTIVITY_TAB_TS_1
select unique tablespace_name from user_ind_partitions; -- ACTIVITY_IND_TS_1
ALTER TABLE PART SPLIT PARTITION PK_ID_PRD_MAXVALUE
AT (5) INTO (PARTITION PK_ID_PRD5, PARTITION PK_ID_PRD_MAXVALUE);
select unique tablespace_name from user_tab_partitions; -- ACTIVITY_TAB_TS_1
select unique tablespace_name from user_ind_partitions; -- ACTIVITY_TAB_TS_1 ACTIVITY_IND_TS_1
I'm on oracle 8.1.7.4. Default tablespace is users.
The indexes after splitting, gets created in the tablespace of partitions.
Cheers !
June 22, 2004 - 9:01 pm UTC
(funny how the usefulness of the response decreases with requests for examples/clarifications...)
anyway, after looking at this, that is the designed behaviour:
<quote reference manual>
Oracle splits the corresponding local index partition, even if it is marked UNUSABLE. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to the split partitions. The new index partitions inherit their attributes from the partition being split. Oracle stores the new index partitions in the default tablespace of the index partition being split. If that index partition has no default tablespace, then Oracle uses the tablespace of the new underlying table partitions.
</quote>
It is a quite excellent idea to keep the index and table partitions in the same exact tablespace actually. makes transporting a snap.
Didn't mean that...
A reader, June 23, 2004 - 5:36 am UTC
Didn't mean that TOM.
You are always our TOP MAN with *****
sorry if you took it wrong.
Cheers !
Permissions to Split partition
Jeff Hunter, August 03, 2004 - 12:57 pm UTC
I have an 8.1.7.4 database on Solaris 2.8 in which USERB can split USERA's partitions. USERB has been granted DBA permission, so everything works fine. However, when I upgrade this database to 9.2, the USERB gets an error when trying to split USERA's partitioned table.
With the 8.1.7.4 database, the example is:
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Aug 3 12:37:25 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
$Id: login.sql,v 1.1 2003/10/29 15:15:27 jeffh Exp $
system@dev817.us> set define on
system@dev817.us> set verify off
system@dev817.us> col grantee for a15
system@dev817.us>
system@dev817.us> drop user usera cascade;
User dropped.
Elapsed: 00:00:01.07
system@dev817.us> drop user userb cascade;
User dropped.
Elapsed: 00:00:00.11
system@dev817.us>
system@dev817.us> create user usera identified by usera
2 temporary tablespace temp
3 default tablespace users;
User created.
Elapsed: 00:00:00.01
system@dev817.us>
system@dev817.us> grant connect, dba to usera;
Grant succeeded.
Elapsed: 00:00:00.02
system@dev817.us>
system@dev817.us> create user userb identified by userb
2 temporary tablespace temp
3 default tablespace users;
User created.
Elapsed: 00:00:00.01
system@dev817.us>
system@dev817.us> grant connect, dba to userb;
Grant succeeded.
Elapsed: 00:00:00.02
system@dev817.us>
system@dev817.us> connect usera/usera@&1
Connected.
system@dev817.us>
system@dev817.us> create table xyz (
2 id number(10) primary key,
3 data varchar2(20))
4 partition by range (id)
5 (
6 partition part_1 values less than (10),
7 partition part_2 values less than (20),
8 partition part_n values less than (maxvalue))
9 /
Table created.
Elapsed: 00:00:00.06
system@dev817.us> begin
2 for i in 1..30 loop
3 insert into xyz (id, data) values (i, to_char(i));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
system@dev817.us> commit;
Commit complete.
Elapsed: 00:00:00.01
system@dev817.us>
system@dev817.us> connect userb/userb@&1
Connected.
system@dev817.us>
system@dev817.us> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Elapsed: 00:00:00.01
system@dev817.us> select username from dba_users where username = USER;
USERNAME
------------------------------
USERB
Elapsed: 00:00:00.01
system@dev817.us> select granted_role from dba_role_privs where grantee = USER;
GRANTED_ROLE
------------------------------
CONNECT
DBA
Elapsed: 00:00:00.02
system@dev817.us> select grantee,
2 privilege
3 from dba_sys_privs where grantee in (USER,'DBA')
4 order by privilege;
GRANTEE PRIVILEGE
--------------- ----------------------------------------
DBA ADMINISTER DATABASE TRIGGER
DBA ADMINISTER RESOURCE MANAGER
DBA ALTER ANY CLUSTER
DBA ALTER ANY DIMENSION
DBA ALTER ANY INDEX
DBA ALTER ANY INDEXTYPE
DBA ALTER ANY LIBRARY
DBA ALTER ANY OUTLINE
DBA ALTER ANY PROCEDURE
DBA ALTER ANY ROLE
DBA ALTER ANY SEQUENCE
DBA ALTER ANY SNAPSHOT
DBA ALTER ANY TABLE
DBA ALTER ANY TRIGGER
DBA ALTER ANY TYPE
DBA ALTER DATABASE
DBA ALTER PROFILE
DBA ALTER RESOURCE COST
DBA ALTER ROLLBACK SEGMENT
DBA ALTER SESSION
DBA ALTER SYSTEM
DBA ALTER TABLESPACE
DBA ALTER USER
DBA ANALYZE ANY
DBA AUDIT ANY
DBA AUDIT SYSTEM
DBA BACKUP ANY TABLE
DBA BECOME USER
DBA COMMENT ANY TABLE
DBA CREATE ANY CLUSTER
DBA CREATE ANY CONTEXT
DBA CREATE ANY DIMENSION
DBA CREATE ANY DIRECTORY
DBA CREATE ANY INDEX
DBA CREATE ANY INDEXTYPE
DBA CREATE ANY LIBRARY
DBA CREATE ANY OPERATOR
DBA CREATE ANY OUTLINE
DBA CREATE ANY PROCEDURE
DBA CREATE ANY SEQUENCE
DBA CREATE ANY SNAPSHOT
DBA CREATE ANY SYNONYM
DBA CREATE ANY TABLE
DBA CREATE ANY TRIGGER
DBA CREATE ANY TYPE
DBA CREATE ANY VIEW
DBA CREATE CLUSTER
DBA CREATE DATABASE LINK
DBA CREATE DIMENSION
DBA CREATE INDEXTYPE
DBA CREATE LIBRARY
DBA CREATE OPERATOR
DBA CREATE PROCEDURE
DBA CREATE PROFILE
DBA CREATE PUBLIC DATABASE LINK
DBA CREATE PUBLIC SYNONYM
DBA CREATE ROLE
DBA CREATE ROLLBACK SEGMENT
DBA CREATE SEQUENCE
DBA CREATE SESSION
DBA CREATE SNAPSHOT
DBA CREATE SYNONYM
DBA CREATE TABLE
DBA CREATE TABLESPACE
DBA CREATE TRIGGER
DBA CREATE TYPE
DBA CREATE USER
DBA CREATE VIEW
DBA DELETE ANY TABLE
DBA DEQUEUE ANY QUEUE
DBA DROP ANY CLUSTER
DBA DROP ANY CONTEXT
DBA DROP ANY DIMENSION
DBA DROP ANY DIRECTORY
DBA DROP ANY INDEX
DBA DROP ANY INDEXTYPE
DBA DROP ANY LIBRARY
DBA DROP ANY OPERATOR
DBA DROP ANY OUTLINE
DBA DROP ANY PROCEDURE
DBA DROP ANY ROLE
DBA DROP ANY SEQUENCE
DBA DROP ANY SNAPSHOT
DBA DROP ANY SYNONYM
DBA DROP ANY TABLE
DBA DROP ANY TRIGGER
DBA DROP ANY TYPE
DBA DROP ANY VIEW
DBA DROP PROFILE
DBA DROP PUBLIC DATABASE LINK
DBA DROP PUBLIC SYNONYM
DBA DROP ROLLBACK SEGMENT
DBA DROP TABLESPACE
DBA DROP USER
DBA ENQUEUE ANY QUEUE
DBA EXECUTE ANY INDEXTYPE
DBA EXECUTE ANY LIBRARY
DBA EXECUTE ANY OPERATOR
DBA EXECUTE ANY PROCEDURE
DBA EXECUTE ANY TYPE
DBA FORCE ANY TRANSACTION
DBA FORCE TRANSACTION
DBA GLOBAL QUERY REWRITE
DBA GRANT ANY PRIVILEGE
DBA GRANT ANY ROLE
DBA INSERT ANY TABLE
DBA LOCK ANY TABLE
DBA MANAGE ANY QUEUE
DBA MANAGE TABLESPACE
DBA QUERY REWRITE
DBA RESTRICTED SESSION
DBA SELECT ANY SEQUENCE
DBA SELECT ANY TABLE
USERB UNLIMITED TABLESPACE
DBA UPDATE ANY TABLE
115 rows selected.
Elapsed: 00:00:00.05
system@dev817.us>
system@dev817.us> select count(*) from usera.xyz;
COUNT(*)
----------
30
Elapsed: 00:00:00.01
system@dev817.us> alter table usera.xyz
2 split partition part_n at (30)
3 into (partition part_3, partition part_n);
Table altered.
Elapsed: 00:00:00.06
system@dev817.us> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
But the 9.2.0.5 example is:
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Aug 3 12:37:41 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
$Id: login.sql,v 1.1 2003/10/29 15:15:27 jeffh Exp $
system@dev920.us> set define on
system@dev920.us> set verify off
system@dev920.us> col grantee for a15
system@dev920.us>
system@dev920.us> drop user usera cascade;
User dropped.
Elapsed: 00:00:00.30
system@dev920.us> drop user userb cascade;
User dropped.
Elapsed: 00:00:00.12
system@dev920.us>
system@dev920.us> create user usera identified by usera
2 temporary tablespace temp
3 default tablespace users;
User created.
Elapsed: 00:00:00.02
system@dev920.us>
system@dev920.us> grant connect, dba to usera;
Grant succeeded.
Elapsed: 00:00:00.02
system@dev920.us>
system@dev920.us> create user userb identified by userb
2 temporary tablespace temp
3 default tablespace users;
User created.
Elapsed: 00:00:00.01
system@dev920.us>
system@dev920.us> grant connect, dba to userb;
Grant succeeded.
Elapsed: 00:00:00.02
system@dev920.us>
system@dev920.us> connect usera/usera@&1
Connected.
system@dev920.us>
system@dev920.us> create table xyz (
2 id number(10) primary key,
3 data varchar2(20))
4 partition by range (id)
5 (
6 partition part_1 values less than (10),
7 partition part_2 values less than (20),
8 partition part_n values less than (maxvalue))
9 /
Table created.
Elapsed: 00:00:00.10
system@dev920.us> begin
2 for i in 1..30 loop
3 insert into xyz (id, data) values (i, to_char(i));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
system@dev920.us> commit;
Commit complete.
Elapsed: 00:00:00.00
system@dev920.us>
system@dev920.us> connect userb/userb@&1
Connected.
system@dev920.us>
system@dev920.us> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
Elapsed: 00:00:00.01
system@dev920.us> select username from dba_users where username = USER;
USERNAME
------------------------------
USERB
Elapsed: 00:00:00.01
system@dev920.us> select granted_role from dba_role_privs where grantee = USER;
GRANTED_ROLE
------------------------------
DBA
CONNECT
Elapsed: 00:00:00.01
system@dev920.us> select grantee,
2 privilege
3 from dba_sys_privs where grantee in (USER,'DBA')
4 order by privilege;
GRANTEE PRIVILEGE
--------------- ----------------------------------------
DBA ADMINISTER DATABASE TRIGGER
DBA ADMINISTER RESOURCE MANAGER
DBA ADMINISTER SECURITY
DBA ALTER ANY CLUSTER
DBA ALTER ANY DIMENSION
DBA ALTER ANY EVALUATION CONTEXT
DBA ALTER ANY INDEX
DBA ALTER ANY INDEXTYPE
DBA ALTER ANY LIBRARY
DBA ALTER ANY OUTLINE
DBA ALTER ANY PROCEDURE
DBA ALTER ANY ROLE
DBA ALTER ANY RULE
DBA ALTER ANY RULE SET
DBA ALTER ANY SECURITY PROFILE
DBA ALTER ANY SEQUENCE
DBA ALTER ANY SNAPSHOT
DBA ALTER ANY TABLE
DBA ALTER ANY TRIGGER
DBA ALTER ANY TYPE
DBA ALTER DATABASE
DBA ALTER PROFILE
DBA ALTER RESOURCE COST
DBA ALTER ROLLBACK SEGMENT
DBA ALTER SESSION
DBA ALTER SYSTEM
DBA ALTER TABLESPACE
DBA ALTER USER
DBA ANALYZE ANY
DBA AUDIT ANY
DBA AUDIT SYSTEM
DBA BACKUP ANY TABLE
DBA BECOME USER
DBA COMMENT ANY TABLE
DBA CREATE ANY CLUSTER
DBA CREATE ANY CONTEXT
DBA CREATE ANY DIMENSION
DBA CREATE ANY DIRECTORY
DBA CREATE ANY EVALUATION CONTEXT
DBA CREATE ANY INDEX
DBA CREATE ANY INDEXTYPE
DBA CREATE ANY LIBRARY
DBA CREATE ANY OPERATOR
DBA CREATE ANY OUTLINE
DBA CREATE ANY PROCEDURE
DBA CREATE ANY RULE
DBA CREATE ANY RULE SET
DBA CREATE ANY SECURITY PROFILE
DBA CREATE ANY SEQUENCE
DBA CREATE ANY SNAPSHOT
DBA CREATE ANY SYNONYM
DBA CREATE ANY TABLE
DBA CREATE ANY TRIGGER
DBA CREATE ANY TYPE
DBA CREATE ANY VIEW
DBA CREATE CLUSTER
DBA CREATE DATABASE LINK
DBA CREATE DIMENSION
DBA CREATE EVALUATION CONTEXT
DBA CREATE INDEXTYPE
DBA CREATE LIBRARY
DBA CREATE OPERATOR
DBA CREATE PROCEDURE
DBA CREATE PROFILE
DBA CREATE PUBLIC DATABASE LINK
DBA CREATE PUBLIC SYNONYM
DBA CREATE ROLE
DBA CREATE ROLLBACK SEGMENT
DBA CREATE RULE
DBA CREATE RULE SET
DBA CREATE SECURITY PROFILE
DBA CREATE SEQUENCE
DBA CREATE SESSION
DBA CREATE SNAPSHOT
DBA CREATE SYNONYM
DBA CREATE TABLE
DBA CREATE TABLESPACE
DBA CREATE TRIGGER
DBA CREATE TYPE
DBA CREATE USER
DBA CREATE VIEW
DBA DEBUG ANY PROCEDURE
DBA DEBUG CONNECT SESSION
DBA DELETE ANY TABLE
DBA DEQUEUE ANY QUEUE
DBA DROP ANY CLUSTER
DBA DROP ANY CONTEXT
DBA DROP ANY DIMENSION
DBA DROP ANY DIRECTORY
DBA DROP ANY EVALUATION CONTEXT
DBA DROP ANY INDEX
DBA DROP ANY INDEXTYPE
DBA DROP ANY LIBRARY
DBA DROP ANY OPERATOR
DBA DROP ANY OUTLINE
DBA DROP ANY PROCEDURE
DBA DROP ANY ROLE
DBA DROP ANY RULE
DBA DROP ANY RULE SET
DBA DROP ANY SEQUENCE
DBA DROP ANY SNAPSHOT
DBA DROP ANY SYNONYM
DBA DROP ANY TABLE
DBA DROP ANY TRIGGER
DBA DROP ANY TYPE
DBA DROP ANY VIEW
DBA DROP PROFILE
DBA DROP PUBLIC DATABASE LINK
DBA DROP PUBLIC SYNONYM
DBA DROP ROLLBACK SEGMENT
DBA DROP TABLESPACE
DBA DROP USER
DBA ENQUEUE ANY QUEUE
DBA EXECUTE ANY EVALUATION CONTEXT
DBA EXECUTE ANY INDEXTYPE
DBA EXECUTE ANY LIBRARY
DBA EXECUTE ANY OPERATOR
DBA EXECUTE ANY PROCEDURE
DBA EXECUTE ANY RULE
DBA EXECUTE ANY RULE SET
DBA EXECUTE ANY TYPE
DBA FLASHBACK ANY TABLE
DBA FORCE ANY TRANSACTION
DBA FORCE TRANSACTION
DBA GLOBAL QUERY REWRITE
DBA GRANT ANY OBJECT PRIVILEGES
DBA GRANT ANY PRIVILEGE
DBA GRANT ANY ROLE
DBA INSERT ANY TABLE
DBA LOCK ANY TABLE
DBA MANAGE ANY QUEUE
DBA MANAGE TABLESPACE
DBA ON COMMIT REFRESH
DBA QUERY REWRITE
DBA RESTRICTED SESSION
DBA RESUMABLE
DBA SELECT ANY DICTIONARY
DBA SELECT ANY SEQUENCE
DBA SELECT ANY TABLE
DBA UNDER ANY TABLE
DBA UNDER ANY TYPE
DBA UNDER ANY VIEW
USERB UNLIMITED TABLESPACE
DBA UPDATE ANY TABLE
144 rows selected.
Elapsed: 00:00:00.04
system@dev920.us>
system@dev920.us> select count(*) from usera.xyz;
COUNT(*)
----------
30
Elapsed: 00:00:00.02
system@dev920.us> alter table usera.xyz
2 split partition part_n at (30)
3 into (partition part_3, partition part_n);
alter table usera.xyz
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:01.06
system@dev920.us> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
Obviously there is a permission missing somewhere, but which one?
August 03, 2004 - 5:38 pm UTC
(you could make it shorter next time, remove whitespace and such but...)
turning on trace showed:
PARSE ERROR #13:len=118 dep=1 uid=166 oct=3 lid=166 tim=1065984668769021 err=942
select /*+ FIRST_ROWS PARALLEL(XYZ,1) */ 1 from "USERA"."XYZ" PARTITION ("PART_N") where ("ID" < 30) and rownum < 2
EXEC #1:c=20000,e=146573,p=0,cr=21,cu=8,mis=0,r=0,dep=0,og=4,tim=1065984668769832
ERROR #1:err=942 tim=1782647682
WAIT #1: nam='log file sync' ela= 89 p1=3673 p2=0 p3=0
WAIT #1: nam='SQL*Net break/reset to client' ela= 80 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net break/reset to client' ela= 52 p1=1650815232 p2=0 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 829301 p1=1650815232 p2=1 p3=0
XCTEND rlbk=0, rd_only=1
that query "failed to parse", noticed the dep=1 (recursive sql).
tracked it down -- it is coming from the CDC (change data capture) triggers.
you can either
a) contact support and see if they have a workaround
b) disable CDC
c) grant select on this table to userb directly.
Jeff Hunter, August 03, 2004 - 1:01 pm UTC
fudge... I got truncated...
DBA DROP TABLESPACE
DBA DROP USER
DBA ENQUEUE ANY QUEUE
DBA EXECUTE ANY EVALUATION CONTEXT
DBA EXECUTE ANY INDEXTYPE
DBA EXECUTE ANY LIBRARY
DBA EXECUTE ANY OPERATOR
DBA EXECUTE ANY PROCEDURE
DBA EXECUTE ANY RULE
DBA EXECUTE ANY RULE SET
DBA EXECUTE ANY TYPE
DBA FLASHBACK ANY TABLE
DBA FORCE ANY TRANSACTION
DBA FORCE TRANSACTION
DBA GLOBAL QUERY REWRITE
DBA GRANT ANY OBJECT PRIVILEGES
DBA GRANT ANY PRIVILEGE
DBA GRANT ANY ROLE
DBA INSERT ANY TABLE
DBA LOCK ANY TABLE
DBA MANAGE ANY QUEUE
DBA MANAGE TABLESPACE
DBA ON COMMIT REFRESH
DBA QUERY REWRITE
DBA RESTRICTED SESSION
DBA RESUMABLE
DBA SELECT ANY DICTIONARY
DBA SELECT ANY SEQUENCE
DBA SELECT ANY TABLE
DBA UNDER ANY TABLE
DBA UNDER ANY TYPE
DBA UNDER ANY VIEW
USERB UNLIMITED TABLESPACE
DBA UPDATE ANY TABLE
144 rows selected.
Elapsed: 00:00:00.04
system@dev920.us>
system@dev920.us> select count(*) from usera.xyz;
COUNT(*)
----------
30
Elapsed: 00:00:00.02
system@dev920.us> alter table usera.xyz
2 split partition part_n at (30)
3 into (partition part_3, partition part_n);
alter table usera.xyz
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:01.06
system@dev920.us> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
Obviously, I am missing some permission, but I can't find which one...
August 03, 2004 - 5:38 pm UTC
see above.....
Splitting or Adding a partition
William Wong, August 09, 2004 - 11:50 am UTC
I am planning to partition some history tables by date (month) and plan to code the DDL operations of dropping and adding new partition in weekly scheduled archive program instead of relying on DBA to change the table partitions. I am not sure if I should split the last partition into 2 or I should simply add a new partition every week. What do you think is the better choice in my case ? I am not using maxvalue for the last partition, but I plan to put the last partition in a future week so it will be empty when split, because I don't want to spend time rebuilding local indexes. Do you think this is a helpful or not necessary ? Thank you.
August 09, 2004 - 1:56 pm UTC
if you use maxvalue, split. this "end partition" will be emtpy anyway.
if you do not use maxvalue, use add, but set the date only to what you need it to be set to.
Split or Add Partition
William Wong, August 09, 2004 - 3:21 pm UTC
Sound perfectly simple and it clears my thinking too! May be my question should be: Given I can choose freely to use MAXVALUE or not, is there any reason why I should choose one over the other ? I am looking at things like performance, non-portable codings and re-startability in case of failure. Thank you.
August 09, 2004 - 8:09 pm UTC
there are two cases:
a) you expect values to exceed your last "real" partition. why? bad data I suppose. they would all go into this last partition. Using anything short of "maxvalue" would seem to say "bad data upto 01-jan-2005 is OK, but beyond -- no go".
So, it seems maxvalue would apply...
b) you don't expect them ever to exceed that -- in which case, you leave no place for them to go (they fail on the insert)...
otherwise, maxvalue isn't going to perform much differently than "01-jan-2005" would.
verona, September 11, 2004 - 3:06 pm UTC
Hi Tom
I want to drop partitions from a table on a regular basis.
every time i populate the table,i want to drop the oldest partition.for convenience i named the partitions with
the date on them..like partition_20040303..say if i am building the table on 20040404..i want to drop the oldest partition_20040303..
i looked at dba_tab_partitions..but when i joined to HIGH_VALUE....for that partition..the HIGH_VALUE is 20040303..the join didnt work as its of long datatype..
i am doing it manually now..planning to automate this..
any inputs?
September 11, 2004 - 3:32 pm UTC
what about PARTITION_POSITION
index partition name
Steve, October 11, 2004 - 12:09 pm UTC
Hi Tom,
You said:
"It is a quite excellent idea to keep the index and table partitions in the same exact tablespace actually. makes transporting a snap."
1) Is this will affect the parallel operation performance?
2) When I split a partition, the new index partion name has
the same name of corresponding split table partition name.
How do I specify the split index partion name?
Since they(index partion name and table partition name) have differnet name when created.
3) Is it good idea to keep their names same? if so any side effect?
Thanks!
Steve
Thanks!
October 11, 2004 - 1:52 pm UTC
1) no, yes, maybe.
Look -- create tablespace DATA datafile '/d01/data.dbf'
create tablespace INDEX datafile '/d01/index.dbf'
versus
create tablespace BOTH datafile '/d01/both.dbf'
any different? a tablespace doesn't mean "separate devices". Also, we read an INDEX a block at a time to read a TABLE a block at a time -- block -> block -> block -> block -- it is all very serial.
If you are using striping at the OS level, that is all you need here. Also, a tablespace both could be:
create tablespace both datafile '/d01/a.dbf', '/d02/b.dbf', '/d03/c.dbf', ........
as well -- giving us "extent striping" -- extents would be spread across many devices (assuming d01, d02, d03 were physically separate devices)
you want to stripe -- your goal is to achieve EVEN IO across all devices. You can stripe a tablespace, you can stripe a file system -- but putting indexes on disk1 and table on disk2 will never result in even IO really.
2) you can rename indexes. but why not just keep the parity going? seems "nice" actually....
same or different tablespaces
Vadim Gotovsky, October 12, 2004 - 4:07 pm UTC
Hi,
just wanted to share personal experience of working with 2TB database (mix of Batches, OLTP and DSS):
placing tables and indexes onto different tablespaces and placing tablespaces onto different striped devices improved performance 20%-30%. It was true for both Compaq and EMC Clarion CX600 storage.
Contrary to SAME methodology and EMC engineers claim "it does not matter where you put tablespaces, our cache can handle everything" it proved otherwise in our specific environment. We ended up creating separate RAID groups for DATA, INDEX, TEMP, UNDO tablespaces and Redo logs.
So, I guess it all depends.
One advantage of having separate tablespaces for tables and indexes is more flexibility of separating tablespaces in case performance issues arise.
Thanks,
Vadim
October 12, 2004 - 4:13 pm UTC
*tablespaces* had nothing to do with this.
think about it.
it had everything to do with EVEN IO OVER ALL DEVICES.
Creating on massively striped array would do the same thing.
*it was not because you separated index from data*
it was because you evened out your IO
same goal would have been (more easily) achieved by using small extents (so each object is in a couple of hundred extents) and one tablespace with files from each "device". we would have striped the data over all of the available resources.
*tablespaces are for administrative purposes*
*tablespaces are not the thing for "tuning"*
physical segregation of TEMP from DATA (inclusive of undo, idx and "table" stuff) from REDO -- definitely makes sense. but even IO for that data stuff (index is not different from table data, we do single and multi block IO on both, we read them sequentially for a given user if we read both -- they are just data)
same or separate tablespaces
Vadim Gotovsky, October 12, 2004 - 5:16 pm UTC
Hi Tom,
thanks for your reply, it is informative as always. You helped me few times on helpperf in old good days.
But going back to my posting.
I never claimed that performance improvement was due to different tablespaces, or did I? All I told is that putting data and index onto separate tablespaces are just MEANS of putting them onto separate subsystems.
Regarding the data (both Index and Data) reading patterns maybe you can put some light on this situation:
1. we have many OLTP queries performing unique or small range index scans, sometimes they access only index without need to access a table
2. we have batch jobs that perform multi tables/partitions hash joins doing full table/partition scans
In first case single block is read one by one, in second multi block I/O is performed.
When we had everything striped across everything (we do have 100s of small extents and according to statspack, file system and storage analysis I/O was evenly distributed across all oracle files/file systems/disks) access to indexes was 3-5 times slower (again according to stats pack) than when they were moved to a different storage subsystem.
We concluded and it was confirmed by storage engineers that access pattern (single block or continues set of blocks) matters and indexes are better to be on a separate set of striped physical disks. And moving them away proved that assumption. Now, it could be a coincidence, but it is hard to believe.
Also, would not we want to use a different stripe size for indexes and tables ?
Thanks,
Vadim
October 12, 2004 - 7:02 pm UTC
<quote>
placing tables and indexes onto different tablespaces and placing tablespaces
onto different striped devices improved performance 20%-30%.
</quote>
I couldn't find any way to read that other than "put table data and indexes in separate tablespaces for performance"
but what about the table access by the index -- how do you solve that?
and what about the index full scans -- how do you solve that?
In general, you do single block IO against tables -- and indexes.
In general, you do multiblock IO against indexes -- and tables.
and no -- i would not want different stripe sizes (ASM in 10g -- most everything is a megabyte)
what you really wanted to do (to work around the "auto magic read ahead algorithm when we start detecting multi-block IO on the system" your disk vendor had in place was either)
o segregate objects by their general IO type (eg: tables you *never full scan*, indexes you *never fast full scan* -- together,
tables you infrequently full scan and indexes you infrequently fast full scan -- together,
tables you frequently full scan and indexes you frequently fast full scan -- together)
or
o ask them to tune back that adaptive read ahead
You had a semi-special case, but it wasn't "indexes vs tables", it was "things we single block IO vs things we sometimes multi-block IO vs things we frequently multi-block IO" because of the vendors underlying "we'll kick in multi-block IO under the covers, even when you ask for a single block" algorithm
And I would say "alter move", "alter rebuild" is sometimes even easier than "offline tablespace", "move files"
agreed
Vadim Gotovsky, October 12, 2004 - 10:08 pm UTC
Thanks Tom,
1. I should have been more clear
2. One I/O type versus another I/O type - in my case to large extent is tables versus indexes
regards,
Vadim
October 13, 2004 - 7:57 am UTC
well, not to keep dragging this out -- but, it is more than "tables" versus "indexes" if you are going to tune this.
for you see - every index IO is pretty much going to result in a single block table IO (more or less).
So, it seems to me there are tables that are predominantly single block IO. they would benefit from being segregated from the tables/indexes that are predominantly multi-block IO oriented.
Excellent
Henrik Verup, November 05, 2004 - 9:06 am UTC
I just used this to perform my first parttioning and split ever on a non partitioned datawarehouse fact table, and it just worked!
Split partition.
Kashif, November 19, 2004 - 5:20 pm UTC
Hi Tom,
Can you explain how I can analyze the space required for a split partition operation? For example,
alter table t split partition part1 at (1000) into (partition part1, partition part2);
part1 data is in dtspace1, and currently occupies 120M. The corresponding index partition part1 is in tablespace xtspace1, and occupies 100M. How much additional space will I require for the split operation to complete successfully in each of the tablespaces? Hope this makes sense, thanks.
Kashif
November 19, 2004 - 7:56 pm UTC
probably at least 2 to 3x the space -- during the operation it will be duplicated at least.
Can we split partition in parallel?
Robert, June 17, 2006 - 12:06 pm UTC
Tom,
Oracle 8.1.7.4
Can we use any type of parallelism with "alter table...split partition" command?
I have a 30 Gig partition that needs splitting and not sure if I have a large enough down-time window :(
This question is specifically for 8.1.7.4 (but also, what about 9.2)?
Thanks,
Robert
June 17, 2006 - 4:16 pm UTC
Bull's Eye!
Robert, June 19, 2006 - 11:16 am UTC
Tom,
I did call myself looking closely at the documentation before asking you! :( .... but apparently not closely enough. I was surprised at *not* seeing that the split partition would work in parallel because it did seem like something that Oracle could 'easily' do in parallel.
I now see you indeed *can* split a partition in parallel.
Thanks for your help and patience!
Robert.
split partition into different tablespaces
A reader, April 25, 2007 - 2:08 pm UTC
Is there any way that I can split partition into different tablespaces?
SQL> alter table t
2 split partition t1 at (20051201) into
3 (partition t2 store as (tablespace test1), partition t1 store as (tablespace test2));
(partition t2 store as (tablespace test1), partition t1 store as (tablespace test2))
*
ERROR at line 3:
ORA-01738: missing IN keyword
store as only used for LOB, right?
store in only used for SUBPARTITION, right?
The above example is range parition.
Thanks!
April 25, 2007 - 3:50 pm UTC
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t split partition junk
2 at (to_date('15-mar-2003')) into
3 (partition part3 tablespace users, partition junk tablespace assm);
Table altered.
Update indexes + performance
Dawn, April 30, 2007 - 7:31 am UTC
Hi Tom,
I've searched the documentation and your site and can't find the answer to my question. Hopefully you'll know the answer off the top of your head!
The situation is this: Every weeknight, we have a job which runs a procedure that, amongst other things, drops the oldest partition on a table (with local and global indexes) and then splits the catchall partition. This just uses the split partition without the "update indexes" clause, and this causes no issues 99% of the time, since there is no data in the catchall partition.
However, the job has been known to fail, leading to data being placed in the catchall partition. This causes problems in that the global indexes become unusable when the procedure is run manually.
I was just going to add the "update indexes" clause to the end of the split partition, but I'm wondering whether this will impact on performance. Initial tests (with no data in the catchall partition) indicate that the split will take anywhere between 1 and 3 seconds, regardless of whether the "update indexes" clause is there or not, so I'm at a loss as to which is the best option:
a) simply add the "update indexes" clause, so that this is done every time, regardless of whether there is data or not in the catchall partition. (possibly ok, especially if the "update indexes" is only done if any become marked unusable)
b) test whether rows exist in the catchall partition (open cursor, fetch, row found?, close cursor)and run the relevant split partition based on the test. (ensures the correct version of the split partition is used, but introduces the overhead of the rowcheck)
What would you do?
April 30, 2007 - 9:45 am UTC
The update indexes is done transactionally (it does the logical equivalent of deletes and inserts). If there is no data to "update the indexes with", this will have minimum impact performance wise - the time to achieve the partition operation with update indexes will be a function of the amount of data affected.
You seem to want the indexes to be maintained, so, it seems reasonable to use the update indexes - it is an "online" operation...
Update indexes + performance
Dawn, April 30, 2007 - 9:47 am UTC
Thanks for that, Tom - it didn't look like it had any impact on performance, but I couldn't be sure.
Thanks for confirming things for me.
Split partition is raising error: table or view does not exist ...
A reader, August 09, 2007 - 8:34 am UTC
Hi Tom,
Thanks for this fantastic site, having a problem, can you please assist, thanks.
I have a table having range and list partition:
CREATE TABLE TEST
(
REGIONAL_BUS_UNIT_KEY INTEGER,
CUSTOMER_KEY INTEGER,
DAY_KEY INTEGER,
REGISTER_CODE VARCHAR2(2 BYTE),
TRANSACTION_NUM NUMBER(10),
SEQUENCE_NUM NUMBER(3),
ITEM_KEY INTEGER,
ITEM_SET_KEY INTEGER,
POS_UNITS NUMBER(13,2),
POS_EXTENDED_RTL_DLRS NUMBER(11,2),
ORIGINAL_PRICE NUMBER(11,2),
POS_COMPARABLE_USD NUMBER(11,2),
POS_MONTHLY_ACTUAL_DLRS NUMBER(11,2),
ITEMS_PER_SALE_QTY NUMBER(5),
DESTINATION_STORE_NUM NUMBER(5),
EMPLOYEE_KEY INTEGER,
ORIG_EMPLOYEE_KEY INTEGER,
REG_ENTRY_TIME DATE,
TRANSACTION_TYPE_KEY INTEGER,
IPMS_TRANSACTION_CODE NUMBER(2),
DISCOUNT_CODE_KEY INTEGER,
DISCOUNT_PCT NUMBER(5,2),
HOUR_KEY INTEGER,
PROCESS_DATE DATE,
TRANSACTION_COUNT NUMBER(22),
REGISTER_NUM NUMBER(2),
TRANSACTION_ID NUMBER(17),
RETAIL_STORE_KEY INTEGER,
CURRENCY_CODE_KEY NUMBER,
TRANS_UOM VARCHAR2(3 BYTE),
POS_COMPARABLE_EUR NUMBER(11,2),
POS_MONTHLY_ACTUAL_EUR NUMBER(11,2),
REGISTER_KEY NUMBER(3),
GENSEX_CODE NUMBER
)
PARTITION BY RANGE (DAY_KEY)
SUBPARTITION BY LIST (REGIONAL_BUS_UNIT_KEY)
(
PARTITION D20071202 VALUES LESS THAN (6341)
NOLOGGING
NOCOMPRESS
( SUBPARTITION D20071202_NA VALUES (3),
SUBPARTITION D20071202_EU VALUES (5),
SUBPARTITION D20071202_JP VALUES (4),
SUBPARTITION D20071202_AP VALUES (6) ),
PARTITION D20071204 VALUES LESS THAN (6343)
NOLOGGING
NOCOMPRESS
( SUBPARTITION D20071204_NA VALUES (3),
SUBPARTITION D20071204_EU VALUES (5),
SUBPARTITION D20071204_JP VALUES (4),
SUBPARTITION D20071204_AP VALUES (6) ))
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
this is just a test table where as the actual table has partitions since 1999 and till 2008, so its a big table.
my concern is that you can see I missed one partition in between, 1203, I just want to add a partition D20071203 with the value 6342, having same subpartitions.
Can any one help, when I execute this command:
ALTER TABLE HASHIMS.TEST
SPLIT PARTITION D20071204 AT
(6342)
INTO (PARTITION D20071203,
PARTITION D20071204);
it is saying table of view does not exist ...
Can any one assist here, what is the problem and how to resolve?
Thanks a thousand for answering my questions.
August 14, 2007 - 10:46 am UTC
you have subpartitions to split, not a partition - the table and partition are just logical containers here - not physical entities.
To split or to add
Guy Lambregts, August 27, 2007 - 11:55 am UTC
Tom,
10.2.0.3
I' ve range partitioned my biggest tables on insert date.
Every month partition will be created in a dedicated tablespace. My philosophy is :
1. put some "old" tablespace in read only mode, as such we benefit the rman' s skip readonly clause (once inserted data does not need to be changed anymore)
2. add or split the "most recent" partition
I' ve done some tests as well I' ve read this thread and it it seems to me that I have the choice
either
to have SPLIT in November the at that moment still empty "December" partition with the update global indexes clause in order to have another partition for the January data, and this if I use the maxvalue boundary
either
to have ADDED in November either December the January partition if I use a real date boundary
Is it true we can consider both as 100% online with no significant performance impact ? (assuming the December split is done in November)
In our case junk data is not allowed neither likely to occur, is there really not any argument in favour of splitting or adding ?
September 04, 2007 - 11:56 am UTC
add would be more online than split - split will have to consider that it might have to move data, add just adds at the end.
I am ignoring all warning to ask a new question here.
Rajesh, March 09, 2009 - 2:50 pm UTC
I have to split a single monthly partition (for month-end date) into daily partition for each day of the month. I am currently doing it one at a time, that is, by splitting one partition into two like 31st january into 31st January and 1st January. Then 31st January into 31st and 2nd January.( It is all in a loop). My question is can I one partition into more than 2 partition in one command so that they can happen in parallel.
My current process is taking 10 hours to complete ( One month data is about 30 million which is splitting into approximate 1 million rows each day).
I need to bring this time down to about couple of hours.
Hari Krishna, February 05, 2015 - 11:00 am UTC
ALTER TABLE SALES_TIME_PARTITION
SPLIT PARTITION SALES_1998
AT (TO_DATE('25-MAR-1998 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION SALES_1_1998,PARTITION SALES_1998)
UPDATE GLOBAL INDEXES;
Error report -
SQL Error: ORA-14400: inserted partition key does not map to any partition
How to resolve this error ?
Table Definition:
-----------------
CREATE TABLE "HARI"."SALES_TIME_PARTITION"
( "PROD_ID" NUMBER,
"CUST_ID" NUMBER,
"TIME_ID" DATE,
"CHANNEL_ID" NUMBER,
"PROMO_ID" NUMBER,
"QUANTITY_SOLD" NUMBER(10,2),
"AMOUNT_SOLD" NUMBER(10,2)
) PARTITION BY RANGE ("TIME_ID")(
PARTITION "SALES_1998" VALUES LESS THAN (TO_DATE(' 1998-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "SALES_1999" VALUES LESS THAN (TO_DATE(' 1999-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "SALES_2000" VALUES LESS THAN (TO_DATE(' 2000-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "SALES_2001" VALUES LESS THAN (TO_DATE(' 2001-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);