When you try to shrink a table and get this error this is due to the table has functional based index.
How to find:
SELECT dt.owner, dt.table_name,
(CASE
WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
ELSE 'N'
END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type LIKE 'FUNCTION-BASED%'
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = 'CSS_DATA'
ORDER BY 1, 2;
OWNER TABLE_NAME C
------------------------------ ------------------------------ -
CSS_DATA ACTION Y
CSS_DATA ACTIONLOG N
CSS_DATA ACTIONLOG_ARCH1 Y
CSS_DATA ACTIONLOG_OLD Y
CSS_DATA ACTIONNAME Y
CSS_DATA ACTIONPARAMETER Y
CSS_DATA ACTIONPARAMETER_ARCH1 Y
CSS_DATA ACTIONPARAMETER_OLD Y
CSS_DATA ACTIONTYPE Y
CSS_DATA ACTION_PARAMS Y
CSS_DATA ACTION_PARAMS_ARCH1 Y
OWNER TABLE_NAME C
------------------------------ ------------------------------ -
CSS_DATA ADJUSTMENTREASONS Y
CSS_DATA BATCH Y
CSS_DATA BILLING_BILL_CYCLE Y
CSS_DATA BILLING_PRICE_GROUP Y
CSS_DATA BILLING_SERVICES Y
CSS_DATA BILLING_SERVICES_PARAMS Y
CSS_DATA BILLING_SERVICES_PARAM_VALUES Y
CSS_DATA BILLING_SERVICES_SMS Y
CSS_DATA BILL_SERV_PRM_CALC_TYP Y
CSS_DATA BUNDLED_MIGRATION Y
CSS_DATA BUNDLED_MIGRATION_BUNDLES Y
How to find:
SELECT dt.owner, dt.table_name,
(CASE
WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
ELSE 'N'
END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type LIKE 'FUNCTION-BASED%'
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = 'CSS_DATA'
ORDER BY 1, 2;
OWNER TABLE_NAME C
------------------------------ ------------------------------ -
CSS_DATA ACTION Y
CSS_DATA ACTIONLOG N
CSS_DATA ACTIONLOG_ARCH1 Y
CSS_DATA ACTIONLOG_OLD Y
CSS_DATA ACTIONNAME Y
CSS_DATA ACTIONPARAMETER Y
CSS_DATA ACTIONPARAMETER_ARCH1 Y
CSS_DATA ACTIONPARAMETER_OLD Y
CSS_DATA ACTIONTYPE Y
CSS_DATA ACTION_PARAMS Y
CSS_DATA ACTION_PARAMS_ARCH1 Y
OWNER TABLE_NAME C
------------------------------ ------------------------------ -
CSS_DATA ADJUSTMENTREASONS Y
CSS_DATA BATCH Y
CSS_DATA BILLING_BILL_CYCLE Y
CSS_DATA BILLING_PRICE_GROUP Y
CSS_DATA BILLING_SERVICES Y
CSS_DATA BILLING_SERVICES_PARAMS Y
CSS_DATA BILLING_SERVICES_PARAM_VALUES Y
CSS_DATA BILLING_SERVICES_SMS Y
CSS_DATA BILL_SERV_PRM_CALC_TYP Y
CSS_DATA BUNDLED_MIGRATION Y
CSS_DATA BUNDLED_MIGRATION_BUNDLES Y
select INDEX_NAME, INDEX_TYPE
from dba_indexes
where table_owner = 'CSS_DATA' and
table_name = 'ORDER_INSTANCE'; 2 3 4
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_ORDER_INSTANCE NORMAL
ORDER_INSTANCE_MSISDN_IDX NORMAL
OINSTANCE_JOB_ID NORMAL
ORDER_INSTANCE_IDX FUNCTION-BASED NORMAL
Solution:
1-Drop the function-based index.
3- Shrink the table.
4- Recreate the index again on the table.
4- Recreate the index again on the table.
Thank you Mohamed. Keep up the good work.
ReplyDelete