将一个数据库中的sql profile迁移到另一个数据库中

???? 2021-4-14 15132

scenario: sqlid:123456789 在A 数据库下有一个profile,在B数据库下没有。

target: 将该profile 从db A复制到db B

1.在db A 找到对应的profile name: select distinct p.name sql_profile_name, s.sql_id from dba_sql_profile p, DBA_HIST_SQLTEST s where p.name=s.sql_profile and s.sql_id='123456789';

SQL_PROFILE_NAME SQL_ID


SYS_SQLPROF_017 123456789

2.在db A 创建一个schema 并赋予相应权限: create user harris identified by harris1234#; grant connect, resource, dba to harris;

3.在db A 创建一个table:STAGE来储存 sql profile: exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'HARRIS');

4.将sql profile放入table 中: exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'STAGE',STAGING_SCHEMA_OWNER =>'HARRIS',profile_name=>'SYS_SQLPROF_017');

5.export 整个schema到一个dmp file cd /home/oracle/harris exp account/password@hostnameA/db OWNER=HARRIS file=harris.dm log=harris.log

6.在同一路径下cp dmp file到 tmp目录的Harris文件下,为了后续的导出: (owner:oracle) cp harris.dmp /tmp/harris/harris.dmp

7.在cmd下 用pscp从db A 中导出harris.dmp: pscp -P 22 account@hostnameA:/tmp/harris/harris.dmp .

8.在cmd下用pscp将Harris.dmp0导入到db B中: pscp -l account -P 22 harris.dmp hostnameB:/tmp/harris

9.在db B中将tmp/harris下的harris.dmp cp到/home/oracle/harris: cp /tmp/harris/harris.dmp /home/oracle/harris/harris.dmp

10.在db B 创建一个schema 并赋予相应权限: create user harris identified by harris1234#; grant connect, resource, dba to harris;

11.在/home/oracle/harris下imp dmp file: cd /home/oracle/harris imp account/password@dbB file=harris.dmp FROMUSER=HARRIS TOUSER=HARRIS

12.unpack profile: EXEC DBMS_SQLTUNE.UNPACK_STGTBA_SQLPROF(replace => TRUE, staging_table_name => 'STAGE', staginf_schema_owner => 'HARRIS');

13.在dbA 和dbB 中drop schema HARRIS: drop user HARRIS cascade;

最新回复 (0)
返回
发新帖