Simple DBA scripts

File Versions

This can be handy to pull out file versions:

select text from dba_source where name = 'PA_TXN_ACCUMS' and text like '%Header%';

This is another version

SELECT af.app_short_name "Application"      , af.filename "File Name"      , afv.file_version_id      , afv.creation_date      , afv.version "File Version"      , NVL (aap.patch_name, 'Default Installation') "Patch Number"      , '$' || fa.basepath || '/' || af.subdir "Location"   FROM apps.ad_files af      , apps.ad_patch_run_bug_actions apa      , apps.ad_patch_run_bugs aprb      , apps.ad_patch_runs apr      , apps.ad_patch_drivers apd      , apps.ad_applied_patches aap      , apps.ad_file_versions afv      , apps.fnd_application fa  WHERE af.filename = 'PABRLYRB.pls'    AND af.file_id = apa.file_id(+)    AND apa.common_action_id(+) = 4042    AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)    AND aprb.patch_run_id = apr.patch_run_id(+)    AND apr.patch_driver_id = apd.patch_driver_id(+)    AND apd.applied_patch_id = aap.applied_patch_id(+)    AND af.file_id = afv.file_id    AND af.app_short_name = fa.application_short_name;

Installed patches

SELECT *   FROM apps.ad_bugs ab  WHERE ab.bug_number = '14765798';   SELECT *   FROM apps.ad_applied_patches aap  WHERE aap.patch_name = '14765798';

Latest patchset level for a module

SELECT app_short_name        , MAX (patch_level)     FROM ad_patch_driver_minipks GROUP BY app_short_name ORDER BY app_short_name;

Product Patch Level

SELECT   fat.application_name        , fat.creation_date        , fat.description        , fpi.creation_date        , fpi.product_version        , fpi.status        , fpi.patch_level     FROM applsys.fnd_product_installations fpi        , applsys.fnd_application_tl fat    WHERE fat.application_id = fpi.application_id --     AND fat.application_name LIKE '%ontr%' --     AND fpi.status = 'I' ORDER BY 1;

Invalid Objects

-- INVALID OBJECTS -- http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php SELECT owner,        object_type,        object_name,        status FROM   dba_objects WHERE  status = 'INVALID' ORDER BY owner, object_type, object_name;

Nodes

SELECT fn.node_name        , fn.description        , fn.support_cp        , fn.support_forms        , fn.support_web        , fn.support_admin        , fn.status        , fn.server_address        , fn.HOST        , fn.domain        , fn.webhost        , fn.support_db     FROM applsys.fnd_nodes fn ORDER BY node_id;

DBA System / Session Info

SELECT release_name FROM apps.fnd_product_groups; -- current release SELECT * from v$version; SELECT NAME FROM v$database; SELECT instance_name FROM v$instance; SELECT * FROM GLOBAL_NAME; SELECT VALUE FROM v$parameter WHERE NAME = 'db_name'; SELECT TO_NUMBER(TRANSLATE(SUBSTR(VERSION, 1, 9), '1.$', '1')) FROM v$instance; SELECT s.machine FROM v$session s WHERE s.audsid = USERENV('sessionid'); SELECT GLOBAL_NAME FROM GLOBAL_NAME; SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS INSTANCE FROM DUAL; SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL;