Skip to content

Transport SQL profile from one base to another

1. List all profiles to retrieve the profile name

1
SELECT * FROM DBA_SQL_PROFILES;

2. Create table with DBMS to export profile

1
2
3
4
5
6
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
    table_name  => 'TMP_SQL_PROFILES'
,   schema_name => 'SYSTEM'
);
END;

3. Export profile to the temporary table

1
2
3
4
5
6
7
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
    profile_name         => 'SYS_SQLPROF_02701ab7b57c0000'
,   staging_table_name   => 'TMP_SQL_PROFILES'
,   staging_schema_owner => 'SYSTEM'
);
END;
Export/Import this table between both databases

4. Import profile from temporay table in the target database

1
2
3
4
5
6
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
     replace            => true
,    staging_table_name => 'TMP_SQL_PROFILES'
);
END;

Comments