STS（SQL Tuning Set）匯入匯出過程及錯誤處理

lhrbest發表於2017-11-28
SQL

STS匯入或匯出錯誤處理



環境模擬：

create directory d1 as '/home/oracle/scripts';

grant read,write on  directory d1 to public;

create user apps identified by Apps1234;

grant dba ,ADMINISTER ANY SQL TUNING SET to apps;

conn apps/Apps1234

create table apps.ta_lhr as select * from dba_objects;

create table apps.tb_lhr as select * from dba_objects;

select * from apps.ta_lhr where object_id=100;

select * from apps.tb_lhr where object_name='TA_LHR';

使用EM建立system使用者的PS_STS的調優集，然後匯出使用system進行匯出，否則報錯：

Tue Nov 28 09:43:53 2017

Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_j001_10289.trc:

ORA-12012: error on auto execute of job 78165

ORA-19381: cannot create staging table in SYS schema

ORA-06512: at "SYS.DBMS_SQLTUNE", line 3170

ORA-06512: at "SYS.DBMS_SQLTUNE", line 6397

ORA-06512: at line 1

MOS解釋：

Error ORA-19381 When DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF IS EXECUTED as SYS (文件 ID 2131916.1)


This is expected behavior.  STS staging tables cannot be created in SYS schema by design.

Any other user (with the right privileges) should be able to create the staging table and pack STS in it.



匯入如果報錯：

Errors in file

/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_j000_20653.trc:

ORA-12012: error on auto execute of job 78166

ORA-19377: no "SQL Tuning Set" with name like "%" exists for owner like "SYS"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.DBMS_SQLTUNE", line 6868

ORA-06512: at line 1

 

Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_j000_9955.trc:

ORA-12012: error on auto execute of job 78689

ORA-00942: table or view does not exist

ORA-06512: at line 1

解決： 使用SYSTEM進行匯入，或執行以下SQL後再匯入：

impdp system/oracle directory=d1 dumpfile=appsstg.dmp

exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name =>'STS_PS',old_sqlset_owner => 'SYSTEM', new_sqlset_name => 'STS_PS',new_sqlset_owner => 'SYS', staging_table_name => 'STS_PS_STGTAB',staging_schema_owner => 'SYSTEM');

----刪除STS,staging table為STS_PS_STGTAB

SELECT * FROM Dba_Objects d WHERE d.object_name='STS_PS_STGTAB';

SELECT * FROM Dba_Sqlset;

SELECT *

FROM dba_sqlset_statements  

WHERE sqlset_name = 'STS_PS';

DELETE FROM WRI$_SQLSET_DEFINITIONS;

DELETE FROM WRI$_SQLSET_STATEMENTS;

DELETE FROM WRI$_SQLSET_MASK;

DELETE FROM WRI$_SQLSET_STATISTICS;

COMMIT;

 SELECT * FROM WRI$_SQLSET_STATEMENTS;

 SELECT * FROM WRI$_SQLSET_MASK;

 SELECT * FROM WRI$_SQLSET_STATISTICS;

 SELECT * FROM WRI$_SQLSET_STATEMENTS;

How to Move a SQL Tuning Set from One Database to Another (文件 ID 751068.1)

In this Document

Goal
Solution
  Actions to Perform the Transfer:
  Preliminary Setup:
  Actions to Perform the Transfer: Detail
  Create/load STS test_set owned by SYS
  Create stgtab sqlset_tab table in TEST schema:
  Pack test_set into the stgtab
  Transfer the table containing the SQL SET (SQLSET_TAB) table to the TEST system
  Export/import into test system, connect as scott
  Attempt to unpack an STS named 'testtarget_test_set'
  MAPPING
  UNPACK THE SQLSET_TAB TABLE IN THE TEST SYSTEM
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 26-MAR-2013*** 

GOAL

This document explains how to transfer the SQL Tuning Set (STS) from one database to another and demonstrates some issues regarding the transfer of STS and their resolution.

SOLUTION

Actions to Perform the Transfer:

  1. Create/load STS test_set owned by SYS
  2. Create stgtab SQLSET_TAB
  3. Pack test_set into the stgtab
  4. Export/import into test system, conn as scott
  5. Attempt to unpack an STS named 'testtarget_test_set'

Preliminary Setup:

  • Create User,  Create and Populate the table and collect statistics

    SQL> connect test/test 

    create table hold (id number, col_val varchar2(10)); 
    insert into hold values ('1', 'more'); 

    declare v_id number := 1; 
    v_col_val varchar2(10); 
    begin 
    while v_id < 100 loop 
    v_id:=v_id + 1; 
    v_col_val:=v_id||'_more'; 
    insert into hold values(v_id, v_col_val); 
    end loop; 
    end; 
    /

    commit; 

    exec dbms_stats.gather_table_stats(user, 'hold', cascade => true);
  • Check the data
    SQL> select count(*) from hold where id <=100; 

    COUNT(*) 
    ---------- 
    100 

    SQL> select col_val from hold where id = 100; 

    COL_VAL 
    ---------- 
    100_more 

    SQL> select max(id) from hold; 

    MAX(ID) 
    ---------- 
    200


Actions to Perform the Transfer: Detail

  1. Create/load STS test_set owned by SYS

    sqlplus / as sysdba 

    exec dbms_sqltune.create_sqlset(sqlset_name => 'test_set', description =>'11g workload'); 

    declare 
    mycur dbms_sqltune.sqlset_cursor; 
    begin 
    open mycur for 
    select value (P) 
    from table(dbms_sqltune.select_cursor_cache('parsing_schema_name <> ''SYS'' and elapsed_time > 250', null, null, null, null,1, null, 'ALL')
               ) P;

    dbms_sqltune.load_sqlset(sqlset_name => 'test_set', populate_cursor => mycur); 
    end; 
    /

    Display the SQL Stored in the STS:

    select sql_text from dba_sqlset_statements where sqlset_name='test_set';

  2. Create stgtab sqlset_tab table in TEST schema:

    BEGIN 
    DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB', 
    schema_name => 'TEST', 
    tablespace_name => 'USERS'); 
    END; 
    /

  3. Pack test_set into the stgtab

    From SYS
    BEGIN 
    DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'test_set', 
    sqlset_owner => 'SYS', 
    staging_table_name => 'SQLSET_TAB', 
    staging_schema_owner => 'TEST'); 
    END; 
    /

  4. Transfer the table containing the SQL SET (SQLSET_TAB) table to the TEST system

    The staging table SQLSET_TAB cand be exported and imported to the TEST schema of the test system using datapump. See information on export datapump for details of how to do this : 

    Document 556636.1 Oracle Server - Export Data Pump and Import DataPump FAQ

  5. Export/import into test system, connect as scott

    conn scott/tiger EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'testtarget_test_set');


    NOTE: Scott should have DBA privilege because unpacking is done under SCOTT where as the staging table is imported to TEST schema. Without the DBA privilege scott will not be able to access the test schema.

  6. Attempt to unpack an STS named 'testtarget_test_set'


    BEGIN 
    DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'testtarget_test_set', 
    sqlset_owner => 'SCOTT', 
    replace => TRUE, 
    staging_table_name => 'SQLSET_TAB', 
    staging_schema_owner => 'TEST'); 
    END; 
    /

    This will end up with the following error:- 

    BEGIN 
    * 
    ERROR at line 1: 
    ORA-19377: no "SQL Tuning Set" with name like "testtarget_test_set" exists for 
    owner like "TESTTARGET" 
    ORA-06512: at "SYS.DBMS_SQLTUNE", line 5422 
    ORA-06512: at line 2
    The above unpack procedure fails because there is no STS in the staging table named 'testtarget_test_set', owned by SCOTT (or by any other user). Unpack expects you to pass it the name of an STS as it is in the staging table. 

  7. MAPPING

    If you are trying to rename the STS and change its owner before it is unpacked (reconstituted on the test system), use the remap_stgtab_sqlset API as follows, for example:

    exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name => 'test_set', 
    old_sqlset_owner => 'SYS', new_sqlset_name => 'testtarget_test_set', 
    new_sqlset_owner => 'SCOTT', staging_table_name => 'SQLSET_TAB', 
    staging_schema_owner => '');

    Here the staging table is imported to TEST schema of TEST system:

    exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name =>'test_set',old_sqlset_owner => 'SYS', new_sqlset_name => 'testtarget_test_set',new_sqlset_owner => 'SCOTT', staging_table_name => 'SQLSET_TAB',staging_schema_owner => 'TEST');

  8. UNPACK THE SQLSET_TAB TABLE IN THE TEST SYSTEM

    BEGIN 
    DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'testtarget_test_set', 
    sqlset_owner => 'SCOTT', 
    replace => TRUE, 
    staging_table_name => 'SQLSET_TAB', 
    staging_schema_owner => 'TEST'); 
    END; 
    /

    Now the SQL Tuning Set is transferred successfully to another database without any issues.

  9. Then load the plans:
    set serveroutput on
    declare
    my_int pls_integer;
    begin
    my_int := dbms_spm.load_plans_from_sqlset (sqlset_name => 'testtarget_test_set',
    sqlset_owner => 'SCOTT',
    fixed => 'YES',
    enabled => 'YES');
    DBMS_OUTPUT.PUT_line(my_int);
    end;
    /
     
Error ORA-19381 When DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF IS EXECUTED as SYS (文件 ID 2131916.1)

In this Document

Symptoms
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS

  • When unpacking the sqlprofiles using DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF, following error is seen:
    BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST'); END;
    Error at line 1
    ORA-19381: cannot create staging table in SYS schema
    ORA-06512: at "SYS.DBMS_SMB", line 1051
    ORA-06512: at "SYS.DBMS_SQLTUNE", line 6357
    ORA-06512: at line 1

CAUSE

 This is expected behavior.  STS staging tables cannot be created in SYS schema by design.

SOLUTION

Any other user (with the right privileges) should be able to create the staging table and pack STS in it.




About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

   小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店

.............................................................................................................................................

STS（SQL Tuning Set）匯入匯出過程及錯誤處理
 DBA筆試面試講解群
 《DBA寶典》讀者群 歡迎與我聯絡



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2147891/，如需轉載，請註明出處，否則將追究法律責任。

相關文章