BEGIN
-- Enable DBMS_OUTPUT
DBMS_OUTPUT.ENABLE(1000000);
DECLARE
-- Variables to prevent buffer issues
l_db_name VARCHAR2(255);
l_instance_name VARCHAR2(255);
l_host_name VARCHAR2(255);
l_ssl_status VARCHAR2(255);
v_db_version VARCHAR2(30);
v_data_found BOOLEAN := FALSE;
v_output_width CONSTANT NUMBER := 88; -- 20+2+20+2+20+2+30 = 88 chars
BEGIN
-- Debug: Confirm execution start
DBMS_OUTPUT.PUT_LINE('Executing query...');
-- Check version
SELECT banner INTO v_db_version
FROM v$version
WHERE ROWNUM = 1;
-- Header
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));
DBMS_OUTPUT.PUT_LINE(RPAD('-- Database Connection Info', v_output_width - 2, ' ') || '--');
DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(RPAD('DB Name', 20) || ' ' ||
RPAD('Instance Name', 20) || ' ' ||
RPAD('Host Name', 20) || ' ' ||
RPAD('SSL', 30));
DBMS_OUTPUT.PUT_LINE(RPAD('-', 20, '-') || ' ' ||
RPAD('-', 20, '-') || ' ' ||
RPAD('-', 20, '-') || ' ' ||
RPAD('-', 30, '-'));
-- Fetch data
FOR rec IN (
SELECT
d.name,
i.instance_name,
i.host_name,
CASE
WHEN SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') = 'tcps'
THEN 'Yes (Port 5433, SSL enabled)'
ELSE 'No (SSL not enabled)'
END AS ssl_status
FROM
v$database d,
v$instance i,
v$session s
WHERE
s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1)
) LOOP
v_data_found := TRUE;
l_db_name := SUBSTR(rec.name, 1, 255);
l_instance_name := SUBSTR(rec.instance_name, 1, 255);
l_host_name := SUBSTR(rec.host_name, 1, 255);
l_ssl_status := SUBSTR(rec.ssl_status, 1, 255);
DBMS_OUTPUT.PUT_LINE(RPAD(l_db_name, 20) || ' ' ||
RPAD(l_instance_name, 20) || ' ' ||
RPAD(l_host_name, 20) || ' ' ||
RPAD(l_ssl_status, 30));
END LOOP;
-- No data diagnostic
IF NOT v_data_found THEN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('No data found. Check privileges on V$DATABASE, V$INSTANCE, V$SESSION or session data.');
END IF;
-- 11g diagnostic
IF v_db_version LIKE '%11%' THEN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Note: SSL detection via NETWORK_SERVICE_BANNER is unavailable in Oracle 11g. Using SYS_CONTEXT(''USERENV'', ''NETWORK_PROTOCOL'').');
DBMS_OUTPUT.PUT_LINE('If SSL status is incorrect, verify listener.ora for TCPS configuration.');
END IF;
-- Footer
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));
DBMS_OUTPUT.PUT_LINE(RPAD('-- Report Completed', v_output_width - 2, ' ') || '--');
DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));
DBMS_OUTPUT.PUT_LINE(' ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
RAISE;
END;
END;
/
/*
* SQL script with SELECT statements to report database name, instance name, hostname, and SSL connection status.
* Output is formatted for SQL*Plus/Toad with aligned columns using COLUMN commands.
* Features:
* - Retrieves DB Name from V$DATABASE.
* - Retrieves Instance Name and Host Name from V$INSTANCE.
* - Checks SSL status using SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') for tcps (SSL).
* - Reports SSL as 'Yes (Port 5433, SSL enabled)' or 'No (SSL not enabled)'.
* - Includes diagnostic notes for Oracle 11g where SSL detection is limited.
* Requires SELECT privileges on V$DATABASE, V$INSTANCE, V$SESSION, and V$MYSTAT.
* Compatible with Oracle 11g and later.
* To ensure output visibility and alignment:
* - SQL*Plus: Run `SET LINESIZE 100; SET PAGESIZE 50;` before executing.
* - Toad: Ensure output window width is at least 100 characters.
* If no output appears, check:
* - Sufficient privileges on V$DATABASE, V$INSTANCE, V$SESSION.
* - Valid session data in V$SESSION.
* If SSL status is incorrect, verify listener.ora for TCPS configuration or provide custom SSL indicator.
*/
-- Set SQL*Plus formatting for alignment
SET LINESIZE 100
SET PAGESIZE 50
SET FEEDBACK OFF
-- Column formatting for aligned output
COLUMN db_name FORMAT A20 HEADING 'DB Name'
COLUMN instance_name FORMAT A20 HEADING 'Instance Name'
COLUMN host_name FORMAT A20 HEADING 'Host Name'
COLUMN ssl_status FORMAT A30 HEADING 'SSL'
-- Header
PROMPT
PROMPT ------------------------------------------------------------
PROMPT -- Database Connection Info --
PROMPT ------------------------------------------------------------
-- Main query
SELECT
d.name AS db_name,
i.instance_name,
i.host_name,
CASE
WHEN SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') = 'tcps'
THEN 'Yes (Port 5433, SSL enabled)'
ELSE 'No (SSL not enabled)'
END AS ssl_status
FROM
v$database d,
v$instance i,
v$session s
WHERE
s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1); -- Current session
-- Diagnostic note for Oracle 11g
SELECT
CASE
WHEN banner LIKE '%11%'
THEN 'Note: SSL detection via NETWORK_SERVICE_BANNER is unavailable in Oracle 11g. Using SYS_CONTEXT(''USERENV'', ''NETWORK_PROTOCOL''). ' ||
'If SSL status is incorrect, verify listener.ora for TCPS configuration or provide custom SSL indicator.'
ELSE NULL
END AS diagnostic
FROM
v$version
WHERE
ROWNUM = 1;
-- Footer
PROMPT
PROMPT ------------------------------------------------------------
PROMPT -- Report Completed --
PROMPT ------------------------------------------------------------
-- Reset SQL*Plus settings
CLEAR COLUMNS
SET FEEDBACK ON
SET LINESIZE 80
SET PAGESIZE 14
No comments:
Post a Comment