Skip to content

ORA-42016: shape of interim table does not match specified column mapping

ORA-42016: shape of interim table does not match specified column mapping published on 1 комментарий к записи ORA-42016: shape of interim table does not match specified column mapping

try to make table partitioned:
create dummy table:

  CREATE TABLE USER."PERS_DATA_OPERATION_LOG_REDEF" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"OBJECT_OID" NUMBER NOT NULL ENABLE, 
	"OBJECT_SNILS" VARCHAR2(256), 
	"OBJECT_FULL_NAME" VARCHAR2(4000), 
	"SUBJECT_OID" NUMBER, 
	"SUBJECT_SNILS" VARCHAR2(256), 
	"SUBJECT_FULL_NAME" VARCHAR2(4000), 
	"CRT_ON" TIMESTAMP (6) , 
	"OPERATION_TYPE" VARCHAR2(256), 
	 CHECK (OPERATION_TYPE IN ('VIEW','PASS_RESET','REMOVE','BGIR_RESTART')) ENABLE, 
	 CONSTRAINT "PERS_DATA_OPERATION_LOG_PK1" PRIMARY KEY ("ID")
   ) 
     PARTITION BY RANGE ("CRT_ON")    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
     (partition p0 VALUES LESS THAN (TIMESTAMP' 2011-01-01 00:00:00') ) ;

start redef and get error:

exec dbms_redefinition.can_redef_table ('USER','PERS_DATA_OPERATION_LOG',dbms_redefinition.cons_use_pk);
   
exec  DBMS_REDEFINITION.start_redef_table ('USER','PERS_DATA_OPERATION_LOG','PERS_DATA_OPERATION_LOG_REDEF',NULL,DBMS_REDEFINITION.cons_use_pk);

ORA-42016: shape of interim table does not match specified column mapping

this is because original table have a timestamp with local timezone format
how to fix it:

select listagg(column_name,',') within  group  ( order by column_id) from dba_tab_columns where table_name='PERS_DATA_OPERATION_LOG';

begin 
DBMS_REDEFINITION.start_redef_table(
    uname        => 'USER',
    orig_table   => 'PERS_DATA_OPERATION_LOG',
    int_table    => 'PERS_DATA_OPERATION_LOG_REDEF',
    col_mapping =>'ID,OBJECT_OID,OBJECT_SNILS,OBJECT_FULL_NAME,SUBJECT_OID,SUBJECT_SNILS,SUBJECT_FULL_NAME,to_timestamp(crt_on) crt_on,OPERATION_TYPE',
    options_flag => DBMS_REDEFINITION.cons_use_pk);
end;



SET SERVEROUTPUT ON
DECLARE
 l_num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
   uname             => 'USER',
   orig_table        => 'PERS_DATA_OPERATION_LOG',
   int_table         => 'PERS_DATA_OPERATION_LOG_REDEF',
   copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
   copy_triggers     => TRUE,  -- Default
   copy_constraints  => TRUE,  -- Default
   copy_privileges   => TRUE,  -- Default
   ignore_errors     => true,
   num_errors        => l_num_errors);
 DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('USER','PERS_DATA_OPERATION_LOG','PERS_DATA_OPERATION_LOG_REDEF');

EXEC DBMS_REDEFINITION.finish_redef_table('USER', 'PERS_DATA_OPERATION_LOG', 'PERS_DATA_OPERATION_LOG_REDEF' );

Oracle 12c Adaptive Query Optimization overhead

Oracle 12c Adaptive Query Optimization overhead published on Комментариев к записи Oracle 12c Adaptive Query Optimization overhead нет

Trying to find out overhead of new Adaptive Query Optimization feature

use perf to find out process cpu usage ( eg:perf stat -e task-clock -p 9576 sleep 5 )
Screen Shot 2016-08-24 at 21.45.07

Screen Shot 2016-08-24 at 21.46.40
to disable adaptive features I used

alter system set optimizer_features_enable='11.2.0.1';

so the result is:

3000 hpares

3000 hpares

3000 hpares

3000 hpares

3000 hpares

avg

adaptive off

1724,46

1786,04

1696,22

1687,89

1690,89

1717,10

adaptive on

1803,79

1815,98

1966,07

1804,35

1819,35

1841,91

overhead

4,40

1,65

13,73

6,45

7,06

6,78

ps: lotshparses.sql link

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

How to resolve SQL object and column names all the way to base tables and columns in Oracle? published on Комментариев к записи How to resolve SQL object and column names all the way to base tables and columns in Oracle? нет

original from Tanel Poder

was looking for source of v$restore point view, helped article from Tanel =)

 alter session set "_dump_qbc_tree"=1;

result is :

 SELECT  "ADDR","INDX","INST_ID","CON_ID","NRSNAME","NRSINCARN","NRSSCN","NRSTIME","NRSFLAGS","NRSRSPTIME","NRSRID","NRSSTM" FROM "SYS"."X$KCCNRS"   "X$KCCNRS" WHERE (con_id IN (0, 3) );

upd: also it can be done with:

 select view_definition from v$fixed_view_definition where view_name='GV$RESTORE_POINT';

script to find tables with nonindexed FK

script to find tables with nonindexed FK published on Комментариев к записи script to find tables with nonindexed FK нет

need to pass schema name as firs script argument

select table_name, column_name
              from ( select c.table_name, cc.column_name, cc.position column_position
                      from   all_constraints c, all_cons_columns cc
                      where  c.constraint_name = cc.constraint_name
                        and c.constraint_type = 'R' and c.owner=cc.owner and c.owner='&1'
                     minus
                     select i.table_name, ic.column_name, ic.column_position
                      from all_indexes i, all_ind_columns ic
                      where i.index_name = ic.index_name and i.owner=ic.table_owner and i.owner='&1');

Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1)

Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1) published on Комментариев к записи Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1) нет

today faced with ‘enq: TX — index contention’

Screen Shot 2016-08-05 at 21.08.15

awr shows

Screen Shot 2016-08-05 at 20.01.41

helped to make partition index on nonpartition table:

drop index ARADMIN.IT3060;
CREATE UNIQUE INDEX ARADMIN.IT3060_P ON "ARADMIN"."T3060" ("C1") global partition by hash (C1) partitions 16 parallel 16 ;
drop index ARADMIN.T3060_IDX_12;
CREATE index ARADMIN.T3060_IDX_12 ON "ARADMIN"."T3060"  ("C536870937", "C7", "C536870938", "C1") global partition by hash ("C536870937", "C7", "C536870938", "C1")  partitions 16 parallel 16 TABLESPACE "ARSYSTEM" ;
drop index ARADMIN.T3060_IDX_11;
CREATE index ARADMIN.T3060_IDX_11 ON "ARADMIN"."T3060"  ("C536870922", "C7", "C536870941") global partition by hash ("C536870922", "C7", "C536870941")  partitions 16 parallel 16 TABLESPACE "ARSYSTEM" ;

CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started.

CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started. published on Комментариев к записи CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started. нет

faced with

COMMAND EXECUTION FAILURE :
Using configuration parameter file: /ora01/app/grid/12.1.0.2/OPatch/opatchautotemp_rac2/patchwork/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.crf' on 'rac2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac2'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac2'
CRS-2677: Stop of 'ora.mdnsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.crf' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac2'
CRS-2677: Stop of 'ora.gipcd' on 'rac2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-6706: Oracle Clusterware Release patch level ('2222840392') does not match Software patch level ('0'). Oracle Clusterware cannot be started.
CRS-4000: Command Start failed, or completed with errors.
2016/08/01 10:21:54 CLSRSC-117: Failed to start Oracle Clusterware stack

helped solution from:
CRS-1153: There was an error setting Oracle Clusterware to rolling patch mode. (Doc ID 1943498.1)
CRS-6706: Oracle Clusterware Release patch level (‘nnn’) does not match Software patch level (‘mmm’) (Doc ID 1639285.1)

first one:

[root@rac2 ~]# crsctl stop rollingpatch
CRS-1171: Rejecting rolling patch mode change because the patch level is not consistent across all nodes in the cluster. The patch level on nodes rac1 is not the same as the patch level [2369764000] found on nodes rac2.
CRS-4000: Command Stop failed, or completed with errors.

with no luck

second is successfull:

[root@rac1 ~]# kfod op=patchlvl
-------------------
Current Patch level
===================
2369764000
[root@rac1 ~]# kfod op=patches
---------------
List of Patches
===============
19396455
19769480
20299023
20831110
21359755
21436941
21948341
21948344
21948354


[root@rac2 ~]# kfod op=patches
---------------
List of Patches
===============
19396455
[root@rac2 ~]# kfod op=patchlvl
-------------------
Current Patch level
===================
2222840392

[root@rac1 ~]# sh /ora01/app/grid/12.1.0.2/crs/install/rootcrs.sh -patch
Using configuration parameter file: /ora01/app/grid/12.1.0.2/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
...
2016/08/01 10:57:54 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.

2016/08/01 10:59:46 CLSRSC-4005: Failed to patch Oracle Trace File Analyzer (TFA) Collector. Grid Infrastructure operations will continue.
.....
Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [2369764000].

Primary Sidebar

Яндекс.Метрика