Populating the Tables

Now, with a single INSERT focused on the delivery information we know (the driver and the area), all of the required tables are populated:

SQL> INSERT INTO delivery_info(delivery_id, 2 delivery_start, 3 delivery_end, 4 area_desc, 5 driver_name) 6 VALUES 7 (NULL, NULL, NULL, 'LOCAL COLLEGE', 'BIG TED'); 1 row created. SQL> SELECT * FROM delivery; DELIVERY_ID DELIVERY_ DELIVERY_ AREA_ID DRIVER_ID----------- --------- --------- ---------- ---------- 1 13-JAN-02 1 1 SQL> SELECT * FROM area; AREA_ID AREA_DESC---------- ------------------------------ 1 LOCAL COLLEGE SQL> SELECT * FROM driver; DRIVER_ID DRIVER_NAME---------- ------------------------------ 1 BIG TED

18.5 AFTER SUSPEND Triggers

Oracle9i introduced a new type of trigger that fires whenever a statement is suspended. This might occur as the result of a space issue like exceeding an allocated tablespace quota. This functionality can be used in order to repair the conditions and allow the stalled operation to continue. AFTER SUSPEND triggers are a boon to busy developers tired of being held up by space errors, and to even busier DBAs who constantly have to resolve these errors.

For example, consider the situation faced by Batch Only, the star Oracle developer at Totally Controlled Systems. He is responsible for maintaining hundreds of programs that run overnight, performing lengthy transactions to summarize information and move it between disparate applications. At least twice a week, his pager goes off during the wee hours of the morning because one of his programs has encountered this Oracle error.

ERROR at line 1:ORA-01536: space quota exceeded for tablespace 'USERS'

Batch then has the unenviable task of phoning Totally's Senior DBA, Don T. Planahead, and begging for a space quota increase. Don's usual question is, "How much do you need?" to which Batch can only feebly reply, "I don't know because the data load fluctuates so much." This leaves them both very frustrated, because Don wants control over the space allocation for planning reasons, and Batch doesn't want his night's sleep interrupted so often.