-

 SET LINES 150

SET PAGES 1000

SET VERIFY OFF

SET FEEDBACK OFF

SET HEADING ON

SET TRIMSPOOL ON

SET LONG 10000

SET LONGCHUNKSIZE 10000


PROMPT ===================================================

PROMPT       Oracle RAC Session Monitor - Full Version

PROMPT ===================================================


-- Step 1: Input Method

PROMPT Choose input method:

PROMPT Enter 'U' for Username or 'S' for SID/SERIAL#

ACCEPT input_method CHAR DEFAULT 'U'


-- Variables Initialization

DEFINE sid = ''

DEFINE serial = ''

DEFINE instid = 1

DEFINE username = ''

DEFINE sqlid = ''


PROMPT


-- Step 2: If Username mode, accept username and list sessions

PROMPT Enter Username (leave blank if using SID/SERIAL#):

ACCEPT username CHAR


COLUMN inst_id_col NEW_VALUE instid

COLUMN sid_col NEW_VALUE sid

COLUMN serial_col NEW_VALUE serial


SELECT inst_id, sid, serial#, status, program, module, sql_id, event

FROM gv$session

WHERE username = UPPER('&username')

ORDER BY inst_id, sid;


PROMPT


-- Step 3: Accept SID, SERIAL#, and INST_ID

PROMPT Enter SID:

ACCEPT sid NUMBER


PROMPT Enter SERIAL#:

ACCEPT serial NUMBER


PROMPT Enter INST_ID (default 1):

ACCEPT instid NUMBER DEFAULT 1


-- Validation

BEGIN

  IF '&sid' IS NULL OR LENGTH(TRIM('&sid')) = 0 THEN

    DBMS_OUTPUT.PUT_LINE('No SID entered. Exiting.');

    RAISE_APPLICATION_ERROR(-20001, 'Exiting script.');

  END IF;

  IF '&serial' IS NULL OR LENGTH(TRIM('&serial')) = 0 THEN

    DBMS_OUTPUT.PUT_LINE('No SERIAL# entered. Exiting.');

    RAISE_APPLICATION_ERROR(-20002, 'Exiting script.');

  END IF;

END;

/


-- Step 4: Get SQL_ID(s) for session

COLUMN sql_id_col NEW_VALUE sqlid

SELECT DISTINCT sql_id

FROM gv$session

WHERE sid = &sid AND serial# = &serial AND inst_id = &instid

  AND sql_id IS NOT NULL;


PROMPT SQL_ID(s) found for session &sid.&serial on instance &instid:

SEL


Popular posts from this blog

-
Image
How to shutdown a Single Instance Database in Oracle 19c Overview Shutdown is an operation performed when a command is issued by the Administrator to bring down the Database or when it receives a signal to bring down the Database incase of a failure/malfunction or due to SHUTDOWN ABORT comm and execution. The options used by t he Administrator to bring down the Database is called as Shutdown Modes and the Database Server follows a sequence to shutdown a Database . Let's check how does that happen. Concepts The following are the shutdown Modes. Shutdown Modes A database administrator with SYSDBA or SYSOPER privileges can shut down the database using the SQL*Plus SHUTDOWN command or Enterprise Manager. The SHUTDOWN command has the following options that determine the shutdown behavior. Now let's look into each SHUTDOWN option in detail. SHUTDOWN ABORT  This mode is used for emergency situations, such as when shutdown with "IMMEDIATE" option doesn't work. This mode o...
Read more
-
How to startup a Single Instance Database in Oracle 19c Overview An Oracle Database goes through various stages until it is made available to the users. They are categorized into the below stages. Concepts The following are the stages of the startup sequence. Nomount   The instance is started, but is not yet associated with a database. The stages are as follows:  1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)  2. Reads the parameter file to determine the values of initialization parameters  3. Allocates the SGA based on the initialization parameter settings  4. Starts the Oracle background processes  5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax At this stage, no database is associated with the instanc...
Read more