Oracle Database Information
SELECT sys.database_name||': '|| UTL_INADDR.get_host_name||' ['|| UTL_INADDR.get_host_address||']' database_info FROM DUAL
UNION
SELECT banner database_info FROM sys.v_$version
UNION
SELECT DBMS_UTILITY.port_string database_info FROM DUAL
ORDER BY database_info;

Typical DB Information

Oracle Database Options
SELECT ' '||parameter||' -> '||value db_options FROM sys.v_$option ORDER BY db_options;

Typical DB Options

Oracle Database Cluster Options
DECLARE v_bol BOOLEAN := DBMS_UTILITY.IS_CLUSTER_DATABASE;
BEGIN
IF v_bol = TRUE THEN
DBMS_OUTPUT.PUT_LINE('RAC Database');
ELSE
DBMS_OUTPUT.PUT_LINE('Not RAC Database');
END IF;
END;
/
Oracle DB Cluster

Oracle Database Files
SELECT
file# file_no, name file_name,
ROUND(bytes/1024/1024/1024,3) size_mb, blocks, status,
TO_CHAR(online_time,'mm/dd/yyyy hh24:mi:ss') online_time,
TO_CHAR(last_time,'mm/dd/yyyy hh24:mi:ss') last_time,
TO_CHAR(creation_time,'mm/dd/yyyy hh24:mi:ss') creation_time
FROM v$datafile;

Oracle Database Warning Setting
SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;

Oracle Database Instance
SELECT instance_number, instance_name, instance_role, host_name, version, parallel,
TO_CHAR(startup_time,'mm/dd/yyyy hh24:mi:ss') startup_time,
status, database_status, active_state, logins, shutdown_pending, archiver, blocked
FROM gv$instance;

Oracle Database Object Size
SELECT SUBSTR(DECODE(GROUPING(segment_type), 0, segment_type,
'Total ('||To_Char(Sysdate,'mm/dd/yyyy hh24:mi:ss')||')'),1,30) Segment_Type_Detail,
COUNT(*) obj_count,
ROUND(Sum(Bytes)/1024/1024/1024,4) Available_gb
FROM dba_segments
GROUP BY ROLLUP(segment_type);

Oracle DB Object Size

Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: 10/07/2013

  1085