56 lines
3.8 KiB
SQL
56 lines
3.8 KiB
SQL
|
|
|
|
--****************************************************
|
|
-- Migrate SQL Baselines
|
|
--****************************************************
|
|
|
|
|
|
drop table VG_SPM_STGTAB_BASELINE;
|
|
exec DBMS_SPM.CREATE_STGTAB_BASELINE ('VG_SPM_STGTAB_BASELINE' );
|
|
|
|
|
|
set serveroutput on
|
|
DECLARE
|
|
number_of_sqlplans NUMBER;
|
|
BEGIN
|
|
number_of_sqlplans := DBMS_SPM.PACK_STGTAB_BASELINE ( table_name => 'VG_SPM_STGTAB_BASELINE'
|
|
, table_owner => NULL -- Default NULL means current schema
|
|
, sql_handle => NULL -- Default NULL, case sensitive
|
|
, plan_name => '%' -- Default NULL, case sensitive, % wildcards accepted
|
|
, sql_text => NULL -- Default NULL, case sensitive, % wildcards accepted
|
|
, creator => NULL -- Default NULL, case insensitive unless double quoted
|
|
, origin => NULL -- Default NULL, case insensitive unless double quoted,
|
|
-- 'MANUAL-LOAD', 'AUTO-CAPTURE', 'MANUAL_SQLTUNE' or 'AUTO-SQLTUNE'
|
|
, enabled => NULL -- Default NULL, case insensitive, Must be 'YES' or 'NO'
|
|
, accepted => NULL -- Default NULL, case insensitive, Must be 'YES' or 'NO'
|
|
, fixed => NULL -- Default NULL, case insensitive, Must be 'YES' or 'NO'
|
|
, module => NULL -- Default NULL, case sensitive'
|
|
, action => NULL -- Default NULL, case sensitive'
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('Number of Plans packed - ' || number_of_sqlplans );
|
|
END;
|
|
/
|
|
|
|
set serveroutput on
|
|
DECLARE
|
|
number_of_sqlplans NUMBER;
|
|
BEGIN
|
|
|
|
number_of_sqlplans := DBMS_SPM.UNPACK_STGTAB_BASELINE ( table_name => 'VG_SPM_STGTAB_BASELINE'
|
|
, table_owner => NULL -- Default NULL means current schema
|
|
, sql_handle => NULL -- Default NULL, case sensitive
|
|
, plan_name => '%' -- Default NULL, case sensitive, % wildcards accepted
|
|
, sql_text => NULL -- Default NULL, case sensitive, % wildcards accepted
|
|
, creator => NULL -- Default NULL, case insensitive unless double quoted
|
|
, origin => NULL -- Default NULL, case insensitive unless double quoted,
|
|
-- 'MANUAL-LOAD', 'AUTO-CAPTURE', 'MANUAL_SQLTUNE' or 'AUTO-SQLTUNE'
|
|
, enabled => NULL -- Default NULL, case insensitive, Must be 'YES' or 'NO'
|
|
, accepted => NULL -- Default NULL, case insensitive, Must be 'YES' or 'NO'
|
|
, fixed => NULL -- Default NULL, case insensitive, Must be 'YES' or 'NO'
|
|
, module => NULL -- Default NULL, case sensitive'
|
|
, action => NULL -- Default NULL, case sensitive'
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('Number of Plans unpacked - ' || number_of_sqlplans );
|
|
END;
|
|
/
|