Creating Your Own Concurrent Programs

---

Now that you have your own custom Concurrent Manager with its own off hours definition, you can assign your own custom programs to that manager.

If you have been an Oracle DBA (or even an Apps DBA) for any period of time, you likely have your handy, well guarded, bag of scripts that you fall back on to answer all of the questions that users and managers have on why, how, and when. Since you have this treasure trove of proven scripts, why not turn them into Concurrent Programs that you can run on demand or schedule with just a few easy clicks of a button? Not only will you be able to determine what other people's jobs are doing, you will be able to determine which of your own scripts are running longer than you anticipated and move those off to run in the off hours as well. Jobs that DBAs often have that are, by virtue of the work they are doing, long running are coalescing tables in the database and rebuilding indexes (even if you use the Online Index Rebuild feature in Oracle 9i discussed in Chapter 2). While putting your scripts into a concurrent job may allow others to see what you are running (where a cron job will allow you to run your scripts on schedule quietly without anyone knowing about them), being above board with what you are running will allow the end users to see your efforts on their behalf at tuning the system and allow them to verify if it is your job, running at the wrong time, that is causing processing to grind to a halt.

To add Barbara Mathews' script to coalesce tablespaces (RUN_COAL.sql) as a Concurrent Program, look at the script in Table 12.12. Note the $1 variable. This variable will take the value of the concurrent request ID of the coalesce job. Setting this script up as a Concurrent Program, the variable will be filled in whenever this request is run. This means that every time the script uses SQL to create SQL, it will dynamically change the name of the list of tablespaces to coalesce. This will allow you to track the differences (should you choose to remove the rm statement and maintain a series of scripts that were run) in time as compared to the differences in tablespaces over time.

 PROMPT RUN_COAL.SQL PROMPT Copyright 2000, All Rights Reserved, Reed-Matthews, Inc. PROMPT set linesize 180 set pagesize 200 clear buffer set head off set termout off set feedback off spool &1..RMINC_coaltbsp.sql; select 'whenever sqlerror continue' from dual; select 'alter tablespace '||tablespace_name||' coalesce;'           from sys.dba_tablespaces; select 'PROMPT ALL DONE;'  from dual; spool off; spool &1 set echo on set termout on set feedback on @&1..RMINC_coaltbsp.sql host rm &1..RMINC_coaltbsp.sql exit; 

To make this script into a Concurrent Program, simply log into the application as a user with sysadmin responsibility and select Concurrent/Program/Executable. To have this step end successfully, you will need to have your script defined beforehand and located in your %CUSTOM%/sql directory.

Table 12.13 provides the values that you will put into the Concurrent Program Executable screen and Figure 12.8 shows an example of the screen.

Table 12.13: Concurrent Program Executable Values for Run_Coal

Prompt	Value
Executable	RUN_COAL
Short Name	RMINC_RUN_COAL
Application**	YOUR COMPANY custom
Description	Coalesces All Tablespaces
Execution Method	SQL*PLUS
Execution Filename	RUN_COAL
Subroutine Name

Figure 12.8: Concurrent Program Executable Screen

Next, you will navigate to Concurrent/Program/Define and define your script to the system. Table 12.14 shows the screen location to use to look for the values. Figure 12.9 shows the screen that goes along with it, prompts on the screen, and value to assign to the prompt to enable the script in Table 12.11.

Table 12.14: Concurrent Program Definition Values

Screen Location	Prompt	Value
Main	Program	Coalesce Tablespaces (RMINC)
	Short Name	RMINC_RUN_COAL
	Application**	YOUR COMPANY custom
	Description	Coalesces All Tablespaces
	Enabled	÷
Executable	Name	RUN_COAL
	Method	SQL*PLUS
	Options
	Priority
†Request	Type
	Use in SRS	÷
	Allow Disabled Values
	Run alone
	Enable trace
	Restart on system failure	÷
	Nl s compliant
Output	Format
	Save	÷
	Print	÷
	Columns	132
	Rows	45
	Style	Landscape
	Style required
	Printer

Figure 12.9: Concurrent Program Definition Screen

Figure 12.10 shows you the options that you have by following the Session Control button. You can deliberately assign a rollback segment, a default consumer group to the program, and an optimizer mode (e.g., all rows, first rows, choose).

Figure 12.10: Session Controls

Figure 12.11 gives an example of the screen through which you would define incompatibilities. Use this judiciously because it can cause extreme bottlenecks if not used carefully.

Figure 12.11: Concurrent Program Incompatibilities

Figure 12.12 shows the resulting screen that gets spawned when you click on the Parameters button. At this screen, you will input parameters that apply to the relevant program.

Figure 12.12: Concurrent Program Parameters

Finally, by navigating to the Security/Responsibility/Request screen, you can assign the user or users that have the ability to run this Concurrent Program. Given that this new program is one that would typically be run only by a DBA, you should consider carefully allowing anyone other than the sysadmin to have the ability to run the program.

You now have one custom Concurrent Program that you can run on a schedule to do database maintenance on the off hours. Further, you can instruct the application to notify you that the job has completed and that e-mail can be parsed for the word "error" and if that is found, another notice is sent to your pager to notify you that the job did not complete successfully.

---