FUNCTION get_ddl (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
第一位是object_type,那么我们通过dba_objects中查看dblink的类型为什么呢?
1.首先创建dblink
create database link dl_test
connect to hr identified by hr
using 'sztech1';
2.察看类型
select object_name,object_type from dba_objects where object_name='DL_TEST';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DL_TEST DATABASE LINK
3.察看ddl
SQL> SELECT dbms_metadata .get_ddl('DATABASE LINK','DL_TEST','SYS') from dual;
ERROR:
ORA-31600: invalid input value DATABASE LINK for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
no rows selected
看来直接使用这里的object_type有问题,需要换成db_link:
SELECT dbms_metadata .get_ddl('DB_LINK','DL_TEST','SYS') from dual;
SQL> SET long 20000
SQL> SELECT dbms_metadata .get_ddl('DB_LINK','DL_TEST','SYS') from dual
DBMS_METADATA.GET_DDL('DB_LINK','DL_TEST','SYS')
--------------------------------------------------------------------------------
CREATE DATABASE LINK "DL_TEST"
CONNECT TO "HR
USING 'sztech1'