Oracle errors - Revision history

Stuart: /* ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION */

2018-11-03T17:33:38Z

‎ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

2018-11-03T17:26:31Z

‎ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

2018-11-03T16:56:29Z

‎Use exchange partition to alter metadata of the tablespaces without moving the actual data

2018-11-03T16:55:37Z

‎Use exchange partition to alter metadata of the tablespaces without moving the actual data

2018-11-03T16:33:40Z

‎ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

2018-11-03T16:21:25Z

‎Solution

2018-11-03T14:08:07Z

‎Solution

2018-11-03T13:59:16Z

‎Solution

2018-11-03T13:19:51Z

‎ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

2018-11-03T13:19:34Z

‎ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

@@ Line 912: / Line 912: @@
 col column_name for a40
 col data_default for a40
-select internal_column_id, column_name, data_default, hidden_column, virtual_column from dba_tab_cols where owner = upper('&owner.') and table_name =upper('&table_name.');
+select column_id
 INTERNAL_COLUMN_ID COLUMN_NAME                                        DATA_DEFAULT                                       HIDDEN_COLUM VIRTUAL_COLU

@@ Line 912: / Line 912: @@
 col column_name for a40
 col data_default for a40
-select internal_column_id, column_name, data_default, hidden_column, virtual_column from dba_tab_cols where table_name ='XND40';
+select internal_column_id, column_name, data_default, hidden_column, virtual_column from dba_tab_cols where owner = upper('&owner.') and table_name =upper('&table_name.');
 INTERNAL_COLUMN_ID COLUMN_NAME                                        DATA_DEFAULT                                       HIDDEN_COLUM VIRTUAL_COLU

@@ Line 1,200: / Line 1,200: @@
 ====Use exchange partition to alter metadata of the tablespaces without moving the actual data====
 * [https://community.oracle.com/message/2166242#2166242 this] is an explanation of what you are looking for!<br />
-This is a way of doing the exchange with a neat PL/SQL procedure.
+This is a way of doing the exchange with a neat PL/SQL procedure. It might not do what you want though so understand it before running it!
 <pre>
 set serveroutput on size unlimited

@@ Line 959: / Line 959: @@
 It may be an unused column. Original [https://dba.stackexchange.com/questions/196248/what-is-the-sys-c00054-140314103403-column-in-my-regular-oracle-table here]
 <pre>
-SQL> create table t1 as select * from dba_roles;
+create table t1 as select * from dba_roles;
 Table created.
-SQL> desc t1
+desc t1
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
@@ Line 970: / Line 970: @@
   AUTHENTICATION_TYPE                                VARCHAR2(11)
-SQL> alter table t1 set unused (AUTHENTICATION_TYPE);
+alter table t1 set unused (AUTHENTICATION_TYPE);
 Table altered.
-SQL> desc t1
+desc t1
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
@@ Line 980: / Line 980: @@
   PASSWORD_REQUIRED                                  VARCHAR2(8)
-SQL> select column_name from user_tab_cols where table_name = 'T1';
+select column_name from dba_tab_cols where owner = '&owner.' and table_name = '&table_name.';
 COLUMN_NAME
@@ Line 992: / Line 992: @@
 Table altered.
-SQL> select column_name from user_tab_cols where table_name = 'T1';
+select column_name from dba_tab_cols where owner = '&owner.' and table_name = '&table_name.';
 COLUMN_NAME

@@ Line 956: / Line 956: @@
 </pre>
 That last row means extended statistics have been generated for 2 columns in this table.<br />
 ====Solution====
 Drop the extended stats, do what you need to do and recreate the extended stats.

@@ Line 976: / Line 976: @@
 The stats columns can also be seen in their own table
 <pre>
-select extension_name, extension from dba_stat_extensions where table_name = upper('&&table_name.') and extension_name LIKE 'SYS_STS%'
+set lines 1000 pages 100
 </pre>

← Older revision		Revision as of 13:59, 3 November 2018
Line 973:		Line 973:

	select dbms_stats.create_extended_stats ( 'KPK', 'XND40', '("NOMFIC","NUMSEQ")' ) from dual;		select dbms_stats.create_extended_stats ( 'KPK', 'XND40', '("NOMFIC","NUMSEQ")' ) from dual;
		+	</pre>
		+	The stats columns can also be seen in their own table
		+	<pre>
		+	select extension_name, extension from dba_stat_extensions where table_name = upper('&&table_name.') and extension_name LIKE 'SYS_STS%'
	</pre>		</pre>

@@ Line 901: / Line 901: @@
 ===ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION===
-Also can be ORA-54033: column to be modified is used in a virtual column expression<br />
+Also can be <tt>ORA-54033: column to be modified is used in a virtual column expression</tt><br />
 Nothing especially to do with partitions - just any ddl operation that affects the table.<br />
 Hidden columns do not normally show up in a desc unless colinvisible is on