CHAPTER 20 • ORACLE8i SUPPLIED PACKAGES 918 frequency than a day? Oracle allows you to do this by providing a way to define a part of a day. To specify part of a day, you need to divide the day into units. Thus, a day is 24 hours, 1440 minutes, or 86,400 seconds. Once you determine the unit of time that you need to divide the day into, you determine what period of that increment you want to represent. For example, if you want to represent an hour, that is 1/24 day. If you want to represent a half hour, that is 1/48 day (because there are 48 half hours in the day). Perhaps you want your job to run every 5 minutes. In this case, you should use the value 5/1440. The 1440 represents the number of minutes in the day, and the 5 indicates that every 5 units of that 1440 increment (or every 5 minutes), the job will execute. In the examples in Listings 20.1 and 20.2, the jobs are set up to run every hour. Listing 20.3 shows an example of creating a job that will run every minute. Listing 20.3: Running a Job at 1-Minute Intervals BEGIN DBMS_JOB.SUBMIT(job=>:job_num, what=>’run_job;’, next_date=>SYSDATE, interval=>’SYSDATE + 1/1440’); COMMIT; END; / Modifying Jobs With the DBMS_JOB.CHANGE procedure, you can change a variety of job informa- tion, including what should be executed when the job is run, the interval of time between each job execution, and the next time that the job should be executed. For example, suppose that you want to modify job 6, which currently runs every hour, so that it runs every minute. Listing 20.4 shows how to use the DBMS_JOB.CHANGE procedure to change the interval. Listing 20.4: Changing a Job Interval BEGIN DBMS_JOB.CHANGE(job=>6,what=>NULL, next_date=>NULL, interval=>’SYSDATE + 1/1440’); COMMIT; END; / C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 919 Notice the NULL values passed to the WHAT and NEXT_DATE parameters in this example. Because there are no default values for these parameters, they must be given some value when running the procedure. Since NULL values are passed to these param- eters, no changes will be made to their settings. As another example, suppose that you want to have the next job execution occur in three days. Listing 20.5 shows how you could accomplish this modification. Listing 20.5: Changing a Job’s Time of Execution BEGIN DBMS_JOB.CHANGE(job=>6,what=>NULL, next_date=>SYSDATE + 3, interval=>NULL); COMMIT; END; / Suspending or Removing a Job If you wish to suspend the execution of a job, use the DBMS_JOB.BROKEN procedure. You can also use this procedure to restart the execution of a job. Here is an example of breaking a job: EXEC dbms_job.broken(6,TRUE); In this case, job 6 will be broken after this statement is executed. The first parameter is obviously the job number. The second parameter is a Boolean value (TRUE/FALSE) that indicates if the job is broken (TRUE) or not (FALSE). If you wish to unbreak a job, issue the following command: EXEC dbms_job.broken(6,FALSE); If you need to completely remove a job from the Job Scheduling facility, use the DBMS_JOB.REMOVE procedure, as in this example: EXEC dbms_job.remove(2); This example removes job 2, which is a broken job. (You can see this in the query of the DBA_JOBS view shown earlier, in the “Running a Job Once” section.) Monitoring the Job Scheduler The DBA_JOBS (and the USER_ and ALL_ varieties as well) data dictionary view pro- vides a great deal of information about the various jobs in the job queue. The infor- mation includes when the job last ran, when it is scheduled to run again, what the job is, how many errors have occurred when the job has tried to run previously, and SCHEDULING JOBS WITH THE JOB SCHEDULER Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 20 • ORACLE8i SUPPLIED PACKAGES 920 how often the job actually runs. Table 20.2 describes the most commonly used columns of the DBA_JOBS data dictionary view. TABLE 20.2: COMMONLY USED COLUMNS IN THE DBA_JOBS VIEW Column Description JOB The job number assigned to the job WHAT The actual job (PL/SQL routine) that will be executed LOG_USER The user who submitted the job to the job queue PRIV_USER The user whose privileges are ascribed to the job SCHEMA_USER The default user who is used to parse the job (this is the schema that should contain the job) LAST_DATE The last date that the job was successfully run THIS_DATE If the job is executing, this column will be populated; if the job is not exe- cuting, this column will be NULL (can be used to determine how long a job has been running) NEXT_DATE The next execution date for the job TOTAL_TIME The overall time that the system has spent on the job BROKEN Indicates if the job is broken (which means it will not be scheduled for execution) FAILURES Indicates how many times this job has failed since its last success (16 suc- cessive failures will cause the job to become broken) Here is an example of querying the DBA_JOBS view and its results: SELECT job, what, TO_CHAR(last_date,’mm/dd/yyyy hh24:mi:ss’) last_date, TO_CHAR(next_date, ‘mm/dd/yyyy hh24:mi:ss’) next_date, failures, broken FROM dba_jobs; JOB WHAT LAST_DATE NEXT_DATE FAILURES B - 1 begin my_procedure; end; 06/16/2001 13:31:53 06/16/2001 13:36:53 0 N The results show a procedure called MY_PROCEDURE that is scheduled to run. It last ran on June 16 at 1:31 P.M., and it’s ready to go again at 1:36 P.M. on the same day. No failures appear to have occurred with the execution of this job, and it’s not broken. C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 921 Communicating through Pipes The DBMS_PIPE package allows two or more sessions of an Oracle instance to com- municate with each other. When the pipe is established, an area in the SGA is allo- cated for that pipe. Other sessions can attach to that area of shared memory, and then pass messages back and forth. Because all messaging is done through the SGA, be aware that all messages sent through pipes will be lost once the instance is shut down. NOTE Oracle’s Advanced Queuing feature provides for persistency of messages. See Oracle’s documentation for information about Advanced Queuing. The DBMS_PIPE package is very handy when you wish to perform real-time com- munications between Oracle sessions. For example, you might want to monitor the operation of multiple threads. You could establish a pipe between the running processes and an application that you have written to monitor those processes. You can also use a pipe for debugging triggers and stored PL/SQL code. There are two different kinds of pipes in Oracle: public pipes and private pipes. Let’s look at each of these types in a bit more detail. Using Public Pipes Public pipes are asynchronous in nature, and any user who has access to the pipe can read or write to that pipe through the DBMS_PIPE package (as long as they have EXE- CUTE privileges to the package). Once a user reads from the pipe, the message is removed from the pipe. Therefore, there is a risk that the user you intended the mes- sage for in the public pipe will never receive that message. Public pipes can be either created implicitly or explicitly. An implicit public pipe is created automatically the first time it is referenced, and no specific creation procedure is required. An explicit public pipe is created by calling the CREATE_PIPE function and setting the private pipe type flag to N. You must de-allocate an explicitly created pipe with the REMOVE_PIPE function. Using Private Pipes Access to private pipes is much more restricted than access to implicit public pipes. Any session that wishes to connect to the private pipe created by your session must be logged in using the same user ID as the session that created the pipe: SYSDBA or COMMUNICATING THROUGH PIPES Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 20 • ORACLE8i SUPPLIED PACKAGES 922 INTERNAL. Also, you can access the pipe through stored procedures that are running under the same user domain authorization as the creator of the pipe. A private pipe is created by using the CREATE_PIPE function. The pipe is defined as a private pipe if the private pipe type flag is set to Y and there are no implicit private pipes. The name of the private pipe to be created cannot be the same as the name of any other existing private or public pipe. Like public pipes, private pipes are de- allocated by using the REMOVE_PIPE function. Sending and Receiving Messages You send and retrieve messages through the public and private pipes in the same fash- ion. To make pipes work, you first need a sending side and a receiving side session on the same database. The sending side will establish the pipe, using either a private or public pipe (the public pipe can be an implicit pipe or an explicitly named pipe). Sending a Message through a Pipe If you wish to use an explicitly named pipe, use the DBMS_PIPE.CREATE_PIPE func- tion. After creating the pipe (or not, if you are using a public implicit pipe), the next step is to pack the message with the DBMS_PIPE.PACK_MESSAGE procedure. If you are using an implicit pipe, it will be created when you pack it. Finally, once the mes- sage has been packaged, you will send the message through the pipe with the DBMS_PIPE.SEND_MESSAGE function. Listing 20.6 provides an example of sending a message through a pipe. NOTE Listing 20.6 and Listing 20.7 (presented in the next section) are designed to run together. Listing 20.6 provides one side of the pipe (the sending side), and Listing 20.7 provides the other side of the pipe (the receiving side). To see the results of these exam- ples, you would run Listing 20.7 first to set up the receiving side, and then run Listing 20.6 to send the message. Make sure to issue the SET SERVEROUTPUT ON command, as shown in these examples, or you will not see the output. Listing 20.6: Setting Up the Sending Side of a Pipe SET SERVEROUTPUT ON DECLARE V_pipe_name VARCHAR2(30):=’mypipe’; V_pipe_message VARCHAR2(8192); V_return NUMBER; BEGIN C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 923 Dbms_output.put_line(‘Sending the message through the pipe!’); Call dbms_pipe.create pipe to create an explicit pipe. This will be a public pipe by default. v_return:=Dbms_pipe.create_pipe(pipename=>v_pipe_name, private=>FALSE); IF v_return=0 THEN Dbms_output.put_line(‘Pipe Open.’); ELSE Dbms_output.put_line(‘error opening pipe.’); END IF; Now, let’s create a message to send through the pipe. V_pipe_message:=’Do, or do not, there is no try.’; Dbms_pipe.pack_message(v_pipe_message); Now, send the message through the pipe. Wait only 60 seconds. V_return:=Dbms_pipe.send_message(v_pipe_name, 60); IF v_return=0 THEN Dbms_output.put_line(‘Message Sent.’); ELSE Dbms_output.put_line(‘error sending message.’); END IF; Our work is done. V_return:=Dbms_pipe.remove_pipe(v_pipe_name); IF v_return=0 THEN Dbms_output.put_line(‘Pipe Closed.’); ELSE Dbms_output.put_line(‘error closing pipe.’); END IF; END; / Here is the output from the sending session: Sending the message through the pipe! Pipe Open. COMMUNICATING THROUGH PIPES Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 20 • ORACLE8i SUPPLIED PACKAGES 924 Message Sent. Pipe Closed. PL/SQL procedure successfully completed. Receiving a Message through a Pipe On the receiving side, things work pretty much in the reverse. First, you can explicitly open the pipe with the CREATE PIPE command, or you can choose to use an implicit pipe if you are going to be reading a public pipe. Then you use the DBMS_PIPE stored function RECEIVE_MESSAGE to receive a message from the pipe. After receiving the message, you can use the DBMS_PIPE.UNPACK_MESSAGE to unpack the message from the pipe. When you’re finished with the pipes, use the DBMS_PIPE.REMOVE_ PIPE procedure to remove them. Listing 20.7 provides an example of receiving a mes- sage from a pipe. Listing 20.7: Setting Up the Receiving Side of a Pipe SET SERVEROUTPUT ON DECLARE V_pipe_name VARCHAR2(30):=’mypipe’; V_pipe_message VARCHAR2(8192); V_return NUMBER; BEGIN Dbms_output.put_line(‘Receiving the message through the pipe!’); Call dbms_pipe.create pipe to create an explicit pipe. This will be a public pipe by default. v_return:=Dbms_pipe.create_pipe(pipename=>v_pipe_name, private=>FALSE); IF v_return=0 THEN Dbms_output.put_line(‘Pipe Open.’); ELSE Dbms_output.put_line(‘error opening pipe.’); END IF; Now, send the message through the pipe. Wait only 60 seconds. V_return:=Dbms_pipe.receive_message(v_pipe_name, 60); IF v_return=0 THEN C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 925 Dbms_output.put_line(‘received the message through the pipe!’); ELSE Dbms_output.put_line(‘error receiving the message through the pipe!’); END IF; Now, unpack the message. Dbms_pipe.unpack_message(v_pipe_message); What was the message? Dbms_output.put_line(’The message was: ’||v_pipe_message); Our work is done. V_return:=Dbms_pipe.remove_pipe(v_pipe_name); IF v_return=0 THEN Dbms_output.put_line(‘Pipe Removed.’); ELSE Dbms_output.put_line(‘error removing pipe.’); END IF; END; / Here is the output from the receiving session: Receiving the message through the pipe! Pipe Open. received the message through the pipe! The message was: Do, or do not, there is no try. Pipe Removed. PL/SQL procedure successfully completed. We have covered just a few of the many Oracle stored procedures that can simplify the Oracle DBA’s life. Packages such as DBMS_DDL, DBMS_LOB, DBMS_OUTPUT, and DBMS_UTILITY can be very helpful to the DBA. As described earlier in this chapter, you can use the DESC command from SQL*Plus to see the procedures and functions in any of the Oracle-supplied packages. COMMUNICATING THROUGH PIPES Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 21 Oracle8i Database Security FEATURING: Managing Oracle user accounts 928 Enforcing row-level security 950 Enforcing column-level security 968 C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.