Simple database environment detection

Database environment simple detection conn/as sysdba -- output the file to the specified path spool c: \ output www.2cto.com -- (set echo on outputs all query statements) set line 150 -- disable tti off on the header of each page -- disable bti off on the bottom of each page. set pagesize 25tti '****** database version ******' -- database version: select banner database version from v $ version; tti' ****** database information ******* '-- Database name, creation date, read/write mode, select name in archive mode, database creation date, log_mode archive mode, open_mode read/write mode, platform_name system platform from v $ database; tti' ***** instance information **** ** '-- Number of instances, instance name, instance status select instance_number, instance name, host_name host name, status instance status from v $ instance; tti' ****** size of the data block used by the database ****** '-- view the size of the data block used by the database. show parameter block_size; tti' ***** SGA distribution ******* '-- SGA distribution (show sga can also be used) select name category, round (value/1024/1024, 2) size from v $ sga; tti' ****** Log File ******* '-- number of log groups, number of group members, and size of each group select group # group number, number of members, bytes/1024/1024 from v $ log order Group No.; www.2cto.com -- log group, status, online status, file location and name col location for a70 select group # group No., status, type online status, member position from v $ logfile order by group number; tti' ***** control file ******* '-- control the number, name, and storage path of the file. col path for a70 select name path from v $ controlfile; tti' ****** temporary file ******* '-- number, status, size, name, and path of temporary files select file # file number, status, bytes/1024/1024 size, name path from v $ tempfile; tti' ****** data file ******* '-- data file number, name and storage path, tablespace where the file is located, size, and status col file name A65 col tablespace for a15 col file number for 999 select. file_id file number,. file_name file name,. tablespace_name tablespace,. bytes/1024/1024 size,. status, B. status Online, B. CREATION_TIME from dba_data_files a, v $ datafile B where. file_name = B. name order by. file_id; tti' ****** tablespace ******** '-- view the tablespace management mode (local management or data dictionary management ), the type of the tablespace to which the table belongs (system tablespace or unique tablespace), and the management method of the segment (automatic increase or decrease or manual management ). Select tablespace_name tablespace, extent_management segment management, allocation_type tablespace type, segment_space_management segment space management from dba_tablespaces; tti' ***** table space usage ******* '-- query the table space size and usage (used space, available space, and percentage of remaining space) select f. tablespace_name,. total,. total-f.free "used (M)", f. free "remaining", round (f. free/. total) * 100) "percentage remaining" from (select tablespace_name, sum (bytes/1024/1024) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round (sum (bytes/1024/1024) free from dba_free_space group by tablespace_name) f where. tablespace_name = f. tablespace_name (+) order by "percentage remaining "; -- set echo offspool off www.2cto.com -- % % & Database Report & % % conn/as sysdba -- Output file to somewhere spool c: \ output set line 150 tti off bti off set pagesize 25tti '****** Database Version ******* 'select banner AS DBVersion from v $ version; tti' ***** Database information ******* 'select name DBName, created AS Createdate, log_mode AS ArchMode, open_mode RWMode, platform_name from v $ database; tti' ***** Instance information ******* 'select instance_number AS IstNum, instance_name AS InsName, host_name, status InsStatus from v $ instance; tti' ***** Database Block_size ****** 'show parameter block_size; tti' ****** SGA Allot ****** 'select name Type, round (value/1024/1024, 2) MB from v $ sga; tti' ****** Log Files ******* 'select group # AS GroupNum, members AS GroupMem, bytes/1024/1024 MB from v $ log order by GroupNum; www.2cto.com col Locate for a70 select group # AS GroupNum, status, type, member AS Locate from v $ logfile order by GroupNum; tti' * Control Files * 'col Locate for a70 select name AS Locate from v $ controlfile; tti' ****** Temp Files ******* 'select file #, status AS Status, bytes/1024/1024 MB, name Locate from v $ tempfile; tti' * Data File * 'col FileName for a65 col TableSP for a15 col FileNum for 999 select. file_id AS FileNum,. file_name AS FileName,. tablespace_name AS TableSP,. bytes/1024/1024 MB,. status AS Status, B. status AS status, B. CREATION_TIME from dba_data_files a, v $ datafile B where. file_name = B. name order by. file_id; tti' ****** Tablespace ****** 'select tablespace_name AS TName, extent_management AS SegMana, allocation_type AS TType, segment_space_management AS SegSMana from dba_tablespaces; tti' ****** TUsed ******* 'select f. tablespace_name,. total "total (M)",. total-f.free "Used (M)", f. free "Leave (M)", round (f. free/. total) * 100) "Leave (%)" from (select tablespace_name, sum (bytes/1024/1024) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round (sum (bytes/1024/1024) free from dba_free_space group by tablespace_name) f where. tablespace_name = f. tablespace_name (+) order by "Leave (%)"; -- set echo offspool off