select /*+rule*/ idx.table_name,idx.index_name,col.column_name,idx.tablespace_name,(s.bytes/1024/1024) spused, col.descend from user_indexes idx, user_ind_columns col,user_segments s where idx.index_type='NORMAL' and idx.table_type='TABLE' and not Exists (select 1 from user_constraints c where c.constraint_type='P' and c.constraint_name = idx.index_name) and idx.table_name in ('IFM_APPLY','IFM_INCOME','IFM_REDEEM','IFM_REDEEMPLAN') and idx.index_name = s.segment_name and col.index_name = idx.index_name order by idx.table_name,idx.index_name, col.column_position
select * from ( select --/*+rule*/ idx.table_name , idx.index_name , col.column_name , idx.tablespace_name, --(s.bytes/1024/1024) spused, col.descend from --user_indexes idx, ( select * from DBA_INDEXES where owner='DM_NCC202105_S7' --指定用户名 ) idx, --user_ind_columns col ( select * from dba_ind_columns where index_owner='DM_NCC202105_S7' --指定用户名 ) col -- user_segments s where col.index_name = idx.index_name and idx.index_type ='NORMAL' and idx.table_type ='TABLE' /* and not Exists ( select 1 from user_constraints c where c.constraint_type ='P' and c.constraint_name = idx.index_name )*/ and idx.table_name in ('IFM_APPLY', 'IFM_INCOME', 'IFM_REDEEM', 'IFM_REDEEMPLAN') -- and idx.index_name = s.segment_name order by idx.table_name, idx.index_name, col.column_position ) aa where not Exists ( select 1 from user_constraints c where c.constraint_type ='P' and c.constraint_name = aa.index_name )
select * from ( select --/*+rule*/ idx.table_name , idx.index_name , col.column_name , idx.tablespace_name, --(s.bytes/1024/1024) spused, col.descend from --user_indexes idx, ( select * from DBA_INDEXES where owner=current_user() --指定用户名
) idx, --user_ind_columns col ( select * from dba_ind_columns where index_owner=current_user() --指定用户名
) col -- user_segments s where col.index_name = idx.index_name and idx.index_type ='NORMAL' and idx.table_type ='TABLE' /* and not Exists ( select 1 from user_constraints c where c.constraint_type ='P' and c.constraint_name = idx.index_name )*/ and idx.table_name in ('IFM_APPLY', 'IFM_INCOME', 'IFM_REDEEM', 'IFM_REDEEMPLAN') -- and idx.index_name = s.segment_name order by idx.table_name, idx.index_name, col.column_position ) aa where not Exists ( select 1 from user_constraints c where c.constraint_type ='P' and c.constraint_name = aa.index_name )
select table_name,tablespace_name,0 from user_tables where table_name in ('EXCEL_BILLPROCESS','EXCEL_IMPORTLOGS','EXCEL_OUTPUTPROCESS','EXCEL_TRANSLATOR','BD_FUNCINITREG','BD_REFCOLUMN','BD_REFINFO','BD_REFTABLE','PUB_BESIDEBUSICOMP','PUB_BESIDECOMP','PUB_BESIDECOMPPARAM','PUB_BESIDECOMPTYPE','PUB_BESIDEPAGE','PUB_BESIDEPAGECOMP','PUB_BILLTEMPLET','PUB_BILLTEMPLET_B','PUB_BILLTEMPLET_T','PUB_COMPPERSONALIZED','PUB_DATATEMPLET','PUB_DEFTOCLASS','PUB_FORMDATA','PUB_FUNCCODETOCODE','PUB_GUIDELINE','PUB_GUIDELINE_F','PUB_IMPORTREFGETTER','PUB_IMPORTREGISTER','PUB_MYACCOUNTBOOK','PUB_PRINT_CELL','PUB_PRINT_DATAITEM','PUB_PRINT_DATASOURCE','PUB_PRINT_LINE','PUB_PRINT_REGION','PUB_PRINT_TEMPLATE','PUB_PRINT_VARIABLE','PUB_PSN_MATRIX','PUB_PSN_REG','PUB_PSN_RESOURCE','PUB_QUERY_CONDITION','PUB_QUERY_TEMPLET','PUB_QUERYSCHEME','PUB_REFENUM','PUB_REPORT_GROUP','PUB_REPORT_MODEL','PUB_REPORT_TEMPLET','PUB_SYSTEMPLATE','PUB_SYSTEMPLATE_BASE','SM_CUSTOM_APPDETAIL','SM_CUSTOM_APPEXT','SM_CUSTOM_MENU_ICONLIB','SM_CUSTOM_MENU_ITEM','SM_CUSTOM_MENU_SHORTCUT','SM_INDIVIDUAL_PROPERTY','SM_INDIVIDUAL_REGISTER') order by table_name
select * from( select idx.table_name,idx.index_name, col.column_name,idx.tablespace_name,0.125,col.descend from ( select * from DBA_INDEXES where owner =current_user()) idx , ( select * from dba_ind_columns where index_owner=current_user() ) col where col.index_name = idx.index_name and idx.index_type ='NORMAL' and idx.table_type ='TABLE' and idx.table_name in ('BD_SHAREBILLTYPE','BD_AASSIGN','BD_ACCASOA','BD_ACCASOAHISTORY','BD_ACCASS','BD_ACCASSHISTORY','BD_ACCASSITEM','BD_ACCCHART','BD_ACCCTRLASOA','BD_ACCCTRLORG','BD_ACCCTRLRULE','BD_ACCELEMENT','BD_ACCESSORFACREG','BD_ACCFIELDCTRL','BD_ACCFIELDCTTEMP','BD_ACCHALFYEAR','BD_ACCOBLIGATE','BD_ACCOUNT','BD_ACCOUNT_DIST','BD_ACCOUNTMAP','BD_ACCPERIOD','BD_ACCPERIODMONTH','BD_ACCPERIODQUART','BD_ACCPERIODSCHEME','BD_ACCPLYCHART','BD_ACCSYSTEM','BD_ACCTYPE','BD_ACTALLOC','BD_ACTIVITY','BD_ACTIVITY_B','BD_ADDRESS','BD_ADDRESSDOC','BD_ADDRLINKMAN','BD_ADJUSTRATE','BD_AGENTSTORE','BD_ANSYLOG','BD_AREACL','BD_ASSIGINRELATION','BD_ASSIGN_RULE','BD_ASSIGN_TAB','BD_ASSIGN_TEMP','BD_ASSLINENUM','BD_AVGRATE','BD_BALATYPE','BD_BANKACCBAS','BD_BANKACCSUB','BD_BANKACCUSE','BD_BANKDOC','BD_BANKLINKMAN','BD_BANKTYPE','BD_BATCHUPDATEATTR','BD_BATCHUPDATERULE','BD_BATCHUPDATETAB','BD_BATCHUPRULETEAM','BD_BIZEVENTLOG','BD_BMRT','BD_BOM','BD_BOM_ACTIVITY','BD_BOM_B','BD_BOM_BSRC','BD_BOM_LOSS','BD_BOM_OUTPUTS','BD_BOM_POSITION','BD_BOM_REPL','BD_BOM_SELECT','BD_BOM_USEORG','BD_BOM_WIP','BD_BRANDDOC','BD_CAPEXITEM','BD_CAPRT','BD_CAPWT','BD_CASHACCOUNT','BD_CASHFLOW','BD_CASHFLOWUSE','BD_CBSASSIGN','BD_CBSDEFAULT','BD_CBSNODE','BD_CFADJITEMSET','BD_CHANNELTYPE','BD_CODEEDITOR','BD_COMPELECOMPA','BD_COMPELECOMPA_B','BD_COORDINATION','BD_COSTCOMPONENT','BD_COSTCOMPSTRUC','BD_COSTCOMPSTRUC_B','BD_COSTCOMPSTRUCDISTRI','BD_COSTMODE','BD_COSTOBJTYPE','BD_COUNTRY_REGION','BD_COUNTRYZONE','BD_CR_CONFIG','BD_CR_RESOURCE','BD_CROSSBUSIATTR','BD_CROSSBUSIMAP','BD_CROSSRESTDATA','BD_CROSSRULE','BD_CROSSRULEBUSI','BD_CROSSRULEREST','BD_CROSSRULESCOPE','BD_CURRINFO','BD_CURRRATE','BD_CURRTYPE','BD_CUST_COORDINATION','BD_CUST_SUPPLIER','BD_CUST_SUPPLIER_C','BD_CUSTADDRESS','BD_CUSTADDRESSREF','BD_CUSTAXES','BD_CUSTBANK','BD_CUSTCLASS','BD_CUSTCREDITCTL','BD_CUSTFINANCE','BD_CUSTLINKMAN','BD_CUSTMATERIAL','BD_CUSTMATERSUB','BD_CUSTMERGE','BD_CUSTOMER','BD_CUSTOMER_C','BD_CUSTOMER_PF','BD_CUSTORG','BD_CUSTRENAME','BD_CUSTSALE','BD_CUSTSALECLASS','BD_CUSTSTRUCTURE','BD_CUSTTAXTYPES','BD_CUSTUPGRADE','BD_CUSTVAT','BD_DATAREPAIR','BD_DISTRESTRANS_LOG','BD_DISTRIBUTERES','BD_DISTRIBUTERES_B','BD_DISTRIBUTERES_LOG','BD_DISTSYSTEMSET','BD_DISTTOKENCTRL','BD_DISTTOKENCTRL_B','BD_DISTTOKENSET','BD_DISTTOKENSET_B','BD_DISTTRANSDEL_LOG','BD_DOCCHECK','BD_ELEMRELATION','BD_EMATERIAL','BD_EXRATESCHEME','BD_FCLASS','BD_FCLASS_B','BD_FEATURE','BD_FEATURE_B','BD_FEATURECODE','BD_FEATURECODE_B','BD_FEATURECODE_SN','BD_FFILE','BD_FFILE_B','BD_FORMATDOC','BD_FPRICE','BD_FPRICE_B','BD_FREECUSTOM','BD_FRELATION','BD_FRELATION_B','BD_FUNDPLAN','BD_FUNDPLANUSE','BD_GOODSCODE','BD_GOODSTAXES','BD_HOLIDAY','BD_INCOME','BD_INCOMECH','BD_INCOMEPERIOD','BD_INCOTERM','BD_INDUSTRY','BD_INOUTBUSICLASS','BD_INOUTUSE','BD_LINKMAN','BD_MANDATE','BD_MARASSISTANT','BD_MARASSTFRAME','BD_MARASSTRANGE','BD_MARASSTRST','BD_MARASSTRSTVAL','BD_MARBASCLASS','BD_MARCOSTCLASS','BD_MARORG','BD_MARPUCLASS','BD_MARSALECLASS','BD_MATEGRADE','BD_MATERIAL','BD_MATERIAL_C','BD_MATERIAL_PF','BD_MATERIAL_V','BD_MATERIAL_V_C','BD_MATERIALBINDLE','BD_MATERIALCONVERT','BD_MATERIALCOST','BD_MATERIALCOSTMOD','BD_MATERIALFI','BD_MATERIALPFC','BD_MATERIALPFCC','BD_MATERIALPFCCSUB','BD_MATERIALPFCSUB','BD_MATERIALPLAN','BD_MATERIALPROD','BD_MATERIALPU','BD_MATERIALREPL','BD_MATERIALSALE','BD_MATERIALSTOCK','BD_MATERIALTAXTYPE','BD_MATERIALWARH','BD_MATTAXES','BD_MEASDOC','BD_MULTIMODE','BD_NETBANKINFTP','BD_NOTETYPE','BD_PAYMENT','BD_PAYMENTCH','BD_PAYPERIOD','BD_PERIODMAPPING','BD_PERIODMAPPINGDETAIL','BD_PLANSTRATEGY','BD_PLANSTRATEGYGROUP','BD_PLANSTRATEGYGROUP_B','BD_PREDATAIDMAP','BD_PRICEACCURACY','BD_PRODLINE','BD_PROJECT','BD_PROJECT_B','BD_PROJECT_C','BD_PROJECTCLASS','BD_PROJECTCLASS_B','BD_PROJTYPECBS','BD_PSNBANKACC','BD_PSNCL','BD_PSNDOC','BD_PSNDOC_C','BD_PSNIDTYPE','BD_PSNJOB','BD_PSNJOB_C','BD_RACK','BD_RATESCHEMA','BD_RATESCHEMACH','BD_REF_FILTER','BD_REF_PLUGIN','BD_REF_RELATION','BD_REGION','BD_REPITEMMAPPING','BD_REPORTITEM','BD_REPORTSYSTEM','BD_RT','BD_SHIFT','BD_SHIFTMUTEXBU','BD_SHIFTTYPE','BD_STORDOC','BD_SUPABCLASS','BD_SUPABILITYSET','BD_SUPADDRESS','BD_SUPADDRESSREF','BD_SUPCOUNTRYTAXES','BD_SUPFINANCE','BD_SUPLINKMAN','BD_SUPMERGE','BD_SUPORG','BD_SUPPLIER','BD_SUPPLIER_C','BD_SUPPLIER_GRADE','BD_SUPPLIER_GRADE_SYS','BD_SUPPLIER_PF','BD_SUPPLIERBANK_PF','BD_SUPPLIERCLASS','BD_SUPQUALIDOC','BD_SUPQUALILEVEL','BD_SUPQUALITYPE','BD_SUPRENAME','BD_SUPSTOCK','BD_SUPSTRUCTURE','BD_SUPTAXES','BD_SUPUPGRADE','BD_SUPVAT','BD_SYN_CUSTOMER','BD_SYN_MATERIAL','BD_SYN_ORG','BD_SYN_STORDOC','BD_SYN_SUPPLIER','BD_TAXCODE','BD_TAXITEMS','BD_TAXRATE','BD_TAXREGION','BD_TAXREGIONB','BD_TEAM','BD_TEAM_B','BD_TEMPLETITEM','BD_TRANSACTIONCODE','BD_TRANSPORTTYPE','BD_USEDACCBOOK','BD_VOUCHERTYPE','BD_WK','BD_WK_FEED','BD_WKCLASS','BD_WORKCALENDAR','BD_WORKCALENDARDATE','BD_WORKCALENDRULE','BD_WORKCALENDYEAR','BD_WT','CT_EXPSET','CT_PRICETEMPLET','CT_PRICETEMPLET_B','CT_TERMSET','CT_TERMTYPE','FI_RECPAYTYPE','HR_TEMPTABLE1','IC_SETPART','IC_SETPART_B','ORG_LIACTCOSTRG','ORG_MORGSTRU','ORG_MORGSTRU_V','ORG_MORGSTRUMEMBER','ORG_MORGSTRUMEMBER_V','ORG_PRODUCTIONPLAN','ORG_PRODUPMEMBER','PAM_CATE_PARAM','PAM_CATEGORY','PAM_STATUS','PD_MEAINSTRUCL','PD_MEASTOOL','PD_RC','PD_RT','PD_RT_ACTIVITY','PD_RT_ASSORG','PD_RT_B','PD_RT_OPAC','PD_RTTEMPLATE','PD_RTTEMPLATE_B','PM_EPS','PM_WBS','PM_WBS_PREFIX','PM_WBS_STATEDRIVE','QC_REJECTTYPE','QC_REJECTTYPE_B','RESA_ACCASOA','RESA_BIZINDEX','RESA_BIZINDEXCLASS','RESA_CCDEPTS','RESA_CCGROUPDETAIL','RESA_CCSTRUCTURE','RESA_CCWORKCENTER','RESA_CONTROLAREA','RESA_CONTROLAREAITEM','RESA_COSTCENTER','RESA_COSTCENTER_V','RESA_COSTCENTERGROUP','RESA_COSTCENTERSGREL','RESA_ELEMENTSYSTEM','RESA_ELEMENTTYPE','RESA_FACACCRELATE','RESA_FACASOAHISTORY','RESA_FACASSHISTORY','RESA_FACCTRLORG','RESA_FACTOR','RESA_FACTORASOA','RESA_FACTORASS','RESA_FACTORCHART','RESA_FACTORCTRLCA','RESA_FACTORCTRLGROUP','RESA_FACTORFRAMEWORK','RESA_FACTORGROUP','RESA_FACTORSGREL','RESA_FACTORSINGROUP','RESA_INDEXDIMENSION','RESA_POLICYCHAR','RESA_SOSINCA','SCM_QUALITYLEVEL_B','SCM_QUALITYLEVEL_H','SRMSM_MARBASCLSEXT','SRMSM_MARBASCLSEXT_B','SRMSM_SUPPLIEREXT_A','SRMSM_SUPPLIEREXT_H','SRMSM_SUPPLIEREXT_I','SRMSM_SUPPLIEREXT_P','SRMSM_SUPPLIEREXT_Q','TXM_TAXKIND','TXM_TAXRATE','UAPBD_FORMPROJECT','UAPBD_FORMPROJECT_B','UAPBD_FORMPROJECTSYSTEM','VRM_VENDORMATER') order by idx.table_name,idx.index_name,col.column_position)aa where not Exists (select 1 from user_constraints c where c.constraint_type ='P' and c.constraint_name = aa.index_name )
5 个回复
nccloud
1、症状在建表的/dbcreate阶段，会卡很久，5-6分钟
2、排查日志发现有个明显的点会卡5分钟，但是不确定是卡在这里，所以开启am-log的debug模式
3、对比oracle数据库升级场景，发现报错相同，但是卡顿只有几秒
4、打开am-log的debug模式，发现卡在一个索引的扫描
5、协调开发看后发现是类似的方法
6、涉及SQL如下，确认为SQL效率问题
select /*+rule*/ idx.table_name,idx.index_name,col.column_name,idx.tablespace_name,(s.bytes/1024/1024) spused, col.descend from user_indexes idx, user_ind_columns col,user_segments s where idx.index_type='NORMAL' and idx.table_type='TABLE' and not Exists (select 1 from user_constraints c where c.constraint_type='P' and c.constraint_name = idx.index_name) and idx.table_name in ('IFM_APPLY','IFM_INCOME','IFM_REDEEM','IFM_REDEEMPLAN') and idx.index_name = s.segment_name and col.index_name = idx.index_name order by idx.table_name,idx.index_name, col.column_position
开发解答：此处为查询库中已有索引情况、以及索引表空间的剩余情况，之后在做数据处理。
7、联系达梦支持协助分析优化，发现去掉表空间校验后，耗时从6分钟到1分钟。故在环境中采用此临时方案处理
8、在达梦DBA分析过程中发现，将not Exists 放到最后能再提升一半时间，即SQL耗时可以控制到30秒左右。之后继续调整SQL结构，最终实现SQL耗时 3秒
nccloud
最终优化后SQL
select
*
from
(
select
--/*+rule*/
idx.table_name ,
idx.index_name ,
col.column_name ,
idx.tablespace_name,
--(s.bytes/1024/1024) spused,
col.descend
from
--user_indexes idx,
(
select
*
from
DBA_INDEXES
where
owner='DM_NCC202105_S7' --指定用户名
)
idx,
--user_ind_columns col
(
select
*
from
dba_ind_columns
where
index_owner='DM_NCC202105_S7' --指定用户名
)
col
-- user_segments s
where
col.index_name = idx.index_name
and idx.index_type ='NORMAL'
and idx.table_type ='TABLE'
/* and not Exists
(
select
1
from
user_constraints c
where
c.constraint_type ='P'
and c.constraint_name = idx.index_name
)*/
and idx.table_name in ('IFM_APPLY', 'IFM_INCOME', 'IFM_REDEEM', 'IFM_REDEEMPLAN')
-- and idx.index_name = s.segment_name
order by
idx.table_name,
idx.index_name,
col.column_position
)
aa
where
not Exists
(
select
1
from
user_constraints c
where
c.constraint_type ='P'
and c.constraint_name = aa.index_name
)
nccloud
但是因为后端获取不到当前用户，所以改为动态获取
select
*
from
(
select
--/*+rule*/
idx.table_name ,
idx.index_name ,
col.column_name ,
idx.tablespace_name,
--(s.bytes/1024/1024) spused,
col.descend
from
--user_indexes idx,
(
select
*
from
DBA_INDEXES
where
owner=current_user() --指定用户名
)
idx,
--user_ind_columns col
(
select
*
from
dba_ind_columns
where
index_owner=current_user() --指定用户名
)
col
-- user_segments s
where
col.index_name = idx.index_name
and idx.index_type ='NORMAL'
and idx.table_type ='TABLE'
/* and not Exists
(
select
1
from
user_constraints c
where
c.constraint_type ='P'
and c.constraint_name = idx.index_name
)*/
and idx.table_name in ('IFM_APPLY', 'IFM_INCOME', 'IFM_REDEEM', 'IFM_REDEEMPLAN')
-- and idx.index_name = s.segment_name
order by
idx.table_name,
idx.index_name,
col.column_position
)
aa
where
not Exists
(
select
1
from
user_constraints c
where
c.constraint_type ='P'
and c.constraint_name = aa.index_name
)
nccloud
select table_name,tablespace_name,0 from user_tables where table_name in ('EXCEL_BILLPROCESS','EXCEL_IMPORTLOGS','EXCEL_OUTPUTPROCESS','EXCEL_TRANSLATOR','BD_FUNCINITREG','BD_REFCOLUMN','BD_REFINFO','BD_REFTABLE','PUB_BESIDEBUSICOMP','PUB_BESIDECOMP','PUB_BESIDECOMPPARAM','PUB_BESIDECOMPTYPE','PUB_BESIDEPAGE','PUB_BESIDEPAGECOMP','PUB_BILLTEMPLET','PUB_BILLTEMPLET_B','PUB_BILLTEMPLET_T','PUB_COMPPERSONALIZED','PUB_DATATEMPLET','PUB_DEFTOCLASS','PUB_FORMDATA','PUB_FUNCCODETOCODE','PUB_GUIDELINE','PUB_GUIDELINE_F','PUB_IMPORTREFGETTER','PUB_IMPORTREGISTER','PUB_MYACCOUNTBOOK','PUB_PRINT_CELL','PUB_PRINT_DATAITEM','PUB_PRINT_DATASOURCE','PUB_PRINT_LINE','PUB_PRINT_REGION','PUB_PRINT_TEMPLATE','PUB_PRINT_VARIABLE','PUB_PSN_MATRIX','PUB_PSN_REG','PUB_PSN_RESOURCE','PUB_QUERY_CONDITION','PUB_QUERY_TEMPLET','PUB_QUERYSCHEME','PUB_REFENUM','PUB_REPORT_GROUP','PUB_REPORT_MODEL','PUB_REPORT_TEMPLET','PUB_SYSTEMPLATE','PUB_SYSTEMPLATE_BASE','SM_CUSTOM_APPDETAIL','SM_CUSTOM_APPEXT','SM_CUSTOM_MENU_ICONLIB','SM_CUSTOM_MENU_ITEM','SM_CUSTOM_MENU_SHORTCUT','SM_INDIVIDUAL_PROPERTY','SM_INDIVIDUAL_REGISTER') order by table_name
nccloud
10+分钟
select * from( select idx.table_name,idx.index_name, col.column_name,idx.tablespace_name,0.125,col.descend from ( select * from DBA_INDEXES where owner =current_user()) idx , ( select * from dba_ind_columns where index_owner=current_user() ) col where col.index_name = idx.index_name and idx.index_type ='NORMAL' and idx.table_type ='TABLE' and idx.table_name in ('BD_SHAREBILLTYPE','BD_AASSIGN','BD_ACCASOA','BD_ACCASOAHISTORY','BD_ACCASS','BD_ACCASSHISTORY','BD_ACCASSITEM','BD_ACCCHART','BD_ACCCTRLASOA','BD_ACCCTRLORG','BD_ACCCTRLRULE','BD_ACCELEMENT','BD_ACCESSORFACREG','BD_ACCFIELDCTRL','BD_ACCFIELDCTTEMP','BD_ACCHALFYEAR','BD_ACCOBLIGATE','BD_ACCOUNT','BD_ACCOUNT_DIST','BD_ACCOUNTMAP','BD_ACCPERIOD','BD_ACCPERIODMONTH','BD_ACCPERIODQUART','BD_ACCPERIODSCHEME','BD_ACCPLYCHART','BD_ACCSYSTEM','BD_ACCTYPE','BD_ACTALLOC','BD_ACTIVITY','BD_ACTIVITY_B','BD_ADDRESS','BD_ADDRESSDOC','BD_ADDRLINKMAN','BD_ADJUSTRATE','BD_AGENTSTORE','BD_ANSYLOG','BD_AREACL','BD_ASSIGINRELATION','BD_ASSIGN_RULE','BD_ASSIGN_TAB','BD_ASSIGN_TEMP','BD_ASSLINENUM','BD_AVGRATE','BD_BALATYPE','BD_BANKACCBAS','BD_BANKACCSUB','BD_BANKACCUSE','BD_BANKDOC','BD_BANKLINKMAN','BD_BANKTYPE','BD_BATCHUPDATEATTR','BD_BATCHUPDATERULE','BD_BATCHUPDATETAB','BD_BATCHUPRULETEAM','BD_BIZEVENTLOG','BD_BMRT','BD_BOM','BD_BOM_ACTIVITY','BD_BOM_B','BD_BOM_BSRC','BD_BOM_LOSS','BD_BOM_OUTPUTS','BD_BOM_POSITION','BD_BOM_REPL','BD_BOM_SELECT','BD_BOM_USEORG','BD_BOM_WIP','BD_BRANDDOC','BD_CAPEXITEM','BD_CAPRT','BD_CAPWT','BD_CASHACCOUNT','BD_CASHFLOW','BD_CASHFLOWUSE','BD_CBSASSIGN','BD_CBSDEFAULT','BD_CBSNODE','BD_CFADJITEMSET','BD_CHANNELTYPE','BD_CODEEDITOR','BD_COMPELECOMPA','BD_COMPELECOMPA_B','BD_COORDINATION','BD_COSTCOMPONENT','BD_COSTCOMPSTRUC','BD_COSTCOMPSTRUC_B','BD_COSTCOMPSTRUCDISTRI','BD_COSTMODE','BD_COSTOBJTYPE','BD_COUNTRY_REGION','BD_COUNTRYZONE','BD_CR_CONFIG','BD_CR_RESOURCE','BD_CROSSBUSIATTR','BD_CROSSBUSIMAP','BD_CROSSRESTDATA','BD_CROSSRULE','BD_CROSSRULEBUSI','BD_CROSSRULEREST','BD_CROSSRULESCOPE','BD_CURRINFO','BD_CURRRATE','BD_CURRTYPE','BD_CUST_COORDINATION','BD_CUST_SUPPLIER','BD_CUST_SUPPLIER_C','BD_CUSTADDRESS','BD_CUSTADDRESSREF','BD_CUSTAXES','BD_CUSTBANK','BD_CUSTCLASS','BD_CUSTCREDITCTL','BD_CUSTFINANCE','BD_CUSTLINKMAN','BD_CUSTMATERIAL','BD_CUSTMATERSUB','BD_CUSTMERGE','BD_CUSTOMER','BD_CUSTOMER_C','BD_CUSTOMER_PF','BD_CUSTORG','BD_CUSTRENAME','BD_CUSTSALE','BD_CUSTSALECLASS','BD_CUSTSTRUCTURE','BD_CUSTTAXTYPES','BD_CUSTUPGRADE','BD_CUSTVAT','BD_DATAREPAIR','BD_DISTRESTRANS_LOG','BD_DISTRIBUTERES','BD_DISTRIBUTERES_B','BD_DISTRIBUTERES_LOG','BD_DISTSYSTEMSET','BD_DISTTOKENCTRL','BD_DISTTOKENCTRL_B','BD_DISTTOKENSET','BD_DISTTOKENSET_B','BD_DISTTRANSDEL_LOG','BD_DOCCHECK','BD_ELEMRELATION','BD_EMATERIAL','BD_EXRATESCHEME','BD_FCLASS','BD_FCLASS_B','BD_FEATURE','BD_FEATURE_B','BD_FEATURECODE','BD_FEATURECODE_B','BD_FEATURECODE_SN','BD_FFILE','BD_FFILE_B','BD_FORMATDOC','BD_FPRICE','BD_FPRICE_B','BD_FREECUSTOM','BD_FRELATION','BD_FRELATION_B','BD_FUNDPLAN','BD_FUNDPLANUSE','BD_GOODSCODE','BD_GOODSTAXES','BD_HOLIDAY','BD_INCOME','BD_INCOMECH','BD_INCOMEPERIOD','BD_INCOTERM','BD_INDUSTRY','BD_INOUTBUSICLASS','BD_INOUTUSE','BD_LINKMAN','BD_MANDATE','BD_MARASSISTANT','BD_MARASSTFRAME','BD_MARASSTRANGE','BD_MARASSTRST','BD_MARASSTRSTVAL','BD_MARBASCLASS','BD_MARCOSTCLASS','BD_MARORG','BD_MARPUCLASS','BD_MARSALECLASS','BD_MATEGRADE','BD_MATERIAL','BD_MATERIAL_C','BD_MATERIAL_PF','BD_MATERIAL_V','BD_MATERIAL_V_C','BD_MATERIALBINDLE','BD_MATERIALCONVERT','BD_MATERIALCOST','BD_MATERIALCOSTMOD','BD_MATERIALFI','BD_MATERIALPFC','BD_MATERIALPFCC','BD_MATERIALPFCCSUB','BD_MATERIALPFCSUB','BD_MATERIALPLAN','BD_MATERIALPROD','BD_MATERIALPU','BD_MATERIALREPL','BD_MATERIALSALE','BD_MATERIALSTOCK','BD_MATERIALTAXTYPE','BD_MATERIALWARH','BD_MATTAXES','BD_MEASDOC','BD_MULTIMODE','BD_NETBANKINFTP','BD_NOTETYPE','BD_PAYMENT','BD_PAYMENTCH','BD_PAYPERIOD','BD_PERIODMAPPING','BD_PERIODMAPPINGDETAIL','BD_PLANSTRATEGY','BD_PLANSTRATEGYGROUP','BD_PLANSTRATEGYGROUP_B','BD_PREDATAIDMAP','BD_PRICEACCURACY','BD_PRODLINE','BD_PROJECT','BD_PROJECT_B','BD_PROJECT_C','BD_PROJECTCLASS','BD_PROJECTCLASS_B','BD_PROJTYPECBS','BD_PSNBANKACC','BD_PSNCL','BD_PSNDOC','BD_PSNDOC_C','BD_PSNIDTYPE','BD_PSNJOB','BD_PSNJOB_C','BD_RACK','BD_RATESCHEMA','BD_RATESCHEMACH','BD_REF_FILTER','BD_REF_PLUGIN','BD_REF_RELATION','BD_REGION','BD_REPITEMMAPPING','BD_REPORTITEM','BD_REPORTSYSTEM','BD_RT','BD_SHIFT','BD_SHIFTMUTEXBU','BD_SHIFTTYPE','BD_STORDOC','BD_SUPABCLASS','BD_SUPABILITYSET','BD_SUPADDRESS','BD_SUPADDRESSREF','BD_SUPCOUNTRYTAXES','BD_SUPFINANCE','BD_SUPLINKMAN','BD_SUPMERGE','BD_SUPORG','BD_SUPPLIER','BD_SUPPLIER_C','BD_SUPPLIER_GRADE','BD_SUPPLIER_GRADE_SYS','BD_SUPPLIER_PF','BD_SUPPLIERBANK_PF','BD_SUPPLIERCLASS','BD_SUPQUALIDOC','BD_SUPQUALILEVEL','BD_SUPQUALITYPE','BD_SUPRENAME','BD_SUPSTOCK','BD_SUPSTRUCTURE','BD_SUPTAXES','BD_SUPUPGRADE','BD_SUPVAT','BD_SYN_CUSTOMER','BD_SYN_MATERIAL','BD_SYN_ORG','BD_SYN_STORDOC','BD_SYN_SUPPLIER','BD_TAXCODE','BD_TAXITEMS','BD_TAXRATE','BD_TAXREGION','BD_TAXREGIONB','BD_TEAM','BD_TEAM_B','BD_TEMPLETITEM','BD_TRANSACTIONCODE','BD_TRANSPORTTYPE','BD_USEDACCBOOK','BD_VOUCHERTYPE','BD_WK','BD_WK_FEED','BD_WKCLASS','BD_WORKCALENDAR','BD_WORKCALENDARDATE','BD_WORKCALENDRULE','BD_WORKCALENDYEAR','BD_WT','CT_EXPSET','CT_PRICETEMPLET','CT_PRICETEMPLET_B','CT_TERMSET','CT_TERMTYPE','FI_RECPAYTYPE','HR_TEMPTABLE1','IC_SETPART','IC_SETPART_B','ORG_LIACTCOSTRG','ORG_MORGSTRU','ORG_MORGSTRU_V','ORG_MORGSTRUMEMBER','ORG_MORGSTRUMEMBER_V','ORG_PRODUCTIONPLAN','ORG_PRODUPMEMBER','PAM_CATE_PARAM','PAM_CATEGORY','PAM_STATUS','PD_MEAINSTRUCL','PD_MEASTOOL','PD_RC','PD_RT','PD_RT_ACTIVITY','PD_RT_ASSORG','PD_RT_B','PD_RT_OPAC','PD_RTTEMPLATE','PD_RTTEMPLATE_B','PM_EPS','PM_WBS','PM_WBS_PREFIX','PM_WBS_STATEDRIVE','QC_REJECTTYPE','QC_REJECTTYPE_B','RESA_ACCASOA','RESA_BIZINDEX','RESA_BIZINDEXCLASS','RESA_CCDEPTS','RESA_CCGROUPDETAIL','RESA_CCSTRUCTURE','RESA_CCWORKCENTER','RESA_CONTROLAREA','RESA_CONTROLAREAITEM','RESA_COSTCENTER','RESA_COSTCENTER_V','RESA_COSTCENTERGROUP','RESA_COSTCENTERSGREL','RESA_ELEMENTSYSTEM','RESA_ELEMENTTYPE','RESA_FACACCRELATE','RESA_FACASOAHISTORY','RESA_FACASSHISTORY','RESA_FACCTRLORG','RESA_FACTOR','RESA_FACTORASOA','RESA_FACTORASS','RESA_FACTORCHART','RESA_FACTORCTRLCA','RESA_FACTORCTRLGROUP','RESA_FACTORFRAMEWORK','RESA_FACTORGROUP','RESA_FACTORSGREL','RESA_FACTORSINGROUP','RESA_INDEXDIMENSION','RESA_POLICYCHAR','RESA_SOSINCA','SCM_QUALITYLEVEL_B','SCM_QUALITYLEVEL_H','SRMSM_MARBASCLSEXT','SRMSM_MARBASCLSEXT_B','SRMSM_SUPPLIEREXT_A','SRMSM_SUPPLIEREXT_H','SRMSM_SUPPLIEREXT_I','SRMSM_SUPPLIEREXT_P','SRMSM_SUPPLIEREXT_Q','TXM_TAXKIND','TXM_TAXRATE','UAPBD_FORMPROJECT','UAPBD_FORMPROJECT_B','UAPBD_FORMPROJECTSYSTEM','VRM_VENDORMATER') order by idx.table_name,idx.index_name,col.column_position)aa where not Exists (select 1 from user_constraints c where c.constraint_type ='P' and c.constraint_name = aa.index_name )