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...
Posts
-- ==================================================================================== -- Script Name : pdb_db_growth_by_days.sql -- Purpose : Analyze Database Growth over Last N Days (User-Driven, PDB-aware) -- Author : ChatGPT for Raunak -- Requirements: Run from SQL*Plus inside PDB with access to DBA_HIST_* views -- ==================================================================================== SET ECHO OFF SET LINESIZE 200 SET PAGESIZE 100 SET FEEDBACK OFF SET VERIFY OFF SET TRIMSPOOL ON -- === Accept user input for days === ACCEPT v_days NUMBER PROMPT 'Enter number of days to analyze growth (e.g., 30): ' -- === Generate spool file with timestamp === COLUMN spool_file_name NEW_VALUE spool_file SELECT 'db_growth_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual; SPOOL &spool_file -- === Column formatting === COLUMN START_TIME FORMAT A20 COLUMN END_TIME FORMAT A20 COLUMN TABLESPACE_NAME FORMAT A30 C...
-- ==================================================================================== -- Script Name : unused_objects_report.sql -- Purpose : Identify unused or stale objects based on user-defined inactivity days -- Author : ChatGPT for Raunak -- ==================================================================================== SET ECHO OFF SET LINESIZE 200 SET PAGESIZE 100 SET FEEDBACK OFF SET VERIFY OFF SET TRIMSPOOL ON -- === Accept user input for age threshold === ACCEPT v_days NUMBER PROMPT 'Enter number of days to consider an object as UNUSED (e.g., 90): ' -- === Generate timestamped spool file === COLUMN spool_file_name NEW_VALUE spool_file SELECT 'unused_objects_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual; SPOOL &spool_file PROMPT ============================================================================ PROMPT Report: Objects Unused for at least &v_days days PROMPT ====================...
-- ============================================================================================ -- Script Name : sql_plan_flip_diagnose.sql -- Purpose : Detect SQLs with Plan Flips, Diagnose Plan Performance, Manage Baselines -- Requirements: Must be run in SQL*Plus. Needs access to AWR views, DBA_SQL_PLAN_BASELINES -- ============================================================================================ SET ECHO OFF SET LINESIZE 300 SET PAGESIZE 100 SET VERIFY OFF SET FEEDBACK OFF SET TIMING OFF SET TRIMSPOOL ON SET LONG 1000000 SET LONGCHUNKSIZE 1000000 -- === Generate spool file with timestamp === COLUMN spool_file_name NEW_VALUE spool_file SELECT 'sql_plan_flip_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual; SPOOL &spool_file PROMPT ============================================================================ PROMPT [Step 1] Detect SQLs with Plan Flips in Last 7 Days PROMPT ====================================...
-- ============================================================================= -- Script Name : pdb_db_growth_by_days.sql -- Purpose : Analyze Database Growth over Last N Days (User-Driven, PDB-aware) -- Author : ChatGPT (for Raunak) -- Requirements: AWR enabled, read access to DBA_HIST_* views, executed in a PDB -- ============================================================================= ACCEPT days NUMBER PROMPT 'Enter number of days to analyze (e.g., 7, 30, 60): ' SET LINESIZE 200 SET PAGESIZE 100 SET VERIFY OFF SET FEEDBACK OFF SET TRIMSPOOL ON -- === Column Formatting === COLUMN START_TIME FORMAT A20 COLUMN END_TIME FORMAT A20 COLUMN TABLESPACE_NAME FORMAT A30 COLUMN OWNER FORMAT A20 COLUMN OBJECT_NAME FORMAT A30 COLUMN SUBOBJECT_NAME FORMA...
sql_perf_analyzer.sh #!/bin/bash # Filename: sql_perf_analyzer.sh # Description: Intelligent SQL Performance Diagnostic Toolkit (Shell Wrapper) # Author: ChatGPT Toolkit for Oracle # === Setup Variables === SCRIPT_DIR=$(dirname "$0") SQL_DIR="$SCRIPT_DIR/sql" LOG_DIR="$SCRIPT_DIR/output/logs" REPORT_DIR="$SCRIPT_DIR/output/reports" DATE_TAG=$(date '+%Y%m%d_%H%M%S') LOG_FILE="$LOG_DIR/sql_perf_analysis_$DATE_TAG.log" mkdir -p "$LOG_DIR" "$REPORT_DIR" # === Prompt Inputs === echo "Enter SQL_ID to diagnose: " read SQL_ID echo "Enter GOOD timeframe (start time YYYY-MM-DD HH24:MI): " read GOOD_START echo "Enter GOOD timeframe (end time YYYY-MM-DD HH24:MI): " read GOOD_END echo "Enter BAD timeframe (start time YYYY-MM-DD HH24:MI): " read BAD_START echo "Enter BAD timeframe (end time YYYY-MM-DD HH24:MI): " read BAD_END echo "Output format? (1=Text, 2=HTML, 3=Bot...
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...