Well, it opens up the interesting possibility of:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t
  2  exchange partition p_0012
  3  with table NEW_DATA
  4  without validation
  5  /

-- you could easily swap a new set of data in there at the drop of a hat (and not lose grants, etc)....


You could also turn this table with a single partition into a multi-partitioned table without a dump and reload (or using the dbms redefinition package in 9i)

 

you'd split that last one multiple times.  a split can only specify the attributes of two partitions - the "new one" and the "smaller old one".

as there isn't any data in this partition, splitting it won't "add" any new entries to your global indexes?  only new inserts would do that?

if data does exist in this partition and would cause the global indexes to go unusable, you can:

ops$tkyte@ORA9IR2> alter table t
  2  split partition junk
  3  at ( to_date('15-mar-2003'))
  4  into ( partition part3, partition junk )<b>
  5  update global indexes;</b>
 
Table altered.
 

You have a mixture of validated and non-validated constraints, that is the root cause.


your script, upto and including "alter table org_hier_ex" is run...

ops$tkyte@ORA10GR1> INSERT INTO ORG_HIER_EX VALUES(1,2,'My organization','31-mar-2005');
 
1 row created.
 
ops$tkyte@ORA10GR1> alter table ORG_HIER_EX enable /* novalidate */ constraint org_hier_ex_pk ;
 
Table altered.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> -- exchange partition
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
  2  INCLUDING INDEXES WITHOUT VALIDATION;
 
Table altered.
 
ops$tkyte@ORA10GR1> -- if i need to exchange back this will fail for sure ORA-14130
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
  2  INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION


Your issue, but:

ops$tkyte@ORA10GR1> INSERT INTO ORG_HIER_EX VALUES(1,2,'My organization','31-mar-2005');
 
1 row created.
 
ops$tkyte@ORA10GR1> alter table ORG_HIER_EX enable novalidate constraint org_hier_ex_pk ;
 
Table altered.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> -- exchange partition
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
  2  INCLUDING INDEXES WITHOUT VALIDATION;
 
Table altered.
 
ops$tkyte@ORA10GR1> -- if i need to exchange back this will fail for sure ORA-14130
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
  2  INCLUDING INDEXES WITHOUT VALIDATION;
 
Table altered.


works -- your constraint enable validated them and made them "not match" (and only due to the existence of DATA, no data no problems)
 

can you do a tiny example? (no need for fancy dynamic sql, you know all of the names, i took out the dynamic stuff because it made it hard to see what we were doing exactly)

ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2
  2  ADD CONSTRAINT org_hier2_pk
  3  PRIMARY KEY(as_of_date, org_id)
  4  USING INDEX (
  5      CREATE UNIQUE INDEX org_hier2_pk ON ORG_HIER2(as_of_date, org_id) LOCAL compress 1
  6  ) noVALIDATE;
 
Table altered.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> DROP TABLE ORG_HIER_EX;
 
Table dropped.
 
ops$tkyte@ORA10GR1> CREATE TABLE ORG_HIER_EX
  2  AS
  3  SELECT * FROM ORG_HIER2 WHERE 1=2;
 
Table created.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER_EX
  2  ADD CONSTRAINT org_hier_ex_pk
  3  PRIMARY KEY(as_of_date, org_id)
  4  USING INDEX (
  5      CREATE UNIQUE INDEX org_hier_ex_pk ON ORG_HIER_EX(as_of_date, org_id) compress 1
  6  ) noVALIDATE;
 
Table altered.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> INSERT INTO ORG_HIER_EX VALUES(1,2,'My organization','31-mar-2005');
 
1 row created.
 
ops$tkyte@ORA10GR1> alter table ORG_HIER_EX enable novalidate constraint org_hier_ex_pk ;
 
Table altered.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> -- exchange partition
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
  2  INCLUDING INDEXES WITHOUT VALIDATION;
 
Table altered.
 
ops$tkyte@ORA10GR1> -- if i need to exchange back this will fail for sure ORA-14130
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER2 EXCHANGE PARTITION P200503 WITH TABLE ORG_HIER_EX
  2  INCLUDING INDEXES WITHOUT VALIDATION;
 
Table altered.
 
 

the problem here is the "index goes away" and when you enable the constraint, a new index is created (you need not rebuild the index by the way, the enable does that)


 
ops$tkyte@ORA10GR1> ALTER TABLE ORG_HIER_EX ENABLE NOVALIDATE CONSTRAINT org_hier_ex_pk
  2  USING INDEX (
  3      CREATE UNIQUE INDEX org_hier_ex_pk ON ORG_HIER_EX(as_of_date, org_id)
  4          compress 1
  5  ) ;
 
Table altered.


no rebuild (you are doing the index twice).
 

well, you should use 32 or 64, but not 35 for hash partitioning.  You'll find you have a distinctly "bell" shaped distribution.

Actually, in your case, you don't have enough distinct values to hash into so many partitions (you have clumping now, many partitions are empty, others "overfull" and some just right).  

You could use composite range/list as well to ensure a "day is a day".  


<quote from next edition of expert one on one>
Hash Partition Using Powers of Two

I mentioned earlier that the number of partitions should be a power of two. This is easily observed to be true. To demonstrate, we'll set up a stored procedure to automate the creation of a hash partitioned table with N partitions (N will be a parameter). This procedure will construct a dynamic query to retrieve the counts of rows by partition and then display the counts and a simple histogram of the counts by partition. Lastly, it will open this query and let us see the results. This procedure starts with the hash table creation. We will use a table named T:

ops$tkyte@ORA10G> create or replace
  2  procedure hash_proc
  3            ( p_nhash in number,
  4              p_cursor out sys_refcursor )
  5  authid current_user
  6  as
  7      l_text     long;
  8      l_template long :=
  9             'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' ||
 10               'from t partition ( $PNAME$ ) union all ';
 11  begin
 12      begin
 13          execute immediate 'drop table t';
 14      exception when others
 15          then null;
 16      end;
 17
 18      execute immediate '
 19      CREATE TABLE t ( id )
 20      partition by hash(id)
 21      partitions ' || p_nhash || '
 22      as
 23      select rownum
 24        from all_objects';

Next, we will dynamically construct a query to retrieve the count of rows by partition. It does this using the "template" query defined earlier. For each partition, we'll gather the count using the partition-extended table name and union all of the counts together:

 25
 26      for x in ( select partition_name pname,
 27                        PARTITION_POSITION pos
 28                   from user_tab_partitions
 29                  where table_name = 'T'
 30                  order by partition_position )
 31      loop
 32          l_text := l_text ||
 33                    replace(
 34                    replace(l_template,
 35                          '$POS$', x.pos),
 36                          '$PNAME$', x.pname );
 37      end loop;

Now, we'll take that query and select out the partition position (PNAME) and the count of rows in that partition (CNT). Using RPAD, we'll construct a rather rudimentary but effective histogram:

 38
 39      open p_cursor for
 40         'select pname, cnt,
 41          substr( rpad(''*'',30*round( cnt/max(cnt)over(),2),''*''),1,30) hg
 42            from (' || substr( l_text, 1, length(l_text)-11 ) || ')
 43           order by oc';
 44
 45  end;
 46  /
Procedure created.

If we run this with an input of 4, for four hash partitions, we would expect to see output similar to the following:

ops$tkyte@ORA10G> variable x refcursor
ops$tkyte@ORA10G> set autoprint on
ops$tkyte@ORA10G> exec hash_proc( 4, :x );
PL/SQL procedure successfully completed.
 
PN        CNT HG
-- ---------- ------------------------------
p1      12141 *****************************
p2      12178 *****************************
p3      12417 ******************************
p4      12105 *****************************

The simple histogram depicted shows a nice, even distribution of data over each of the four partitions. Each has close to the same number of rows in it. However, if we simply go from four to five hash partitions, we'll see the following:
ops$tkyte@ORA10G> exec hash_proc( 5, :x );
PL/SQL procedure successfully completed.
 
PN        CNT HG
-- ---------- ------------------------------
p1       6102 **************
p2      12180 *****************************
p3      12419 ******************************
p4      12106 *****************************
p5       6040 **************
This histogram points out that the first and last partitions have just half as many rows as the interior partitions. The data is not very evenly distributed at all. We'll see the trend continue for six and seven hash partitions:
ops$tkyte@ORA10G> exec hash_proc( 6, :x );
PL/SQL procedure successfully completed.
 
PN        CNT HG
-- ---------- ------------------------------
p1       6104 **************
p2       6175 ***************
p3      12420 ******************************
p4      12106 *****************************
p5       6040 **************
p6       6009 **************
6 rows selected.
 
ops$tkyte@ORA10G> exec hash_proc( 7, :x );
PL/SQL procedure successfully completed.
 
PN        CNT HG
-- ---------- ------------------------------
p1       6105 ***************
p2       6176 ***************
p3       6161 ***************
p4      12106 ******************************
p5       6041 ***************
p6       6010 ***************
p7       6263 ***************
7 rows selected.
As soon as we get back to a number of hash partitions that is a power of two, we achieve the goal of even distribution once again:
ops$tkyte@ORA10G> exec hash_proc( 8, :x );
PL/SQL procedure successfully completed.
 
PN        CNT HG
-- ---------- ------------------------------
p1       6106 *****************************
p2       6178 *****************************
p3       6163 *****************************
p4       6019 ****************************
p5       6042 ****************************
p6       6010 ****************************
p7       6264 ******************************
p8       6089 *****************************
8 rows selected.
If you continue this experiment up to 16 partitions, you would see the same effects for the ninth through the fifteenth partitions-a skewing of the data to the interior partitions, away from the edges, and then upon hitting the sixteenth partition you would see a flattening-out again. The same would be true again up to 32 partitions, and then 64, and so on. This example just points out the importance of using a power of two as the number of hash partitions.
</quote> 

well, beyond "sorry", I don't know what else to say.  There is no mandatory relationship between an index name and the table name.  There is no mandatory relationship between local index partition names and their corresponding table partition names.

if you don't use the default naming convention - well, then you don't use the default naming convention.  indexes are not named after tables and vice versa.

ops$tkyte@ORA10GR2> select i.index_name, i.partition_name, t.partition_name
  2    from user_ind_partitions i, user_tab_partitions t
  3   where i.index_name = 'T_INDX'
  4     and t.table_name = 'T'
  5     and i.partition_position = t.partition_position
  6  /

INDEX_NAME                     PARTITION_ PARTITION_
------------------------------ ---------- ----------
T_INDX                         P1         PART_P1
T_INDX                         P2         PART_P2


works for the special case of a locally partitioned index of course. 

EXECUTE IMMEDIATE '  
        SELECT 'P'||TO_CHAR(as_of_date,'YYYYMM')
        FROM '||v_ex_table||' WHERE ROWNUM=1
      ' into v_part;


select into...


ops$tkyte%ORA10GR2> declare
  2          l_dummy dual.dummy%type;
  3  begin
  4          execute immediate 'select dummy from dual' into l_dummy;
  5          dbms_output.put_line( l_dummy );
  6  end;
  7  /
X

PL/SQL procedure successfully completed.
 

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  ,
  4    x   ,
  5    y
  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  as
 14  select created, user_id, username from all_users
 15  /

Table created.
 

look to your initial extent, each segment will of course always allocate at least one initial extent.

You have 8,760 segments, say you have a 1mb initial extent.  Instant 9gb of storage.

ops$tkyte%ORA10GR2> select 8760/1024 from dual;

 8760/1024
----------
 8.5546875
 

...
Oracle Database splits the corresponding local index partition, even if it is marked UNUSABLE. The database 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. <b>The database stores the new index partitions in the default tablespace of the index partition being split. If that index partition has no default tablespace, then the database uses the tablespace of the new underlying table partitions.</b>
....