Updating partition key column would cause a partition change validating dynextrunc(dbms_random.value('11111111','99999999'))); end loop;commit;end;/ alter table HASH_PARTAB_TEST add constraint uk_HASH_PARTAB_TESTunique (num) using index tablespace indx nologging; col class format a5select substr(keystring,1,2) as class,count(*)from HASH_PARTAB_TESTgroup by substr(keystring,1,2); update HASH_PARTAB_TEST set keystring = replace(keystring,' AC',' AA') where keystring like ' AC%'; 82 rows updated.One of the relatively newer features in Oracle concerns the moving of rows.*/ update x.orparttest ( or_partition=p4 ) set num=100; update x.orparttest ( or_partition=p4 ) set num=35; select * from x.orparttest ( or_partition=p4 ); select * from x.orparttest ( or_partition=p8 ); /* delete */ delete from x.orparttest ( or_partition=p4 ); select * from x.orparttest; quit; /* load to an existing table */ data new; do i=31 to 39; num=i; output;end; run; data new2; do i=1 to 9; num=i; output;end; run; proc append base= x.orparttest ( or_partition=p4 ) data= new; run; /* insert should fail 14401, 00000, "inserted partition key is outside specified partition" // *Cause: the concatenated partition key of an inserted record is outside // the ranges of the two concatenated partition bound lists that // delimit the partition named in the INSERT statement // *Action: do not insert the key or insert it in another partition */ proc append base= x.orparttest ( or_partition=p4 ) data= new2; run; /* load to an existing table */ proc append base= x.orparttest ( or_partition=p4 bulkload=yes bl_load_method=truncate ) data= new; run; /* insert should fail 14401 */ proc append base= x.orparttest ( or_partition=p4 bulkload=yes bl_load_method=truncate ) data= new2; run;libname x oracle user=scott pw=tiger path=oraclev9; proc delete data=x.orparttest; run; data x.
What developers might not realise is that it’s possible to write queries and DML statements that target individual partitions by specifying the partition name directly.
SQL All of the cities are located in the Denver area (city ID of 282 in this example).
Broomfield is further north of the Denver metro area and has been slated to become part of the Boulder area office group (using a city ID of 283).
Does a row move to another table, or is row movement constrained to the rows container (i.e., a table)?
An early use of row movement was highlighted in Oracle8i, and row movement then, as well as now, applied to moving rows in a partitioned table.