Skip to main content

Transport SQL profile from one base to another

· One min read
Franck Blettner
Franck Blettner
Creator

How to export, transport and reimport an Oracle SQL profile between databases.

1. List all profiles to retrieve the profile name​

SELECT * FROM DBA_SQL_PROFILES;

2. Create table with DBMS to export profile​

BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
    table_name  => 'TMP_SQL_PROFILES'
,   schema_name => 'SYSTEM'
);
END;

3. Export profile to the temporary table​

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​

BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
     replace            => true
,    staging_table_name => 'TMP_SQL_PROFILES'
);
END;