ORACLE迁移:索引、存储过程批量导出

在迁移时,使用数据泵导出数据时为了避免用时过长,和导入报错,所以使用exclude=STATISTICS,INDEX,PROCEDURE 过滤掉索引和统计信息,存储过程,然后手工导出创建语句重新创建

1. 手工提取序列创建语句

select 'create sequence ' || sequence_name || ' minvalue ' || min_value ||
       ' maxvalue ' || max_value || ' start with ' || last_number ||
       ' increment by ' || increment_by || (case
         when cache_size = 0 then
          ' nocache'
         else
          ' cache ' || cache_size
       end) || ';'
  from dba_sequences;

但实际操作中，很多时候避免新库取值是不重复使用老库序列，需要增大序列的值，所以修改上面语句

select 'create sequence '||sequence_name||
　　' minvalue '||min_value||
　　' maxvalue '||max_value||
　　' start with '|| to_number(last_number +1000) ||
　　' increment by '||increment_by||
　　(case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';' from dba_sequences; 

当然增加的数量要更加实际的情况而定（MAX_VALUE的值），我这里的是增加1000.

2. 批量导出创建job

语句如下：

SELECT  'SYS.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 => '''||INTERVAL||''');' 
FROM dba_jobs; 

例子：

1. 旧库生成创建语句
   SQL> SELECT ‘SYS.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 => ‘’’||INTERVAL||’’’);’ FROM dba_jobs;

‘SYS.DBMS_JOB.SUBMIT(JOB=>:JOB,WHAT=>’’’||WHAT||’’’,NEXT_DATE=>TO_DATE(’’’||TO_CHAR(NEXT_DATE,‘DD-MM-YYYYHH24:MI:SS’)||’’’,’‘DD-MM-YYYYHH24:MI:SS’’),INTERVAL=>’’’||INTERVAL||’’’);’

SYS.dbms_job.submit(job => :job,what => ‘xyz_pkg.xyz_p1;’,next_date => to_date(‘21-11-2015 15:26:22’, ‘dd-mm-yyyy hh24:mi:ss’),interval => ‘sysdate+1/1440’);
SYS.dbms_job.submit(job => :job,what => ‘xyz_pkg.xyz_p2;’,next_date => to_date(‘21-11-2015 15:26:17’, ‘dd-mm-yyyy hh24:mi:ss’),interval => ‘sysdate+1/1440’);

2. 新库创建JOB

将上面产生的语句加入至：

VAR job NUMBER
begin
SYS.dbms_job.submit(job => :job,what => ‘xyz_pkg.xyz_p1;’,next_date => to_date(‘21-11-2015 15:26:22’, ‘dd-mm-yyyy hh24:mi:ss’),interval => ‘sysdate+1/1440’);
SYS.dbms_job.submit(job => :job,what => ‘xyz_pkg.xyz_p2;’,next_date => to_date(‘21-11-2015 15:26:17’, ‘dd-mm-yyyy hh24:mi:ss’),interval => ‘sysdate+1/1440’);
COMMIT;
END;
/

使用SYS执行就可以了。

3. 然后修改job的属主

脚本如下：

set serveroutput on
declare
v_job_id number;
v_user varchar2(50);
v_nlsenv VARCHAR2(4000);
cursor c_tab is select job,schema_user,nls_env from dba_jobs where log_user=‘SYS’;
begin
open c_tab;
loop
fetch c_tab into v_job_id,v_user,v_nlsenv;
exit when c_tab%NOTFOUND;
sys.dbms_ijob.CHANGE_ENV(v_job_id,‘TONY’,‘TONY’,‘TONY’,v_nlsenv); ----修改目标用户
commit;
end loop;
close c_tab;
end;
/

修改完属主，JOB就迁移完成了。

3. 批量导出INDEXES语句

SELECT DBMS_METADATA.GET_DDL(u.OBJECT_TYPE, u.object_name,u.OWNER)
FROM DBA_OBJECTS u
where u.OBJECT_TYPE=‘INDEX’ AND u.owner in(‘TONY’);

导出语句时需要格式化，方便使用。

set linesize 180
set pages 999
set long 90000
spool /home/oracle/craete_index02.sh

spool off
–设置按单词换行
col a for a200 wrapped word

–去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘STORAGE’,FALSE);
–输出信息采用缩排或换行格式化
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘PRETTY’,true);
–确保每个语句都带分号
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘SQLTERMINATOR’,true);

4. 导出表空间

方法 一

set linesize 180
set pages 999
set long 90000
col a for a200 wrapped word
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
spool /home/oracle/tablespace_name02.sh

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

spool off 

方法二

set linesize 180
set pages 999
set long 90000

declare
cursor c2 is select tablespace_name from dba_tablespaces   where contents='PERMANENT' ;
v_tablespac c2%rowtype;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
open c2;
loop
fetch c2 into v_tablespac ;
exit when c2%notfound;
DBMS_OUTPUT.put_line(to_char(dbms_metadata.get_ddl('TABLESPACE',v_tablespac.tablespace_name))||';');
end loop;
close c2;
end;
/

5. 创建用户脚本

set serveroutput on
set line 100
set pagesize 0
declare
cursor c1 is select username from dba_users a where a.account_status=‘OPEN’ ;
v_user c1%rowtype;
begin
open c1;
loop
fetch c1 into v_user ;
exit when c1%notfound;
DBMS_OUTPUT.put_line(to_char(dbms_metadata.get_ddl(‘USER’,v_user.username))||’;’);
end loop;
close c1;
end;
/

6. 授权系统权限脚本

select ‘grant ‘||t.privilege ||’ to ‘||t.grantee || decode(t.admin_option,‘YES’,’ with admin;’,’;’)
from dba_sys_privs t
where t.grantee not in
(‘SPATIAL_WFS_ADMIN_USR’,
‘DIP’
);

7. --授角色脚本

select ‘grant ‘||granted_role||’ to ‘||grantee||’;’ from dba_role_privs
where grantee not in
(‘SPATIAL_WFS_ADMIN_USR’,
‘DIP’,
‘MDDATA’
);

8. – 表权限

select ‘grant ‘||t.privilege||’ on ‘||t.grantor||’.’||t.table_name||’ to ‘||t.grantee||decode(t.grantable,‘YES’,’ with grant;’,’;’)
from dba_tab_privs t
where t.grantor not in
(‘SYS’,‘SYSTEM’,‘DBSNMP’,‘PERFSTAT’,‘OUTLN’,‘WMSYS’,‘CONNECT’,‘DBA’,
‘EXP_FULL_DATABASE’,‘IMP_FULL_DATABASE’,‘OEM_MONITOR’,‘RESOURCE’,‘JAVADEBUGPRIV’)
order by t.grantee
/