STS（SQL Tuning Set）导入导出过程及错误处理

最新推荐文章于 2022-02-27 23:10:01 发布
最新推荐文章于 2022-02-27 23:10:01 发布
阅读量623 收藏
点赞数
文章标签： 数据库 面试

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宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

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

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

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

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

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

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

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

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面宝典》读者群       小麦苗的微店

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

ico_mailme_02.png
 DBA笔试面试讲解群
 《DBA宝典》读者群 欢迎与我联系



来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2147891/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2147891/

coco3600
关注
Dav_笔记12：Automatic SQL Tuning 之 4 管理SQL STS
Dav_2099的博客
08-01 1111
SQL调优集（STS）是一个数据库对象，它包含一个或多个SQL语句及其执行统计信息和执行上下文，并且可以包括用户优先级排名。您可以将SQL语句加载到来自不同SQL源的SQL调优集中，例如AWR，共享SQL区域或用户提供的自定义SQL。STS包括：■一组SQL语句■关联的执行上下文，例如用户架构，应用程序模块名称和操作，绑定值列表以及游标编​​译环境■关联的基本执行统计信息，例如已用时间，CPU时间，缓冲区获取，磁盘读取，已处理的行，游标提取，执行次数，完成执行次数，优化程序成本和命令类型。
Dav_笔记12：Automatic SQL Tuning 之 3 SQL-Tuning
最新发布
Dav_2099的博客
08-01 2457
您可以手动调用SQL Tuning Advisor以按需调整一个或多个SQL语句。要调整多个语句，必须创建SQL调优集（STS）。SQL调优集是一个数据库对象，它存储SQL语句及其执行上下文。您可以使用命令行API或企业管理器创建SQL调优集。请参见“Dav_笔记12：Automatic SQL Tuning 之 4 管理SQL调优集”。
Oracle SQL tuning 数据库优化步骤分享(图文教程)
09-10
SQL Turning 是Quest公司出品的Quest Central软件中的一个工具。Quest Central是一款集成化、图形化、跨平台的数据库管理解决方案，可以同时管理 Oracle、DB2 和 SQL server 数据库
sql tuning set
cuili9760的博客
01-22 287
sql tuning set是性能调整中重要的一个环节。以下我们先看该功能的使用，再来说明其用途。 （1）创建： [oracle@localhost ~]$ sqlplus /nolog SQL*Plus: R...
sql tuning set/sql tuning advisor(待完善)
cuili9760的博客
01-22 193
创建sql tuning set sql tuning set是性能调整中重要的一个环节。以下我们先看该功能的使用，再来说明其用途。 （1）创建： [oracle@localhost ~]$ sqlplus /...
SQL TUNING SET FROM AWR
ciyixi4265的博客
03-20 158
我们现在测试 从AWR导入 DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY ( begin_snap IN NUMBER, end_snap ...
SQL Tuning Set操作
u010719917的专栏
08-05 1183
a SQL Tuning Set About SQL Tuning Sets 1.A set of SQL statements 2.Associated execution context   such as user schema,   application module name and action,   list of bind values,   and
SQL Tuning Advisor使用总结
03-14
本文将详细介绍如何利用SQL Tuning Advisor来提高SQL语句的执行效率，包括从Automatic Workload Repository (AWR)、游标缓存以及SQL调优集(SQL Tuning Set, STS)中获取SQL语句并建立、执行及获取调优结果的方法。...
在Oracle实例间移动SQL调整工具集
03-03
SQL调整工具集（SQL Tuning Set，STS）是Oracle 10g的SQL Tuning Advisor特性的一个组成部分。每个调整工具集都包含一个或几个SQL语句，以及正确解释它们所需的上下文信息。SQL Tuning Advisor用一个调整工具集作为...
sts中Lombok的导入和使用方法（傻瓜图解）
abberr的博客
12-06 2382
1 Lombok的导入方法 1.sts中导入lombok.jar包 2.添加到BuildPath路径上 2 Lombok插件介绍 Lombok项目是一个Java库，他会自动插入编辑器个构建工具中，Lombok提供了一组有用的注释，用来消除Java类中大量样板代码。进屋个字符@Data就可以替换数百行代码从而产生干净，简介且易于维护的Java类。 3 Lombok常用方法 3.1 @ Getter/@Setter 自动生成get/set方法 @Setter @Getter pu
SQL Tuning set -- 从游标获取SQL
文档搬运工
03-20 584
原文地址：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/managing-sql-tuning-sets.html#GUID-91D1B886-A6D7-40B8-93D5-112B8C6E6AFE 其实，这个话题是一道OCM考试题，只是考试的时候，考场环境会提供已经做好的sql set ，只需要数据泵...
利用SPA（SQL Performance Analyzer）对比两个SQL Tuning Set
cpyz30116的博客
03-03 186
简单介绍： 1.A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their exe...
SQL Tuning Task功能笔记
congjiao5415的博客
09-11 356
http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php 自动SQL调优建议：统计信息，SQL Profile，访问路径，SQL结构分析 1 创建一个...
SQL Tuning --通过创建Index性能提升40倍
知跬步，至千里
04-01 2446
起因 上周四下午的时候收到Oracle DB的High Disk I/O Loading Session警示邮件，查找发现是如下这段SQL导致High Disk I/O： SELECT * FROM CLW3014 WHERE AUDIT_DATE = (SELECT MAX(AUDIT_DATE)AUDIT_DATE FROM CLW3014 WHERE BOOK_NO = :B3 AN...
Oracle SQL tuning 步骤
anjichan4261的博客
04-02 410
SQL是的全称是Structured Query Language(结构化查询语言)。SQL是一个在80年代中期被使用的工业标准数据库查询语言。不要把SQL语言与商业化产品如Microsoft SQL server或开源产品MySQL相混淆。所有的使用SQL缩略词的这些都是SQL标准的一部分。一、SQL tuning之前的调整 下面这个粗略的方法能够节省数千小时乏味的SQL...
STS导入Gradle项目报错
weixin_47072772的博客
02-27 1952
这个问题困扰我好久了，在我坚持不懈的努力下，终于给搞好了。(曾经也百度过可能每个情况不同一直没有找到解决问题的办法) 看图:一开始我以为是我Gradle版本的问题，我有空就下每个不同版本的Gradle去实验。结果还是一直出现这个问题。 终于在我一次次的实验下我找到了问题所在，在导入项目的时候一定要Next，看图 走到下载Gradle文件的时候，你会发现sts这个软件没有识别到你的java_home和gardle_home。虽然你的系统已经有java_home和gardle_home但就是识别不到，所以
【DB笔试面试607】在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？
小麦苗DBA宝典
08-02 811
♣题目部分在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？ ♣答案部分可以使用coe_load_sql_profile.sql...
使用coe_load_sql_profile脚本通过sql_profile绑定执行计划
DBA小站
08-26 855
##使用情景： 在部分spm不能使用的情况下，如sql中含有远程表，spm绑定不生效。 使用sql_profile绑定内存中已有的正确执行计划。 ##使用方式： 1、上传脚本 coe_load_sql_profile.sql https://download.csdn.net/download/royzhang7/21387886 2、登录数据库，要用有DBA权限的非sys用户（system或其他DBA权限用户） 3、执行 @coe_load_sql_profile.sql 4、输入参数(这里如果
STS安装lombok失败及路径含中文问题解决方案
本文主要讨论了在Spring Tool Suite (STS) 中安装lombok后遇到启动问题的解决方案，特别是当STS安装路径包含中文字符时如何处理。以下是对主要内容的详细说明： **安装lombok的步骤：** 1. 首先，你需要从官方网站...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值