整体拷贝oracle数据库的一种方法实践

前提：最近需要对项目数据库做一次拷贝，也可以理解做一个测试数据库。不是很想用oracle的imp命令，就想把所有的建表语句之类的都保存下来。数据库中有900+表，二十多张视图，十几个序列，一个存储过程（去存储过程化，把存储过程都去掉了），一个函数，所以还是可以尝试的。

第一步：先看一下数据库表字段有多少种类型

SELECT DISTINCT DATA_type FROM USER_TAB_COLS;

TIMESTAMP(6)
NVARCHAR2
NUMBER
CHAR
CLOB
DATE
VARCHAR2
BLOB

竟然还有NVARCHAR2 太毒瘤了！不看都不知道，检查了一下只有一个不重要的表使用了，就顺手直接修改了。
查看一下有没有字段设置了默认值，还是有的，那就需要用到 data_default,

SELECT * FROM USER_TAB_COLS b WHERE data_default IS NOT null ORDER BY B.COLUMN_ID;

第二步：成品create语句

SELECT 
CASE WHEN b.COLUMN_ID = 1 THEN 'create table '||B.table_name||'(' END sql1,
B.COLUMN_NAME||'  '||
CASE B.DATA_TYPE WHEN 'VARCHAR2' THEN 'VARCHAR2('||B.data_length||')'
	WHEN 'CHAR' THEN 'VARCHAR2('||B.data_length||')'
	WHEN 'NVARCHAR2' THEN 'VARCHAR2('||B.data_length||')'
	WHEN 'NUMBER' THEN  CASE WHEN B.data_scale IS NULL THEN 'NUMBER' ELSE  'NUMBER('||nvl(B.data_precision,B.data_length)||','||B.data_scale||')' END 
	ELSE  B.DATA_TYPE END  sql2,
CASE WHEN b.data_default IS NOT null THEN ' default ' ELSE null END sql3,
b.data_default sql4,
CASE WHEN b.nullable = 'N' THEN ' not null ' ELSE '' END sql5,
CASE WHEN b.COLUMN_ID = c.COLUMN_ID THEN '); ' ELSE ',' END sql6
FROM USER_TAB_COLS B 
INNER JOIN 
(SELECT aa.table_name,max(bb.COLUMN_ID) COLUMN_ID 
FROM user_tables aa
LEFT JOIN USER_TAB_COLS bb
ON aa.table_name = bb.table_name
group BY aa.table_name) c
ON b.table_name = c.table_name
ORDER BY b.table_name,B.COLUMN_ID

关于这个create语句，有几点注意事项。
1.其中默认有的分行了，多了"，要整体替换掉
2.为什么要分开这么多列，一是为了好看，结构层次分明，二是因为字段类型是long，不能直接||来拼接。不影响使用，直接所有列一起复制到文本文档就能直接用。
3.default要在前面
4.纯number没有写精度的就会报错 纯写number的data_scale是空
5.自己有点强迫症，把nvarche2，char全部替换成了varchar2。如果不需要的童鞋可以去掉。为什么要替换char是因为在某些情况下char类型的数据总会有点问题。

第三步：给表加主键，这个分享两种写法。

方法1:

select    'alter table '||b.table_name||' add constraint '||a.constraint_name||' primary key ('||WMSYS.WM_CONCAT(a.column_name)||');' col
from user_cons_columns a, user_constraints b 
where a.constraint_name = b.constraint_name 
  and b.constraint_type = 'P' 
 AND b.table_name in (select table_name from user_tables)
GROUP BY   a.constraint_name,b.table_name;

方法2.不用wmsys的写法，这个没啥好解释的，其实一样的。

SELECT 
	CASE WHEN a.position = 1 THEN 'alter table '||a.table_name||' add constraint '||a.constraint_name||' primary key (' END sql1,
	a.column_name ,
	CASE WHEN a.position = d.position THEN ');' ELSE ',' END sql2
from user_cons_columns a
INNER JOIN  user_constraints b 
  on a.constraint_name = b.constraint_name 
  and b.constraint_type = 'P' 
INNER JOIN user_tables c 
ON a.table_name = c.table_name
inner JOIN 
(SELECT aa.constraint_name,max(aa.position) position 
FROM user_cons_columns aa
group BY aa.constraint_name) d
ON a.constraint_name = d.constraint_name
order BY   a.table_name,a.constraint_name,a.position;

第四步：序列

考虑到有类似create sequence req_no increment by 1 start with 1 maxvalue 9999 cycle;这样的循环序列，所以拷贝序列的语句如下：

SELECT 'create sequence '||sequence_name||' increment by '||increment_by||' start with '||last_number||' maxvalue '||max_value||
 CASE WHEN cycle_flag = 'Y' THEN ' cycle;' ELSE ' ;'  END sql1 FROM USER_SEQUENCES;

第五步：表注释与表字段注释

select 'comment on table  '||table_name||' is '''|| comments||''';' aa from user_tab_comments WHERE comments IS NOT NULL AND table_type = 'TABLE'
ORDER BY table_name;
select 'comment on column  '||table_name||'.'||column_name ||' is '''|| trim(comments)||''';' aa from user_col_comments WHERE comments IS NOT NULL
ORDER BY table_name;

第二段，有一些表注释有换行符，会有一些分行的数据，变成了用"分开的两行，需要单独处理一下。

第六步：视图

SELECT 'CREATE OR REPLACE VIEW '||VIEW_name||' AS ' a,text,';' b FROM USER_VIEWS iew;

还是会有一些分行的数据，变成了用"分开的两行，需要单独处理一下。

第七步：插入数据，这一步因为两个数据库都在内网，也没想着留下数据的脚本，就投机取巧了一下，建立了一个数据链接，此步骤不适用两个在不同网络的数据库。

1.建立数据库连接

create database link xxxx1 connect to xxxx2  identified by xxxx3 using 'xxx.xx.xx.xx:1521/orcl'; 

ps:xxxx1是自己定义的链接名，xxxx2是原数据库用户名，xxxx3是原数据库密码，后面是原数据库IP地址链接
2.测试数据库连接，如果链接有问题就删掉，修改一下重建。

SELECT * FROM dual@xxxx1

3.插入数据的语句

SELECT 'insert into '||table_name||' select * from '||table_name||'@xxxx1;' FROM user_tables ORDER BY table_name;

第八步：存储过程只有1个，函数也只有1个，就直接拿过去脚本了。也没有研究有没有存在数据库中。

最后补充一个踩坑，最一开始我弄的建表语句更为敷衍，直接结合了第2步和第7步，

SELECT 'create table '||table_name||' as select * from '||table_name||'@xxxx1;' FROM user_tables ORDER BY table_name;

一开始的确挺爽的，但是发现有些表明明有数据但是查询不出来，发现这种create的方式，会把字段短的varchar2改成char，这个就很蛋疼了。
说字段为char类型的时候如果数据长度不够就会自动补空格。

这数据不规范，这个指标表的数据，字段带空格。mybatis里面原本用=判断的就查不出来。