数据库日常运维工作中我们经常会将数据库从一个数据库迁移到另一个数据库，在数据库迁移之前我们需要对源库做相应的信息查询统计。以下是本人在运维过程中总结的部分常用的数据库迁移SQL。

1.1 数据库版本查询

--查询数据库的版本号，确认迁移前后数据库版本是否兼容。
select id_code;

1.2 获取表空间定义语句

--获取用户创建的表空间定义语句，查询以后经过简单的修改可以直接在目标库直接执行。
DECLARE
 v_tablespaces VARCHAR2(1000) :=('TEST;TSB');
 v_sqls VARCHAR2(32767);
BEGIN
  dbms_output.enable(9999999);
select  listagg(T.TABLESPACE_NAME,';') WITHIN GROUP (ORDER BY TABLESPACE_NAME) into v_tablespaces  FROM  (select TABLESPACE_NAME
from SYS.DBA_TABLESPACES  where TABLESPACE_NAME not in ('SYSTEM','ROLL','TEMP','MAIN','HMAIN') )T;
  FOR x IN (SELECT regexp_substr(v_tablespaces, '[^;]+', 1, LEVEL) u
              FROM dual
            CONNECT BY LEVEL <=
                      LENGTH(v_tablespaces) - LENGTH(REPLACE(v_tablespaces, ';')) + 1
            ) LOOP
    dbms_output.put_line(CHR(10));
    dbms_output.put_line('----------------------分割线----------------------------------');
    dbms_output.put_line('--表空间' || x.u || '脚本');
    dbms_output.put_line('--创建表空间');
    SELECT to_char(dbms_metadata.get_ddl('TABLESPACE', x.u))
      INTO v_sqls
      FROM dual;
    dbms_output.put_line(v_sqls || ';');
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception!');
END;

1.3 获取用户定义语句

--获取创建用户的DDL语句以及赋权语句，查询结果直接在目标库执行。
DECLARE
  v_users VARCHAR2(1000);
  v_sqls VARCHAR2(32767);
BEGIN
  dbms_output.enable(9999999);
  select  listagg(t.username,';') WITHIN GROUP (ORDER BY username) into v_users  FROM (select username from dba_users where username not in('SYSDBA','SYS','SYSAUDITOR','SYSSSO') )t;
  
  FOR x IN (SELECT regexp_substr(v_users, '[^;]+', 1, LEVEL) u
              FROM dual
            CONNECT BY LEVEL <=
                       LENGTH(v_users) - LENGTH(REPLACE(v_users, ';')) + 1
            ) LOOP
    --dbms_output.put_line(CHR(10));
    --dbms_output.put_line('----------------------分割线----------------------------------');
    dbms_output.put_line('--创建用户' || x.u || '脚本');
    --dbms_output.put_line('--创建用户');
    SELECT to_char(dbms_metadata.get_ddl('USER', x.u))
      INTO v_sqls
      FROM dual;
    dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';' || CHR(10)) || ';',';'));
    dbms_output.put_line('--角色权限');
    SELECT to_char(dbms_metadata.get_granted_ddl('ROLE_GRANT', x.u))
      INTO v_sqls
      from dual;
    dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';' || CHR(10)) || ';',';'));
    dbms_output.put_line('--对象权限');
    SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', x.u)
      INTO v_sqls
      from dual;
    dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';' || CHR(10)) || ';',';'));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception!');
END;

1.4 获取对象信息

--获取数据库中对象的数据量，便于迁移后源库和目标库做对比。
  SELECT DECODE(OBJECT_TYPE, 
                         'SCH','模式', 
                         'TABLE','表', 
                         'VIEW','视图', 
                         'MATERIALIZED VIEW','物化视图', 
                         'PROCEDURE','存储过程', 
                         'FUNCTION','函数', 
                         'SEQUENCE','序列', 
                         'TRIGGER','触发器', 
                         'PACKAGE','包', 
                         'PACKAGE BODY','包主体', 
                         'CLASS','类', 
                         'SYNONYM','同义词', 
                         'DOMAIN','域', 
                         'TYPE','自定义类型', 
                         'INDEX','索引', 
                         'CONSTRAINT','约束', 
                         'SEQUENCE','序列', 
                         'TABLE PARTITION','分区表') AS 对象名称, 
         OWNER AS 所属用户, 
         COUNT(*)AS 数量 
         
    FROM ALL_OBJECTS 
   WHERE OWNER NOT IN ('SYS', 
                       'SYSTEM', 
                       'SYSAUDITOR', 
                       'SYSJOB', 
                       'SYSSSO', 
                       'CTISYS') 
GROUP BY OBJECT_TYPE, 
         OWNER 
ORDER BY OWNER ASC, 
         OBJECT_TYPE DESC;

1.5 表行信息统计

--查询出数据库每个用户表行信息，迁移后源库和目标库的数据对比。
begin
for rec in (select owner,table_name from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')) loop
call SP_TAB_STAT_INIT(rec.owner,rec.table_name);
end loop;
end;
/
select owner,table_name,tablespace_name,status,num_rows from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')
order by owner,num_rows desc;

1.6 建库参数查询

--查询建库参数，通过查询结果在目标库初始化实例。
set pagesize 200;
select '版本' as 名称,SVR_VERSION as 信息 from v$instance union all
select '数据库名' as 名称,CUR_DATABASE() union all
select '实例名',INSTANCE_NAME from v$instance union all
select '永久魔术值',to_char(PERMANENT_MAGIC()) union all
select '簇大小',SF_GET_EXTENT_SIZE ()||'页' union all
select '页大小',PAGE()/1024||'K' union all
select '字符集',DECODE(UNICODE,'0','GB18030','1','UTF-8','2','EUC-KR') union all
select '大小写',DECODE(SF_GET_CASE_SENSITIVE_FLAG,'0','不敏感','1','敏感') union all
select '空格填充',DECODE(BLANK_PAD_MODE,'0','否','1','是') union all
select '日志大小',MAX(RLOG_SIZE/1024/1024)||'MB' from v$rlogfile union all
select '归档状态',DECODE(ARCH_MODE,'Y','启用','N','未启用') from v$database union all
select '创建时间', cast(CREATE_TIME as varchar(50)) from v$database  union all
select '关键字保留',para_value from v$dm_ini where para_name ='EXCLUDE_RESERVED_WORDS'   union all
select '兼容性',DECODE(PARA_VALUE,'0','不兼容','1','SQL92','2','兼容ORACLE','3','SQL Server','4','MySQL') as "兼容的数据库"  from v$dm_ini where para_name ='COMPATIBLE_MODE'
union all  select '是否以字符为单位',PARA_VALUE   from v$dm_ini where para_name ='LENGTH_IN_CHAR' ;