Mgmt r check pel
From Oramosswiki
CREATE OR REPLACE
PROCEDURE MGMT_R_CHECK_PEL(p_source_object_owner all_objects.owner%TYPE
,p_source_object_name all_objects.object_name%TYPE
,p_target_object_owner all_objects.owner%TYPE
,p_target_object_name all_objects.object_name%TYPE
,p_target_object_partition_name all_tab_partitions.partition_name%TYPE
) IS
--******************************************************************************
--*
--* Description
--* ***********
--* Measures
--* ----------------------------------------------------------------------------
--* Date Author Description
--* =========== ================= ============================================
--* 01-APR-2006 Jeff Moss Created
--* 12-FEB-2007 Jeff Moss Added code to c_attributes cursor to not
--* get partitions or subpartitions
--* Added check to ensure correct number of
--* partitions in source and subpartitions in
--* target if exchanging a partitioned source with
--* a composite partitioned target.
--* 29-MAR-2007 Jeff Moss Changed Subpartition counting to realise that
--* NONE is the phrase returned when the table is
--* Not subpartitioned - not NULL.
--* 13-JUL-2007 Jeff Moss Fixed bug on column comparison - now compares
--* source with target as opposed to source!
--* 18-JAN-2010 Jeff Moss Display src/tgt sub/partitioning types.
--******************************************************************************
CURSOR c_attributes IS
SELECT ao_src.object_type src_object_type
, at_src.cluster_owner src_cluster_owner
, at_src.iot_type src_iot_type
, ai_src.pct_threshold src_pct_threshold
, ai_src.include_column src_include_column
, apt_src.partitioning_type src_partitioning_type
, apt_src.partition_count src_partition_count
, apt_src.subpartitioning_type src_subpartitioning_type
, ao_tgt.object_type tgt_object_type
, at_tgt.cluster_owner tgt_cluster_owner
, at_tgt.iot_type tgt_iot_type
, ai_tgt.pct_threshold tgt_pct_threshold
, ai_tgt.include_column tgt_include_column
, apt_tgt.partitioning_type tgt_partitioning_type
, apt_tgt.partition_count tgt_partition_count
, apt_tgt.subpartitioning_type tgt_subpartitioning_type
, atp.subpartition_count tgt_subpartition_count
FROM all_objects ao_src
, all_tables at_src
, all_indexes ai_src
, all_part_tables apt_src
, all_objects ao_tgt
, all_tables at_tgt
, all_indexes ai_tgt
, all_part_tables apt_tgt
, (SELECT table_owner
, table_name
, subpartition_count
FROM all_tab_partitions
WHERE table_owner = p_target_object_owner
AND table_name = p_target_object_name
AND partition_name = p_target_object_partition_name
) atp
WHERE ao_src.owner = p_source_object_owner
AND ao_src.object_name = p_source_object_name
AND ao_src.owner = at_src.owner(+)
AND ao_src.object_name = at_src.table_name(+)
AND ao_src.owner = ai_src.owner(+)
AND ao_src.object_name = ai_src.table_name(+)
AND ao_src.owner = apt_src.owner(+)
AND ao_src.object_name = apt_src.table_name(+)
AND ao_tgt.owner = p_target_object_owner
AND ao_tgt.object_name = p_target_object_name
AND ao_tgt.owner = at_tgt.owner(+)
AND ao_tgt.object_name = at_tgt.table_name(+)
AND ao_tgt.owner = ai_tgt.owner(+)
AND ao_tgt.object_name = ai_tgt.table_name(+)
AND ao_tgt.owner = apt_tgt.owner(+)
AND ao_tgt.object_name = apt_tgt.table_name(+)
AND ao_tgt.owner = atp.table_owner(+)
AND ao_tgt.object_name = atp.table_name(+)
AND ao_src.object_type NOT IN('TABLE PARTITION','TABLE SUBPARTITION')
AND ao_tgt.object_type NOT IN('TABLE PARTITION','TABLE SUBPARTITION');
CURSOR c_hakan IS
WITH src AS
(
SELECT tab$.spare1 src_hakan_factor
FROM sys.tab$
, all_objects ao
WHERE ao.object_id = tab$.obj#
AND ao.owner = p_source_object_owner
AND ao.object_name = p_source_object_name
)
, tgt AS
(
SELECT tab$.spare1 tgt_hakan_factor
FROM sys.tab$
, all_objects ao
WHERE ao.object_id = tab$.obj#
AND ao.owner = p_target_object_owner
AND ao.object_name = p_target_object_name
)
SELECT *
FROM src ,tgt;
CURSOR c_columns IS
WITH src AS
(
SELECT atc_src.column_name src_column_name
, atc_src.data_type src_data_type
, atc_src.data_length src_data_length
, atc_src.data_precision src_data_precision
, atc_src.data_scale src_data_scale
, atc_src.nullable src_nullable
, atc_src.column_id src_column_id
FROM all_tab_columns atc_src
WHERE atc_src.owner = p_source_object_owner
AND atc_src.table_name = p_source_object_name
)
, tgt as
(
SELECT atc_tgt.column_name tgt_column_name
, atc_tgt.data_type tgt_data_type
, atc_tgt.data_length tgt_data_length
, atc_tgt.data_precision tgt_data_precision
, atc_tgt.data_scale tgt_data_scale
, atc_tgt.nullable tgt_nullable
, atc_tgt.column_id tgt_column_id
FROM all_tab_columns atc_tgt
WHERE atc_tgt.owner = p_target_object_owner
AND atc_tgt.table_name = p_target_object_name
)
SELECT *
FROM src FULL OUTER JOIN tgt
ON src.src_column_id = tgt.tgt_column_id
ORDER BY src_column_id;
CURSOR c_constraints IS
WITH src AS
(
SELECT constraint_name src_constraint_name
, constraint_type src_constraint_type
, search_condition src_search_condition
, r_owner src_r_owner
, r_constraint_name src_r_constraint_name
, delete_rule src_delete_rule
, status src_status
, deferrable src_deferrable
, validated src_validated
, generated src_generated
, rely src_rely
, invalid src_invalid
FROM all_constraints
WHERE table_name = p_source_object_name
AND owner = p_source_object_owner
-- exclude NOT NULL constraints as we check them at the columns stage.
AND NOT (constraint_type = 'C' AND generated = 'GENERATED NAME')
)
, tgt AS
(
SELECT constraint_name tgt_constraint_name
, constraint_type tgt_constraint_type
, search_condition tgt_search_condition
, r_owner tgt_r_owner
, r_constraint_name tgt_r_constraint_name
, delete_rule tgt_delete_rule
, status tgt_status
, deferrable tgt_deferrable
, deferred tgt_deferred
, validated tgt_validated
, generated tgt_generated
, bad tgt_bad
, rely tgt_rely
, invalid tgt_invalid
FROM all_constraints
WHERE table_name = p_target_object_name
AND owner = p_target_object_owner
-- exclude NOT NULL constraints as we check them at the columns stage.
AND NOT (constraint_type = 'C' AND generated = 'GENERATED NAME')
)
SELECT *
FROM src FULL OUTER JOIN tgt
ON src.src_constraint_name = tgt.tgt_constraint_name
ORDER BY src.src_constraint_name;
CURSOR c_indexes IS
WITH src AS
(
SELECT ai.index_name src_index_name
, ai.index_type src_index_type
, ai.uniqueness src_uniqueness
, ai.compression src_compression
, ai.pct_threshold src_pct_threshold
, ai.include_column src_include_column
, ai.status src_status
, aic.column_name src_column_name
, aic.column_position src_column_position
, aic.descend src_descend
FROM all_indexes ai
, all_ind_columns aic
WHERE ai.table_name = p_source_object_name
AND ai.table_owner = p_source_object_owner
AND ai.index_name = aic.index_name
AND ai.owner = aic.index_owner
)
, tgt AS
(
SELECT ai.index_name tgt_index_name
, ai.index_type tgt_index_type
, ai.uniqueness tgt_uniqueness
, aip.compression tgt_compression
, ai.pct_threshold tgt_pct_threshold
, ai.include_column tgt_include_column
, aip.status tgt_status
, aic.column_name tgt_column_name
, aic.column_position tgt_column_position
, aic.descend tgt_descend
FROM all_indexes ai
, all_tab_partitions atp
, all_ind_partitions aip
, all_ind_columns aic
WHERE ai.table_name = p_target_object_name
AND ai.table_owner = p_target_object_owner
AND atp.partition_name = p_target_object_partition_name
AND atp.table_name = ai.table_name
AND atp.table_owner = ai.table_owner
AND atp.partition_position = aip.partition_position
AND ai.index_name = aic.index_name
AND ai.owner = aic.index_owner
AND ai.index_name = aip.index_name
AND ai.owner = aip.index_owner
-- only get partitioned indexes on the target - they are the only ones we can handle.
AND ai.partitioned = 'YES'
)
SELECT *
FROM src FULL OUTER JOIN tgt
ON src.src_index_name = tgt.tgt_index_name
AND src.src_column_position = tgt.tgt_column_position
ORDER BY src.src_index_name
, src.src_column_position;
r_attributes c_attributes%ROWTYPE;
r_hakan c_hakan%ROWTYPE;
l_bad_column_exists BOOLEAN;
l_bad_constraint_exists BOOLEAN;
l_bad_index_exists BOOLEAN;
BEGIN
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Starting run of mgmt_r_check_pel...');
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Parameters...');
dbms_output.put_line('P_SOURCE_OBJECT_OWNER: '||p_source_object_owner);
dbms_output.put_line('P_SOURCE_OBJECT_NAME: '||p_source_object_name);
dbms_output.put_line('P_TARGET_OBJECT_OWNER: '||p_target_object_owner);
dbms_output.put_line('P_TARGET_OBJECT_NAME: '||p_target_object_name);
dbms_output.put_line('P_TARGET_OBJECT_PARTITION_NAME: '||p_target_object_partition_name);
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Check object level compatibility...');
dbms_output.put_line(LPAD('*',100,'*'));
OPEN c_attributes;
FETCH c_attributes INTO r_attributes;
IF c_attributes%NOTFOUND OR c_attributes%NOTFOUND IS NULL THEN
-- No attributes found for the supplied parameters
CLOSE c_attributes;
dbms_output.put_line('Sorry - parameters supplied do not match any objects in the dictionary');
ELSE
CLOSE c_attributes;
-- Check that the objects can undertake a PEL...
-- Heap Tables can be exchanged with Partitions or Subpartitions
-- on Heap Tables or Materialized Views (including Prebuilt)
-- Materialized Views can be exchanged with Partitions or Subpartitions
-- on Heap Tables or Materialized Views (including Prebuilt)
-- All other objects can not be exchanged.
IF r_attributes.src_cluster_owner IS NOT NULL
OR r_attributes.tgt_cluster_owner IS NOT NULL THEN
dbms_output.put_line('FAILURE: Clustered Tables are incompatible with Partition Exchange');
ELSIF r_attributes.src_object_type = 'IOT'
AND r_attributes.tgt_object_type = 'IOT'
AND (NVL(r_attributes.src_include_column,-1) != NVL(r_attributes.src_include_column,-1)
OR NVL(r_attributes.src_pct_threshold,-1) != NVL(r_attributes.tgt_pct_threshold,-1)
) THEN
dbms_output.put_line('FAILURE: Index Organised Tables with different Overflow characteristics - incompatible for partition exchange');
ELSIF (r_attributes.src_object_type = 'IOT'
AND r_attributes.tgt_object_type = 'IOT'
AND NVL(r_attributes.src_include_column,-1) = NVL(r_attributes.src_include_column,-1)
AND NVL(r_attributes.src_pct_threshold,-1) = NVL(r_attributes.tgt_pct_threshold,-1)
)
OR
(r_attributes.src_object_type IN('TABLE','MATERIALIZED_VIEW')
AND r_attributes.tgt_object_type IN('TABLE','MATERIALIZED_VIEW')
) THEN
dbms_output.put_line('PASS: The source and target objects are ones which can be partition exchanged');
ELSE
dbms_output.put_line('FAILURE: The source and target objects are not ones which can be partition exchanged');
END IF;
-- Now check hakan factors...
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Check Hakan Factor compatability...');
dbms_output.put_line(LPAD('*',100,'*'));
OPEN c_hakan;
FETCH c_hakan INTO r_hakan;
IF c_hakan%NOTFOUND OR c_hakan%NOTFOUND IS NULL THEN
CLOSE c_hakan;
dbms_output.put_line('WARNING:Hakan Factors not found...');
ELSE
CLOSE c_hakan;
IF r_hakan.src_hakan_factor != r_hakan.tgt_hakan_factor THEN
dbms_output.put_line('FAILURE: Hakan Factors differ - Partition Exchange will fail');
ELSE
dbms_output.put_line('PASS: Hakan Factors same: '||TO_CHAR(r_hakan.src_hakan_factor));
END IF;
END IF;
-- Now check columns...
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Check column level compatability...');
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line(RPAD('SIDE',7)||
RPAD('COLUMN_ID',10)||
RPAD('COLUMN_NAME',31)||
RPAD('DATA_TYPE',9)||
'LENGTH '||
'PRECISION '||
'SCALE '||
'NULL?'
);
dbms_output.put_line(RPAD('=',7,'=')||
RPAD('=',10,'=')||
RPAD('=',31,'=')||
RPAD('=',9,'=')||
RPAD('=',7,'=')||
RPAD('=',10,'=')||
RPAD('=',6,'=')||
RPAD('=',5,'=')
);
-- set flag to keep track of whether we have any bad columns...
l_bad_column_exists := FALSE;
FOR r_columns IN c_columns LOOP
IF r_columns.src_column_id = r_columns.tgt_column_id
AND r_columns.src_column_name = r_columns.tgt_column_name
AND r_columns.src_data_type = r_columns.tgt_data_type
AND r_columns.src_data_length = r_columns.tgt_data_length
AND NVL(r_columns.src_data_precision,-1) = NVL(r_columns.tgt_data_precision,-1)
AND NVL(r_columns.src_data_scale,-1) = NVL(r_columns.tgt_data_scale,-1)
AND r_columns.src_nullable = r_columns.tgt_nullable
THEN
dbms_output.put_line('SAME '||
RPAD(NVL(TO_CHAR(r_columns.src_column_id),'-'),10)||
RPAD(NVL(r_columns.src_column_name,'-'),31)||
RPAD(NVL(r_columns.src_data_type,'-'),9)||
RPAD(NVL(TO_CHAR(r_columns.src_data_length),'-'),7)||
RPAD(NVL(TO_CHAR(r_columns.src_data_precision),'-'),10)||
RPAD(NVL(TO_CHAR(r_columns.src_data_scale),'-'),6)||
RPAD(NVL(r_columns.src_nullable,'-'),6)
);
ELSE
l_bad_column_exists := TRUE;
IF r_columns.src_column_id IS NULL THEN
dbms_output.put_line('SOURCE '||
RPAD(NVL(TO_CHAR(r_columns.tgt_column_id),'-'),10)||
RPAD(NVL(r_columns.tgt_column_name,'-'),31)||
'MISSING'
);
ELSE
dbms_output.put_line('SOURCE '||
RPAD(NVL(TO_CHAR(r_columns.src_column_id),'-'),10)||
RPAD(NVL(r_columns.src_column_name,'-'),31)||
RPAD(NVL(r_columns.src_data_type,'-'),9)||
RPAD(NVL(TO_CHAR(r_columns.src_data_length),'-'),7)||
RPAD(NVL(TO_CHAR(r_columns.src_data_precision),'-'),10)||
RPAD(NVL(TO_CHAR(r_columns.src_data_scale),'-'),6)||
RPAD(NVL(r_columns.src_nullable,'-'),6)
);
END IF;
IF r_columns.tgt_column_id IS NULL THEN
dbms_output.put_line('TARGET '||
RPAD(NVL(TO_CHAR(r_columns.src_column_id),'-'),10)||
RPAD(NVL(r_columns.src_column_name,'-'),31)||
'MISSING'
);
ELSE
dbms_output.put_line('TARGET '||
RPAD(NVL(TO_CHAR(r_columns.tgt_column_id),'-'),10)||
RPAD(NVL(r_columns.tgt_column_name,'-'),31)||
RPAD(NVL(r_columns.tgt_data_type,'-'),9)||
RPAD(NVL(TO_CHAR(r_columns.tgt_data_length),'-'),7)||
RPAD(NVL(TO_CHAR(r_columns.tgt_data_precision),'-'),10)||
RPAD(NVL(TO_CHAR(r_columns.tgt_data_scale),'-'),6)||
RPAD(NVL(r_columns.tgt_nullable,'-'),6)
);
END IF;
END IF;
END LOOP;
dbms_output.put_line(LPAD('*',100,'*'));
-- indicate whether any column checks failed
IF l_bad_column_exists THEN
dbms_output.put_line('FAILURE: At least one column is different - Partition Exchange will fail');
ELSE
dbms_output.put_line('PASS: All columns are the same');
END IF;
-- Now check constraints...
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Check constraint level compatability...');
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line(RPAD('SIDE',7)||
RPAD('CONSTRAINT NAME',31)||
RPAD('TYPE',5)||
RPAD('R_OWNER',26)||
RPAD('R_CONSTRAINT_NAME',31)
);
dbms_output.put_line('/ '||
RPAD('DELETE_RULE',12)||
RPAD('STATUS',9)||
RPAD('DEFERRABLE',15)||
RPAD('VALIDATED',14)||
RPAD('GENERATED',15)||
RPAD('RELY',5)||
RPAD('INVALID',8)
);
dbms_output.put_line(RPAD('=',100,'='));
-- set flag to keep track of whether we have any bad columns...
l_bad_constraint_exists := FALSE;
FOR r_constraints IN c_constraints LOOP
IF r_constraints.src_constraint_name = r_constraints.tgt_constraint_name
AND NVL(r_constraints.src_constraint_type,'12') = NVL(r_constraints.tgt_constraint_type,'12')
AND NVL(r_constraints.src_r_owner,'1234567890123456789012345678901') =
NVL(r_constraints.tgt_r_owner,'1234567890123456789012345678901')
AND NVL(r_constraints.src_r_constraint_name,'1234567890123456789012345678901') =
NVL(r_constraints.tgt_r_constraint_name,'1234567890123456789012345678901')
AND NVL(r_constraints.src_delete_rule,'1234567890') = NVL(r_constraints.tgt_delete_rule,'1234567890')
AND NVL(r_constraints.src_status,'123456789') = NVL(r_constraints.tgt_status,'123456789')
AND NVL(r_constraints.src_deferrable,'123456789012345') = NVL(r_constraints.tgt_deferrable,'123456789012345')
AND NVL(r_constraints.src_validated,'12345678901234') = NVL(r_constraints.tgt_validated,'12345678901234')
AND NVL(r_constraints.src_generated,'123456789012345') = NVL(r_constraints.tgt_generated,'123456789012345')
AND NVL(r_constraints.src_rely,'12345') = NVL(r_constraints.tgt_rely,'12345')
AND NVL(r_constraints.src_invalid,'12345678') = NVL(r_constraints.tgt_invalid,'12345678')
THEN
dbms_output.put_line('SAME '||
RPAD(NVL(r_constraints.src_constraint_name,'-'),31)||
RPAD(NVL(r_constraints.src_constraint_type,'-'),5)||
RPAD(NVL(r_constraints.src_r_owner,'-'),26)||
RPAD(NVL(r_constraints.src_r_constraint_name,'-'),31)
);
dbms_output.put_line('/ '||
RPAD(NVL(r_constraints.src_delete_rule,'-'),12)||
RPAD(NVL(r_constraints.src_status,'-'),9)||
RPAD(NVL(r_constraints.src_deferrable,'-'),15)||
RPAD(NVL(r_constraints.src_validated,'-'),14)||
RPAD(NVL(r_constraints.src_generated,'-'),15)||
RPAD(NVL(r_constraints.src_rely,'-'),5)||
RPAD(NVL(r_constraints.src_invalid,'-'),8)
);
ELSE
l_bad_constraint_exists := TRUE;
IF r_constraints.src_constraint_name IS NULL THEN
dbms_output.put_line('SOURCE '||
RPAD(NVL(r_constraints.tgt_constraint_name,'-'),31)||
RPAD(NVL(r_constraints.tgt_constraint_type,'-'),5)||
'MISSING'
);
ELSE
dbms_output.put_line('SOURCE '||
RPAD(NVL(r_constraints.src_constraint_name,'-'),31)||
RPAD(NVL(r_constraints.src_constraint_type,'-'),5)||
RPAD(NVL(r_constraints.src_r_owner,'-'),26)||
RPAD(NVL(r_constraints.src_r_constraint_name,'-'),31)
);
dbms_output.put_line('/ '||
RPAD(NVL(r_constraints.src_delete_rule,'-'),12)||
RPAD(NVL(r_constraints.src_status,'-'),9)||
RPAD(NVL(r_constraints.src_deferrable,'-'),15)||
RPAD(NVL(r_constraints.src_validated,'-'),14)||
RPAD(NVL(r_constraints.src_generated,'-'),15)||
RPAD(NVL(r_constraints.src_rely,'-'),5)||
RPAD(NVL(r_constraints.src_invalid,'-'),8)
);
END IF;
IF r_constraints.tgt_constraint_name IS NULL THEN
dbms_output.put_line('TARGET '||
RPAD(NVL(r_constraints.src_constraint_name,'-'),31)||
RPAD(NVL(r_constraints.src_constraint_type,'-'),5)||
'MISSING'
);
ELSE
dbms_output.put_line('TARGET '||
RPAD(NVL(r_constraints.tgt_constraint_name,'-'),31)||
RPAD(NVL(r_constraints.tgt_constraint_type,'-'),5)||
RPAD(NVL(r_constraints.tgt_r_owner,'-'),26)||
RPAD(NVL(r_constraints.tgt_r_constraint_name,'-'),31)
);
dbms_output.put_line('/ '||
RPAD(NVL(r_constraints.tgt_delete_rule,'-'),12)||
RPAD(NVL(r_constraints.tgt_status,'-'),9)||
RPAD(NVL(r_constraints.tgt_deferrable,'-'),15)||
RPAD(NVL(r_constraints.tgt_validated,'-'),14)||
RPAD(NVL(r_constraints.tgt_generated,'-'),15)||
RPAD(NVL(r_constraints.tgt_rely,'-'),5)||
RPAD(NVL(r_constraints.tgt_invalid,'-'),8)
);
END IF;
END IF;
END LOOP;
dbms_output.put_line(LPAD('*',100,'*'));
-- indicate whether any constraint checks failed
IF l_bad_constraint_exists THEN
dbms_output.put_line('FAILURE: At least one constraint is different - Partition Exchange will fail');
ELSE
dbms_output.put_line('PASS: All constraints are the same');
END IF;
dbms_output.put_line(LPAD('*',100,'*'));
-- Now check indexes...
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Check Index level compatability...');
dbms_output.put_line('NOTE - If WITHOUT INDEXES is used in the exchange then the following may not be relevant.');
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line(RPAD('SIDE',7)||
RPAD('INDEX NAME',31)||
RPAD('INDEX TYPE',28)||
RPAD('UNIQUENESS',11)||
RPAD('COMPRESSION',12)||
RPAD('PCT_THRESHOLD',14)
);
dbms_output.put_line('/ '||
RPAD('INCLUDE_COLUMN',15)||
RPAD('STATUS',9)||
RPAD('COLUMN_NAME',31)||
RPAD('COLUMN POSITION',16)||
RPAD('DESCEND',8)
);
dbms_output.put_line(RPAD('=',100,'='));
-- set flag to keep track of whether we have any bad columns...
l_bad_index_exists := FALSE;
FOR r_indexes IN c_indexes LOOP
IF r_indexes.src_index_name = r_indexes.tgt_index_name
AND NVL(r_indexes.src_index_type,'1234567890123456789012345678') =
NVL(r_indexes.tgt_index_type,'1234567890123456789012345678')
AND NVL(r_indexes.src_uniqueness,'1234567890') = NVL(r_indexes.tgt_uniqueness,'1234567890')
AND NVL(r_indexes.src_compression,'123456789') = NVL(r_indexes.tgt_compression,'123456789')
AND NVL(r_indexes.src_pct_threshold,-1) = NVL(r_indexes.tgt_pct_threshold,-1)
AND NVL(r_indexes.src_include_column,-1) = NVL(r_indexes.tgt_include_column,-1)
AND NVL(r_indexes.src_status,'123456789') =
(CASE WHEN NVL(r_indexes.tgt_status,'123456789') = 'USABLE'
THEN 'VALID'
ELSE 'INVALID'
END)
AND NVL(r_indexes.src_column_name,'1234567890123456789012345678901') =
NVL(r_indexes.tgt_column_name,'1234567890123456789012345678901')
AND NVL(r_indexes.src_column_position,-1) = NVL(r_indexes.tgt_column_position,-1)
AND NVL(r_indexes.src_descend,'12345') = NVL(r_indexes.tgt_descend,'12345')
THEN
dbms_output.put_line('SAME '||
RPAD(NVL(r_indexes.src_index_name,'-'),31)||
RPAD(NVL(r_indexes.src_index_type,'-'),28)||
RPAD(NVL(r_indexes.src_uniqueness,'-'),11)||
RPAD(NVL(r_indexes.src_compression,'-'),12)||
RPAD(NVL(TO_CHAR(r_indexes.src_pct_threshold),'-'),14)
);
dbms_output.put_line('/ '||
RPAD(NVL(TO_CHAR(r_indexes.src_include_column),'-'),15)||
RPAD(NVL(r_indexes.src_status,'-'),9)||
RPAD(NVL(r_indexes.src_column_name,'-'),31)||
RPAD(NVL(TO_CHAR(r_indexes.src_column_position),'-'),16)||
RPAD(NVL(r_indexes.src_descend,'-'),8)
);
ELSE
l_bad_index_exists := TRUE;
IF r_indexes.src_index_name IS NULL THEN
dbms_output.put_line('SOURCE '||
RPAD(NVL(r_indexes.tgt_index_name,'-'),31)||
RPAD(NVL(r_indexes.tgt_index_type,'-'),28)||
'MISSING'
);
ELSE
dbms_output.put_line('SOURCE '||
RPAD(NVL(r_indexes.src_index_name,'-'),31)||
RPAD(NVL(r_indexes.src_index_type,'-'),28)||
RPAD(NVL(r_indexes.src_uniqueness,'-'),11)||
RPAD(NVL(r_indexes.src_compression,'-'),12)||
RPAD(NVL(TO_CHAR(r_indexes.src_pct_threshold),'-'),14)
);
dbms_output.put_line('/ '||
RPAD(NVL(TO_CHAR(r_indexes.src_include_column),'-'),15)||
RPAD(NVL(r_indexes.src_status,'-'),9)||
RPAD(NVL(r_indexes.src_column_name,'-'),31)||
RPAD(NVL(TO_CHAR(r_indexes.src_column_position),'-'),16)||
RPAD(NVL(r_indexes.src_descend,'-'),8)
);
END IF;
IF r_indexes.tgt_index_name IS NULL THEN
dbms_output.put_line('TARGET '||
RPAD(NVL(r_indexes.src_index_name,'-'),31)||
RPAD(NVL(r_indexes.src_index_type,'-'),28)||
'MISSING'
);
ELSE
dbms_output.put_line('TARGET '||
RPAD(NVL(r_indexes.tgt_index_name,'-'),31)||
RPAD(NVL(r_indexes.tgt_index_type,'-'),28)||
RPAD(NVL(r_indexes.tgt_uniqueness,'-'),11)||
RPAD(NVL(r_indexes.tgt_compression,'-'),12)||
RPAD(NVL(TO_CHAR(r_indexes.tgt_pct_threshold),'-'),14)
);
dbms_output.put_line('/ '||
RPAD(NVL(TO_CHAR(r_indexes.tgt_include_column),'-'),15)||
RPAD(NVL(r_indexes.tgt_status,'-'),9)||
RPAD(NVL(r_indexes.tgt_column_name,'-'),31)||
RPAD(NVL(TO_CHAR(r_indexes.tgt_column_position),'-'),16)||
RPAD(NVL(r_indexes.tgt_descend,'-'),8)
);
END IF;
END IF;
END LOOP;
dbms_output.put_line(LPAD('*',100,'*'));
-- indicate whether any index checks failed
IF l_bad_index_exists THEN
dbms_output.put_line('FAILURE: At least one index is different - Partition Exchange will fail');
ELSE
dbms_output.put_line('PASS: All indexes are the same');
END IF;
-- Now check sub/partitioning...
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Check partitioning/subpartitioning types/counts');
dbms_output.put_line(LPAD('*',100,'*'));
dbms_output.put_line('Source partitioning type: '||r_attributes.src_partitioning_type);
dbms_output.put_line('Source subpartitioning type: '||r_attributes.src_subpartitioning_type);
dbms_output.put_line('Target partitioning type: '||r_attributes.tgt_partitioning_type);
dbms_output.put_line('Target subpartitioning type: '||r_attributes.tgt_subpartitioning_type);
IF r_attributes.src_partitioning_type IS NULL
AND NVL(r_attributes.tgt_subpartitioning_type,'NONE') != 'NONE' THEN
dbms_output.put_line('FAILURE: Source is not partitioned but target is subpartitioned');
ELSIF NVL(r_attributes.src_partitioning_type,'X') = 'LIST'
AND NVL(r_attributes.tgt_subpartitioning_type,'X') != 'LIST' THEN
dbms_output.put_line('FAILURE: Source is LIST partitioned but target is: '||NVL(r_attributes.tgt_subpartitioning_type,'UNSET'));
ELSIF NVL(r_attributes.src_partitioning_type,'X') = 'RANGE'
AND NVL(r_attributes.tgt_subpartitioning_type,'X') != 'RANGE' THEN
dbms_output.put_line('FAILURE: Source is RANGE partitioned but target is not');
ELSIF NVL(r_attributes.src_partitioning_type,'X') = NVL(r_attributes.tgt_subpartitioning_type,'X')
AND NVL(r_attributes.src_partition_count,-1) != NVL(r_attributes.tgt_subpartition_count,-1) THEN
dbms_output.put_line('FAILURE: '||r_attributes.src_partitioning_type||' partitioned source has '||r_attributes.src_partition_count||' partitions, but '||
r_attributes.tgt_subpartitioning_type||' subpartitioned target has '||r_attributes.tgt_subpartition_count||' subpartitions');
ELSE
dbms_output.put_line('PASS: Source and target partitioning and subpartitioning are acceptable');
END IF;
END IF;
dbms_output.put_line(LPAD('*',100,'*'));
EXCEPTION
WHEN OTHERS THEN
IF c_hakan%ISOPEN THEN
CLOSE c_hakan;
END IF;
IF c_attributes%ISOPEN THEN
CLOSE c_attributes;
END IF;
RAISE;
END mgmt_r_check_pel;
/
Then grant execute on the procedure so others can run it...if needed.
grant execute on mgmt_r_check_pel to public;
Then run the procedure for your source and target tables...
exec mgmt_r_check_pel('SRC_OWNER','SRC_TABLE','TGT_OWNER','TGT_TABLE','TGT_PARTITION_NAME');