STS（SQL Tuning Set）導入導出過程及錯誤處理 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

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

解決：使用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;

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

In this Document

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#FIX">Solution</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#aref_section21">Actions to Perform the Transfer:</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#aref_section22">Preliminary Setup:</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#aref_section23">Actions to Perform the Transfer: Detail</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#aref_section26">Pack test_set into the stgtab</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#aref_section210">MAPPING</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282017287833625&id=751068.1&_afrWindowMode=0&_adf.ctrl-state=1cdjyghok0_4#REF">References</a>

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***

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.

<a></a>

Create/load STS test_set owned by SYS

Create stgtab SQLSET_TAB

Pack test_set into the stgtab

Export/import into test system, conn as scott

Attempt to unpack an STS named 'testtarget_test_set'

Create User, Create and Populate the table and collect statistics

<>>

>>>>>>

<a href="http://blog.itpub.net/26736162/abstract/1/" target="_blank"></a> <a href="http://www.cnblogs.com/lhrbest" target="_blank"></a> <a href="http://blog.itpub.net/26736162/viewspace-1624453/" target="_blank"></a> <a href="http://blog.itpub.net/26736162/viewspace-2134706/" target="_blank"></a> <a href="http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826" target="_blank"></a> <a href="https://weidian.com/s/793741433?wfr=c&ifr=shopdetail%20" target="_blank">&</a> <a href="http://blog.itpub.net/26736162/viewspace-2142121/" target="_blank"></a> <a href="http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=307287619&idx=1&sn=9149fab6ae1aecfd1288316c5edc3805&scene=19#wechat_redirect" target="_blank"></a>

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

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

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

STS（SQL Tuning Set）導入導出過程及錯誤處理 STS導入或導出錯誤處理
<a target="_blank" href="http://wpa.qq.com/msgrd?v=3&uin=646634621&site=qq&menu=yes"></a>

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

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

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

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

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

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List