Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Monday, 30 May 2016

Query to Get Database Details


SELECT DISTINCT 'Database' "Parameter" , banner "Value" FROM v$version
UNION ALL
SELECT 'Database Size' ,
  TO_CHAR(ROUND(SUM(bytes)/1024/1024/1024,2))
  || ' GB' "Size(GB)"
FROM dba_data_files
UNION ALL
SELECT 'Tablespaces' , TO_CHAR(COUNT(*)) FROM dba_tablespaces
UNION ALL
SELECT 'APPS Release',RELEASE_NAME FROM fnd_product_groups
UNION ALL
SELECT DISTINCT 'APPL_TOP',
  value
FROM fnd_env_context
WHERE VARIABLE_NAME       = 'APPL_TOP'
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
UNION ALL
SELECT DISTINCT 'ORACLE_HOME',
  value
FROM fnd_env_context
WHERE VARIABLE_NAME       = 'ORACLE_HOME'
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
UNION ALL
SELECT DISTINCT 'APPLCSF',
  value
FROM fnd_env_context
WHERE VARIABLE_NAME       = 'APPLCSF'
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
UNION ALL
SELECT DISTINCT 'APPLOUT',
  value
FROM fnd_env_context
WHERE VARIABLE_NAME       = 'APPLOUT'
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
UNION ALL
SELECT DISTINCT 'APPLLOG',
  value
FROM fnd_env_context
WHERE VARIABLE_NAME       = 'APPLLOG'
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
UNION ALL
SELECT DISTINCT 'DB HOST',
  value
FROM fnd_env_context
WHERE VARIABLE_NAME       = 'HOST'
AND concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM fnd_env_context
  )
UNION ALL
SELECT DISTINCT name,
  value
FROM v$parameter
WHERE name IN ('user_dump_dest','utl_file_dir','nls_language','nls_territory')
UNION ALL
SELECT 'EUL Schema',
  owner
FROM
  (SELECT owner ,
    RANK() OVER (ORDER BY created DESC) ranking
  FROM all_objects
  WHERE object_name LIKE 'EUL%DOCUMENT%'
  )
WHERE ranking = 1
UNION ALL
SELECT 'Language',value FROM v$parameter WHERE name LIKE '%nls_lang%'
Share this article :

0 comments:

Post a Comment