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;

In this Document

APPLIES TO:

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

1.  
   
   >> 
   
    
    
    
    
    
   <>>
             
   
   >> 
    
   
   
   
   
   

2.  
   > 
   > 
   > 
    
   

3. 
   
    
   > 
   > 
   > 
   > 
    
   

4.  
   
   
    

5. >

   
   

6. 
   
    
   > 
   > 
   > 
   > 
   > 
    
   
   
    
   
   
    
    
    
    
    
    
   
    
   
   

7.  

   > 
   >> 
   >> 
   >
   >>>>>>

8.  
   > 
   > 
   > 
   > 
   > 
    
   

9. 
   
   
   
   
   
   >
   >
   >
   >
   
   
   
    

• 
  
  >>
  
  
  
  
  

&