ORACLE迁移记录

生成oracle中dblink创建语句：

SELECT to_char(dbms_metadata.get_ddl('DB_LINK', 'DB_LINK_NAME', 'SCHEMAS')) FROM dual;

生成job创建语句：

SELECT 'declare job number; dbms_job.submit(job => job,what => ''' || what ||
       ''',next_date => to_date(''' ||
       to_char(next_date, 'dd-mm-yyyy hh24:mi:ss') ||
       ''', ''dd-mm-yyyy hh24:mi:ss''),interval => ''' ||
       REPLACE(INTERVAL, '''', '''''') || '''); commit; end;'
  FROM user_jobs;

生成注释创建语句： 

select 'comment on table ' || a.table_name || ' is ' || '''' || a.comments || '''' || ';'
  from user_tab_comments a
 where a.table_type in ('TABLE', 'VIEW');

select 'comment on column ' || a.table_name || '.' || a.column_name ||
       ' is ' || '''' || a.comments || '''' || ';'
  from user_col_comments a
 where a.COMMENTS is not null；

生成用户下的所有表、索引、存储过程、函数的DDL语句：

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME)
  FROM USER_OBJECTS U
 WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION');