Transporting a SQL Profile

To transport a SQL profile:

1. Connect SQL*Plus to the database with the appropriate privileges, and then use the CREATE_STGTAB_SQLPROF procedure to create a staging table to hold the SQL profiles.
   The following example creates my_staging_table in the dba1 schema:

   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( 
       table_name  => 'my_staging_table'
   ,   schema_name => 'dba1' 
   );
   END;
   /
   

2. Use the PACK_STGTAB_SQLPROF procedure to export SQL profiles into the staging table.
   The following example populates dba1.my_staging_table with the SQL profile my_profile:

   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (  
       profile_name         => 'my_profile'
   ,   staging_table_name   => 'my_staging_table'
   ,   staging_schema_owner => 'dba1' 
   );
   END;
   / 
   

3. Move the staging table to the database where you plan to unpack the SQL profiles.
   Move the table using your utility of choice. For example, use Oracle Data Pump or a database link.
4. On the database where you plan to import the SQL profiles, use UNPACK_STGTAB_SQLPROF to unpack SQL profiles from the staging table.
   The following example shows how to unpack SQL profiles in the staging table:

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