Monday, March 20, 2006

Script to find all the files

I have built this over the years.. stolen pieces, added pieces, removed pieces. I am sure the baseline came from some public source. If you can identify it let me know. This is a script I run every 24 hours on every one of my databases and store the results.


SELECT
A.*
FROM
(
SELECT
DECODE(D.TABLESPACE_NAME,NULL,DECODE(FILES.TYPE,'CONTROL','CONTROL_FILE','LOG','REDO_LOG_FILE'
,'ARCHIVE_LOG_DEST','ARCHIVE_LOG_DEST','DUMP_DEST '
,'DUMP_DEST','TEMP','TRUE_TEMP'),D.TABLESPACe_NAME) "TABLESPACE_NAME"
,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME
,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )"MOUNT_POINT"
,FILES.FILE_NAME "FILE_NAME_PATH"
,tO_CHAR(FILES.BYTES / 1024/1024,'999,999,999.99') "MEG"
,COUNT "BUFFER_WAITS"
, TIME "SECONDS_WAITED"
,D.AUTOEXTENSIBLE
,D.MAXBYTES/1024/1024 "MAX_MEG"
,tO_CHAR(FILE_FREE.FREE_BYTES / 1024/1024,'999,999,999.99') "FILE_FREE_MEG"
,HIGH.HIGHWATERM
FROM
(
SELECT FILE_NAME,BYTES "BYTES",'DATA' "TYPE",FILE_ID FROM DBA_DATA_FILES UNION ALL SELECT MEMBER "FILE_NAME",BYTES "BYTES",'LOG' "DATA",0 "FILE_ID" FROM V$LOGFILE F, V$LOG L WHERE F.GROUP#=L.GROUP#
UNION ALL SELECT NAME "FILE_NAME",SUM(RECORD_SIZE) "BYTES",'CONTROL' "TYPE",0 FILE_ID FROM V$CONTROLFILE,V$CONTROLFILE_RECORD_SECTION GROUP BY NAME
union all select REPLACE(VALUE,'location=','') "FILE_NAME",0 "BYTES",'ARCHIVE_LOG_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 and vp.name like 'log_archive_dest%' and vp.value is not null and vp.isdefault='FALSE'
UNION ALL SELECT VALUE "FILE_NAME",0 "BYTES",'DUMP_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 AND UPPER(NAME) LIKE '%DUMP%DEST%'
UNION ALL SELECT NAME "FILE_NAME",BYTES,'TEMP' "TYPE",0 FILE_ID FROM V$TEMPFILE
) FILES
,V$INSTANCE V
,DBA_DATA_FILES D
,(SELECT * FROM v$datafile df,x$kcbfwait fw WHERE 1=1 AND FW.INDX+1 = DF.FILE#) WAITS
,(SELECT FILE_ID,SUM(BYTES) "FREE_BYTES" FROM DBA_FREE_SPACE GROUP BY FILE_ID) FILE_FREE
,(SELECT A.TABLESPACE_NAME ,A.FILE_NAME ,A.FILE_ID,
ROUND(((B.MAXIMUM+C.BLOCKS-1)*D.DB_BLOCK_SIZE)/1024/1024) HIGHWATERM
FROM DBA_DATA_FILES A ,(SELECT FILE_ID,MAX(BLOCK_ID) MAXIMUM FROM DBA_EXTENTS GROUP BY FILE_ID) B
,DBA_EXTENTS C ,(SELECT VALUE DB_BLOCK_SIZE FROM V$PARAMETER WHERE UPPER(NAME)='DB_BLOCK_SIZE') D
WHERE 1=1 AND A.FILE_ID = B.FILE_ID AND C.FILE_ID = B.FILE_ID AND C.BLOCK_ID = B.MAXIMUM ORDER BY
A.TABLESPACE_NAME
,A.FILE_NAME
) HIGH
WHERE
1=1
AND D.FILE_NAME (+)=FILES.FILE_NAME
AND D.FILE_ID= WAITS.FILE# (+)
AND FILE_FREE.FILE_ID(+) = FILES.FILE_ID
AND HIGH.FILE_ID (+) = FILES.FILE_ID
ORDER BY SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') ),REPLACE(REPLACE(FILES.FILE_NAME,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )'/',NULL),'oracle/'V.INSTANCE_NAME'/',NULL)
) a
WHERE
1=1
ORDER BY 1,2,3,4
;
Certainly isn't the fastest or the prettiest script out there. But the information it provides is beneficial. You will need to modify the mount point parsing to match your environment. I couldn't find the one I built for windows which takes that into account.

2 comments:

uiyui said...

Joy in warcraft leveling living comes wow lvl from having wow lvl fine emotions,wow power level trusting them,power leveling giving them power leveling the freedom of wrath of the lich king power leveling a bird in the open.wlk power leveling Joy in living can age of conan gold never be assumed as a pose,or put on from guildwars gold the outside as a mask. People who have this joy don not need maple story mesos to talk about it; they radiate it. wow gold They just live out their joy and let wow power leveling it splash its sunlight and glow into other lives as naturally as bird sings.

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa