Define Owner = Perfstat
Define prefix = delta
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
Create or Replace View & Owner .. & prefix. $ SNAPSHOT AS
SELECT
E.SNAP_ID
, e.dbid
, e.instance_number
, E.SNAP_TIME
, Decode (sign (B.Snap_time-E.Startup_time), - 1, NULL, ROUND ((e.snap_time-b.snap_time) * 24 * 60 * 60) SNAP_Seconds
, E.Startup_time, Parallel, Version, DB_NAME, Instance_name, Host_Name
From & Owner..stats $ SNAPSHOT B JOIN & OWNER..stats $ SNAPSHOT E
ON (e.snap_id = b.snap_id 1 and e.dbid = b.dbid and E.instance_number = b.instance_number)
Join stats $ database_instance i on (i.startup_time = E.Startup_time and e.dbid = i.dbid and e.instance_number = I.instance_number)
WHERE NOT (E.Startup_time Between B.Snap_time and E.SNAP_TIME)
/
Begin
For v in (
SELECT TABLE_NAME, '& Prefix.' || Substr (Table_name, INSTR (Table_Name, '$')) View_name from all_tab_columns
WHERE TABLE_NAME LIKE 'Stats $%' and Table_name Not in ('Stats $ Snapshot', 'Stats $ Database_Instance ")
And nullable = 'n' and color_name in ('snap_id', 'dbid', 'instance_number') group by table_name haVing count (*) = 3
LOOP
DBMS_OUTPUT.PUT_LINE ('');
DBMS_OUTPUT.PUT_LINE ('crete or replace view "); N.Snap_seconds'; N.Snap_seconds ';, N. Snap_seconds';
For c in (
Select * from all_tab_columns
WHERE NOT (NULLABLE = 'y' and data_type in ('number')) and Table_name = v.table_name and owner = '& Owner.'
LOOP
DBMS_OUTPUT.PUT_LINE (', e.' || c.column_name);
End loop;
For c in (
Select * from all_tab_columns
WHERE NULLABLE = 'y' and data_type in ('number') and Table_name = v.table_name and owner = '& =') LOOP
DBMS_OUTPUT.PUT_LINE (', e.' || c.column_name || '-b.' || c.column_name || '' || c.column_name);
End loop;
DBMS_OUTPUT.PUT_LINE (', n.startup_time instance_startup_time, n.db_name, n.instance_name, n.host_name');
DBMS_OUTPUT.PUT_LINE ('from & Owner .. & prefix. $ SNAPSHOT N JOIN & OWNER ..' || v.table_name || 'e');
DBMS_OUTPUT.PUT_LINE ('on (e.snap_id = n.snap_id and e.dbid = n.dbid and e.instance_number = N.INSTANCE_NUMBER)');
DBMS_OUTPUT.PUT_LINE ('Join & Owner ..' || v.table_name || 'b');
DBMS_OUTPUT.PUT ('ON (e.snap_id = b.snap_id 1 and e.dbid = b.dbid and e.instance_number = B.INSTANCE_NUMBER');
For c in (
Select * from all_cons_columns join all_constraints using (Owner, constraint_name)
WHERE constraint_type = 'p' and color_name not in ('snap_id', 'dbid', 'instance_number')
And Owner = '& Owner.' And all_constraints.table_name = v.table_name
LOOP
DBMS_OUTPUT.PUT ('and e.' || c.column_name || '= b.' || c.column_name);
End loop;
DBMS_OUTPUT.PUT_LINE (')');
DBMS_OUTPUT.PUT_LINE ('/');
End loop;
DBMS_OUTPUT.PUT_LINE ('');
END;
.
Set Feedback Off ServerOutput on Size 100000
Spool delta.tmp
/
Spool off
Set feedback on echo on
START DELTA.TMP
