Table Partition Error - SQL & PL/SQL

Hello all,
I have created a table and stored it into two partitions. For example, if name < 'T', then partition A, otherwise, partition B. The problem is that if I update the name from 'David' to 'Wilson', then error occurs:
ORA-14402: updating partition key column would cause a partition change.
Is this normal and how to solve this?
Regards,
George 

In 8i, you can can alter the table to ENABLE ROW MOVEMENT (it is disabled by default). If you are using 8.0.x, you need to trap this situation (as if it were an error) and then delete and reinsert the row.

Related

Partitioning:different [linked] tables to be stored in the same partitions

Hello, dear All,
I've been playing with Oracle 11g partitioning for some while and I've got the following scenario.
1. I have several tables one of which is a root and others refer to it by foreign keys. E.g.
table A (id number not null, when_inserted date); -- root table
table B (id number not null, ..., a_id not null references A(id) on delete cascade)
table C (id number not null, ..., a_id not null references A(id) on delete cascade)
2. I want table A to be INTERVAL partitioned by when_inserted column (monthly) and others to be interval (how else?) partitioned by a_id (ideally it would be reference-partitioning but the latter can not be used with INTERVAL type).
3. I want to run periodic cleanup job that would remove old data by using ALTER TABLE A DROP PARTITION XXX; where XXX would be the partition with the oldest data.
I tried this:
create table th (iid number not null,
               iname varchar2(64) not null,
               when_inserted date default sysdate not null )
partition by range (when_inserted)
interval (numtoyminterval(1,'MONTH'))
store in (history_tasks_1, history_tasks_2, history_tasks_3, history_tasks_4, history_tasks_5, history_tasks_6, history_tasks_7)
(partition t_01 values less than (to_date('1-1-2012', 'DD-MM-YYYY')) tablespace history_tasks_1);
-- dependent table
create table tiop (iid number not null,
th_id number not null references th (iid) on delete cascade)
partition by range (th_id)
interval (10)
store in (history_tasks_1, history_tasks_2, history_tasks_3, history_tasks_4, history_tasks_5, history_tasks_6, history_tasks_7)
(partition t_01 values less than (10) tablespace history_tasks_1);
So how could I specify that data for tables B, C etc will be stored in the same partitions as their master records in table A?
As far as I understand drop partition would only succeed if all data inside it are isolated from others, right? Or it will fail in any case due to the presence of foreign keys?
Please advise me how to fix the issue.
Thank you very much,
Max 
You are confusing clusters and partitions. A partition is a subdivision of one and only one table.
What you want is not possible.i
------------
Sybrand Bakker
Senior Oracle DBA 
Hi,
thank you for the answer, you are probably right, partitioning is fairly very new to me. So is there any way to remove dependent data from multiple tables by the single ALTER TABLE ... DROP PARTITION?
Cascading DELETE could be too costly sometimes.
Thank you,
Max

Create partition issues

Hi,
When I am trying to run this sql i am getting an error : Invalid data type ORA:00902
alter table Month_tbl
ADD PARTITION by range (month_sid)
(PARTITION Year_2006 VALUES LESS THAN (200601));
month_sid is an integer.
any suggestions?>
Thanks in advance 
what iss type of column "month_sid"? 
Integer having values like 200801,200902 etc 
Pls use the command in this format
alter table <table_name>
add PARTITION <partition_name> VALUES LESS THAN (25);
There is no need to specify the type of partition.
NB: The table to which you are adding partitions should already be partitioned.Otherwise its difficult to add partitions to non partitioned table.

DDL Generation Issue

DM 3.0.0-665
When generating incremental DDL I have found that the tool generates a drop PK statement, but does not drop associated PK index. This leads to an error when it tries to create the PK constraint after performing the data manipulation.
DM generated the following
ALTER TABLE LAND_REPORT2.FIELD_DEDUCTION DROP CONSTRAINT FIELD_DEDUCTION_FK1 CASCADE ;
ALTER TABLE LAND_REPORT2.FIELD_DEDUCTION DROP CONSTRAINT FIELD_DEDUCTION_FK2 CASCADE ;
ALTER TABLE LAND_REPORT2.FIELD_DEDUCTION DROP CONSTRAINT FIELD_DEDUCTION_PK CASCADE ;
DROP TRIGGER LAND_REPORT2.FIELD_DEDUCTION_BRI_TRG
;
ALTER TABLE LAND_REPORT2.FIELD_DEDUCTION RENAME TO bcp_FIELD_DEDUCTION
;
CREATE TABLE LAND_REPORT2.FIELD_DEDUCTION
(
FIELD_DEDUCTION_ID NUMBER NOT NULL ,
DEDUCTION_ID NUMBER NOT NULL ,
SIACS_FIELD_ID NUMBER NOT NULL ,
APPLICABLE_AREA NUMBER ,
DEDUCTION_VALUE NUMBER
) LOGGING
;
INSERT INTO LAND_REPORT2.FIELD_DEDUCTION
(FIELD_DEDUCTION_ID , DEDUCTION_ID , SIACS_FIELD_ID , APPLICABLE_AREA , DEDUCTION_VALUE )
SELECT
FIELD_DEDUCTION_ID , DEDUCTION_ID , SIACS_FIELD_ID , APPLICABLE_AREA , DEDUCTION_VALUE
FROM
bcp_FIELD_DEDUCTION
;
ALTER TABLE LAND_REPORT2.FIELD_DEDUCTION
ADD CONSTRAINT FIELD_DEDUCTION_PK PRIMARY KEY ( FIELD_DEDUCTION_ID ) ;
The last statement give the following error
ALTER TABLE LAND_REPORT2.FIELD_DEDUCTION
ADD CONSTRAINT FIELD_DEDUCTION_PK PRIMARY KEY ( FIELD_DEDUCTION_ID )
Error report:
SQL Error: ORA-00955: name is already used by an existing object
00955. 00000 - "name is already used by an existing object"
*Cause:   
*Action:
As a side issue, if I want to add a column to a table and generate ddl which does not do the following
1. unload data
2. drop table
3. recreate table
4. reload data
Is this possible. All is needed in this instance is ALTER TABLE ADD COLUMN.
Before pointing me to the video http://download.oracle.com/otn_hosted_doc/sqldev/importddl/importddl.html
I have watched it, and it is still not clear.
Regards
Paul
Further errors in DDL generation
see below
CREATE INDEX MM_ROLLUP_PRE_INSP_NU1 ON LAND_REPORT2.MM_ROLLUP_PRE_INSP
(
FIELD_ID ASC
)
LOGGING
NOCOMPRESS
NOSORT
NOPARALLEL
Error at Command Line:56 Column:52
Error report:
SQL Error: ORA-01409: NOSORT option may not be used; rows are not in ascending order
01409. 00000 - "NOSORT option may not be used; rows are not in ascending order"
*Cause:    Creation of index with NOSORT option when rows were not ascending.
For non-unique indexes the rowid is considered part of the index
key. Therefore, if you create an index nosort and two of the rows
in the table have the same key and are stored in ascending order,
but get split accross two extents where the dba of the first block
in the second extent is less than the dba of the last block in the
first extent, then the create index nosort may fail.
*Action:   Create the index without the NOSORT option, or ensure table is
stored in one extent.
Edited by: pjflynn on Mar 31, 2011 11:45 AM 
Hi,
Regarding your side issue on adding Columns, provided the new column is added at the end of the column list, it should normally generate an ALTER TABLE ADD COLUMN statement.
David 
David,
Got there in the end. Restarted DM, imported design, swapped target model and switched off Use "Schema" property in the compare options.
It was as if the changes to the data model were not being persisted to the generation stage.
Thanks.
Paul
Please close. 
Hi Paul,
Thanks also for reporting the additional NOSORT problem. We already have this logged as a bug, as this problem was reported in an earlier message
DM 3.0: Index generation deverses from index definition (Version 3.0.0.665)
The problem only occurs in incremental DDL for a Compare/Merge operation. Full DDL Generation should be fine.
Thanks,
David

alter table add parition

hi friends,i am execute below script and its given me error like 'INVALID DATAYPE' ALTER TABLE FINAL_RG1 ADD PARTITION BY RANGE (PART_20151205) ( partition PART_20151205 VALUES LESS THAN (to_date('2015-12-06','YYYY-MM-DD')));
What is the data type of PART_20151205?
Aren't you experiencing the same issue as the one described at Re: Partitions - Creation - Invalid Datatype Error?
is part_20151205 is a column in FINAL_RG1 table?? You need to keep column name after RANGE keyword in in your syntax As other's already asked if Part_20151205 is a column, what is its datatype?
SQL> alter table final_rg1 add partition part_20151205 values less than (to_date('2015-12-05','YYYY-MM-DD')); Table altered.
I think OP needs to follow the the instructions in the link provided by you.(Assuming he is trying to partition the non-partitioned table).
Oh, I think I see....are you trying to turn a non-partitioned table into a partitioned table?
Rajnish Chauhan wrote:
 
hi friends,
i am execute below script and its given me error like 'INVALID DATAYPE'
ALTER TABLE FINAL_RG1
ADD PARTITION BY RANGE (PART_20151205)
( partition PART_20151205 VALUES LESS THAN (to_date('2015-12-06','YYYY-MM-DD')));
There IS no 'BY RANGE' clause for ADD PARTITION. The BY RANGE is a clause used when you CREATE a partitioned table. See CREATE TABLE in the docshttps://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm CREATE TABLE PurposeUse the CREATE TABLE statement to create one of the following types of tables: A relational table, which is the basic structure to hold user data. An object table, which is a table that uses an object type for a column definition. An object table is explicitly defined to hold object instances of a particular type. table_partitioning_clauses ::= Description of the illustration table_partitioning_clauses.gif (range_partitions::=, hash_partitions ::=, list_partitions ::=, reference_partitioning::=, composite_range_partitions::=, composite_list_partitions::=, and system_partitioning::=)range_partitions::=

INSERTING DATA  INTO TABLE

HI
I Have a table tableA which has fields col1,col2,col3,col4 where col1 is primary key and i have a second table tableB which has three fields like cola,colb, colc this table cola is foreign key which references col1 of tableA.
TableB has some data and i want to insert new data into this. i know i will get the integrity constraint error, but my question is without using disable constraint statement is there any method through which we can achieve this.
regards
srinivas 
Hello
If you are getting an integrity contraint error when you insert the new data into table B, it suggests that the data you are inserting is incorrect. If the contraint is there, it it's probably there for a reason. The new data that you are inserting into table B relies on data in table A, so you need to make sure that the data is there in table A first, and then you won't get the problem.
Is there any reason why you can't add the required data to table A first?
David 
So your requirement is to insert some data into a table that will violate its relational integrity constraints without first disabling those constrants? If you don't mind me saying so, that's a bizarre thing to ask. It's one thing to apply relational integrity constraints to some existing rubbish data without being required to correct that data. It's another thing to deliberately insert corrupt data into an existing rigourous database.
Still, if you must:
ALTER TABLE t1 DROP CONSTRAINT t1_t2_fk
/
INSERT INTO t1 VALUES ('Cak', 'Cak', 2, 2, 2, 'etc')
/
ALTER TABLE t1 ADD CONSTRAINT t1_t2_fk FOREIGN KEY (cola)
REFERENCES t2 (col_pk) ENABLE NOVALIDATE
/Knock yerself out.
Cheers, APC

Categories

Resources