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博客

spa for 10g升级到11g 步骤

最新文章