表结构的迁移

在oracle中，将表结构迁移其实有多种方法：

1、exp导出，且row＝n

2、利用DBMS_METADATA.GET_DDL，利用该方法可以将当初的建表语句导出成文本，且建表语句包含storage。

set pagesize 0
set long 90000
set feedback off
set echo off 
SELECT DBMS_METADATA.GET_DDL('TABLE', U.TABLE_NAME) FROM USER_TABLES U;
SELECT DBMS_METADATA.GET_DDL('INDEX', U.INDEX_NAME) FROM USER_INDEXES U;
SELECT DBMS_METADATA.GET_DDL('VIEW', U.VIEW_NAME) FROM User_Views U;

set pagesize 0

set long 90000

set feedback off

set echo off

SELECT DBMS_METADATA.GET_DDL('TABLE', U.TABLE_NAME) FROM USER_TABLES U;

SELECT DBMS_METADATA.GET_DDL('INDEX', U.INDEX_NAME) FROM USER_INDEXES U;

SELECT DBMS_METADATA.GET_DDL('VIEW', U.VIEW_NAME) FROM User_Views U;

3、利用user_tables and user_tab_columns导出，优点是不涉及到storage，仅仅导出建表的create语句。

SELECT decode(ta.column_id,
              tb.mincol_id,
              'CREATE TABLE ' || ta.table_name || chr(10) || '(' || chr(10),
              '') || rpad(column_name, 40) || data_type ||
       decode(data_type,
              'NUMBER',
              decode(sign(data_precision),
                     1,
                     '(' || data_precision || ',' || data_scale || ')',
                     ''),
              decode(sign(instr('DATE,LONG,LONG RAW,BLOB,CLOB,FLOAT,UNDEFINED,MLSLABEL,',
                                data_type || ',')),
                     1,
                     '',
                     '(' || data_length || ')')) ||
       decode(ta.column_id, tb.maxcol_id, chr(10) || ');', ',')
  FROM user_tab_columns ta,
       (SELECT table_name,
               MAX(column_id) maxcol_id,
               MIN(column_id) mincol_id
          FROM user_tab_columns
         GROUP BY table_name) tb,
       user_tables tc
 WHERE ta.table_name = tb.table_name
   AND ta.table_name = tc.table_name
 ORDER BY ta.table_name, ta.column_id;

SELECT decode(ta.column_id,

tb.mincol_id,

'CREATE TABLE ' || ta.table_name || chr(10) || '(' || chr(10),

'') || rpad(column_name, 40) || data_type ||

decode(data_type,

'NUMBER',

decode(sign(data_precision),

'(' || data_precision || ',' || data_scale || ')',

''),

decode(sign(instr('DATE,LONG,LONG RAW,BLOB,CLOB,FLOAT,UNDEFINED,MLSLABEL,',

data_type || ',')),

'',

'(' || data_length || ')')) ||

decode(ta.column_id, tb.maxcol_id, chr(10) || ');', ',')

FROM user_tab_columns ta,

(SELECT table_name,

MAX(column_id) maxcol_id,

MIN(column_id) mincol_id

FROM user_tab_columns

GROUP BY table_name) tb,

user_tables tc

WHERE ta.table_name = tb.table_name

AND ta.table_name = tc.table_name

ORDER BY ta.table_name, ta.column_id;

一条评论

发表回复取消回复

表结构的迁移

相关文章

如何做一份完善的补丁分析

ORA-600[6122]报错处理

查找疯狂增长的arch-续

一条评论

发表回复 取消回复

发表回复取消回复