spa for 10g升级到11g 步骤

1.creater user
create user spa identified by spa default tablespace HERO2_TABLESPACE_DATA;
grant dba to spa;

2.create sts
CONN spa/spa
DECLARE
  l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  --DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'spa_10g_sts_hero2');
  DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_10g_sts_hero2');
  OPEN l_cursor FOR
    SELECT VALUE(P)
      FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''HERO2_BILLING_0831''',
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  1,
                                                  NULL,
                                                  'ALL')) P;
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'spa_10g_sts_hero2',
                           populate_cursor => l_cursor);
END;
/

SELECT sql_text
FROM   dba_sqlset_statements
WHERE  sqlset_name = 'spa_10g_sts_hero2';

3.pack sts
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'STGTAB_SQLSET',schema_name => 'SPA',tablespace_name=>'HERO2_TABLESPACE_DATA');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name=> 'spa_10g_sts_hero2',sqlset_owner=> 'SPA',staging_schema_owner => 'SPA',staging_table_name   => 'STGTAB_SQLSET');
4.expdp
create tablespace HERO2_TABLESPACE_DATA datafile '/U01/app/oracle/oradata/actvdb/HERO2_TABLESPACE_DATA.dbf' size 100m ;
create tablespace HERO2_TABLESPACE_INDEX datafile '/U01/app/oracle/oradata/actvdb/HERO2_TABLESPACE_INDEX.dbf' size 100m ;
create profile PF_HERO2 limit
  sessions_per_user 5;
-- Create profile 
create profile PF_HERO2_BILLING_0831 limit
  sessions_per_user 90;


expdp spa/spa schemas=spa dumpfile=hero_spa.dmp directory=DATA_PUMP_OUT2INNER
impdp \'/ as sysdba\' schemas=spa dumpfile=hero_spa.dmp directory=DATA_PUMP_OUT3INNER




5.create sts in 11g
exec dbms_SQLTUNE.create_sqlset(sqlset_name => 'spa_10g_sts_hero2');
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name=> 'spa_10g_sts_hero2',sqlset_owner=> 'SPA',staging_schema_owner => 'SPA',staging_table_name   => 'STGTAB_SQLSET',replace=>true);

6.create spa
VARIABLE v_task VARCHAR2(64);
exec DBMS_SQLPA.DROP_ANALYSIS_TASK('spa10to11g_hero2');
EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_10g_sts_hero2',task_name => 'spa10to11g_hero2');
PRINT :v_task
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => 'spa10to11g_hero2',
    execution_type  => 'CONVERT SQLSET',
    execution_name  => 'convert_10g');
END;
/
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => 'spa10to11g_hero2',
    execution_type  => 'TEST EXECUTE',
    execution_name  => 'exec_11g');
END;
/
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => 'spa10to11g_hero2',
    execution_type   => 'compare performance', 
    execution_params => dbms_advisor.arglist(
                          'execution_name1', 
                          'convert_10g', 
                          'execution_name2', 
                          'exec_11g')
    );
END;
/
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON
SPOOL /tmp/spa_report.htm
SELECT DBMS_SQLPA.report_analysis_task('spa10to11g_hero2', 'HTML', 'ALL','ALL')
FROM   dual;
SPOOL OFF

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

转载于:http://blog.itpub.net/26390465/viewspace-1793479/