Databases Reference
In-Depth Information
CONNECT perfstat@TESTDB/perfstat
EXECUTE STATSPACK.SNAP;
CONNECT hr@TESTDB/hr
INSERT INTO HR.TRAVELS(EMPLOYEE_ID, LOCATION_ID, START_DATE, END_DATE)
SELECT E.EMPLOYEE_ID, L.LOCATION_ID, SYSDATE, SYSDATE + ROWNUM FROM
HR.EMPLOYEES E, HR.LOCATIONS L;
CONNECT perfstat@TESTDB/perfstat
EXECUTE STATSPACK.SNAP;
@?/rdbms/admin/spreport
When requested, use the last two snapshots to prepare the report (using the
TRAVELS_COUNTER
table).
With the following script, we can produce the same report using the sequence to generate
the primary keys:
CONNECT hr@TESTDB/hr
CREATE OR REPLACE TRIGGER TR_TRAVELS_INS
BEFORE INSERT ON TRAVELS FOR EACH ROW
WHEN (NEW.TRAVELID IS NULL)
BEGIN
SELECT TRAVEL_SEQ.NEXTVAL INTO :NEW.TRAVELID FROM DUAL;
END;
/
CONNECT perfstat@TESTDB/perfstat
EXECUTE STATSPACK.SNAP;
CONNECT hr@TESTDB/hr
INSERT INTO HR.TRAVELS(EMPLOYEE_ID, LOCATION_ID, START_DATE, END_DATE)
SELECT E.EMPLOYEE_ID, L.LOCATION_ID, SYSDATE, SYSDATE + ROWNUM FROM
HR.EMPLOYEES E, HR.LOCATIONS L;
CONNECT perfstat@TESTDB/perfstat
EXECUTE STATSPACK.SNAP;
@?/rdbms/admin/spreport
As mentioned earlier, use the last two snapshots to prepare a report.
