Front page   Edit Freeze Diff Upload Copy Rename Reload   New List of pages Search Recent changes   Help   RSS of recent changes

技術文書/Oracle/11g/設定の確認

Last-modified: Sat, 29 Jan 2011 15:27:48 JST (4322d)
Top > 技術文書 > Oracle > 11g > 設定の確認


作ったDBの設定を確認したいときのスクリプトを書いてみました。

スクリプトファイル EditEdit(GUI)

自由にご利用ください。無保証です。

fileexamine.sql

使い方 EditEdit(GUI)

sqlplus から実行します。examine.log に結果が出力されます。

sqlplus / as sysdba @examine

出力例 EditEdit(GUI)

SQL> select to_char(sysdate, 'YY/MM/DD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY/MM/DDHH24:MI:SS')                                           
---------------------------------------------------                             
09/04/13 23:53:24                                                               

SQL> 
SQL> -- control files
SQL> alter database backup controlfile to trace;

データベースが変更されました。

SQL> 
SQL> -- version
SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production    
PL/SQL Release 11.1.0.6.0 - Production                                          
CORE    11.1.0.6.0      Production                                                      
TNS for Linux: Version 11.1.0.6.0 - Production                                  
NLSRTL Version 11.1.0.6.0 - Production                                          

SQL> 
SQL> -- instance
SQL> col instance_name format a20
SQL> col host_name format a20
SQL> col status format a10
SQL> select instance_number, instance_name, host_name, status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME        HOST_NAME            STATUS                
--------------- -------------------- -------------------- ----------            
              1 orcl                 ora1                 OPEN                  

SQL> 
SQL> -- database
SQL> col name format a10
SQL> col db_unique_name format a10
SQL> col log_mode format a20
SQL> select dbid, name, db_unique_name, log_mode from v$database;

      DBID NAME       DB_UNIQUE_ LOG_MODE                                       
---------- ---------- ---------- --------------------                           
1190527688 ORCL       orcl       NOARCHIVELOG                                 

SQL> 
SQL> -- session
SQL> col sid format 99999
SQL> col serial# format 99999
SQL> col username format a10
SQL> col machine format a10
SQL> col osuser format a10
SQL> col program format a20
SQL> select sid, serial#, username, machine, osuser, program, status from v$session;

   SID SERIAL# USERNAME   MACHINE    OSUSER     PROGRAM              STATUS     
------ ------- ---------- ---------- ---------- -------------------- ---------- 
   128     193            ora1       oracle     oracle@ora1 (q003)   ACTIVE     
   135       1            ora1       oracle     oracle@ora1 (q000)   ACTIVE     
   138     137            ora1       oracle     oracle@ora1 (W000)   ACTIVE     
   139     361 SYS        ora1       oracle     sqlplus@ora1 (TNS V1 ACTIVE     
                                                -V3)                            
                                                                                
   149       1            ora1       oracle     oracle@ora1 (QMNC)   ACTIVE     
   151       1            ora1       oracle     oracle@ora1 (FBDA)   ACTIVE     
   153      10            ora1       oracle     oracle@ora1 (SMCO)   ACTIVE     
   155     240            ora1       oracle     oracle@ora1 (CJQ0)   ACTIVE     
   156       1            ora1       oracle     oracle@ora1 (MMNL)   ACTIVE     
   157       1            ora1       oracle     oracle@ora1 (MMON)   ACTIVE     
   158       1            ora1       oracle     oracle@ora1 (RECO)   ACTIVE     
   159       1            ora1       oracle     oracle@ora1 (SMON)   ACTIVE     
   160       1            ora1       oracle     oracle@ora1 (CKPT)   ACTIVE     
   161       1            ora1       oracle     oracle@ora1 (LGWR)   ACTIVE     
   162       1            ora1       oracle     oracle@ora1 (MMAN)   ACTIVE     
   163       1            ora1       oracle     oracle@ora1 (DIA0)   ACTIVE     
   164       3            ora1       oracle     oracle@ora1 (DBW0)   ACTIVE     
   165       1            ora1       oracle     oracle@ora1 (PSP0)   ACTIVE     
   166       1            ora1       oracle     oracle@ora1 (DBRM)   ACTIVE     
   167       1            ora1       oracle     oracle@ora1 (DIAG)   ACTIVE     
   168       1            ora1       oracle     oracle@ora1 (VKTM)   ACTIVE     
   169       1            ora1       oracle     oracle@ora1 (PMON)   ACTIVE     

22行が選択されました。

SQL> 
SQL> -- configuration in control file
SQL> col name format a50
SQL> select * from v$controlfile;

STATUS     NAME                                               IS_RECOVE         
---------- -------------------------------------------------- ---------         
BLOCK_SIZE FILE_SIZE_BLKS                                                       
---------- --------------                                                       
           /u02/oradata/orcl/control01.ctl                    NO                
     16384            584                                                       
                                                                                
           /u02/oradata/orcl/control02.ctl                    NO                
     16384            584                                                       
                                                                                

SQL> 
SQL> -- SGA
SQL> col name format a20
SQL> col value format 999999999999
SQL> select * from v$sga;

NAME                         VALUE                                              
-------------------- -------------                                              
Fixed Size                 2143984                                              
Variable Size            222300432                                              
Database Buffers          83886080                                              
Redo Buffers               4829184                                              

SQL> 
SQL> -- tablespace usage
SQL> col tablespace_name format a20
SQL> col used format 99.99999999
SQL> select
  2      tablespace_name,
  3      (1-sumfree.blocks/sumdat.blocks)*100 as used
  4  from (
  5      select tablespace_name, sum(blocks) as blocks
  6      from dba_data_files
  7      group by tablespace_name
  8  ) sumdat
  9  join (
 10      select tablespace_name, sum(blocks) as blocks
 11      from dba_free_space
 12      group by tablespace_name
 13  ) sumfree
 14  using (tablespace_name)
 15  order by tablespace_name;

TABLESPACE_NAME              USED                                               
-------------------- ------------                                               
SYSAUX                45.01250000                                               
SYSTEM                35.37343750                                               
UNDOTBS                6.88920455                                               
USERS                   .06250000                                               

SQL> 
SQL> select
  2      tablespace_name,
  3      (sumdat.blocks/(sumdat.blocks+sumfree.blocks))*100 as used
  4  from (
  5      select tablespace_name, sum(bytes_used) as blocks
  6      from v$temp_space_header
  7      group by tablespace_name
  8  ) sumdat
  9  join (
 10      select tablespace_name, sum(bytes_free) as blocks
 11      from v$temp_space_header
 12      group by tablespace_name
 13  ) sumfree
 14  using (tablespace_name)
 15  order by tablespace_name;

TABLESPACE_NAME              USED                                               
-------------------- ------------                                               
TEMP                  18.00000000                                               

SQL> 
SQL> -- size on tablespace
SQL> col tablespace_name format a15
SQL> col sum(bytes) format 999999999999
SQL> select tablespace_name, sum(bytes) from dba_data_files
  2  group by tablespace_name;

TABLESPACE_NAME    SUM(BYTES)                                                   
--------------- -------------                                                   
SYSAUX              524288000                                                   
UNDOTBS             276824064                                                   
USERS               104857600                                                   
SYSTEM              524288000                                                   

SQL> 
SQL> -- size on temporary tablespace
SQL> select tablespace_name, bytes from dba_temp_files;

TABLESPACE_NAME      BYTES                                                      
--------------- ----------                                                      
TEMP             104857600                                                      

SQL> 
SQL> -- extents on tablespace
SQL> select tablespace_name, initial_extent, next_extent, min_extents, max_extents,
  2  pct_increase, status from dba_tablespaces;

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE 
--------------- -------------- ----------- ----------- ----------- ------------ 
STATUS                                                                          
----------                                                                      
SYSTEM                   16384       16384           1         505           50 
ONLINE                                                                          
                                                                                
SYSAUX                   65536                       1  2147483645              
ONLINE                                                                          
                                                                                
UNDOTBS                  65536                       1  2147483645              
ONLINE                                                                          
                                                                                
TEMP                   1048576     1048576           1                        0 
ONLINE                                                                          
                                                                                
USERS                    65536                       1  2147483645              
ONLINE                                                                          
                                                                                

SQL> 
SQL> -- datafiles on tablespace
SQL> col tablespace_name format a15
SQL> col file_name format a45
SQL> col bytes format 999999999999
SQL> select tablespace_name, bytes, file_name from dba_data_files
  2  order by tablespace_name, file_name;

TABLESPACE_NAME         BYTES FILE_NAME                                         
--------------- ------------- ---------------------------------------------     
SYSAUX              524288000 /u02/oradata/orcl/sysaux01.dbf                    
SYSTEM              524288000 /u02/oradata/orcl/system01.dbf                    
UNDOTBS             276824064 /u02/oradata/orcl/undotbs01.dbf                   
USERS               104857600 /u02/oradata/orcl/users01.dbf                     

SQL> 
SQL> -- users
SQL> col default_tablespace format a20
SQL> col temporary_tablespace format a20
SQL> select username, default_tablespace, temporary_tablespace, created
  2  from dba_users
  3  order by username;

USERNAME   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE CREATED                    
---------- -------------------- -------------------- --------                   
DBSNMP     SYSAUX               TEMP                 08-08-14                   
DIP        USERS                TEMP                 08-08-14                   
ORACLE_OCM USERS                TEMP                 08-08-14                   
OUTLN      SYSTEM               TEMP                 08-08-14                   
SYS        SYSTEM               TEMP                 08-08-14                   
SYSTEM     SYSTEM               TEMP                 08-08-14                   
TSMSYS     USERS                TEMP                 08-08-14                   

7行が選択されました。

SQL> 
SQL> -- logfile
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#         BYTES    MEMBERS ARCHIVED  STATUS      
---------- ---------- ---------- ------------- ---------- --------- ----------  
FIRST_CHANGE# FIRST_TI                                                          
------------- --------                                                          
         1          1         67     104857600          1 NO        INACTIVE    
      1940857 09-04-11                                                          
                                                                                
         2          1         68     104857600          1 NO        CURRENT     
      1962013 09-04-12                                                          
                                                                                
         3          1         66     104857600          1 NO        INACTIVE    
      1923899 09-04-10                                                          
                                                                                

SQL> col number format a30
SQL> col member format a45
SQL> select * from v$logfile;

    GROUP# STATUS     TYPE                                                      
---------- ---------- ---------------------                                     
MEMBER                                        IS_RECOVE                         
--------------------------------------------- ---------                         
         1            ONLINE                                                    
/u02/oradata/orcl/redo01.log                  NO                                
                                                                                
         2            ONLINE                                                    
/u02/oradata/orcl/redo02.log                  NO                                
                                                                                
         3            ONLINE                                                    
/u02/oradata/orcl/redo03.log                  NO                                
                                                                                

SQL> 
SQL> -- initial parameters
SQL> col name format a40
SQL> col value format a30
SQL> select name, value from v$parameter
  2  order by name;

NAME                                     VALUE                                  
---------------------------------------- ------------------------------         
O7_DICTIONARY_ACCESSIBILITY              FALSE                                  
active_instance_count                                                           
aq_tm_processes                          0                                      
archive_lag_target                       0                                      
(略)

289行が選択されました。

SQL> 
SQL> -- NLS parameters
SQL> col parameter format a30
SQL> select * from v$nls_parameters;

PARAMETER                      VALUE                                            
------------------------------ ------------------------------                   
NLS_LANGUAGE                   JAPANESE                                         
NLS_TERRITORY                  JAPAN                                            
NLS_CURRENCY                   \                                                
NLS_ISO_CURRENCY               JAPAN                                            
(略)                

19行が選択されました。

SQL> 
SQL> spool off


URL B I U SIZE Black Maroon Green Olive Navy Purple Teal Gray Silver Red Lime Yellow Blue Fuchsia Aqua White