skip to content »

Ora 14402 updating partition

ora 14402 updating partition-71

Please note that instead of using an expression, I have used a deterministic function.

Why would a row move and who or what controls that movement? With newer releases of the Oracle RDBMS, where else does row movement come into play, and are there any gotcha's with respect to row movement operations?In order to demonstrate that, I will create a demonstration user with a non-partitioned table with privileges and additional dependent objects on it: The challenge is now to change this table into a partitioned one while it is used with DML & queries by end users.For this purpose, we introduced already in 9i (if I recall it right) the package DBMS_REDEFINITION.Please note that this declaration is different than using “default” clause for a normal column as you can’t refer column names with “default” clause.Lets check the data dictionary view: SELECT column_name, data_type, data_length, data_default, virtual_column FROM user_tab_cols WHERE table_name = 'EMPLOYEE'; COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT | VIRTUAL_COLUMN EMPL_ID | NUMBER | 22 | null | NO EMPL_NM | VARCHAR2 | 50 | null | NO MONTHLY_SAL | NUMBER | 22 | null | NO BONUS | NUMBER | 22 | null | NO TOTAL_SAL | NUMBER | 22 | "MONTHLY_SAL"*12 "BONUS" | YES DROP TABLE EMPLOYEE PURGE; CREATE OR REPLACE FUNCTION get_empl_total_sal ( p_monthly_sal NUMBER, p_bonus NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN p_monthly_sal * 12 p_bonus; END; CREATE TABLE EMPLOYEE (empl_id NUMBER, empl_nm VARCHAR2(50), monthly_sal NUMBER(10,2), bonus NUMBER(10,2), total_sal NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) VIRTUAL ); We have included the “VIRTUAL” clause in the table definition.If there is a requirement to change the structure of a table that is already in use productively, it may be impossible to get a maintenance downtime for that table, because it is constantly in use.

That can be the case for all kind of structural changes of a table, particularly for the change from an ordinary heap table into a partitioned table, which I am going to take here as an example, because I am getting asked frequently in my courses how to achieve it.

First step would be to ask, whether it can be used in this case: select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release - Production PL/SQL Release - Production CORE Production TNS for Linux: Version - Production NLSRTL Version - Production Because there is no Primary Key on the original table, I have to use CONS_USE_ROWID, else I could use CONS_USE_PK.

There are no objections against the online redefinition of the table here – else an error message would appear.

Many partitioned table examples use regions, cities and states as list examples.

What happens if you use a city as a partition key and an office in that city moves elsewhere?

If you don’t mention the datatype, Oracle will decide it based on the result of the expression.