源端:Oracle 11.1.0.7
目标端:Oracle 11.2.0.4
最近做的一个数据迁移的CASE中遇到的问题,一个VIEW在完成数据迁移后由VALID的状态变为了INVALID。
从源库中使用get_ddl拿到的该VIEW创建语句如下:
CREATE OR REPLACE FORCE VIEW "APPS"."IGW_BUDGET_CATEGORY_V1" ("PROPOSAL_ID", "VERSION_ID", "BUDGET_PERIOD_ID", "LINE_ITEM_ID", "EXPENDITURE_TYPE", "EXPENDITURE_CATEGORY_FLAG", "BUDGET_CATEGORY_CODE", "BUDGET_CATEGORY", "BASE_AMT", "OH_APPLIED_FLAG", "OH_COST", "EB_COST") AS
SELECT pbcv.proposal_id , pbcv.version_id , pbcv.budget_period_id , pbcv.line_item_id , pbcv.expenditure_type , pbcv.expenditure_category_flag , pbcv.budget_category_code , fl.meaning budget_category , sum(pbcv.line_item_cost+pbcv.eb_cost) base_amt , decode(pbcv.oh_cost, 0,'N', 'Y') oh_applied_flag , pbcv.oh_cost , pbcv.eb_cost
from igw_budget_complete_v pbcv , igw_lookups_v fl
WHERE pbcv.budget_category_code = fl.lookup_code and fl.lookup_type = 'IGW_BUDGET_CATEGORY'
group by pbcv.proposal_id, pbcv.version_id, pbcv.budget_period_id ,pbcv.budget_category_code, fl.meaning, pbcv.line_item_id, pbcv.expenditure_type ,pbcv.expenditure_category_flag;
拿到该语句进行了查看发现该语句本身就存在问题,理论上无论哪个数据库版本都无法运行才对,在