索引 ,主键,字段

user_tab_columns
user_tab_cols
user_cons_columns

--索引
select * from user_indexes a where a.table_name = 'DCS_RESERVED_DATA_RNSCB'；
--主键
select a.constraint_name,a.column_name from user_cons_columns a ,user_constraints b
where a.constraint_name = b.constraint_name and b.constraint_type='P' and a.table_name = 'DCS_RESERVED_DATA_T_FXRQJCDZ';

---获取拉链表主键和非主键字段，用来生成MD5值
get_md5_sql = """SELECT LISTAGG(CASE
WHEN POSITION IS NOT NULL THEN
COLUMN_NAME
END,
'||''#''||') WITHIN GROUP(ORDER BY COLUMN_ID) AS MD5_FIELD,
LISTAGG(CASE
WHEN POSITION IS NULL THEN
COLUMN_NAME
END,
'||''#''||') WITHIN GROUP(ORDER BY COLUMN_ID) AS NON_MD5_FIELD
FROM (SELECT CASE
WHEN T1.DATA_TYPE LIKE 'NUMBER%' THEN
'TO_CHAR(' || T1.COLUMN_NAME || ')'
WHEN T1.DATA_TYPE LIKE 'DATE%' THEN
'TO_CHAR(' || T1.COLUMN_NAME || ',''YYYYMMDDHH24MISS'')'
ELSE
T1.COLUMN_NAME
END AS COLUMN_NAME,
T2.POSITION,
T1.COLUMN_ID
FROM USER_TAB_COLUMNS T1
LEFT JOIN USER_CONS_COLUMNS T2
ON T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
AND T2.POSITION IS NOT NULL
WHERE T1.TABLE_NAME = '{}'
AND T1.COLUMN_NAME NOT IN ('REC_ID_IN_SRC',
'DEL_FLAG',
'NON_MD5_FIELD',
'MD5_FIELD',
'DATA_BATCH_DATE_TIME',
'DATA_START_DATE_TIME',
'DATA_END_DATE_TIME',
'DATA_LOAD_DATE_TIME',
'ETL_JOB_NAME',
'BATCH_NO',
'SRC_SYS_ID',
'SRC_TBL_NAME'
{}))
""".format(table_name, remove_col)

---获取表是否在数据库中存在
get_join_cond_sql = f"""SELECT LISTAGG(CASE
WHEN DATA_TYPE = 'DATE' THEN
'NVL(TAR.' || COLUMN_NAME || ', DATE ''1900-01-01'')=NVL(SRC.' ||
COLUMN_NAME || ', DATE ''1900-01-01'')'
WHEN DATA_TYPE = 'NUMBER' THEN
'NVL(TAR.' || COLUMN_NAME || ', 0)=NVL(SRC.' || COLUMN_NAME ||
', 0)'
ELSE
'NVL(TAR.' || COLUMN_NAME || ', ''#'')=NVL(SRC.' || COLUMN_NAME ||
', ''#'')'
END,
' and ') WITHIN GROUP(ORDER BY COLUMN_ID) AS RN
FROM USER_TAB_COLS
WHERE TABLE_NAME = '{table_name}'
AND COLUMN_NAME NOT IN ('DATA_BATCH_DATE_TIME',
'DATA_START_DATE_TIME',
'DATA_LOAD_DATE_TIME',
'ETL_JOB_NAME',
'BATCH_NO',
'MD5_FIELD',
'NON_MD5_FIELD',
'DATA_CLCT_DATE_TIME',
'DATA_END_DATE_TIME')
AND COLUMN_ID IS NOT NULL
ORDER BY COLUMN_ID"""