#topicpath
----
#contents
----
作ったDBの設定を確認したいときのスクリプトを書いてみました。
* スクリプトファイル [#uc35717b]
自由にご利用ください。無保証です。
&ref(examine.sql);
* 使い方 [#r5ee8939]
sqlplus から実行します。examine.log に結果が出力されます。
#pre{{
sqlplus / as sysdba @examine
}}
* 内容 [#c3904431]
#pre{{
-- to file
spool ./examine.log
set pages 1000
set echo on
select to_char(sysdate, 'YY/MM/DD HH24:MI:SS') from dual;
-- control files
alter database backup controlfile to trace;
-- version
select * from v$version;
-- instance
col instance_name format a20
col host_name format a20
col status format a10
select instance_number, instance_name, host_name, status from v$instance;
-- database
col name format a10
col db_unique_name format a10
col log_mode format a20
select dbid, name, db_unique_name, log_mode from v$database;
-- session
col sid format 99999
col serial# format 99999
col username format a10
col machine format a10
col osuser format a10
col program format a20
select sid, serial#, username, machine, osuser, program, status from v$session;
-- configuration in control file
col name format a50
select * from v$controlfile;
-- SGA
col name format a20
col value format 999999999999
select * from v$sga;
-- tablespace usage
col tablespace_name format a20
col used format 99.99999999
select
tablespace_name,
(1-sumfree.blocks/sumdat.blocks)*100 as used
from (
select tablespace_name, sum(blocks) as blocks
from dba_data_files
group by tablespace_name
) sumdat
join (
select tablespace_name, sum(blocks) as blocks
from dba_free_space
group by tablespace_name
) sumfree
using (tablespace_name)
order by tablespace_name;
select
tablespace_name,
(sumdat.blocks/(sumdat.blocks+sumfree.blocks))*100 as used
from (
select tablespace_name, sum(bytes_used) as blocks
from v$temp_space_header
group by tablespace_name
) sumdat
join (
select tablespace_name, sum(bytes_free) as blocks
from v$temp_space_header
group by tablespace_name
) sumfree
using (tablespace_name)
order by tablespace_name;
-- size on tablespace
col tablespace_name format a15
col sum(bytes) format 999999999999
select tablespace_name, sum(bytes) from dba_data_files
group by tablespace_name;
-- size on temporary tablespace
select tablespace_name, bytes from dba_temp_files;
-- extents on tablespace
select tablespace_name, initial_extent, next_extent, min_extents, max_extents,
pct_increase, status from dba_tablespaces;
-- datafiles on tablespace
col tablespace_name format a15
col file_name format a45
col bytes format 999999999999
select tablespace_name, bytes, file_name from dba_data_files
order by tablespace_name, file_name;
-- users
col default_tablespace format a20
col temporary_tablespace format a20
select username, default_tablespace, temporary_tablespace, created
from dba_users
order by username;
-- logfile
select * from v$log;
col number format a30
col member format a45
select * from v$logfile;
-- initial parameters
col name format a40
col value format a30
select name, value from v$parameter
order by name;
-- NLS parameters
col parameter format a30
select * from v$nls_parameters;
spool off
set echo off
quit
}}
* 出力例 [#r4c5ff00]
#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
asm_diskgroups
asm_diskstring
asm_power_limit 1
asm_preferred_read_failure_groups
audit_file_dest /u02/oradata/admin/orcl/adump
audit_sys_operations FALSE
audit_syslog_level
audit_trail DB
background_core_dump partial
background_dump_dest /u02/oradata/diag/rdbms/orcl/o
rcl/trace
backup_tape_io_slaves FALSE
bitmap_merge_area_size 1048576
blank_trimming FALSE
buffer_pool_keep
buffer_pool_recycle
circuits
client_result_cache_lag 3000
client_result_cache_size 0
cluster_database FALSE
cluster_database_instances 1
cluster_interconnects
commit_logging
commit_point_strength 1
commit_wait
commit_write
compatible 11.1.0
control_file_record_keep_time 7
control_files /u02/oradata/orcl/control01.ct
l, /u02/oradata/orcl/control02
.ctl
(略)
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
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR-MM-DD
NLS_DATE_LANGUAGE JAPANESE
NLS_CHARACTERSET JA16EUC
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY \
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
(略)
19行が選択されました。
SQL>
SQL> spool off
}}
----
#comment
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
