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

スクリプトファイル

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

fileexamine.sql

使い方

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

#pre{{ sqlplus / as sysdba @examine }}

出力例

#pre{{ 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 }}



添付ファイル: fileexamine.sql 729件 [詳細]

トップ   差分 履歴 リロード   一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2011-01-29 (土) 15:27:48