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;