SQL profiles

Dropping sql profile
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE (
    name => 'SYS_SQLPROF_014ae4a7160b0000'
);
END;
/
Disable and drop
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('coe_033tdm0nr5fyv_1362405563','STATUS','ENABLED');


Creating sql profile
execute dbms_sqltune.accept_sql_profile(task_name =>'H0059060148742017.4521533565', task_owner => 'SVADAPALLI',replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

just add force_match=true at the end of executing profile.




List Sql profiles
SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM   DBA_SQL_PROFILES;


To disable parallel execution on a sql statement

my_sqltext CLOB;
profile_attributes sqlprof_attr := sqlprof_attr();
BEGIN
select sql_text into my_sqltext from dba_hist_sqltext where sql_id = '&sql_id';
profile_attributes.extend; profile_attributes(1) := 'OPT_PARAM(''parallel_execution_enabled'', ''false'')';
profile_attributes.extend; profile_attributes(2) := 'IGNORE_OPTIM_EMBEDDED_HINTS';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => my_sqltext, category => 'DEFAULT', name => '&sql_id', profile => profile_attributes, description => '&sql_id', replace => TRUE, force_match => TRUE);
END;
/




execute dbms_sqltune.accept_sql_profile(task_name =>'H005906069542053.3029564583', task_owner => 'SVADAPALLI', replace => TRUE, force_match => true);

create stage table under a schema
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.


Packing sql profiles
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0148466dd7d70000');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_014823a6e5130002');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_014a36cd9c400000');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0148239c3d530001');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01486561319b0001');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0147fec9ffeb0002');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0147ff9cdf3c0003');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0147f9ddfab30001');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0148655b74790000');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0148a341366d0000');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_014823b9f9da0003');

expdp/impdp stage table

target database unpack sql profiles

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');



select * from dba_sql_profiles;