Oracle Database Name And Other Details (database server OS):

1. Database Name, Oracle Version Queries

SELECT SYS.database_name FROM DUAL;

SELECT SUBSTR(global_name,1,30) Database_name FROM sys.global_name;

SELECT name, TO_CHAR(created,'mm/dd/yyyy hh24:mi:ss') created FROM v$database;

SELECT * FROM product_component_version;

SELECT SUBSTR(property_name,1,30) property_name, SUBSTR(property_value,1,30) property_value, SUBSTR(description,1,40) description
FROM sys.database_properties
WHERE property_name = 'GLOBAL_DB_NAME'
OR property_name = 'NLS_RDBMS_VERSION'
OR property_name = 'NLS_DATE_FORMAT'
OR property_name ='NLS_TIME_TZ_FORMAT'
OR property_name ='NLS_TIMESTAMP_FORMAT'
OR property_name ='NLS_CALENDAR'
OR property_name ='NLS_ISO_CURRENCY'
OR property_name ='NLS_CURRENCY'
OR property_name ='DEFAULT_TEMP_TABLESPACE' ;

SELECT banner FROM v$version;

SELECT DBMS_UTILITY.port_string OS_of_Server FROM DUAL;

Oracle Database Users:

2a. Currently logged users in the database (useful in 8.x versions and higher)

SELECT
  db.name||' - '||
  SUBSTR(s.osuser,1,15)||'/'||SUBSTR(s.schemaname,1,10) dw_user,
  s.status,
  TO_CHAR(s.logon_time,'HH24:MI:SS AM') dw_login,
  SUBSTR(s.program,1,15) application
FROM v$session s,
  (SELECT name FROM v$database) db
WHERE s.type != 'BACKGROUND'
AND s.osuser is not null
ORDER BY 1
;

SELECT
  SUBSTR(machine||'/'||username,1,20) username,
  DECODE(audsid,userenv('sessionid'),UPPER(osuser)) nw_user,
  type||': '||status "Type/Status",
  SUBSTR(UPPER(osuser),1,15)||'/'||SUBSTR(schemaname,1,12) usr,
  SUBSTR(REPLACE(program,'C:\WINNT\Profiles\',''),1,25) Program,
  TO_CHAR(logon_time,'HH24:MI:SS AM') TIME
FROM v$session
WHERE type != 'BACKGROUND'
AND osuser IS NOT NULL
ORDER BY 1
;

2b. User ID, application/program and process details

SELECT osuser, program, process, sid
FROM v$session
WHERE sid =
  (SELECT sid
  FROM v$mystat
  WHERE ROWNUM = 1);

Oracle Database and Object Sizes:

3a. Database Size

SELECT current_timestamp as_of,
  ROUND(SUM(us.bytes)/1073741824,3) used_size_gb,
  ROUND(SUM(ts.bytes)/1073741824,3) total_db_size_gb
FROM sys.space_used us, sys.space_total ts
WHERE ts.tablespace_name = us.tablespace_name (+)
;

3b. Object Sizes In The Database (8i and higher)

SELECT
  SUBSTR(segment_name,1,25) segment_name,
  SUBSTR(segment_type,1,12) segment_type,
  SUBSTR(tablespace_name,1,10) tablespace,
  extents,
  ROUND(bytes/1048576,2) space_mb,
  ROUND(bytes/1073741824,2) space_gb
FROM sys.dba_segments
WHERE segment_name like UPPER('%&p_OBJECT%')
AND owner = UPPER('&p_OWNER')
ORDER BY SUBSTR(segment_name,1,25) ;

3c. Object size and Creation Date

SELECT
  ao.owner,
  ao.object_name,
  TO_CHAR(ao.created,'mm/dd/yyyy hh24:mi:ss') created_on,
  ROUND(SUM(ds.bytes)/1048576,2) size_in_mb,
  ROUND(SUM(ds.bytes)/1073741824,2) size_in_gb
FROM sys.all_objects ao,
  sys.dba_segments ds
WHERE ds.segment_name = ao.object_name
AND ao.object_name LIKE UPPER('&p_obj_name%')
AND ao.object_type = UPPER('&p_obj_type')
GROUP BY ao.owner, ao.object_name, ao.created
;

Oracle Database Details:

4. Database Name, and various other settings: First query the v$parameter to find names and values and see necessary details as shown in second query

SELECT name, SUBSTR(value,1,30) value FROM v$parameter;

SELECT
  SUBSTR(name,1.30),
  SUBSTR(value,1,30) value
FROM v$parameter
WHERE name IN ('db_name', 'instance_name', 'utl_file_dir', 'optimizer_features_enable');

Oracle Database Performance:

5. Hit rate computation

SELECT
  TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') as_of,
  SUM(pins) hits,
  SUM(reloads) misses,
  SUM(pins)/SUM(pins+reloads) hit_ratio
FROM v$librarycache;

6. Database file reads and Writes

SELECT
  DECODE(GROUPING(SUBSTR(df.file_name,1,40)),
   0,SUBSTR(df.file_name,1,40),
   'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')') details,
  SUM(fs.phyrds) reads,
  SUM(fs.phywrts) writes
FROM sys.v_$filestat fs, sys.dba_data_files df
WHERE fs.file# = df.file_id
GROUP BY ROLLUP(SUBSTR(df.file_name,1,40));

7a. Objects that have been invalid for greater than N days (30, 60, etc.)

SELECT
  owner, object_name, object_type, status, last_ddl_time, TRUNC(SYSDATE - last_ddl_time) days
FROM all_objects
WHERE status <> 'VALID'
AND TRUNC(SYSDATE - last_ddl_time) > &p_days;

7b. Procedure to compile all invalid objects that belong to a specific user using the DBMS_UTILITY COMPILE_SCHEMA feature

CREATE OR REPLACE PROCEDURE Compile_All_Invalid(p_owner IN dba_objects.owner%TYPE) AS
BEGIN
   DBMS_UTILITY.COMPILE_SCHEMA(p_owner, FALSE);
EXCEPTION WHEN OTHERS THEN RAISE;
END Compile_All_Invalid;

For corrections or feedback, other useful links please contact webmaster

  74058