Intermediate
Q: | |
30-8. | Here’s a suggested procedure: /* Filename on web page: analyze_oem.sp */ CREATE OR REPLACE PROCEDURE analyze_oem IS -- Analyze the OEM schema. This job will be submitted -- every hour by DBMS_JOB. -- cursor to get the current hour CURSOR curs_get_hour IS SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; v_current_hour NUMBER(2); -- option for analyze command v_method_opt VARCHAR2(15); BEGIN -- get the current hour OPEN curs_get_hour; FETCH curs_get_hour INTO v_current_hour; CLOSE curs_get_hour; -- if the current hour is 1 or 2 inclusive -- then perform a complete analysis otherwise just do -- it for the indexes IF v_current_hour IN (1,2) THEN v_method_opt := NULL; ELSE v_method_opt := 'FOR ALL INDEXES'; END IF; DBMS_UTILITY.ANALYZE_SCHEMA( SCHEMA => 'OEM', METHOD => 'COMPUTE', METHOD_OPT => v_method_opt ); END analyze_oem; |
Q: | |
30-9. | This is one possible implementation: /* Filename on web page: add_trx.month.sp*/ CREATE OR REPLACE PROCEDURE add_trx_month IS /* || This function adds a new partition to the account_trx || table. It is to be run on the 20th of the month to || create a partition for the next month. */ -- cursor to build date format requirements CURSOR curs_get_date_info IS SELECT '01-' || TO_CHAR(ADD_MONTHS(SYSDATE,1),'MON-YYYY') upper_bound, TO_CHAR(SYSDATE,'YYYYMM') ... |
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.