DB Creation   «Prev  Next»

Lesson 9 The Oracle Shared Server HELP Command
Objective Use Oracle Shared Server's HELP command.

Oracle Shared Server HELP Commands

Oracle Shared Server, also known as Oracle Multi-Threaded Server (MTS), is a configuration that allows multiple user processes to share very few server processes. This architecture reduces the amount of memory required for user processes, but it introduces a level of complexity in managing and monitoring these shared server processes.
Interactively requesting help information specifically about the Oracle Shared Server environment while you are connected to the database is not directly supported within the SQL*Plus environment or any other standard Oracle client tool. The Oracle Shared Server architecture is managed and monitored predominantly via the Oracle Database’s data dictionary views and the Oracle Enterprise Manager.
To retrieve information about the shared server environment and diagnose issues, you can query various data dictionary views. Some of the relevant views include:
  1. V$SHARED_SERVER: Provides information about the shared server processes.
  2. V$SHARED_SERVER_MONITOR: Displays monitor information for the shared servers.
  3. V$CIRCUIT: Shows information about virtual circuits, which are used in a shared server configuration.
  4. V$DISPATCHER: Provides information about the dispatcher processes.
  5. V$DISPATCHER_CONFIG: Shows configuration information for the dispatchers.
  6. V$QUEUE: Displays information about the shared server queues.
Here is an example query to get information about the shared server processes:

This query will return information about each shared server process, including its status (whether it’s idle or busy), the number of requests it has processed, and the amount of data it has received and sent. For a comprehensive diagnosis and help regarding Oracle Shared Server, it is recommended to:
  1. Refer to the official Oracle Database documentation specific to your version.
  2. Utilize Oracle Enterprise Manager, which provides a graphical interface to monitor and manage the shared server environment.
  3. Consult Oracle Support and Oracle’s official forums for help and best practices.
Oracle’s documentation is extensive and provides in-depth details on how to configure, manage, and monitor Oracle Shared Server. It is a valuable resource for anyone working with this architecture.

HELP Commands on Oracle Shared Server

Question: Which command do I need to enter into SQL*Plus in order to receive "HELP Commands" on Oracle Shared Server?
To receive "HELP Commands" on Oracle Shared Server, you can simply type the following command at the SQL*Plus prompt:
HELP Commands

This will display a list of all of the SQL*Plus commands that are available on Oracle Shared Server. You can also use the following commands to get help on specific SQL*Plus commands:
HELP <command_name>


For example, to get help on the `SELECT` statement, you would type the following command at the SQL*Plus prompt:

This would display a detailed help message for the `SELECT` statement. If you are using a version of SQL*Plus that is older than 12.2, you may need to install the SQL*Plus command-line help tables before you can use the `HELP` command. To do this, run the following SQL script from SQL*Plus:

This will create the SQL*Plus command-line help tables and populate them with SQL*Plus help data. Once the SQL*Plus command-line help tables are installed, you can use the `HELP` command to get help on any SQL*Plus command.

Use Of Roles

Using roles has several benefits, including:
  1. Reducing the number of grants and thereby making it easier to manage security.
  2. Dynamically changing the privileges for many users with a single grant or revoke.
  3. Selectively enabling or disabling depending on the application.
Roles can be used for most system and object privileges. Privileges granted through a role cannot be used for creating an object (views, packages, procedures, and functions). You need to grant privileges directly to the user for this.

Shell Script to count the number of Instances on a UNIX Server

  1. Question: How does one write a shell script to count the number of instances on a UNIX server?
  2. Answer: You can do this in several ways, but I start at the oratab file to get the instance count:
cat /etc/oratab | grep -v "^\#" | grep -v "^*" | grep -v "^$" | wc -l

Concurrently perform Multiple Tasks

Because you can open multiple windows simultaneously on Shared Server, you can perform multiple administrative and non-administrative tasks concurrently. You can use Shared Server to administer
  1. a single database or to simultaneously administer multiple databases.
  2. Centralize database administration tasks.
  3. You can administer both local and remote databases running on any Oracle platform at any location worldwide.

In addition, these Oracle platforms can be connected by any network protocol(s) supported by Oracle Network Services and the Multi-Protocol Interchange.
  1. Dynamically execute SQL, PL/SQL on the Shared Server.
  2. You can use Shared Server to enter, edit, and execute statements.
  3. Shared Server also maintains a history of statements executed.
Thus, you can re-execute statements without retyping them, a particularly useful feature if you need to execute lengthy statements repeatedly.

Server Manager and Database Administration [Legacy]

Server Manager was designed to replace SQL*DBA and provide administrative functionality through an easy-to-use interface. It was used to perform traditional administrative tasks, such as
  1. database startup,
  2. shutdown,
  3. backup, and
  4. recovery.

Rather than manually have entered the SQL commands to perform these tasks, Server Manager contained a graphical interface to execute the commands quickly and conveniently.