-- FILE: oa_info.sql -- -- DESCRIPTION: -- This script will provide a basic layout of an Oracle Applications database. -- -- AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com -- Copyright (C) 1998 AppsDBA Consulting -- DATE: 07/22/97 -- -- REQUIREMENTS: -- -- MODIFICATIONS: -- 08/12/97, A. Rivenes, Added free space mapping -- 09/04/97, A. Rivenes, Added product group info, ORACLE ID mapping -- 09/17/97, A. Rivenes, Added SGA information -- 11/19/97, A. Rivenes, Added formatting for OA queries -- 12/23/97, A. Rivenes, Modified formatting to match dbinfo for space summaries. -- 11/04/98, A. Rivenes, Added options to fnd_product_group query -- 11/06/98, A. Rivenes, Updated DECODE for product installations and added -- status information. -- 09/13/99, A. Rivenes, Added additional queries to match adutconf.sql. -- -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program. If not, see . -- -- SET PAGESIZE 9999; SET LINESIZE 132; SET VERIFY off; SET FEEDBACK off; ACCEPT spoolf CHAR PROMPT 'Enter spool file name --> '; SPOOL &spoolf; -- PROMPT ; PROMPT > Database Name/Status ; SELECT db.name "DB Name", SUBSTR(gn.global_name,1,30) "Global Name", db.log_mode "Archive Status" FROM v$database db, global_name gn; -- PROMPT ; PROMPT > Database Compatibility ; COLUMN banner HEADING 'Oracle Version' FORMAT A60; COLUMN compat HEADING 'Compatible' FORMAT A18 TRUNCATE; SELECT banner, compat FROM ( SELECT banner FROM v$version WHERE UPPER(banner) LIKE 'ORACLE%'), ( SELECT value compat FROM v$parameter WHERE name = 'compatible') ; -- PROMPT ; PROMPT > Database Options ; COLUMN parameter HEADING 'Parameter' FORMAT a30; COLUMN value HEADING 'Option' FORMAT a6; SELECT SUBSTR(parameter,1,30) parameter, SUBSTR(value,1,6) value FROM v$option; -- PROMPT; PROMPT > SGA memory allocation ; COLUMN name HEADING 'Name ' FORMAT A30; COLUMN value HEADING 'Size' FORMAT 999,999,999; SELECT name, value FROM v$sga; -- PROMPT ; PROMPT > Control File Locations:; SELECT SUBSTR(name,1,60) "Control File", status FROM v$controlfile; -- PROMPT ; PROMPT > Redo Logs and Archive Status; SELECT SUBSTR(vlf.member,1,40) "Member Name", vl.group# "Group", vl.status "Status", vl.archived "Archived", vl.bytes / 1024 "Size (K)", vl.sequence# "Sequence" FROM v$logfile vlf, v$log vl WHERE vlf.group# = vl.group# ORDER BY 1, vl.group#, vlf.member; -- PROMPT ; PROMPT > Datafile Allocation (by tablespace) ; COLUMN tspace HEADING 'Tablespace' FORMAT A25; COLUMN fname HEADING 'File Name' FORMAT A50; COLUMN fsize HEADING 'Total Space' FORMAT A11; BREAK ON tspace ON fname; SELECT tablespace_name tspace, file_name fname, TRUNC(SUM(bytes)/1024/1024,2)||' MB' fsize FROM sys.dba_data_files GROUP BY tablespace_name, file_name ORDER BY tablespace_name; -- PROMPT ; PROMPT > Total Tablespace Allocated ; SET HEAD off; SELECT TRUNC(SUM(bytes)/1024/1024/1024,2)||' GB' FROM sys.dba_data_files; SET HEAD on; -- PROMPT ; PROMPT > Total Segment Space Allocated By Objects ; SET HEAD off; SELECT TRUNC(SUM(bytes)/1024/1024/1024,2)||' GB' FROM sys.dba_segments; SET HEAD on; -- PROMPT ; PROMPT > Rollback Segment Details ; COLUMN pct_increase HEADING '%Incr%' FORMAT 999; COLUMN extends HEADING 'Ext' FORMAT 999; SELECT SUBSTR(seg.segment_name,1,6) "RB Seg", seg.owner "Owner", SUBSTR(seg.tablespace_name,1,10) "Tablespace", seg.initial_extent "Initial", seg.next_extent "Next", seg.pct_increase , SUBSTR(seg.status,1,7) "Status", rs.optsize "Optimal", rs.extends FROM sys.dba_rollback_segs seg, v$rollstat rs WHERE seg.segment_id = rs.usn; -- PROMPT; PROMPT > Tablespace Allocation Details; COLUMN created_size HEADING 'Create (MB)' FORMAT 9999,999.99; COLUMN max_free HEADING 'Max Free (MB)' FORMAT 9999,999.99; COLUMN tot_free HEADING 'Tot Free (MB)' FORMAT 9999,999.99; COLUMN pct_free HEADING 'Free %' FORMAT 999.999; SELECT SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name", tot_create_blk /1024/1024 created_size, ROUND(max_free_blk / 1024/1024,2) max_free, ROUND(tot_free_blk / 1024/1024,2) tot_free, ROUND((tot_free_blk/tot_create_blk*100),3) pct_free FROM ( SELECT tablespace_name, SUM(bytes) tot_create_blk FROM sys.dba_data_files GROUP BY tablespace_name ) data_files, ( SELECT tablespace_name, MAX(bytes) max_free_blk, SUM(bytes) tot_free_blk FROM sys.dba_free_space GROUP BY tablespace_name ) free_space WHERE data_files.tablespace_name = free_space.tablespace_name ORDER BY data_files.tablespace_name; -- PROMPT ; PROMPT > Count of Objects By Owner - performs a count of objects by ; PROMPT > the owner. ; COLUMN name HEADING 'Owner' FORMAT A12 TRUNCATE; COLUMN tables HEADING 'Table' FORMAT 9,999; COLUMN clustr HEADING 'Cluster' FORMAT 9,999; COLUMN indexes HEADING 'Index' FORMAT 9,999; COLUMN views HEADING 'View' FORMAT 9,999; COLUMN synonm HEADING 'Synonym' FORMAT 9,999; COLUMN sequen HEADING 'Sequence' FORMAT 9,999; COLUMN proced HEADING 'Procedure' FORMAT 9,999; COLUMN funct HEADING 'Function' FORMAT 9,999; COLUMN packge HEADING 'Package' FORMAT 9,999; COLUMN triger HEADING 'Trigger' FORMAT 9,999; -- SOURCE Neil 19-FEB-1997 SELECT u.name, SUM(DECODE(o.type, 2, 1, 0 )) tables, SUM(DECODE(o.type, 3, 1, 0 )) clustr, SUM(DECODE(o.type, 1, 1, 0 )) indexes, SUM(DECODE(o.type, 4, 1, 0 )) views, SUM(DECODE(o.type, 5, 1, 0 )) synonm, SUM(DECODE(o.type, 6, 1, 0 )) sequen, SUM(DECODE(o.type, 7, 1, 0 )) proced, SUM(DECODE(o.type, 8, 1, 0 )) funct, SUM(DECODE(o.type, 9, 1, 0 )) packge, SUM(DECODE(o.type, 12, 1, 0 )) triger FROM sys.obj$ o, sys.user$ u WHERE owner# = u.user# GROUP BY u.name ; -- PROMPT ; PROMPT > Total Space By Segment Owner ; COLUMN owner HEADING 'Owner' FORMAT A20 TRUNCATE; COLUMN totsp HEADING 'Total Space' FORMAT A16; SELECT s.owner, TO_CHAR(SUM(s.bytes)/1024/1024,'9,999,990.99')||' MB' totsp FROM dba_segments s GROUP BY s.owner ORDER BY s.owner; -- PROMPT ; PROMPT > Oracle Applications Product Status ; COLUMN prdstat HEADING 'Status' FORMAT A13; COLUMN indstat HEADING 'Industry' FORMAT A15; SELECT SUBSTR(a.APPLICATION_NAME,1,30) "Application", SUBSTR(b.PRODUCT_VERSION,1,7) "Version", DECODE(b.STATUS, 'I', 'Installed', 'S', 'Shared', 'N', 'Not Installed', 'L', 'Custom', b.status) prdstat, DECODE(b.industry, 'C', 'Commercial', 'G', 'Educational', 'B', 'Project Billing', 'P', 'Project Costing', b.industry ) indstat, -- a.application_id "Appl ID", SUBSTR(d.oracle_username,1,10) "ORACLE ID", SUBSTR(b.tablespace,1,10) "Data", SUBSTR(b.index_tablespace,1,10) "Index", SUBSTR(b.temporary_tablespace,1,10) "Temp", b.sizing_factor "Sizing" -- FROM appdemo.fnd_application a, -- appdemo.fnd_product_groups c, -- appdemo.fnd_oracle_userid d, -- appdemo.fnd_product_installations b FROM applsys.fnd_application a, applsys.fnd_product_groups c, applsys.fnd_oracle_userid d, applsys.fnd_product_installations b WHERE a.application_id = b.application_id -- AND b.status = 'I' AND b.oracle_id = d.oracle_id ORDER BY a.application_name / -- PROMPT ; PROMPT > Oracle Applications Product Groups ; COLUMN group_id HEADING 'Group ID' FORMAT 999; COLUMN group_name HEADING 'Group Name' FORMAT A44; COLUMN relname HEADING 'Release' FORMAT A14; COLUMN group_type HEADING 'Group Type' FORMAT A15; COLUMN mo HEADING 'Multi|Org' FORMAT A8; COLUMN ml HEADING 'Multi|Lingual' FORMAT A8; COLUMN mc HEADING 'Multi|Currency' FORMAT A8; SELECT product_group_id group_id, product_group_name group_name, release_name relname, product_group_type group_type, DECODE( multi_org_flag, NULL, 'Disabled', '1', 'Enabled', 'Disabled' ) mo, DECODE( multi_lingual_flag, NULL, 'Disabled', '1', 'Enabled', 'Disabled' ) ml, DECODE( multi_currency_flag, NULL, 'Disabled', '1', 'Enabled', 'Disabled' ) mc FROM applsys.fnd_product_groups -- FROM appdemo.fnd_product_groups / -- PROMPT ; PROMPT > Oracle Applications Registered Applications ; COLUMN aid HEADING 'Appl ID' FORMAT 999999; COLUMN anam HEADING 'Appl Name' FORMAT A30; COLUMN ashrt HEADING 'Appl Short|Name' FORMAT A10; COLUMN app_pre HEADING 'Appl|Prefix' FORMAT A10; COLUMN bpath HEADING 'Base Path' FORMAT A10; SELECT application_id aid, application_name anam, application_short_name ashrt, application_prefix app_pre, basepath bpath FROM fnd_application ORDER BY application_id / -- PROMPT ; PROMPT > Oracle Applications Registered ORACLE Schemas ; COLUMN oid HEADING 'Oracle ID' FORMAT 999999; COLUMN onam HEADING 'Oracle Schema' FORMAT A20; COLUMN inum HEADING 'Install_Group' FORMAT 999; COLUMN rflag HEADING 'Read|Only' FORMAT A10; SELECT oracle_id oid, oracle_username onam, install_group_num inum, DECODE(read_only_flag,'A','Enabled', 'B','Restricted', 'D','Disabled', read_only_flag) rflag FROM fnd_oracle_userid ORDER BY 1 / -- PROMPT ; PROMPT > Oracle Applications Installed Languages ; COLUMN iflag HEADING 'Installed?' FORMAT A9; COLUMN lcode HEADING 'Lang|Code' FORMAT A4; COLUMN nlang HEADING 'Language' FORMAT A30; SELECT DECODE(installed_flag,'I','Installed', 'B','Base', 'Unknown') iflag, language_code lcode, nls_language nlang FROM fnd_languages WHERE installed_flag IN ('I','B') ORDER BY installed_flag / -- SPOOL off; -- EXIT;