![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
作ったDBの設定を確認したいときのスクリプトを書いてみました。
自由にご利用ください。無保証です。
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
examine.sql 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 }}
#adsense(160x600)
