Oracle-DB: List tables suitable for partition exchange
Partition Exchange requires a partitioned and a non-partitioned table with the same column and index structure. All indexes of the partitioned table have to be local partitioned. The following SQL lists all combinations of structure-identical partitioned and non-partitioned tables that are suitable for partition exchange. This information may be useful before changing the structure of tables or indexes to be informed about possible partition exchange operation and to prevent from crashing such operations. WITH Tables AS (SELECT /*+ NO_MERGE MATERIALIZE */ tc.Owner, tc.Table_Name, COUNT(*) Columns, SUM(ORA_HASH(tc.Data_Type) * tc.Column_ID * tc.Data_Length * NVL(tc.Data_Precision,1) * NVL(DECODE(tc.Data_Scale, 0, -1, tc.Data_Scale),1)) Structure_Hash FROM DBA_Tab_Columns tc JOIN DBA_Tables t ON t.Owner = tc.Owner AND t.Table_Name = tc.Table_Name /* exclude views */