Database Reference
In-Depth Information
The following example, an excerpt of the
profile_cloning.sql
script, shows how to clone a SQL profile inside a
single database. First, the
mystgtab
staging table is created in the current schema:
dbms_sqltune.create_stgtab_sqlprof(table_name => 'MYSTGTAB',
schema_name => user,
tablespace_name => 'USERS');
Then, a SQL profile named
opt_estimate
is copied from the data dictionary to the staging table:
dbms_sqltune.pack_stgtab_sqlprof(profile_name => 'opt_estimate',
profile_category => 'TEST',
staging_table_name => 'MYSTGTAB',
staging_schema_owner => user);
The name of the SQL profile must be changed before copying it back into the data dictionary. At the same time,
its category is changed as well:
dbms_sqltune.remap_stgtab_sqlprof(old_profile_name => 'opt_estimate',
new_profile_name => 'opt_estimate_clone',
new_profile_category => 'TEST_CLONE',
staging_table_name => 'MYSTGTAB',
staging_schema_owner => user);
Finally, the SQL profile is copied from the staging table into the data dictionary. Because the parameter replace is
set to
TRUE
, a SQL profile with the same name would be overwritten:
dbms_sqltune.unpack_stgtab_sqlprof(profile_name => 'opt_estimate_clone',
profile_category => 'TEST_CLONE',
replace => TRUE,
staging_table_name => 'MYSTGTAB',
staging_schema_owner => user);
Dropping SQL Profiles
You can use the
drop_sql_profile
procedure in the
dbms_sqltune
package to drop a SQL profile from the data
dictionary. The
name
parameter specifies the name of the SQL profile. The
ignore
parameter specifies whether an
error is raised in case the SQL profile doesn't exist. It defaults to
FALSE
:
dbms_sqltune.drop_sql_profile(name => 'opt_estimate',
ignore => TRUE);
Privileges
To create, alter, and drop a SQL profile, the
create any sql profile
,
drop any sql profile
, and
alter any sql
profile
system privileges are required, respectively. However, as of version 11.1, these three system privileges are
deprecated in favor of the
administer sql management
system privilege object. No object privileges for SQL profiles
exist. To use the SQL Tuning Advisor, the
advisor
system privilege is required.
