[ 메뉴얼 ]
- 입력 화면 수정하는 방법(스크린 편집 메뉴 실행)
※ 입력 필드에 대한 마이너스(-) 값이 허용되지 않는 필드에 대하여 별도 처리할 경우 필드에 대하여 sign 허용에 체크
하고 From Dict에 언체크 한다.
② SE93에서T-CODE 부여
2012년 1월 26일 목요일
[SAP] Table Maintenance View 유지보수 생성
[ 메뉴얼 ]
- 입력 화면 수정하는 방법(스크린 편집 메뉴 실행)
※ 입력 필드에 대한 마이너스(-) 값이 허용되지 않는 필드에 대하여 별도 처리할 경우 필드에 대하여 sign 허용에 체크
하고 From Dict에 언체크 한다.
② SE93에서T-CODE 부여
2012년 1월 20일 금요일
oracle 시스템 조회 쿼리 모음1
1.Locks Holders and Requests
àDisplays blocking user session, the locked object, and the sessions requesting the object
select distinct sh.username||'('||sh.sid||')' "Lock Holder"
,ao.object_name "Object",
l.type,
decode(lmode,1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE','?') "LOCK MODE"
,SR.USERNAME||'('||SR.SID||')' "OBJECT REQUESTER"
from v$session sh, all_objects ao, v$lock l, v$session sr
where l.id1 = ao.object_id
and sh.sid = l.sid
and sh.lockwait is null
and sr.lockwait is not null
and l.type='TM'
order by 1,2,3
2.Memory Allocated Current Count
àReturns the amount of memory currently allocated to all sessions
select sum(value)
from v$statname n,v$sesstat s
where n.statistic#=s.statistic#
and name='session uga memory'
3.Memory Allocated Max count
àReturns the maximum amount of memory allocated to all sessions
select sum(value)
from v$statname n,v$sesstat s
where n.statistic#=s.statistic#
and name='session uga memory max'
4.MTS Circuit Report
àReturns information regarding the virtual circuits in the shared server
select rawtohex(c.circuit) "Address",
d.name "Dispatcher",ss.name "Server",
s.sid,s.serial#,
s.status,c.queue, c.messages, c.bytes
from v$circuit c, v$dispatcher d, v$session s, v$shared_server ss
where c.dispatcher = d.paddr(+)
and c.server = ss.paddr(+)
and c.saddr = s.saddr(+)
order by c.circuit
5.MTS Dispatcher Report
àReturns information regarding the dispatcher process in the shared server
select name, status, accept, messages, bytes
owned, created, idle*100 "IDLE", busy*100 "BUSY",
round((busy/(idle + busy))*100) "Percent Busy"
from v$dispatcher
order by name
6.MTS Processes
àLists user, dispatcher, server and user process information for a multi-threaded environment
select sess.username,
sess.status,
cir.queue "Query Location",
dis.name "Disp Name",
dis.status "Disp status",
ss.name "Serv Name",
ss.status "Serv Status"
from v$circuit cir, v$session sess, v$dispatcher dis, v$shared_server ss
where sess.saddr = cir.saddr
and cir.dispatcher = dis.paddr
and cir.server = ss.paddr(+)
7.MTS Queue Report
àAverage number of seconds that a shared server request waited in the queue
Select rawtohex(paddr) "Address",
Type,queued,wait,totalq, round(decode(totalq,0,0,wait/(totalq * 0.1))) "Average wait"
From v$queue
Order by paddr
8.MTS Shared Server Report
àDisplay various information regarding the shared server processes.
select name,
rawtohex(paddr) "Paddr",
status,messages,bytes,messages/bytes "Average Bytes",
breaks, rawtohex(circuit) "Circuit", requests,
idle*.01 "Idle", busy*.01 "Idle",
round(busy/(idle+busy)*100) "Percent Busy"
from v$shared_server
order by name
9.Open Transaction Count
à Returns a count of open transactions [Use as a proxy for system overhead]
Select sum(xacts)
From v$rollstat
10.Parse Efficiency
àPercentage of time that SQL Statement were parsed only once
select round(sum(decode(name,'opened cursors cumulative',value,0))
/sum(decode(name,'parse count',value,0))*100,2)
from v$sysstat
11.Parse Report
àReturns various parse information
select ptc.value "Parse Time CPU",
pte.value "Parse Time Elapsed",
pc.value "Parse Count"
from v$sysstat ptc, v$sysstat pte, v$sysstat pc
where ptc.statistic#=96
and pte.statistic#=97
and pc.statistic#=98
12.Physical Read Count
àTotal Number of physical reads
Select sum(value)
From v$sysstat
Where name='physical read'
13.Process Report
àInformation about background and user process currently accessing the database
select p.pid, p.spid, p.username, s.username "Oracle User" ,
decode(s.terminal,null,p.terminal,s.terminal) Terminal,
decode(s.program,null,p.program,s.program) Program,
p.latchwait, s.lockwait,
decode(s.command,0,'None',nvl(a.name,'Unknown')) Command
from audit_actions a, v$process p ,v$session s
where s.paddr = p.addr
and a.action(+) = s.command
14.Recursive Calls Count
àTotal number of recursive calls(Oracle issued SQL Statements)
select value
from v$sysstat
where name='recursive calls'
15.Redo Log Allocation Latch Contention
àPercentage of time that a process attempted to acquire a redo log latch hold by another process
select round(greatest(
(sum(decode(ln.name,'redo copy',misses,0))
/ greatest(sum(decode(ln.name,'redo copy', gets,0)),1)),
(sum(decode(ln.name,'redo allocation',misses,0))
/greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)),
(sum(decode(ln.name,'redo copy', immediate_misses,0))
/greatest(sum(decode(ln.name,'redo copy', immediate_gets,0))
+sum(decode(ln.name,'redo copy',immediate_misses,0)),1)),
(sum(decode(ln.name,'redo allocation',immediate_misses,0))
/greatest(sum(decode(ln.name,'redo allocation',immediate_gets,0))
+sum(decode(ln.name,'redo allocation',immediate_misses,0)),1)))*100,2)
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
16.Redo Log Buffer Contention
àNumber of times a user process waited for redo log buffer space
select value
from v$sysstat
where name='redo log space waittime'
17.Redo Log Report – Assorted Information regarding the redo logs
select sum(decode(name,'redo blocks written',value,0)) "Block Writes",
sum(decode(name,'redo entried',value,0)) "Entries",
sum(decode(name,'redo size', value,0)) "Size",
sum(decode(name,'redo log space requests',value,0)) "Space Requests",
sum(decode(name,'redo synch writes',value,0))"Synch Writes" ,
sum(decode(name,'redo writes',value,0)) "Writes"
from v$sysstat
18.Reload Efficiency
àPercentage of time that SQL Statements were reloaded
Select round((1-(sum(reloads)/sum(pins)))*100,2)
From v$librarycache
19.Rollback Segment Contention
àPercentage that a request for data resulted in a wait for a rollback segment
Select round(sum(waits)/sum(gets),2)
From v$rollstat
20.Rollback Segment Current Report
àShows which rollback segments contain active transactions from which users
select n.usn
, n.name
, s.username Name
, s.osuser
, rs.extents
, rs.wraps
, rs.rssize "SIze(Bytes)"
from v$rollname n, v$rollstat rs, v$session s, v$transaction t
where t.addr = s.taddr(+)
and rs.usn(+) = n.usn
and t.xidusn(+) = n.usn
and rs.status = 'ONLINE'
order by n.usn
àDisplays blocking user session, the locked object, and the sessions requesting the object
select distinct sh.username||'('||sh.sid||')' "Lock Holder"
,ao.object_name "Object",
l.type,
decode(lmode,1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE','?') "LOCK MODE"
,SR.USERNAME||'('||SR.SID||')' "OBJECT REQUESTER"
from v$session sh, all_objects ao, v$lock l, v$session sr
where l.id1 = ao.object_id
and sh.sid = l.sid
and sh.lockwait is null
and sr.lockwait is not null
and l.type='TM'
order by 1,2,3
2.Memory Allocated Current Count
àReturns the amount of memory currently allocated to all sessions
select sum(value)
from v$statname n,v$sesstat s
where n.statistic#=s.statistic#
and name='session uga memory'
3.Memory Allocated Max count
àReturns the maximum amount of memory allocated to all sessions
select sum(value)
from v$statname n,v$sesstat s
where n.statistic#=s.statistic#
and name='session uga memory max'
4.MTS Circuit Report
àReturns information regarding the virtual circuits in the shared server
select rawtohex(c.circuit) "Address",
d.name "Dispatcher",ss.name "Server",
s.sid,s.serial#,
s.status,c.queue, c.messages, c.bytes
from v$circuit c, v$dispatcher d, v$session s, v$shared_server ss
where c.dispatcher = d.paddr(+)
and c.server = ss.paddr(+)
and c.saddr = s.saddr(+)
order by c.circuit
5.MTS Dispatcher Report
àReturns information regarding the dispatcher process in the shared server
select name, status, accept, messages, bytes
owned, created, idle*100 "IDLE", busy*100 "BUSY",
round((busy/(idle + busy))*100) "Percent Busy"
from v$dispatcher
order by name
6.MTS Processes
àLists user, dispatcher, server and user process information for a multi-threaded environment
select sess.username,
sess.status,
cir.queue "Query Location",
dis.name "Disp Name",
dis.status "Disp status",
ss.name "Serv Name",
ss.status "Serv Status"
from v$circuit cir, v$session sess, v$dispatcher dis, v$shared_server ss
where sess.saddr = cir.saddr
and cir.dispatcher = dis.paddr
and cir.server = ss.paddr(+)
7.MTS Queue Report
àAverage number of seconds that a shared server request waited in the queue
Select rawtohex(paddr) "Address",
Type,queued,wait,totalq, round(decode(totalq,0,0,wait/(totalq * 0.1))) "Average wait"
From v$queue
Order by paddr
8.MTS Shared Server Report
àDisplay various information regarding the shared server processes.
select name,
rawtohex(paddr) "Paddr",
status,messages,bytes,messages/bytes "Average Bytes",
breaks, rawtohex(circuit) "Circuit", requests,
idle*.01 "Idle", busy*.01 "Idle",
round(busy/(idle+busy)*100) "Percent Busy"
from v$shared_server
order by name
9.Open Transaction Count
à Returns a count of open transactions [Use as a proxy for system overhead]
Select sum(xacts)
From v$rollstat
10.Parse Efficiency
àPercentage of time that SQL Statement were parsed only once
select round(sum(decode(name,'opened cursors cumulative',value,0))
/sum(decode(name,'parse count',value,0))*100,2)
from v$sysstat
11.Parse Report
àReturns various parse information
select ptc.value "Parse Time CPU",
pte.value "Parse Time Elapsed",
pc.value "Parse Count"
from v$sysstat ptc, v$sysstat pte, v$sysstat pc
where ptc.statistic#=96
and pte.statistic#=97
and pc.statistic#=98
12.Physical Read Count
àTotal Number of physical reads
Select sum(value)
From v$sysstat
Where name='physical read'
13.Process Report
àInformation about background and user process currently accessing the database
select p.pid, p.spid, p.username, s.username "Oracle User" ,
decode(s.terminal,null,p.terminal,s.terminal) Terminal,
decode(s.program,null,p.program,s.program) Program,
p.latchwait, s.lockwait,
decode(s.command,0,'None',nvl(a.name,'Unknown')) Command
from audit_actions a, v$process p ,v$session s
where s.paddr = p.addr
and a.action(+) = s.command
14.Recursive Calls Count
àTotal number of recursive calls(Oracle issued SQL Statements)
select value
from v$sysstat
where name='recursive calls'
15.Redo Log Allocation Latch Contention
àPercentage of time that a process attempted to acquire a redo log latch hold by another process
select round(greatest(
(sum(decode(ln.name,'redo copy',misses,0))
/ greatest(sum(decode(ln.name,'redo copy', gets,0)),1)),
(sum(decode(ln.name,'redo allocation',misses,0))
/greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)),
(sum(decode(ln.name,'redo copy', immediate_misses,0))
/greatest(sum(decode(ln.name,'redo copy', immediate_gets,0))
+sum(decode(ln.name,'redo copy',immediate_misses,0)),1)),
(sum(decode(ln.name,'redo allocation',immediate_misses,0))
/greatest(sum(decode(ln.name,'redo allocation',immediate_gets,0))
+sum(decode(ln.name,'redo allocation',immediate_misses,0)),1)))*100,2)
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
16.Redo Log Buffer Contention
àNumber of times a user process waited for redo log buffer space
select value
from v$sysstat
where name='redo log space waittime'
17.Redo Log Report – Assorted Information regarding the redo logs
select sum(decode(name,'redo blocks written',value,0)) "Block Writes",
sum(decode(name,'redo entried',value,0)) "Entries",
sum(decode(name,'redo size', value,0)) "Size",
sum(decode(name,'redo log space requests',value,0)) "Space Requests",
sum(decode(name,'redo synch writes',value,0))"Synch Writes" ,
sum(decode(name,'redo writes',value,0)) "Writes"
from v$sysstat
18.Reload Efficiency
àPercentage of time that SQL Statements were reloaded
Select round((1-(sum(reloads)/sum(pins)))*100,2)
From v$librarycache
19.Rollback Segment Contention
àPercentage that a request for data resulted in a wait for a rollback segment
Select round(sum(waits)/sum(gets),2)
From v$rollstat
20.Rollback Segment Current Report
àShows which rollback segments contain active transactions from which users
select n.usn
, n.name
, s.username Name
, s.osuser
, rs.extents
, rs.wraps
, rs.rssize "SIze(Bytes)"
from v$rollname n, v$rollstat rs, v$session s, v$transaction t
where t.addr = s.taddr(+)
and rs.usn(+) = n.usn
and t.xidusn(+) = n.usn
and rs.status = 'ONLINE'
order by n.usn
오라클 uptime조회 쿼리
※ 오라클 uptime조회 쿼리
col host_name form a10 heading "Host"col instance_name form a8 heading "Instance" newline
col stime form a40 Heading "Database Started At" newline
col uptime form a60 heading "Uptime" newline
set heading off
select 'Hostname : ' || host_name
,'Instance Name : ' || instance_name
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance
※ Buffer Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;
※ Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
※ Data Dictionary Cache Hit Ratio
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;
※ Tablespace 사용량
SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;
※ 서버 메모리
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool
col host_name form a10 heading "Host"col instance_name form a8 heading "Instance" newline
col stime form a40 Heading "Database Started At" newline
col uptime form a60 heading "Uptime" newline
set heading off
select 'Hostname : ' || host_name
,'Instance Name : ' || instance_name
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance
※ Buffer Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;
※ Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
※ Data Dictionary Cache Hit Ratio
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;
※ Tablespace 사용량
SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;
※ 서버 메모리
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool
ORACLE Tablespace 체크 쿼리
set linesize 209
set pagesize 26
SET FEEDBACK OFF
SET HEADING ON
set timing off
set echo off
SET UNDERLINE =
variable xxx char(200)
column "Tablespace" format A16
column "Initial extent(Mb)" format 999,999
column "Status" format a7
column "Next extent(MB)" format 999,999
column "Max extents" format 99,999,999,999
column "Pct_increase" format 999
COLUMN "USED SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "USED BLOCKS" FORMAT 999,999,999
COLUMN "USED USAGE" FORMAT A10
COLUMN "FREE SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "FREE BLOCKS" FORMAT 999,999,999
COLUMN "FREE USAGE" FORMAT A10
COLUMN "TOTAL SIZE(Mb)" FORMAT 99,999,999,999
COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999
break on "Tablespace"
declare
cursor c1 is select 'Print Date:'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||' ***** Tablespace Information from DataBase :'||name||' Instance:'||instance||' *****' xxx
from v$database,v$thread
where rownum=1;
begin
open c1;
fetch c1 into :xxx;
close c1;
end;
/
set pagesize 1
print xxx
set pagesize 24
TTITLE RIGHT FORMAT 999 'Page:' SQL.PNO
BTITLE CENTER '================================================================================================================================================================================================================='
select substr(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
MAX(A.initial_extent)/1024 "Initial extent(Kb)",
MAX(A.next_extent)/1024 "Next extent(Kb)",
MAX(A.max_extents) "Max extents",
MAX(A.pct_increase) "Pct_increase",
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-(SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS",
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID) "TOTAL BLOCKS"
from dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 1;
TTITLE OFF
BTITLE OFF
SET FEEDBACK ON
set pagesize 26
SET FEEDBACK OFF
SET HEADING ON
set timing off
set echo off
SET UNDERLINE =
variable xxx char(200)
column "Tablespace" format A16
column "Initial extent(Mb)" format 999,999
column "Status" format a7
column "Next extent(MB)" format 999,999
column "Max extents" format 99,999,999,999
column "Pct_increase" format 999
COLUMN "USED SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "USED BLOCKS" FORMAT 999,999,999
COLUMN "USED USAGE" FORMAT A10
COLUMN "FREE SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "FREE BLOCKS" FORMAT 999,999,999
COLUMN "FREE USAGE" FORMAT A10
COLUMN "TOTAL SIZE(Mb)" FORMAT 99,999,999,999
COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999
break on "Tablespace"
declare
cursor c1 is select 'Print Date:'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||' ***** Tablespace Information from DataBase :'||name||' Instance:'||instance||' *****' xxx
from v$database,v$thread
where rownum=1;
begin
open c1;
fetch c1 into :xxx;
close c1;
end;
/
set pagesize 1
print xxx
set pagesize 24
TTITLE RIGHT FORMAT 999 'Page:' SQL.PNO
BTITLE CENTER '================================================================================================================================================================================================================='
select substr(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
MAX(A.initial_extent)/1024 "Initial extent(Kb)",
MAX(A.next_extent)/1024 "Next extent(Kb)",
MAX(A.max_extents) "Max extents",
MAX(A.pct_increase) "Pct_increase",
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-(SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS",
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID) "TOTAL BLOCKS"
from dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 1;
TTITLE OFF
BTITLE OFF
SET FEEDBACK ON
피드 구독하기:
글 (Atom)