Oracle 的 dbms_metadata 如何产生与表模式文件分离的约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3784722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can dbms_metadata from Oracle produce constraints seprate from the table schema files?
提问by Ronaldus
Why is dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE) not generating constraints in separate files? At this moment this pl/sql pastes the constraints after the table schema definition files. What flag do I have to use to achieve this separate constraints schema definition directory?
为什么 dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE) 不在单独的文件中生成约束？此时此 pl/sql 将约束粘贴在表模式定义文件之后。我必须使用什么标志来实现这个单独的约束模式定义目录？
回答by Gary Myers
DBMS_METADATA.GET_DDL returns a single CLOB, not a file and definitely not multiple files.
DBMS_METADATA.GET_DDL 返回单个 CLOB，而不是文件，绝对不是多个文件。
The only way to get separate CLOBs for each constraint is to do a separate GET_DDL for each constraint:
为每个约束获取单独的 CLOB 的唯一方法是为每个约束做一个单独的 GET_DDL：
select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)
from user_constraints;
回答by Ronaldus
extractObj()
{
APT_CMD=${APT_CMD}"exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE); APT_CMD=${APT_CMD}"exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
" "
# Add FORCE to view statements # Add FORCE to view statements
APT_CMD=${APT_CMD}"exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'FORCE', TRUE); APT_CMD=${APT_CMD}"exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'FORCE', TRUE);
" "
# Make ref integrity constraints alter statements +-
APT_CMD=${APT_CMD}"exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
"
# Remap the schema name to blank = # Remap the schema name to blank
# This is done by processing the tmp file using sed # This is done by processing the tmp file using sed
# Add a terminator to the end of the extracted SQL # Add a terminator to the end of the extracted SQL
APT_CMD=${APT_CMD}"exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); <> # APT_CMD=${APT_CMD}"exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
" #"
# Make the output look 'PRETTY'!! = # Make the output look 'PRETTY'!!
APT_CMD=${APT_CMD}"execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE); <> # APT_CMD=${APT_CMD}"execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
" #"
if [[ ${OBJECT_TYPE} = 'DB_LINK' ]]
then
APT_CMD=${APT_CMD}"select substr(to_char(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'))
,1
,INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'\"',1,3)
)||
'<USER>'||
substr(to_char(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'))
,INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'\"',1,4)
, INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'VALUES',1,1)-INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'\"',1,4)
)||
'''<PW>'''||
substr(to_char(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'))
,INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'''',1,2)+1
,INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'''',1,3)-INSTR(DBMS_METADATA.GET_DDL('DB_LINK','PSINTERFACE'),'''',1,2)
)||
'<DB>'';'
from dual
"
else
APT_CMD=${APT_CMD}" select DBMS_METADATA.GET_DDL('${OBJECT_TYPE}','${OBJECT_NAME}') from dual APT_CMD=${APT_CMD}" select DBMS_METADATA.GET_DDL('${OBJECT_TYPE}','${OBJECT_NAME}')||'/' from dual
" = "
fi +-
APT_CMD=${APT_CMD}" ; " = APT_CMD=${APT_CMD}" ; "
if [[ -n ${APT_DEBUG} ]] +-
then
logMsg ${LM_INFO} "${APT_CMD}"
fi
SPOOLFILE=${OBJDIR}/$(print ${OBJECT_NAME} | tr "[A-Z]" "[a-z]") = SPOOLFILE=${OBJDIR}/$(print ${OBJECT_NAME} | tr "[A-Z]" "[a-z]")
CMDLINE=${OBJECT_DIR}/$(print ${OBJECT_NAME} | tr "[A-Z]" "[a-z]").sql CMDLINE=${OBJECT_DIR}/$(print ${OBJECT_NAME} | tr "[A-Z]" "[a-z]").sql
print " ...extracting ${OBJECT_TYPE} ${OBJECT_NAME}" <>
oraSql ${L_APT_USER} ${L_APT_PASSWD} ${L_APT_SID} "${APT_CMD}" ${SPOOLFILE}.tmp >/dev/null oraSql ${L_APT_USER} ${L_APT_PASSWD} ${L_APT_SID} "${APT_CMD}" ${SPOOLFILE}.tmp
=
# Remove any references to the local schema - note other schema references used in triggers will remain!!! # Remove any references to the local schema - note other schema references used in triggers will remain!!!
cat ${SPOOLFILE}.tmp | sed "s/\"${UPPER_APT_USER}\"\.//g" >${SPOOLFILE}.sql <> cat ${SPOOLFILE}.tmp | sed "s/\"${L_APT_USER}\"\.//g" >${SPOOLFILE}.sql
rm ${SPOOLFILE}.tmp = rm ${SPOOLFILE}.tmp
print "@${CMDLINE}" >>${BUILD_FILE} print "@${CMDLINE}" >>${BUILD_FILE}
+-
# If the object type is table further parse it to strip out the constraints to constraint files
if [[ ${OBJECT_DIR} = tables ]]
then
# This splits the second part of the file (after the first semi-colon) to a temporary constraint file for further processing
mkDir ${BASE_DIR}/${L_APT_USER}/constraints
CONTMP=${BASE_DIR}/${L_APT_USER}/constraints/${OBJECT_NAME}.tmp
cat ${SPOOLFILE}.sql | awk "BEGIN { x=0 }
// { if (x == 1) { print; } }
/\;/ { x=1; }
END { }" | grep -v ^$ >${CONTMP}
# Split each alter statement into a seperate constraint file in the constraints directory
while read line
do
if [[ $(print $line | grep -c 'ALTER TABLE') > 0 ]]
then
CONSTRAINT=$(print $line | tr "[A-Z]" "[a-z]" | cut -d\" -f4)
CONFILE=${BASE_DIR}/${L_APT_USER}/constraints/${CONSTRAINT}.sql
UPPERCONSTRAINT=$(print ${CONSTRAINT} | tr "[a-z]" "[A-Z]")
print " .extracting CONSTRAINT ${UPPERCONSTRAINT}"
fi
print $line>>${CONFILE}
done<${CONTMP}
rm -f ${CONTMP}
fi
}
