Views:
Scriptes:
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%' /
Ablauf:
BEGIN dbms_sqltune.create_stgtab_sqlprof ( TABLE_NAME => 'SQL_PROF_TRANSFERS' , schema_name => 'SYSTEM'); END; /
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 => '<sql_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_%' /
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
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
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 => '<sql_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_%' /
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME'); END; /
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')
Oracle:
Netz: