Oralce数据库迁移-dbms_metadata.get_ddl 的使用方法总结

本文介绍如何使用Oracle的dbms_metadata包来获取数据库对象的定义语句,包括表、视图、索引、过程等功能,并展示了如何调整输出格式。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

(1)dbms_metadata.get_ddl 的使用方法总结

 --输出信息采用缩排或换行格式化
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'pretty', true);
    --确保每个语句都带分号
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'sqlterminator', true);
    --关闭表索引、外键等关联(后面单独生成)
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'constraints', false);
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'ref_constraints', false);
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'constraints_as_alter', false);
    --关闭存储、表空间属性
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'storage', false);
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'tablespace', false);
    --关闭创建表的PCTFREE、NOCOMPRESS等属性
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'segment_attributes', false);

1 获取profile

SELECT DBMS_METADATA.GET_DDL('PROFILE','APP_USER') FROM DUAL;

1 获取db_link定义metadata信息如下:

SELECT dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER) FROM DBA_DB_LINKS;

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;

select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;

set pagesize 0

set long 90000

set feedback off

set echo off

spool schema_ddl.sql

SELECTDBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner) FROM DBA_TABLES u;

SELECTDBMS_METADATA.GET_DDL('VIEW',u.view_name,u.owner) FROM DBA_VIEWS u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner)FROM DBA_INDEXES u;

selectdbms_metadata.get_ddl('PROCEDURE',u.object_name, u.owner,) from dba_objects uwhere u.object_type = 'PROCEDURE';

selectdbms_metadata.get_ddl('FUNCTION',u.object_name, u.owner,) from dba_objects uwhere u.object_type = 'FUNCTION';

spool off;

1.显示设置:

/*创建DBMS_METADATA 
@?/rdbms/admin/catmeta.sql 
*/ 
SET SERVEROUTPUT ON 
SET LINESIZE 1000 
SET FEEDBACK OFF 
set long 999999 
SET PAGESIZE 1000 
/*若希望不显示storage参数: 
EXECDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

 

set line 200

set pagesize 0

set long 99999

set feedback off

set echo off

select dbms_metadata.get_ddl('TABLESPACE','TBS_NAME') from dual;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TS.tablespace_name)

FROM DBA_TABLESPACES TS;

 

selectdbms_metadata.get_ddl('USER','EPAY_USER') from dual;

SELECTDBMS_METADATA.GET_DDL('USER',U.username)  FROM DBA_USERS U;

4 )

4) 获取物化视图

1) that would bethe correct approach 

2) you definitely have the ability to either a) run more than one query intothe same output file or b) run a single query that is a bunch of union ALLstatements. 


3) you can get materialized views. 


 

ops$tkyte%ORA10GR2>exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE );

 

PL/SQL proceduresuccessfully completed.

 

ops$tkyte%ORA10GR2>create materialized view mv

  2  as

  3 select * from t;

 

Materializedview created.

 

ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2>select dbms_metadata.get_ddl( 'MATERIALIZED_VIEW', 'MV' ) from dual

  2  /

 

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV')

-------------------------------------------------------------------------------

 

  CREATE MATERIALIZED VIEW"OPS$TKYTE"."MV"

  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40INITRANS 1 MAXTRANS 255 NOCOMPRESS LO

GGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

  BUILD IMMEDIATE

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

  REFRESH FORCE ON DEMAND

  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACKSEGMENT

  DISABLE QUERY REWRITE

  AS SELECT "T"."X""X","T"."Y" "Y" FROM "T""T";

 

5) 获取公共同义词

SELECTDBMS_METADATA.get_ddl(OBJECT_TYPE,OBJECT_NAME,schema => 'PUBLIC') FROMDBA_OBJECTS WHERE OBJECT_NAME='PUBLIC'

6) config

get the table ddl without constraints then if you are going to get theconstraints later. 

 

ops$tkyte%ORA11GR2> create table t (xint primary key);

 

Table created.

 

ops$tkyte%ORA11GR2> connect /

Connected.

ops$tkyte%ORA11GR2> selectdbms_metadata.get_ddl('TABLE','T') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','T')

-------------------------------------------------------------------------------

 

 CREATE TABLE "OPS$TKYTE"."T"

  (    "X" NUMBER(*,0),

        PRIMARY KEY ("X")

 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING

 TABLESPACE "USERS" ENABLE

   )SEGMENT CREATION DEFERRED

 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

 TABLESPACE "USERS"

ops$tkyte%ORA11GR2> execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);

 

PL/SQL procedure successfully completed.

 

ops$tkyte%ORA11GR2> selectdbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

-------------------------------------------------------------------------------

 

 CREATE TABLE "OPS$TKYTE"."T"

  (    "X" NUMBER(*,0)

   )SEGMENT CREATION DEFERRED

 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

 TABLESPACE "USERS"

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SET PAGESIZE 1000

 
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;


set head off
set pages 0
set long 9999999

SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS');

 

 

帮我转换成SQLSERVER 版本 CREATE OR REPLACE PROCEDURE IMES.SP_CHECK_ROUTE ( T_STATIONNAME IN VARCHAR2, T_SN IN VARCHAR2, TRES OUT VARCHAR2) AS V_COUNT NUMBER; V_CURRENT_STATION IMES.P_SN_STATUS.STATION_TYPE%TYPE; --当前站 V_NEXT_STATION IMES.P_SN_STATUS.NEXT_STATION_TYPE%TYPE; --下一站 V_WORK_ORDER IMES.P_SN_STATUS.WORK_ORDER%TYPE; --工单 V_ROUTE_NAME IMES.P_SN_STATUS.ROUTE_NAME%TYPE; --route名字 V_START_STATION IMES.P_SN_STATUS.STATION_TYPE%TYPE; --工单对应开始站 V_CURRENT_STATUS IMES.P_SN_STATUS.CURRENT_STATUS%TYPE; --当前状态 V_SNSTATUS VARCHAR2 (100); --当前状态 V_WORK_FLAG IMES.P_SN_STATUS.WORK_FLAG%TYPE; --SN 是否报废,1报废,0 OK V_INPUT_STATION IMES.P_SN_STATUS.STATION_TYPE%TYPE; --当前站点对应投入站 V_LINE IMES.M_STATION.LINE%TYPE; --线别名字 V_STAGE IMES.M_STATION.STAGE%TYPE; --区段名 V_NEXT_DESC IMES.M_STATION_TYPE.STATION_TYPE_DESC%TYPE; ----下一站描述 BEGIN ---注释页面选择的station对应参数1.0.0.0 By Dulin 2020/12/5 --V1.0.0.1 By Penn 2021/4/21 增加翻译功能 TRES := 'OK'; IMES.SP_GET_PLACE (T_STATIONNAME, v_LINE, v_STAGE, V_INPUT_STATION); ----------------------------------------------------------------SN 当前状态检查--------------------------------------------- BEGIN ------------查询SN当前状态参数----------------- SELECT A.CURRENT_STATUS, IMES.FN_SNStatus_Result (CURRENT_STATUS), A.WORK_FLAG, A.STATION_TYPE, A.ROUTE_NAME, A.NEXT_STATION_TYPE, A.WORK_ORDER INTO V_CURRENT_STATUS, V_SNSTATUS, V_WORK_FLAG, V_CURRENT_STATION, V_ROUTE_NAME, V_NEXT_STATION, V_WORK_ORDER FROM IMES.P_SN_STATUS A WHERE SERIAL_NUMBER = T_SN AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN --------------SN 不存在------------------------ TRES := IMES.FN_GET_TRANSLATE_MSG ('A0001', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark GOTO ENDP; WHEN OTHERS THEN TRES := IMES.FN_GET_TRANSLATE_MSG ( 'B0001,SP_CHECK_ROUTE,' || SQLERRM || '<' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '>', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark GOTO ENDP; END; ---------------SN的各种状态------------------ IF V_WORK_FLAG = '1' THEN TRES := 'SCRAP'; ELSIF V_WORK_FLAG = '3' THEN TRES := 'IN LAB'; ELSIF ( V_CURRENT_STATUS = '1' OR V_CURRENT_STATUS = '2' OR V_CURRENT_STATUS = '3' OR V_CURRENT_STATUS = '4' OR V_CURRENT_STATUS = '7') THEN TRES := V_SNSTATUS; ELSE SELECT COUNT (*) INTO V_COUNT FROM IMES.P_QC_OFFLINE A WHERE A.SERIAL_NUMBER = T_SN AND (A.QC_IN_TIME IS NULL OR A.QC_OUT_TIME IS NULL); IF V_COUNT > 0 THEN TRES := ' IN OFFLINE QC!'; END IF; END IF; IF TRES <> 'OK' THEN TRES := IMES.FN_GET_TRANSLATE_MSG ('A0182,SCRAP', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE '', --Line V_INPUT_STATION, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark GOTO ENDP; END IF; -------------------------SN Hold 检查----------------------------- IMES.SP_CHECK_HOLD (T_SN, T_STATIONNAME, TRES); IF TRES <> 'OK' THEN GOTO ENDP; END IF; -------------------------------------------------------------------------SN 流程检查--------------------------------------------------------------- -----------没有强制指站------------- IF (V_NEXT_STATION IS NULL) OR (V_NEXT_STATION = '0') THEN -----------------SN生成之后未生产过--------------------- IF (V_CURRENT_STATION = '0') THEN BEGIN -----------查询SN对应工单的默认投入站别----------------------- SELECT START_STATION_TYPE INTO V_START_STATION FROM IMES.P_WO_BASE WHERE WORK_ORDER = V_WORK_ORDER AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN -----------工单不存在--------------------------- TRES := IMES.FN_GET_TRANSLATE_MSG ('A0004', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark GOTO ENDP; WHEN OTHERS THEN TRES := IMES.FN_GET_TRANSLATE_MSG ( 'B0001,SP_CHECK_ROUTE,' || SQLERRM || '<' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '>', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark GOTO ENDP; END; --如果工单对应站别等于当前所选站点对应的投入站别 IF (V_START_STATION = V_INPUT_STATION) THEN TRES := 'OK'; ELSE SELECT IMES.FN_GET_STATIONTYPE_DESC (V_START_STATION) INTO V_NEXT_DESC FROM DUAL; ------工单开始站不存在--------- IF V_NEXT_DESC = '' OR V_NEXT_DESC IS NULL THEN --TRES := 'WO Start Station Type NG'; TRES := IMES.FN_GET_TRANSLATE_MSG ('A0583', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark ELSE --------------------------工单开始站点存在,报错SN 流程错误------------------- TRES := IMES.FN_GET_TRANSLATE_MSG ( 'A0416,' || T_SN || ',' || V_START_STATION || '(' || V_NEXT_DESC || ')', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark END IF; END IF; ELSE ---------如果SN当前站点不为空,正常流程检查----------------- BEGIN --------------------根据当前站点及SN状态和下一站查询流程,能够查到说明流程正确-------------------------- SELECT NEXT_STATION_TYPE INTO V_NEXT_STATION FROM IMES.M_ROUTE_DETAIL WHERE RESULT = V_CURRENT_STATUS AND ROUTE_NAME = V_ROUTE_NAME AND STATION_TYPE = V_CURRENT_STATION AND NEXT_STATION_TYPE = V_INPUT_STATION AND ROWNUM = 1; TRES := 'OK'; EXCEPTION WHEN OTHERS THEN BEGIN --------------------流程错误,获取下一站站点名称并报错--------------------------- SELECT NEXT_STATION_TYPE INTO V_NEXT_STATION FROM IMES.M_ROUTE_DETAIL A WHERE A.ROUTE_NAME = V_ROUTE_NAME AND A.SEQ = (SELECT MAX (SEQ) FROM IMES.M_ROUTE_DETAIL WHERE STATION_TYPE = V_CURRENT_STATION AND ROUTE_NAME = V_ROUTE_NAME AND RESULT = V_CURRENT_STATUS); TRES := IMES.FN_GET_TRANSLATE_MSG ( 'A0416,' || T_SN || ',' || V_NEXT_STATION || '(' || IMES.FN_GET_STATIONTYPE_DESC ( V_NEXT_STATION) || ')', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark EXCEPTION WHEN OTHERS THEN ---------------根据当前站在流程里面查不到下一站,如果是良品状态则ROUTE END,否则REPAIR NG---------- IF V_CURRENT_STATUS = '0' THEN TRES := 'ROUTE END'; ELSE TRES := 'REPAIR NG'; END IF; END; END; END IF; ELSE ----------------------------------有强制指站------------------------------- IF ((V_CURRENT_STATUS = '0') OR (V_CURRENT_STATUS = '9')) THEN IF V_NEXT_STATION = V_INPUT_STATION THEN TRES := 'OK'; ELSE TRES := IMES.FN_GET_TRANSLATE_MSG ( 'A0416,' || T_SN || ',' || V_NEXT_STATION || '(' || IMES.FN_GET_STATIONTYPE_DESC (V_NEXT_STATION) || ')', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); --Remark END IF; ELSE TRES := 'REPAIR NG'; END IF; END IF; <<ENDP>> NULL; EXCEPTION WHEN OTHERS THEN TRES := IMES.FN_GET_TRANSLATE_MSG ( 'B0001,SP_CHECK_ROUTE,' || SQLERRM || '<' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '>', --ERR_CODE,PARAM1,PARAM2..PARAM10 T_SN, --INPUT_VALUE V_LINE, --Line T_STATIONNAME, --Station 'SP_CHECK_ROUTE', --PROGRAM '', --IP '', --empNo ''); END; /
最新发布
07-25
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值