sql profile is collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a sql statement
step1 # check for sql profiles on database
select distinct
p.name sql_profile_name,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile;
step 2# create staging table in schema to store SQL profiles
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');
PL/SQL procedure successfully completed.
step 3# load the sql profiles in staging table
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');
PL/SQL procedure successfully completed.
step 4#
Export the stage table using expdp
import the table using impdp
step 5# Unpack the staging table
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');
—Nikhil Tatineni—
—Oracle In Memory—
