=====Mit Oracle SQL Profilen arbeiten und SQL Profile zwischen Datenbanken austauschen====
==== SQL Profile in der DB auswerten ====
Views:
* dba_sql_profiles
* sys.sqlobj$data
* sys.sqlobj$
Scriptes:
* Alle Profile => http://orapowershell.codeplex.com/SourceControl/latest#sql/sql_profile.sql
* Details für ein Profile => http://orapowershell.codeplex.com/SourceControl/latest#sql/sql_profile_details.sql
====Ein SQL Profile umbennenen ====
Wird mit den Tuning Advisor ein Profile erzeugt, wird diesen Profile ein generiert Name vergeben.
Umbennen mit:
begin
dbms_sqltune.alter_sql_profile (
name => 'SYS_SQLPROF_0347a6bed3cc0008'
, attribute_name => 'NAME'
, value => 'GPI_BUG_1078'
);
end;
/
--- automatisch erzeugen mit:
column command format a90 heading "Command" WORD_WRAPPED
select 'begin '
||chr(10)||'dbms_sqltune.alter_sql_profile('
||chr(10)||' name=>'''||pf.name||''''
||chr(10)||' ,attribute_name=>''NAME'''
||chr(10)||' ,value=>''BUG_7867_GUP_'||vs.sql_id||''');'
||chr(10)||'end;'
||chr(10)||'/'
as command
from gv$sql vs
, dba_sql_profiles pf
where pf.name=vs.sql_profile
and sql_profile is not null
and pf.sql_text like 'DELETE%'
/
==== SQL Profile zwischen Datenbanken austauschen ====
Ablauf:
* Staging Tabelle erzeugen
* Profile in die Tabelle kopieren
* Exportieren aus der Quelle
* Importieren in das Ziel
* Aus der Staging Tabelle Profile "installieren"
===Staging Tabelle erzeugen===
begin
dbms_sqltune.create_stgtab_sqlprof (
table_name => 'SQL_PROF_TRANSFERS'
, schema_name => 'SYSTEM');
end;
/
===Profile in die Tabelle kopieren===
Name vom Profil ermitteln und Profile in die Tabelle kopieren
select name
, category
, substr(sql_text,1,100) as sql_text
, to_char(last_modified,'dd.mm.RR hh24:mi') as last_mod
, description
, type
, status
, force_matching
from dba_sql_profiles
order by last_modified,name
/
begin
dbms_sqltune.pack_stgtab_sqlprof (
profile_name => ''
, profile_category => 'default'
, staging_table_name => 'SQL_PROF_TRANSFERS'
, staging_schema_owner => 'SYSTEM'
);
end;
/
select count(*) from SQL_PROF_TRANSFERS;
COUNT(*)
----------
1
--- create Script:
column command format a90 heading "Command" WORD_WRAPPED
select 'begin '
||chr(10)||'dbms_sqltune.pack_stgtab_sqlprof('
||chr(10)||' profile_name => '''||pf.name||''''
||chr(10)||' , profile_category => ''DEFAULT'''
||chr(10)||' , staging_table_name => ''SQL_PROF_TRANSFERS'''
||chr(10)||' , staging_schema_owner => ''SYSTEM'');'
||chr(10)||'end;'
||chr(10)||'/'
as command
from gv$sql vs
, dba_sql_profiles pf
where pf.name=vs.sql_profile
and sql_profile is not null
and pf.name like 'BUG_7867_GUP_%'
/
=== Exportieren aus der Quelle ===
Export Directory suchen oder anlegen
vi export_SQL_PROFILE_GPIDB_DB.dpctl
DIRECTORY=IMPDP_GPIDB_REPO
LOGFILE=expdp_08-08-2014_16_40_GPIDB1.log
DUMPFILE=expdp_08-08-2014_16_40_GPIDB1.dmp
REUSE_DUMPFILES=Y
COMPRESSION=ALL
TABLES=SYSTEM.SQL_PROF_TRANSFERS
JOB_NAME=EXPDP_SQL_PROFILE
expdp "'/ as sysdba'"„ parfile=export_SQL_PROFILE_GPIDB_DB.dpctl
Auf das Ziel übertragen
=== Importieren aus der Quelle ===
vi import_SQL_PROFILE_GPIDB_DB.dpctl
DIRECTORY=IMPDP_GPIDB_REPO
LOGFILE=impdp_08-08-2014_16_51_DEVGPIDB1.log
DUMPFILE=expdp_08-08-2014_16_40_PRODGPIDB1.dmp
TABLES=SYSTEM.SQL_PROF_TRANSFERS
JOB_NAME=IMPDP_SQL_PROFILE
impdp "'/ as sysdba'"„ parfile=import_SQL_PROFILE_GPIDB_DB.dpctl
=== einlesen im Ziel===
check ob die Daten auch da sind:
select count(*) from SQL_PROF_TRANSFERS;
select OBJ_NAME from SQL_PROF_TRANSFERS;
begin
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => ''
, profile_category => 'DEFAULT'
, REPLACE => TRUE
, staging_table_name => 'SQL_PROF_TRANSFERS'
, staging_schema_owner => 'SYSTEM');
end;
/
--- create Script:
column command format a90 heading "Command" WORD_WRAPPED
select 'begin '
||chr(10)||'dbms_sqltune.UNPACK_STGTAB_SQLPROF('
||chr(10)||' profile_name => '''||OBJ_NAME||''''
||chr(10)||' , profile_category => ''DEFAULT'''
||chr(10)||' , REPLACE => TRUE'
||chr(10)||' , staging_table_name => ''SQL_PROF_TRANSFERS'''
||chr(10)||' , staging_schema_owner => ''SYSTEM'');'
||chr(10)||'end;'
||chr(10)||'/'
as command
from SQL_PROF_TRANSFERS
where OBJ_NAME like 'BUG_7867_GUP_%'
/
==== Ein SQL Profile löschen ====
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME');
END;
/
==== SQL Profile Verwendung über die Category steuern====
Mit dem init.ora Parameter **"SQLTUNE_CATEGORY = category_name"** kann gesteuert werden, welche Profile von der aktuellen Session bzw. der gesamten DB gesucht werden sollen.
-- Change the category of the profile so it will be used only by sessions with category set to TEST.
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name => 'SYS_SQLPROF_0347a6bed3cc0008'
, attribute_name => 'CATEGORY'
, value => 'TEST')
==== SQL Profile manuell anlegen====
sieh auch http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/
==== Quellen ====
Oracle:
* http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqltun.htm#ARPLS68380
* http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#ARPLS68432
Netz:
* http://intermediatesql.com/oracle/how-to-add-a-hint-to-oracle-query-without-touching-its-text/
* http://www.allguru.net/database/create-oracle-sql-profile-tuning/
* http://www.allguru.net/database/migrate-oracle-sql-profile/
* http://oraxperts.com/node/108
* http://www.ora-solutions.net/web/2008/10/19/renaming-sql-profiles-generated-with-oem/
* http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/