<?xml version="1.0" encoding="UTF-8" ?>
<jsp:root
	xmlns:jsp="http://java.sun.com/JSP/Page"
	xmlns:og="hybs-taglib.tld" 
	version="2.0">
<jsp:directive.page language="java" contentType="text/html; charset=UTF-8" />

<og:head headType="result" />
<jsp:directive.include file="/jsp/custom/result_info.jsp" />
<og:comment
	system   = "GE:エンジンサンプル"
	pgid     = "GE1004"
	title    = "DB検索"
	date     = "2007/12/12"
	author   = "K.HASEGAWA"
	text     = "ZZZZ"
	version  = "001"
/>

<og:query command="{@command}" debug="{@debug}" dbid="{@DBID}" >
		select distinct
			df.FILE_ID																"No" ,
			df.TABLESPACE_NAME														,
			df.FILE_NAME															,
			round(df.USER_BYTES/(1024*1024))										"TOTAL(MB)",
			round(((df.USER_BYTES) - COALESCE(fs.FREE_EXTENTS,0))/(1024*1024),1)	"USED(MB)",
			round(COALESCE(fs.FREE_EXTENTS,0)/(1024*1024),1)						"FREE(MB)",
			round((COALESCE(fs.FREE_EXTENTS,0)/df.USER_BYTES)*100,1)				"FREE(%)",
			round(df.USER_BYTES/(1024*1024)*2)										NEW_MB,
			df.AUTOEXTENSIBLE														AUTO,
			df.ONLINE_STATUS														STATUS ,
			case when round((COALESCE(fs.FREE_EXTENTS,0)/df.USER_BYTES)*100,1) &lt; 30
						AND ( df.AUTOEXTENSIBLE = 'NO' OR df.AUTOEXTENSIBLE = 'TEMP' )
				 then '1' ELSE '0' end FLAG
		from
			(select
				FILE_ID			,
				TABLESPACE_NAME	,
				FILE_NAME		,
				AUTOEXTENSIBLE	,
				sum(USER_BYTES) USER_BYTES ,
				ONLINE_STATUS
			from
				DBA_DATA_FILES
			group by FILE_ID,TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,ONLINE_STATUS
			union
			select
				99			,
				'TEMP'		,
				NAME		,
				'TEMP'		,
				BYTES		,
				STATUS
			from v$tempfile
			) df,
			(select
				TABLESPACE_NAME,
				FILE_ID,
				sum(BYTES) FREE_EXTENTS
			from
				DBA_FREE_SPACE
			group by TABLESPACE_NAME,FILE_ID
			) fs
		where
			df.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)
		and	df.FILE_ID         = fs.FILE_ID(+)
		<og:where startKey="and">
			<og:and value="df.TABLESPACE_NAME like '{@OBJECT_NAME}'" />
		</og:where>
		order by 2,3
</og:query>

<og:viewLink command="{@command}">
	<og:link column="NEW_MB" href="resizeDB.jsp" target="SUB_WIND" onLink="[AUTO]" markList="NO"
		keys="command,FILE_NAME,NEW_MB,FILETYPE" vals="NEW,[FILE_NAME],[NEW_MB],DATAFILE" />
	<og:link column="NEW_MB" href="resizeDB.jsp" target="SUB_WIND" onLink="[AUTO]" markList="TEMP"
		keys="command,FILE_NAME,NEW_MB,FILETYPE" vals="NEW,[FILE_NAME],[NEW_MB],TEMPFILE" />
</og:viewLink>

<og:viewMarker command="{@command}">
	<og:columnMarker column="FREE(%)" onMark="[FLAG]" markList="1" >
		<span style="background-color:#ccff00">[FREE(%)]</span>
	</og:columnMarker>
</og:viewMarker>

<og:view
	viewFormType = "HTMLTable"
	command    = "{@command}"
	writable   = "false"
	noDisplay  = "FLAG"
	startNo    = "{@startNo}"
	pageSize   = "{@pageSize}"
/>

<jsp:directive.include file="/jsp/common/htmlend.jsp" />
</jsp:root>
