SQLs for DBA

Tablespace Usage:

SELECT d.tablespace_name,
  ROUND (NVL (a.bytes / 1024 / 1024, 0), 2) size_in_mb,
  ROUND (DECODE (d.contents, 'UNDO', NVL (u.bytes, 0) / 1024 / 1024, NVL (a.bytes - NVL (f.bytes, 0), 0) / 1024 / 1024), 2) usage_in_mb,
  ROUND (DECODE (d.contents, 'UNDO', NVL (u.bytes / a.bytes * 100, 0), NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0)), 2) usage_perc,
  a.autoext,
  ROUND (DECODE (d.contents, 'UNDO', NVL (a.bytes - NVL (u.bytes, 0), 0) / 1024 / 1024, NVL (f.bytes, 0) / 1024 / 1024), 2) free_in_mb,
  d.status, a.num_of_file, d.contents, d.extent_management, d.segment_space_management
FROM sys.dba_tablespaces d,
  (
    SELECT tablespace_name,
      SUM (bytes) bytes, COUNT (file_id) num_of_file,
      DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
    FROM dba_data_files
    GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name, SUM (bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f,
  (
    SELECT tablespace_name, SUM (bytes) bytes
    FROM ( 
      SELECT tablespace_name, SUM (bytes) bytes, status FROM dba_undo_extents WHERE status = 'ACTIVE'
      GROUP BY tablespace_name, status
     
      UNION ALL
      SELECT tablespace_name, SUM (bytes) bytes, status FROM dba_undo_extents WHERE status = 'UNEXPIRED'
      GROUP BY tablespace_name, status
    )
    GROUP BY tablespace_name
  ) u
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND d.tablespace_name = u.tablespace_name(+)
  AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')

UNION ALL
SELECT d.tablespace_name,
  NVL (a.bytes / 1024 / 1024, 0) size_in_MB,
  ROUND (NVL (t.bytes, 0) / 1024 / 1024, 2) usage_in_MB,
  ROUND (NVL (t.bytes / a.bytes * 100, 0), 2) usage_perc,
  a.autoext,
  ROUND ((NVL (a.bytes, 0) / 1024 / 1024 - NVL (t.bytes, 0) / 1024 / 1024), 2) free_in_MB,
  d.status, a.COUNT, d.contents, d.extent_management, d.segment_space_management
FROM sys.dba_tablespaces d,
  (
    SELECT tablespace_name, SUM (bytes) bytes, COUNT (file_id) COUNT,
      DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
    FROM dba_temp_files
    GROUP BY tablespace_name
  ) a,
  (
    SELECT ss.tablespace_name, SUM ( (ss.used_blocks * ts.blocksize)) bytes
    FROM gv$sort_segment ss, sys.ts$ ts
    WHERE ss.tablespace_name = ts.name
    GROUP BY ss.tablespace_name
  ) t
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY'
ORDER BY 1 desc

Configuration files and Log files for Oracle Business Intelligence

Log files (diagnostics)

<OBI_LOGS> = /u01/fmw/instances/instance1/diagnostics/logs

Log Name (.log)	Location
NQQuery	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
NQServer	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
NQSAdminTool	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
NQSUDMLExec	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
RPD migration utility	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
SAW log	<OBI_LOGS>/OracleBIPresentationServicesComponent/coreapplication_obips1
JavaHost	<OBI_LOGS>/OracleBIJavaHostComponent/coreapplication_obijh1
NQScheduler	<OBI_LOGS>/OracleBISchedulerComponent/coreapplication_obisch1
NQCluster	<OBI_LOGS>/OracleBIClusterControllerComponent/coreapplication_obiccs1
ODBC	<OBI_LOGS>/OracleBIODBCComponent/coreapplication_obips1
OPMN system	<OBI_LOGS>/OPMN/opmn/

Configuration files

BI Component	Configuration File	File Location
Oracle BI Server	NQSConfig.INI logconfig.xml	ORACLE_INSTANCE\config\OracleBIServerComponent\coreapplication_obisn For example:   \instances\instance1\config\OracleBIServerComponent\coreapplication_obis1 Note: Although DBFeatures.ini is also located in this directory, do not edit this file directly. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about how to edit features for a database.
Oracle BI Presentation Services	instanceconfig.xml	ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn For example:   \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 Note: Do not add elements to the instanceconfig.xml file unless you are overriding the stated default values. Override only those settings that are necessary for configuring the system to meet the needs of your organization.
Cluster Controller	ClusterConfig.xml ccslogging.xml	ORACLE_INSTANCE\config\OracleBIApplication\coreapplication For example:   \instances\instance1\config\OracleBIApplication\coreapplication
Oracle BI Scheduler	instanceconfig.xml ccslogging.xml (for Cluster Controller)	ORACLE_INSTANCE\config\OracleBISchedulerComponent\coreapplication_obischn For example:   \instances\instance1\config\OracleBISchedulerComponent\coreapplication_obisch1
JavaHost	config.xml logging_config.xml	ORACLE_INSTANCE\config\OracleBIJavaHostComponent\coreapplication_obijhn For example:   \instances\instance1\config\OracleBIJavaHostComponent\coreapplication_obijh1
Oracle BI Presentation Services Plug-in	bridgeconfig.properties	MW_HOME\user_projects\domains\domain_name\config\fmwconfig\biinstances\coreapplication For example:   mw_home\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication