ASM sql's from rdbms instance <<<<<<<<<<<<<<<<<<
SQL> show parameter instance_type
NAME Type VALUE
------------------------------------ ------ ------------------------------
instance_type string RDBMS
COLUMN GROUP_NUMBER FORMAT 999999 HEADING GROUP_|NUMBER
COLUMN INSTANCE_NAME FORMAT A7 HEADING INSTANC|E_NAME
COLUMN DB_NAME FORMAT A7
COLUMN STATUS FORMAT A9
COLUMN SOFTWARE_VERSION FORMAT A16
COLUMN COMPATIBLE_VERSION FORMAT A18
set pagesize 200
SET VERIFY OFF
SET LINESIZE 80
-- SPOOL txt
SELECT
GROUP_NUMBER
,INSTANCE_NAME
,DB_NAME
,STATUS
,SOFTWARE_VERSION
,COMPATIBLE_VERSION
FROM sys.V_$ASM_CLIENT ;
GROUP_ INSTANC
NUMBER E_NAME DB_NAME STATUS SOFTWARE_VERSION COMPATIBLE_VERSION
------- ------- ------- --------- ---------------- ------------------
1 +ASM orcl CONNECTED 11.2.0.1.0 11.2.0.0.0
2 +ASM orcl CONNECTED 11.2.0.1.0 11.2.0.0.0
SET VERIFY ON
SET LINESIZE 1500
COLUMN GROUP_NUMBER FORMAT 999999 HEADING GROUP_|NUMBER
COLUMN NAME FORMAT A12
COLUMN SECTOR_SIZE FORMAT 999999 HEADING SECTOR|_SIZE
COLUMN BLOCK_SIZE FORMAT 99999 HEADING BLOCK|_SIZE
COLUMN ALLOCATION_UNIT_SIZE FORMAT 9999999999 HEADING ALLOCATION|_UNIT_SIZE
COLUMN STATE FORMAT A9
COLUMN TYPE FORMAT A6
COLUMN TOTAL_MB FORMAT 99999999
COLUMN FREE_MB FORMAT 9999999
COLUMN REQUIRED_MIRROR_FREE_MB FORMAT 999999999999 HEADING REQUIRED_MIR|ROR_FREE_MB
COLUMN USABLE_FILE_MB FORMAT 9999999 HEADING USABLE_|FILE_MB
COLUMN OFFLINE_DISKS FORMAT 9999999 HEADING OFFLINE|_DISKS
COLUMN UNBALANCED FORMAT A5 HEADING UNBAL|ANCED
COLUMN COMPATIBILITY FORMAT A13
COLUMN DATABASE_COMPATIBILITY FORMAT A11 HEADING DATABASE_CO|MPATIBILITY
SET VERIFY OFF
SET LINESIZE 158
-- SPOOL .txt
SELECT
GROUP_NUMBER
,NAME
,SECTOR_SIZE
,BLOCK_SIZE
,ALLOCATION_UNIT_SIZE
,STATE
,TYPE
,TOTAL_MB
,FREE_MB
,REQUIRED_MIRROR_FREE_MB
,USABLE_FILE_MB
,OFFLINE_DISKS
-- ,UNBALANCED
,COMPATIBILITY
,DATABASE_COMPATIBILITY
FROM SYS.V_$ASM_DISKGROUP ;
GROUP_ SECTOR BLOCK ALLOCATION REQUIRED_MIR USABLE_ OFFLINE DATABASE_CO
NUMBER NAME _SIZE _SIZE _UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB ROR_FREE_MB FILE_MB _DISKS COMPATIBILITY MPATIBILITY
------- ------------ ------- ------ ----------- --------- ------ --------- -------- ------------- -------- -------- ------------- -----------
1 DATA 512 4096 1048576 CONNECTED NORMAL 9216 5657 896 2380 0 11.2.0.0.0 10.1.0.0.0
2 FRA 512 4096 1048576 CONNECTED EXTERN 9216 8998 0 8998 0 11.2.0.0.0 10.1.0.0.0
COLUMN GROUP_NUMBER FORMAT 999999999 HEADING GROUP_|NUMBER
COLUMN FILE_NUMBER FORMAT 999999999 HEADING FILE_N|UMBER
COLUMN COMPOUND_INDEX FORMAT 9999999999 HEADING COMPOUN|D_INDEX
COLUMN INCARNATION FORMAT 999999999 HEADING INCARN|ATION
COLUMN BLOCK_SIZE FORMAT 999999999 HEADING BLOCK|_SIZE
COLUMN BLOCKS FORMAT 999999999 HEADING BLO|CKS
COLUMN BYTES FORMAT 999999999 HEADING BYT|ES
COLUMN SPACE FORMAT 999999999 HEADING SPA|CE
COLUMN TYPE FORMAT A2 HEADING TY|PE
COLUMN REDUNDANCY FORMAT A5 HEADING REDUN|DANCY
COLUMN STRIPED FORMAT A4 HEADING STRI|PED
COLUMN CREATION_DATE FORMAT A7 HEADING CREATIO|N_DATE
COLUMN MODIFICATION_DATE FORMAT A9 HEADING MODIFICAT|ION_DATE
COLUMN REDUNDANCY_LOWERED FORMAT A9 HEADING REDUNDANC|Y_LOWERED
SET VERIFY OFF
SET LINESIZE 200
set pagesize 200
-- SPOOL .txt
SELECT
GROUP_NUMBER
,FILE_NUMBER
,COMPOUND_INDEX
,INCARNATION
,BLOCK_SIZE
,BLOCKS
,BYTES
,SPACE
,TYPE
,REDUNDANCY
,STRIPED
,CREATION_DATE
,MODIFICATION_DATE
,REDUNDANCY_LOWERED
FROM SYS.V_$ASM_FILE ;
-- WHERE ROWNUM<=100;
-- SPOOL OFF
-- CLEAR COLUMNS
SET VERIFY ON
SET LINESIZE 1500
-- UNDEFINE YOUR_VAR
GROUP_ FILE_N COMPOUN INCARN BLOCK BLO BYT SPA TY REDUN STRI CREATIO MODIFICAT REDUNDANC
NUMBER UMBER D_INDEX ATION _SIZE CKS ES CE PE DANCY PED N_DATE ION_DATE Y_LOWERED
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- -- ----- ---- ------- --------- ---------
1 253 16777469 731105699 512 3 1536 2097152 AS MIRRO COAR 30-SEP- 30-SEP-10 U
MP R SE 10
AR
AM
ET
ER
FI
LE
1 256 16777472 731226373 8192 88321 723525632 ########## DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 257 16777473 731226431 8192 70401 576724992 ########## DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 258 16777474 731226451 8192 12161 99622912 204472320 DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 259 16777475 731226455 8192 641 5251072 12582912 DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 260 16777476 731227229 16384 595 9748480 50331648 CO HIGH FINE 01-OCT- 15-MAR-12 U
NT 10
RO
LF
IL
E
1 261 16777477 731227231 16384 595 9748480 50331648 CO HIGH FINE 01-OCT- 15-MAR-12 U
NT 10
RO
LF
IL
E
1 262 16777478 731227291 512 102401 52429312 110100480 ON MIRRO COAR 01-OCT- 15-MAR-12 U
LI R SE 10
NE
LO
G
1 263 16777479 731227315 512 102401 52429312 110100480 ON MIRRO COAR 01-OCT- 15-MAR-12 U
LI R SE 10
NE
LO
G
1 264 16777480 731227335 512 102401 52429312 110100480 ON MIRRO COAR 01-OCT- 15-MAR-12 U
LI R SE 10
NE
LO
G
1 265 16777481 731227415 8192 3713 30416896 62914560 TE MIRRO COAR 01-OCT- 15-MAR-12 U
MP R SE 10
FI
LE
1 266 16777482 731227433 8192 12801 104865792 214958080 DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 267 16777483 731228339 512 5 2560 2097152 PA MIRRO COAR 01-OCT- 15-MAR-12 U
RA R SE 10
ME
TE
RF
IL
E
2 256 33554688 741266959 512 102401 52429312 53477376 ON UNPRO COAR 24-JAN- 15-MAR-12 U
LI T SE 11
NE
LO
G
2 257 33554689 741266861 512 102401 52429312 53477376 ON UNPRO COAR 24-JAN- 15-MAR-12 U
LI T SE 11
NE
LO
G
2 258 33554690 741267209 512 102401 52429312 53477376 ON UNPRO COAR 24-JAN- 15-MAR-12 U
LI T SE 11
NE
LO
G
COLUMN GROUP_NUMBER FORMAT 999999 HEADING GROUP_|NUMBER
COLUMN ENTRY_NUMBER FORMAT 999999 HEADING ENTRY_|NUMBER
COLUMN REDUNDANCY FORMAT A10
COLUMN STRIPE FORMAT A6
COLUMN SYSTEM FORMAT A3 HEADING SYS|TEM
COLUMN NAME FORMAT A20
SET VERIFY OFF
SET LINESIZE 70
set pagesize 200
SELECT
GROUP_NUMBER
,ENTRY_NUMBER
,REDUNDANCY
,STRIPE
,SYSTEM
,NAME
FROM SYS.V_$ASM_TEMPLATE ;
GROUP_ ENTRY_ REDUN SYS
NUMBER NUMBER DANCY STRIPE TEM Disk Group
------- ------- ---------- ------ --- --------------------
1 60 MIRROR COARSE Y PARAMETERFILE
1 61 MIRROR COARSE Y ASMPARAMETERFILE
1 62 MIRROR COARSE Y ASMPARAMETERBAKFILE
1 63 MIRROR COARSE Y DUMPSET
1 64 HIGH FINE Y CONTROLFILE
1 65 MIRROR COARSE Y FLASHFILE
1 66 MIRROR COARSE Y ARCHIVELOG
1 67 MIRROR COARSE Y ONLINELOG
1 68 MIRROR COARSE Y DATAFILE
1 69 MIRROR COARSE Y TEMPFILE
1 170 MIRROR COARSE Y BACKUPSET
1 171 MIRROR COARSE Y AUTOBACKUP
1 172 MIRROR COARSE Y XTRANSPORT
1 173 MIRROR COARSE Y CHANGETRACKING
1 174 MIRROR COARSE Y FLASHBACK
1 175 MIRROR COARSE Y DATAGUARDCONFIG
1 176 MIRROR COARSE Y OCRFILE
1 177 MIRROR COARSE Y OCRBACKUP
1 178 HIGH COARSE Y ASM_STALE
2 60 UNPROT COARSE Y PARAMETERFILE
2 61 UNPROT COARSE Y ASMPARAMETERFILE
2 62 UNPROT COARSE Y ASMPARAMETERBAKFILE
2 63 UNPROT COARSE Y DUMPSET
2 64 UNPROT FINE Y CONTROLFILE
2 65 UNPROT COARSE Y FLASHFILE
2 66 UNPROT COARSE Y ARCHIVELOG
2 67 UNPROT COARSE Y ONLINELOG
2 68 UNPROT COARSE Y DATAFILE
2 69 UNPROT COARSE Y TEMPFILE
2 170 UNPROT COARSE Y BACKUPSET
2 171 UNPROT COARSE Y AUTOBACKUP
2 172 UNPROT COARSE Y XTRANSPORT
2 173 UNPROT COARSE Y CHANGETRACKING
2 174 UNPROT COARSE Y FLASHBACK
2 175 UNPROT COARSE Y DATAGUARDCONFIG
2 176 UNPROT COARSE Y OCRFILE
2 177 UNPROT COARSE Y OCRBACKUP
2 178 UNPROT COARSE Y ASM_STALE
38 rows selected.
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM v_$asm_diskgroup
ORDER BY name
Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA 512 4,096 1,048,576 CONNECTED NORMAL 9,216 3,559 38.62
FRA 512 4,096 1,048,576 CONNECTED EXTERN 9,216 218 2.37
--------------- --------------
Grand Total: 18,432 3,777
COL name FORMAT A15 HEADING 'Diskgroup|Name' WRAP
COL database_compatibility FORMAT A10 HEADING 'Diskgroup|Version'
COL instance_name FORMAT A20 HEADING 'Serviced Database|Client' WRAP
COL db_name FORMAT A8 HEADING 'Database|Name'
COL status FORMAT A12 HEADING 'Status'
COL software_version FORMAT A10 HEADING 'Database|Version'
Set linesize 120
SELECT
g.name
,g.database_compatibility
,c.instance_name
,c.db_name
,c.status
,c.software_version
FROM v_$asm_client c, v_$asm_diskgroup_stat g
where c.group_number = g.group_number;
Diskgroup Diskgroup Serviced Database Database Database
Name Version Client Name Status Version
--------------- ---------- -------------------- -------- ------------ ----------
DATA 10.1.0.0.0 +ASM orcl CONNECTED 11.2.0.1.0
FRA 10.1.0.0.0 +ASM orcl CONNECTED 11.2.0.1.0
TTITLE 'ASM disk statistics (From v$asm_disk_stat)'
col path format a50
select PATH, READS, WRITES, READ_TIME, WRITE_TIME,
READ_TIME/decode(READS,0,1,READS) "avgrdtime ",
WRITE_TIME/decode(WRITES,0,1,WRITES) "avgwrtime"
from v_$asm_disk_stat;
PATH READS WRITES READ_TIME WRITE_TIME avgrdtime avgwrtime
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
ORCL:ASMDISK01 2153 886 495.332 32.228 .230065954 .036374718
ORCL:ASMDISK02 2834 2081 845.988 77.66 .298513761 .037318597
ORCL:ASMDISK03 2995 2243 564.308 115.436 .188416694 .051465002
ORCL:ASMDISK04 2226 1615 459.576 97.564 .206458221 .060411146
ORCL:ASMDISK05 62 571 23.84 2.812 .384516129 .004924694
ORCL:ASMDISK06 22 28 22.688 1.032 1.03127273 .036857143
ORCL:ASMDISK07 44 173 19.392 4.784 .440727273 .027653179
ORCL:ASMDISK08 63 217 8.404 15.944 .133396825 .073474654
8 rows selected.
set pagesize 200
break on inst_id skip 1
column inst_id format 9999999 heading "Instance ID" justify left
column name format a15 heading "Disk Group" justify left
column total_mb format 999,999,999 heading "Total (MB)" justify right
column free_mb format 999,999,999 heading "Free (MB)" justify right
column pct_free format 999.99 heading "% Free" justify right
prompt ======================================================================
select inst_id, name, total_mb, free_mb, round((free_mb/total_mb)*100,2) pct_free from gv$asm_diskgroup
where total_mb != 0
order by inst_id, name;
SQL>
Instance ID Disk Group Total (MB) Free (MB) % Free
----------- --------------- ------------ ------------ -------
1 DATA 9,216 5,657 61.38
FRA 9,216 8,998 97.63
ASM disk size
PROMPT
PROMPT
PROMPT
PROMPT =======================================
PROMPT
SELECT DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb"
FROM DUAL,
(SELECT sum(a.bytes) TOTAL
FROM DBA_DATA_FILES a
WHERE (a.TABLESPACE_NAME IN ('SYSTEM','SYSAUX')
OR a.TABLESPACE_NAME LIKE '%UNDO%')) DF,
(SELECT sum(b.bytes) TOTAL FROM V$Log b) LOG,
(SELECT sum((cffsz+1)*cfbsz) TOTAL FROM X$Kcccf c) CONTROL;
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
1335 150 18.59375 1503.59375
SELECT NAME, STATE, TOTAL_MB, FREE_MB FROM v$asm_diskgroup;
Disk Group State Total (MB) Free (MB)
--------------- ----------- ------------ ------------
DATA CONNECTED 9,216 5,657
FRA CONNECTED 9,216 8,998
Col "ASIGNADO" FOR 9999999990 head "ASIGNADO|(Mb)"
Col "LIBRE" FOR 9999999990 head "LIBRE|(Mb)"
Col "OCUPADO" FOR 9999999990 head "OCUPADO|(Mb)"
SELECT INST_ID, NAME, TOTAL_MB "ASIGNADO", FREE_MB "LIBRE", (TOTAL_MB - FREE_MB) "OCUPADO"
FROM GV$ASM_DISK
ORDER BY INST_ID, NAME;
ASIGNADO LIBRE OCUPADO
Instance ID Disk Group (Mb) (Mb) (Mb)
----------- --------------- ----------- ----------- -----------
1 ASMDISK01 2304 1425 879
ASMDISK02 2304 1408 896
ASMDISK03 2304 1415 889
ASMDISK04 2304 1409 895
ASMDISK05 2304 2250 54
ASMDISK06 2304 2251 53
ASMDISK07 2304 2249 55
ASMDISK08 2304 2248 56
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
13 rows selected.
Thursday, 15 March 2012
ASM SQLs' from RDBMS Instance
Saturday, 10 March 2012
asmcmd with awk
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
export PATH=${PATH}:${ORACLE_HOME}/bin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export LC_ALL=en_GB
echo
echo
echo " ASM Disk % Full Used (MB) Free (MB) Total (MB) "
echo "======================================================================"
asmcmd lsdg | \
awk -v sq="'" 'BEGIN { getline } {
printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
}'
#awk -F|sq="'" 'BEGIN { getline } {
# printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
# }'
echo
echo
[oracle@oel5u4-+ASM dbhome_1] sh Asm.sh
ASM Disk % Full Used (MB) Free (MB) Total (MB)
======================================================================
DATA/ 84% 4,761 896 5,657
FRA/ 100% 8,998 0 8,998
==================
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
export PATH=${PATH}:${ORACLE_HOME}/bin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export LC_ALL=en_GB
echo
echo
echo " ASM Disk % Full Used (MB) Free (MB) Total (MB) " >asm.txt
echo "======================================================================" >>asm.txt
asmcmd lsdg | \
awk -v sq="'" 'BEGIN { getline } {
printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
}' >>asm.txt
#awk -F|sq="'" 'BEGIN { getline } {
# printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
# }'
echo >>asm.txt
echo >>asm.txt
[oracle@oel5u4-+ASM dbhome_1] cat asm.txt
ASM Disk % Full Used (MB) Free (MB) Total (MB)
======================================================================
DATA/ 84% 4,761 896 5,657
FRA/ 100% 8,998 0 8,998
ASM parameters and underscore parameters
ASM parameters and underscore parameters
ALTER SYSTEM SET parameter_name = 'value' COMMENT='xxxxxxxxxx' SCOPE=both;http://anuj-singh.blogspot.com/2011/11/oracle-hidden-parameters.html
col "Instance Value" format a20
col parameter format a30
set pagesize 200
select a.ksppinm "Parameter", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and ksppinm like '%asm%'
order by a.ksppinm;
Parameter Instance Value
------------------------------ --------------------
_asm_acd_chunks 1
_asm_allow_only_raw_disks TRUE
_asm_allow_resilver_corruption FALSE
_asm_ausize 1048576
_asm_blksize 4096
_asm_disk_repair_time 14400
_asm_droptimeout 60
_asm_emulmax 10000
_asm_emultimeout 0
_asm_kfdpevent 0
_asm_libraries ufs
_asm_maxio 1048576
_asm_stripesize 131072
_asm_stripewidth 8
_asm_wait_time 18
_asmlib_test 0
_asmsid asm
asm_diskgroups DATA
asm_diskstring /dev/raw/raw1
asm_power_limit 1
20 rows selected.
=====================
set pagesize 200
column parameter format a37
column description format a30 word_wrapped
column "Session Value" format a10
column "Instance Value" format a10
select
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from
x$ksppi a,
x$ksppcv b,
x$ksppsv c
where
a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '%asm%' escape '\'
order by 1
/
SQL> set pagesize 200
SQL> /
Parameter Description Session Va Instance V
------------------------------------- ------------------------------ ---------- ----------
_asm_acd_chunks initial ACD chunks created 1 1
_asm_allow_only_raw_disks Discovery only raw devices TRUE TRUE
_asm_allow_resilver_corruption Enable disk resilvering for FALSE FALSE
external redundancy
_asm_ausize allocation unit size 1048576 1048576
_asm_blksize metadata block size 4096 4096
_asm_disk_repair_time seconds to wait before 14400 14400
dropping a failing disk
_asm_droptimeout timeout before offlined disks 60 60
get dropped (in 3s ticks)
_asm_emulmax max number of concurrent disks 10000 10000
to emulate I/O errors
_asm_emultimeout timeout before emulation 0 0
begins (in 3s ticks)
_asm_kfdpevent KFDP event 0 0
_asm_libraries library search order for ufs ufs
discovery
_asm_maxio Maximum size of individual I/O 1048576 1048576
request
_asm_stripesize ASM file stripe size 131072 131072
_asm_stripewidth ASM file stripe width 8 8
_asm_wait_time Max/imum time to wait before 18 18
asmb exits
_asmlib_test Osmlib test event 0 0
_asmsid ASM instance id asm asm
asm_diskgroups disk groups to mount DATA DATA
automatically
asm_diskstring disk set locations for /dev/raw/r /dev/raw/r
discovery aw1 aw1
asm_power_limit number of processes for disk 1 1
rebalancing
20 rows selected.
----
for fix control
set pages 5000 lines 200
col name format a30
col cur_val format a20
select i.ksppinm name , v.ksppstvl cur_val, v.ksppstdf default_val, v.ksppstvf
from x$ksppi i, x$ksppcv v
where i.indx = v.indx
and i.ksppinm = '_fix_control';
NAME CUR_VAL DEFAULT_V KSPPSTVF------------------------------ -------------------- --------- ----------_fix_control 9550277:OFF FALSE 0
SELECT i.instance_name instance, -- b.ksppstvl "Session_Value", c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig, -- above is instance_value a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION" FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$instance i WHERE a.indx = b.indx AND a.indx = c.indx AND (a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size') and a.ksppinm not in ('__oracle_base') -- and a.ksppinm in ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target','_pga_limit_target_perc') and c.ksppstvl not in ('TRUE','FALSE') order by 3 /
col DESCRIPTION for a30 col VALUE for a20 col PARAMETER for a30SELECT i.instance_name instance, -- b.ksppstvl "Session_Value", c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig, -- above is instance_value a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION" FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$instance i WHERE a.indx = b.indx AND a.indx = c.indx AND (a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size') and a.ksppinm not in ('__oracle_base') and c.ksppstvl not in ('TRUE','FALSE') order by 3 /
===
set linesize 300 pagesize 300
col parameter for a30
col session for a28
col instance for a12
col "Session Value" for a15
col "Instance Value" for a15
col IS_SESSION_MODIFIABLE for a25
col IS_SYSTEM_MODIFIABLE for a25
col description for a70
col comment for a30
col "Default Value" for a15
SELECT a.ksppinm "Parameter",
b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
and upper(a.ksppinm) like upper('%&1%')
/
===
set linesize 300 pagesize 300
col name for a40
col VALUE for a100
col "THREAD#" for a10
column num noprint
SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%'
MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;
THREAD# NAME VALUE
---------- ---------------------------------------- ----------------------------------------------------------------------------------------------------
* processes 3000
* sga_max_size 204010946560
* large_pool_size 2684354560
* pga_aggregate_limit 42949672960
* _ksb_restart_policy_times 0, 60, 120, 240
======
VARIABLE BgnSnap NUMBERVARIABLE EndSnap NUMBERVARIABLE DID NUMBERVARIABLE INST_NUMBER numberexec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;exec select DBID into :DID from v$database;exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
set linesize 300 pagesiz 300col NAME for a45col BVAL for a20col EVAL for a20select e.parameter_name name , b.value bval , decode(b.value, e.value, NULL, e.value) eval from dba_hist_parameter b , dba_hist_parameter e where b.snap_id(+) = :BgnSnap and e.snap_id = :EndSnap and b.dbid(+) = :DID and e.dbid = :DID and b.instance_number(+) = 1 and e.instance_number = 1 and b.parameter_hash(+) = e.parameter_hash and ( nvl(b.isdefault, 'X') = 'FALSE' or nvl(b.ismodified,'X') != 'FALSE' or e.ismodified != 'FALSE' or nvl(e.value,0) != nvl(b.value,0) ) and e.parameter_name not like '\_\_%' escape '\' order by e.parameter_name; ASM reading files with direct OS access
ASM reading files with direct OS access
SQL> select GROUP_KFFXP,DISK_KFFXP,AU_KFFXP from x$kffxp where number_kffxp in (select file_number from v$asm_alias where name like 'spfile%' );
GROUP_KFFXP DISK_KFFXP AU_KFFXP
----------- ---------- ----------
1 0 994
SQL> select GROUP_KFFXP,DISK_KFFXP,AU_KFFXP from x$kffxp where number_kffxp in (select file_number from v$asm_alias where name like 'spfile%' );
GROUP_KFFXP DISK_KFFXP AU_KFFXP
----------- ---------- ----------
1 0 994
col path format a30
select disk_number,path from v$asm_disk where GROUP_NUMBER=1 and disk_number in (0,994);
DISK_NUMBER PATH
----------- ------------------------------
0 /dev/raw/raw1
dd if=/dev/raw/raw1 bs=1024k count=1 skip=101|strings|more
SQL> !dd if=/dev/raw/raw1 bs=1024k count=1 skip=101|strings|more
U !
Is the column updatable?,
Column name,
Table name,
Table owner,
$Description of all updatable columns,
Is the column deletable?,
Is the column insertable?,
Is the column updatable?,
Column name,
Table name,
Table owner,
Description of updatable columns,
status of release,
version number,
product name,
5version and status information for component products,
global database name,
global database name,
8transaction branch ID at this database of the connection,
1session number at this database of the connection,
2the database id at the other end of the connection,
G"C" for request commit, else "N" for prepare or request readonly commit,
-in: name of local user; out: owner of db link,
/in: client database name; out: outgoing db link,
1"in" for incoming connections, "out" for outgoing,
Linformation about incoming and outgoing connections for pending transactions,
/global commit number for committed transactions,
8Oracle user name of the end-user at the topmost database,
)name of the host machine for the end-user,
8operating system specific name for the end-user terminal,
/operating system specific name for the end-user,
Dtime automatic recovery (RECO) last tried to recover the transaction,
:time of manual force decision (null if not forced locally),
Bvalue of SYSDATE when the row was inserted (tx or system recovery),
%text for "commit work comment <text>",
'C for commit, R for rollback, else null,
qyes => part of the transaction committed and part rolled back (commit or rollback with the FORCE option was used),
Bcollecting, prepared, committed, forced commit, or forced rollback,
globally unique transaction id,
!string of form: n.n.n, n a number,
5info about distributed transactions awaiting recovery,
Timestamp for the grant,
,Permission to make REFERENCES to the column?,
Permission to UPDATE the column?,
Sunday, 4 March 2012
dbms_scheduler example
dbms_scheduler example
prompt Show Scheduler database objects
TTITLE 'Scheduler Objects:'
COL owner FORMAT A20 HEADING 'Owner'
COL object_type FORMAT A12 HEADING 'Object|Type'
COL object_name FORMAT A30 HEADING 'Name'
COL created FORMAT A20 HEADING 'Created On'
COL status FORMAT A12 HEADING 'Status'
SELECT
owner ,object_type ,object_name ,created ,status
FROM dba_objects
WHERE object_type IN ('PROGRAM', 'JOB', 'JOB CLASS', 'SCHEDULE', 'WINDOW')
ORDER BY object_type, OBJECT_name;
Object
Owner Type Name Created On Status
-------------------- ------------ ------------------------------ -------------------- ------------
SYS JOB BSLN_MAINTAIN_STATS_JOB 04-JAN-19 VALID
SYS JOB CLEANUP_NON_EXIST_OBJ 04-JAN-19 VALID
prompt Show Schedule objects
prompt 'Schedules:'
COL owner FORMAT A08 HEADING 'Owner'
COL schedule_name FORMAT A30 HEADING 'Schedule'
COL start_date FORMAT A20 HEADING 'Starts|On'
COL end_date FORMAT A20 HEADING 'Ends|On'
COL repeat_interval FORMAT A45 HEADING 'Interval'
SELECT
owner ,schedule_name ,to_char(start_date, 'mm/dd/yyyy hh24:mi:ss') start_date ,to_char(end_date, 'mm/dd/yyyy hh24:mi:ss') end_date ,repeat_interval
FROM dba_scheduler_schedules;
prompt Show Program objects : What program objects are available?
TTITLE 'Programs:'
COL owner FORMAT A08 HEADING 'Owner'
COL program_name FORMAT A30 HEADING 'Program'
COL program_type FORMAT A16 HEADING 'Type'
COL program_action FORMAT A60 HEADING 'Action'
SELECT
owner
,program_name
,program_type
,program_action
FROM dba_scheduler_programs;
Owner Program Type Action
-------- ------------------------------ ---------------- ------------------------------------------------------------
SYS PURGE_LOG_PROG STORED_PROCEDURE dbms_scheduler.auto_purge
SYS FILE_WATCHER_PROGRAM STORED_PROCEDURE dbms_isched.file_watch_job
SYS PMO_DEFERRED_GIDX_MAINT PLSQL_BLOCK dbms_part.cleanup_gidx_internal(
prompt What program's arguments are attached?
TTITLE 'Program Arguments:'
COL owner FORMAT A20 HEADING 'Owner'
COL program_name FORMAT A30 HEADING 'Program'
COL argument_name FORMAT A12 HEADING 'Arg Name'
COL argument_position FORMAT 999 HEADING 'Arg|Pos'
COL argument_type FORMAT A25 HEADING 'Arg Type'
COL default_value FORMAT A12 HEADING 'Default|Value'
COL out_argument FORMAT A06 HEADING 'Out|Arg?'
SELECT owner ,program_name ,argument_name ,argument_position ,argument_type ,default_value ,out_argument FROM dba_scheduler_program_args;
Arg Default Out
Owner Program Arg Name Pos Arg Type Value Arg?
-------------------- ------------------------------ ------------ ---- ------------------------- ------------ ------
SYS HS_PARALLEL_SAMPLING 9 SYS.HSBLKNAMLST FALSE
SYS HS_PARALLEL_SAMPLING 10 SYS.HSBLKNAMLST FALSE
prompt Show Job objects
COL owner FORMAT A20 HEADING 'Owner'
COL job_name FORMAT A30 HEADING 'Job'
COL program_name FORMAT A20 HEADING 'Program|Name'
COL job_class FORMAT A35 HEADING 'Job Class'
COL job_type FORMAT A12 HEADING 'Job|Type'
COL job_action FORMAT A50 HEADING 'Job|Action'
prompt 'Jobs:---- '
SELECT owner ,job_name ,state ,job_class ,job_type ,job_action FROM dba_scheduler_jobs;
Job Job
Owner Job State Job Class Type Action
-------------------- ------------------------------ --------------- ----------------------------------- ------------ --------------------------------------------------
SYS PURGE_LOG SCHEDULED DEFAULT_JOB_CLASS
SYS FILE_WATCHER DISABLED SCHED$_LOG_ON_ERRORS_CLASS
COL owner FORMAT A08 HEADING 'Owner'
COL job_name FORMAT A30 HEADING 'Job'
COL program_name FORMAT A30 HEADING 'Program|Name'
COL schedule_name FORMAT A30 HEADING 'Schedule|Name'
COL enabled FORMAT A08 HEADING 'Enabled?'
COL state FORMAT A15 HEADING 'State'
COL restartable FORMAT A08 HEADING 'Restart|-able?'
COL start_date FORMAT A37 HEADING 'Start|Date'
prompt 'Job Components:'
SELECT
owner ,job_name ,program_name ,schedule_name ,enabled ,state ,restartable ,start_date
FROM dba_scheduler_jobs;
Program Schedule Restart Start
Owner Job Name Name Enabled? State -able? Date
-------- ------------------------------ ------------------------------ ------------------------------ -------- --------------- -------- -------------------------------------
SYS PURGE_LOG PURGE_LOG_PROG DAILY_PURGE_SCHEDULE TRUE SCHEDULED FALSE 08-JUL-19 03.00.00.584504 AM ETC/GMT
SYS FILE_WATCHER FILE_WATCHER_PROGRAM FILE_WATCHER_SCHEDULE FALSE DISABLED FALSE
prompt What are a Job's arguments?
COL job_name FORMAT A30 HEADING 'Job'
COL argument_name FORMAT A12 HEADING 'Arg Name'
COL argument_position FORMAT 999 HEADING 'Arg|Pos'
COL value FORMAT A32 HEADING 'Argument Value'
prompt 'Job Arguments:'
SELECT job_name ,argument_name ,argument_position ,value FROM dba_scheduler_job_args;
Arg
Job Arg Name Pos Argument Value
------------------------------ ------------ ---- --------------------------------
DRA_REEVALUATE_OPEN_FAILURES 1 TRUE
DRA_REEVALUATE_OPEN_FAILURES 2 TRUE
DRA_REEVALUATE_OPEN_FAILURES 3 TRUE
DRA_REEVALUATE_OPEN_FAILURES 4 900
prompt Show Scheduled Tasks History: Show a high-level view of scheduled task execution history
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A37 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A30 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
prompt 'Scheduled Tasks History:'
SELECT
log_id ,log_date ,owner ,job_name ,status
FROM dba_scheduler_job_log
where 1=1
and log_date>sysdate -1 ;
Log# Log Date Owner Job Status
----- ------------------------------------- ------ ------------------------------ ----------
7344 14-MAR-23 10.00.00.355353 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623
7346 14-MAR-23 10.00.00.357186 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623
7348 14-MAR-23 10.00.00.358230 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623
7350 14-MAR-23 10.00.00.360661 PM +00:00 SYS ORA$AT_SA_SPC_SY_230624
prompt What scheduled tasks failed during execution, and Error details
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A32 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date'
COL error# FORMAT 999999 HEADING 'Error|Nbr'
prompt 'Scheduled Tasks That Failed:'
SELECT
log_id ,log_date ,owner ,job_name ,status ,actual_start_date ,error# FROM dba_scheduler_job_run_details
WHERE status <> 'SUCCEEDED'
ORDER BY actual_start_date;
prompt Which jobs are running right now ?
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL session_id FORMAT 999999 HEADING 'Session'
COL running_instance FORMAT A10 HEADING 'Running|Instance'
COL elapsed_time FORMAT A12 HEADING 'Elapsed|Time'
TTITLE 'Scheduled Tasks Running Right Now:'
SELECT owner ,job_name ,session_id ,running_instance ,elapsed_time FROM dba_scheduler_running_jobs;
column job_name format a20
column status format a12
column actual_start_date format a36
column run_duration format a14
select
job_name, status, actual_start_date, run_duration
from
dba_scheduler_job_run_details
where
job_name = 'GATHER_STATS_JOB'
order by
actual_start_date
;
select
error#,
status
from
dba_scheduler_job_run_details
where
status like '%FAIL%';
set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18
var local_offset number
begin
select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/
select dsj.owner,
dsj.job_name,
dsj.program_name,
dsjlmax.status,
dsj.state,
-- to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
extract(day from dsj.last_run_duration) ||':'||
lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
-- to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
(select max(log_date)
from dba_scheduler_job_log dsjl2
where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner in ('SYSTEM','SYS')
and dsj.job_name = dsjlmax.job_name;
==========
dbms_scheduler example
begin
dbms_scheduler.create_job(
job_name => 'SCOTT_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin dbms_stats.gather_schema_stats(ownname =>"SCOTT",
estimate_percent => 30,
cascade=>true,
method_opt=> 'for all columns size 1',
degree => 5 ,
options => 'GATHER STALE '); end; '
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;
begin
dbms_scheduler.create_job (
Job_name=> 'anuj_job',
Job_type=> 'PLSQL_BLOCK',
Job_action=> 'update employees set salary = salary * 1.5;',
Start_date=> '10-dec-2011 06:00:00 pm',
Repeat_interval=> 'FREQ=YEARLY',
Comments=> 'testing..... '
);
end;
/
PL/SQL procedure successfully completed.
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
PURGE_LOG TRUE 1
ORA$AUTOTASK_CLEAN TRUE 1
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
FGR$AUTOPURGE_JOB FALSE 0
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
ANUJ_JOB FALSE 0
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0
11 rows selected.
exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy');
SQL> exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy');
PL/SQL procedure successfully completed.
exec dbms_scheduler.stop_job(job_name=> 'anuj_job', force=> true);
SQL> exec dbms_scheduler.drop_job('anuj_job');
PL/SQL procedure successfully completed.
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
ANUJ_JOB_COPY FALSE 0
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
FGR$AUTOPURGE_JOB FALSE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 1
PURGE_LOG TRUE 1
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0
11 rows selected.
exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true);
exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true);
SQL> exec dbms_scheduler.set_attribute ( name => 'ANUJ_JOB_COPY',attribute => 'job_priority', value => 1 );
PL/SQL procedure successfully completed.
EXEC dbms_scheduler.run_job('ANUJ_JOB_COPY') ;
BEGIN
DBMS_SCHEDULER.ENABLE('ANUJ_JOB_COPY');
END;
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.DISABLE('ANUJ_JOB_COPY');
END;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'anuj_full_job1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'anuj_full_job1');
END;
DECLARE
l_job NUMBER := 0;
BEGIN
DBMS_JOB.SUBMIT(l_job,'anuj_full_job;',sysdate,'TRUNC(sysdate)+1+1/288');
END;
BEGIN
DBMS_SCHEDULER.ENABLE('ANUJ_FULL_JOB');
END;
EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB');
EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB');
PL/SQL procedure successfully completed.
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
ANUJ_JOB_COPY TRUE 0
ANUJ_FULL_JOB TRUE 0
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
FGR$AUTOPURGE_JOB FALSE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 1
PURGE_LOG TRUE 1
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0
12 rows selected.
SELECT job_name, operation, status, user_name
FROM dba_scheduler_job_log
WHERE operation != 'RUN'
and owner = 'SCOTT';
SELECT * FROM dba_scheduler_jobs
Monitoring job-scheduling
Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in
Oracle 10g. We will discuss some of these views here.
To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details
To show running jobs:
select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;
To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;
show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
show all jobs and their attributes:
http://www.oracledatabase12g.com/archives/%E4%BA%86%E8%A7%A3sysdate%E5%87%BD%E6%95%B0.html
select * from dba_scheduler_jobs
show all program-objects and their attributes
select * from dba_scheduler_programs;
show all program-arguments:
select * from dba_scheduler_program_args;
select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;
SQL> select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YEAR-MON-DAYHH24:MI:SS')
-----------------------------------------------------------------
TWENTY ELEVEN-DEC-SATURDAY 22:59:13
set linesize 200 pagesize 1400;
col sessiontimezone for a20
SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;
CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE
------------------- ------------------- ------ --------------------
2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00
select current_date, sysdate,dbtimezone,sessiontimezone from dual;
CURRENT_D SYSDATE DBTIME SESSIONTIMEZONE
--------- --------- ------ ---------------------------------------------------------------------------
10-DEC-11 10-DEC-11 +00:00 +00:00
Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.
Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details
Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT 'interval' Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.
Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB
Issues with external Jobs:
http://forums.oracle.com/forums/thread.jspa?threadID=555102
NOTE:389685.1 – Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR=”Launching external job failed: Invalid username or password”
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667
Please note that this article will guide through the correct analysis for Scheduler/Job Problems.
It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future
Keywords
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'MY_MV_REFRESH',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''MV_NAME_HERE'', ''C'')''); END;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
END_DATE => NULL,
ENABLED => TRUE,
COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (12:00AM each day).');
END;
/
SELECT job_name, program_name FROM dba_scheduler_jobs WHERE job_style='LIGHTWEIGHT';
begin
dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin
dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100
, cascade=>true,method_opt=> "for all columns size 1"
,degree => 5 , options => "GATHER STALE"); end;'
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;
SQL> begin
2
3 dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE'
4 ,job_type => 'PLSQL_BLOCK'
5 ,job_action => 'begin
6 dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100
7 , cascade=>true,method_opt=> "for all columns size 1"
8 ,degree => 5 , options => "GATHER STALE"); end;'
9 ,start_date => trunc(sysdate + 1) + 6/24
10 ,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
11 12 ,comments => 'customized stats gathering for risk engine');
13
14 end;
15 /
PL/SQL procedure successfully completed.
grant execute on dbms_scheduler to scott;
exec dbms_scheduler.run_job('ENGINE_JOB_SCHEDULE');
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
session_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
running_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE');
SQL> exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE');
PL/SQL procedure successfully completed.
'begin
dbms_stats.gather_schema_stats(ownname =>'''SCOTT''', estimate_percent => 100
, cascade=>true,method_opt=> '''for all columns size 1'''
,degree => 5 , options => '''GATHER STALE''');
end;'
To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details
To show running jobs:
select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;
To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;
show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
show all jobs and their attributes:
select *
from dba_scheduler_jobs
show all program-objects and their attributes
select *
from dba_scheduler_programs;
show all program-arguments:
select *
from dba_scheduler_program_args;
select 'exec dbms_scheduler.disable(' || ''''||OWNER || '.' || job_name || '''' || ');' Disable_Below_Jobs from dba_scheduler_jobs where STATE='RUNNING' and owner ='SYS';
CREATE TABLE reminders(message VARCHAR2(4000),msg_ts TIMESTAMP );
DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'wakeup_sun');
END;
/
SELECT * FROM reminders;
DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;
/
begin
dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE1'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin
dbms_stats.gather_schema_stats(ownname =>''SCOTT'', estimate_percent => 100
, cascade=>true,method_opt=> ''for all columns size 1''
,degree => 5 , options => ''GATHER STALE''); end;'
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'ENGINE_JOB_SCHEDULE1');
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'ENGINE_JOB_SCHEDULE1');
END;
/
PL/SQL procedure successfully completed.
begin
dbms_scheduler.create_schedule(
schedule_name => 'DEMO_SCHEDULE'
, start_date => '01/01/2006 22:00:00'
, repeat_interval => 'FREQ=WEEKLY'
, comments => 'Weekly at 22:00');
END;
To drop the schedule:
begin
dbms_scheduler.drop_schedule( schedule_name => 'DEMO_SCHEDULE' , force => TRUE );
end;
/
begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB1'
, program_name =>'DEMO_JOB_SCHEDULE'
, schedule_name =>'DEMO_SCHEDULE'
, enabled => FALSE
, comments => 'Run demo program every week at 22:00');
dbms_scheduler.set_job_argument_value(
job_name => 'DEMO_JOB1'
, argument_position => 1
, argument_value => 'param1');
dbms_scheduler.enable('DEMO_JOB1');
commit;
end;
/
begin
dbms_scheduler.create_job
(
job_name => 'RUN_SHELL1',
schedule_name => 'DEMO_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/test/run_script.sh',
enabled => true,
comments => 'Run shell-script'
);
end;
/
Create a Job
/* Run this job every 5 minutes except between 11am and 5pm) */
begin
dbms_scheduler.create_job (
job_name => 'test_job_01',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN update test1 set col1 = col1 + 1; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; byhour=17,18,19,20,21,22,23,00,01,02,03,04,05,06,07,08,09,10; interval=5',
end_date => NULL,
enabled => TRUE,
comments => 'Job to test use of dbms_scheduler');
end;
/
===================================
shell script [oracle@rac02 scripts]$ cat SysGather.sh #!/bin/bash export ORACLE_SID=vihcdbd8 export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH LOGFILE=/home/oracle/logs/sys_gather_`date +"%m%d%y_%H%M"`.log echo $LOGFILE sqlplus -s "/as sysdba" << EOF> $LOGFILE @/u01/app/oracle/scripts/SysGather.sql exit EOF sql script [oracle@rac02 scripts]$ cat SysGather.sql spool Gather.spool alter session set nls_date_format='dd-MON-YYYY hh24:mi'; set time on timing on feedback on select sysdate from dual; set echo on feedback on time on timing on verify on -- show con_name; set pages 300 lines 350 col open_mode for a10 col host_name for a10 col database_role for a15 col db_unique_name for a10 col controlfile_type for a10 col database_role for a17 col host_name for a35 col open_mode for a20 select name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging, startup_time "db up time" from v$database,gv$instance; SET VERIFY OFF select sysdate from dual; prompt ======================================== prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS () prompt ======================================== EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (); prompt ======================================== prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM') prompt ======================================== EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM', Degree => 8); prompt ======================================== prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS' , Degree => 8) prompt ======================================== EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', Degree => 8); prompt ======================================== prompt DBMS_STATS.GATHER_DICTIONARY_STATS prompt ======================================== exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$'); exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_OPR'); exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY'); exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY'); exec dbms_stats.lock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL'); EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS(Degree => 8); exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$'); exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_OPR'); exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY'); exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY'); exec dbms_stats.unlock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL'); select sysdate from dual; spool off ====== BEGIN DBMS_SCHEDULER.create_program ( program_name => 'GATHER_SYS', program_type => 'EXECUTABLE', program_action => '/u01/app/oracle/scripts/SysGather.sh', number_of_arguments => 0, enabled => TRUE, comments => 'Program to Sys gather statistics us a shell script' ); end ; select enabled from dba_scheduler_programs where program_name = 'GATHER_SYS'; BEGIN DBMS_SCHEDULER.create_schedule ( schedule_name => 'GATHER_SYS_SCHEDULE', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'Repeats hourly, on the hour, for ever.'); END; / col START_DATE for a35 col REPEAT_INTERVAL for a35 col END_DATE for a30 select start_date, repeat_interval, end_date from dba_scheduler_schedules where schedule_name = 'GATHER_SYS_SCHEDULE'; begin dbms_scheduler.create_job ( job_name => 'GATHER_JOB', program_name => 'GATHER_SYS', schedule_name => 'GATHER_SYS_SCHEDULE', enabled => true, comments => 'Job, combining GATHER_SYS_SCHEDULE with GATHER_SYS' ); end; select log_id, log_date, status from DBA_scheduler_job_log where job_name = 'GATHER_JOB' ; set linesize 300 col JOB_NAME for a20 col REQ_START_DATE for a35 col ACTUAL_START_DATE for a35 col RUN_DURATION for a18 col STATUS for a14 select job_name,status,req_start_date,actual_start_date,run_duration from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like 'GATHER_JOB%' and req_start_date> sysdate-3 order by req_start_date; #Enable a job. BEGIN DBMS_SCHEDULER.ENABLE('GATHER_JOB'); END; #Disable a job. BEGIN DBMS_SCHEDULER.DISABLE('GATHER_JOB'); END; #Stop a job. BEGIN DBMS_SCHEDULER.STOP_JOB( job_name => 'GATHER_JOB', force => true --optional ); END; # Drop a job. BEGIN DBMS_SCHEDULER.DROP_JOB ( job_name => 'GATHER_JOB', force => true --optional ); END;
====
set line 200 col start_date format a20 col owner format a10 col job_name format a20 col program_name format a20 col enabled format a3 head 'ENA|BLE' col start_date format a21 head 'START DATE' col next_run_date format a21 head 'NEXT RUN DATE' col last_start_date format a21 head 'LAST START DATE' col repeat_interval format a15 head 'REPEAT INTERVAL' word_wrapped col last_run_duration format a14 head 'LAST RUN|DURATION|DD:HH:MM:SS' col run_count format 99,999 head 'RUN|COUNT' col retry_count format 9999 head 'RETRY|COUNT' col max_runs format 999,999 head 'MAX|RUNS' col job_action format a15 head 'CODE' word_wrapped select owner , job_name --, program_name , to_char(cast(start_date as date),'mm/dd/yy-hh24:mi:ss') || ' ' || extract (timezone_abbr from start_date ) start_date --, state , case enabled when 'TRUE' then 'YES' when 'FALSE' then 'NO' end enabled -- last_run_duration is an interval , lpad(nvl(extract (day from last_run_duration ),'00'),2,'0') || ':' || lpad(nvl(extract (hour from last_run_duration ),'00'),2,'0') || ':' || lpad(nvl(extract (minute from last_run_duration ),'00'),2,'0') || ':' || ltrim(to_char(nvl(extract (second from last_run_duration ),0),'09.90')) last_run_duration , to_char(cast(next_run_date as date),'mm/dd/yy-hh24:mi:ss') || ' ' || extract (timezone_abbr from next_run_date ) next_run_date , to_char(cast(last_start_date as date),'mm/dd/yy-hh24:mi:ss') || ' ' || extract (timezone_abbr from last_start_date ) last_start_date , run_count --, max_runs , retry_count , job_action , repeat_interval , state from ALL_SCHEDULER_JOBS --where owner = user order by owner /
ORA-00845: MEMORY_TARGET not supported on this system
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 968M
memory_target big integer 968M
shared_memory_address integer 0
SQL>
alter system set memory_max_target=1200m scope=spfile;
SQL> alter system set memory_max_target=1200m scope=spfile;
System altered.
SQL> alter system set memory_target=1200m scope=spfile;
System altered.
SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
21G 17G 2.9G 86% /
tmpfs 1000M 420K 1000M 1% /dev/shm <<<<------------------ increase this value
/dev/sda1 485M 41M 419M 9% /boot
from root
[root@localhost ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm
/etc/fstab
/dev/mapper/VolGroup-lv_root / ext4 defaults 1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot ext4 defaults 1 2
/dev/mapper/VolGroup-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
shmfs /dev/shm tmpfs size=2048m 0 0 <<<<<<<<<---- add following line
then init 6 <<<<<--- reboot
SQL> startup ;
ORACLE instance started.
Total System Global Area 1255473152 bytes
Fixed Size 1344652 bytes
Variable Size 754977652 bytes
Database Buffers 486539264 bytes
Redo Buffers 12611584 bytes
Database mounted.
============================
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1200M
memory_target big integer 1200M
shared_memory_address integer 0
Oracle database all file info
@all_file.sql
@dba_all_file.sql
All file in database
@dba_all_file.sql
All file in database
SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a64 HEADING 'Filename'
COLUMN filesize FORMAT 99,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 99,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max'
BREAK ON report
COMPUTE SUM OF filesize ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
UNION
SELECT '[ ONLINE REDO LOG ]' , a.member , b.bytes , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$logfile a , v$log b
WHERE a.group# = b.group#
UNION
SELECT '[ CONTROL FILE ]' , a.name , TO_NUMBER(null) , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a ORDER BY 1,2
/
SQL> /
Tablespace Name / File Class Filename File Size Auto Next Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
SYSAUX +DATA/vihaan/datafile/sysaux.257.776974139 241,172,480 YES 10,485,760 34,359,721,984
SYSTEM +DATA/vihaan/datafile/system.256.776974135 503,316,480 YES 10,485,760 34,359,721,984
TEMP +DATA/vihaan/tempfile/temp.264.776974399 20,971,520 YES 655,360 34,359,721,984
UNDOTBS1 +DATA/vihaan/datafile/undotbs1.258.776974143 26,214,400 YES 5,242,880 34,359,721,984
USERS +DATA/vihaan/datafile/users.259.776974145 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] +DATA/vihaan/controlfile/current.260.776974343
[ ONLINE REDO LOG ] +DATA/vihaan/onlinelog/group_1.261.776974351 52,428,800
[ ONLINE REDO LOG ] +DATA/vihaan/onlinelog/group_2.262.776974357 52,428,800
[ ONLINE REDO LOG ] +DATA/vihaan/onlinelog/group_3.263.776974361 52,428,800
---------------
sum 954,204,160
9 rows selected.
Subscribe to:
Posts (Atom)
