Oracle常用导元数据方法

1 说明

前两天领导发邮件要求导出O库一批表和索引的ddl语句做国产化测试，涉及6个系统，6千多张表，还好涉及的用户并不多，要不然很麻烦。
如此大费周折原因，是某国产库无法做元数据迁移。。。额，只能我手动导出，拿去给他们同步。

考虑的方案有两个：

1. 使用get_ddl查询出语句。
2. 使用数据泵导元数据。

两种方式各有优点，分场景使用。

2 get_ddl查询

在需要查询的表不多，且不用导索引时，get_ddl方式比较省力。

缺点就是步骤比较多，表、索引、注释等都需要单独做查询；段属性之类的信息无法屏蔽。最大的问题是比较慢，我导出1000张表及其相关索引花了差不多4个小时，时间主要花在查询索引ddl上。

2.1 创建表清单表

将需要生成ddl的表粘到下面表中：

create table lu9up.cs_tab_250210
(owner           varchar2(30) not null,table_name      varchar2(30) not null
);select * from lu9up.cs_tab_250210 for update;

2.2 部署脚本

进入到某个目录下，创建script_cs_250210.sql脚本，用于跑get_ddl：

cat > script_cs_250210.sql << EOF
declarescripts varchar2(4000);cursor tab isselect owner,table_name,dbms_metadata.get_ddl('TABLE', table_name, owner) || ';' table_scriptsfrom lu9up.cs_tab_250210--where owner = '' and table_name = ''order by owner;
beginfor i in tab loopdbms_output.put_line(chr(10) || '----' || i.owner || '.' ||i.table_name);dbms_output.put_line(i.table_scripts);for j in (select to_char(dbms_metadata.get_ddl(t.index_type,t.index_name,t.owner)) || ';' scriptsfrom (select owner, index_name, 'INDEX' index_typefrom dba_indexes bwhere owner = i.ownerand not exists(select 1from dba_constraints cwhere b.owner = i.ownerand b.index_name = c.constraint_name)and table_name = i.table_nameorder by 3, 2) t) loopdbms_output.put_line(j.scripts);end loop;end loop;
end;
/
EOF

创建script_cs_250210.sh脚本，调用script_cs_250210.sql脚本：

#!/bin/bash
export ORACLE_SID=xxxdb
sqlplus -s / as sysdbba <<EOF
set lines 500
set serveroutput on
spool cs_tab_250210.sql
@script_cs_250210.sql
spool off
EOF

2.3 导出元数据到sql文件

执行script_cs_250210.sh脚本：

nohup sh script_cs_250210.sh &

结果在cs_tab_250210.sql文件。

3 数据泵导出

如果涉及到的表、索引很多的情况下，使用数据泵比较快，几千张表十来分钟就可以导出完毕了，且可读性比较高。

3.1 创建表清单表

将需要生成ddl的表粘到下面表中：

create table lu9up.cs_tab_250210
(owner           varchar2(30) not null,table_name      varchar2(30) not null
);select * from lu9up.cs_tab_250210 for update;

3.2 创建导出目录

创建一个具有oracle权限的导出目录：

create directory csdir as '/home/oracle/lu9up';
grant read,write on directory csdir to lu9up;
grant datapump_exp_full_database to lu9up;
grant datapump_imp_full_database to lu9up;select * from dba_directories;

3.3 生成执行语句

由于得按schema导出，执行以下sql可生成不同schema的数据泵执行语句：

select '--'||owner|| chr(10) ||'cat > cs_'||owner||'_exp_250210.par << EOF' || chr(10) ||'directory=CSDIR' || chr(10) || 'schemas='||owner||'' || chr(10) ||'include=table:"in (select table_name from lu9up.cs_tab_250210 where owner = '''||owner||''')"' ||chr(10) || 'parallel=8' || chr(10) || 'cluster=n' || chr(10) ||'content=metadata_only' || chr(10) ||'dumpfile=cs_'||owner||'_exp_250210.dmp' || chr(10) ||'logfile=cs_'||owner||'_exp_250210.log' || chr(10) || 'EOF' || chr(10) ||chr(10) ||'expdp lu9up/oracle parfile=cs_'||owner||'_exp_250210.par' ||chr(10) ||chr(10)||chr(10)|| 'cat > cs_'||owner||'_imp_250210.par << EOF' || chr(10) ||'directory=CSDIR' || chr(10)|| 'dumpfile=cs_'||owner||'_exp_250210.dmp' ||chr(10) || 'parallel=8' || chr(10) ||'exclude=grant,statistics' || chr(10) ||'transform=segment_attributes:n'|| chr(10) ||'sqlfile='||owner||'.sql' || chr(10) ||'logfile=cs_'||owner||'_imp_250210.log' || chr(10) ||'EOF' || chr(10) ||chr(10) ||'impdp lu9up/oracle  parfile=cs_'||owner||'_imp_250210.par'from (select owner, count(*) ctfrom lu9up.cs_tab_250210group by ownerorder by ct desc);

有多个用户的时候就非常方便，可以直接拿去执行，不用再修改脚本。

其实也可以直接用dblink+impdp不落地导sqlfile，省去了expdp的步骤。

3.4 脚本部署

把cs_xxx_exp_250210.par和cs_xxx_imp_250210.par两个文件部署到数据库服务器，确认有oracle用户权限。

3.5 执行导出

脚本分两个，一个是使用expdp导出元数据到dmp文件，然后再用impdp将dmp文件转化为sqlfile。

expdp：

expdp lu9up/oracle  parfile=cs_xxx_exp_250210.par

impdp：

impdp lu9up/oracle  parfile=cs_xxx_imp_250210.par

结果生成到导出目录csdir中xxx.sql文件。

4 总结

总的来说，使用数据泵导出元数据比较符合规范，内容比较完整，阅读性高，且可以使用参数控制输出的内容。
get_ddl在少量表和索引的情况下，相对比较方便快捷。