[출처 : http://blog.naver.com/kairee/ ]
1. EXPORT
EXP USERID=UID/PWD FILE='/DATA/SAM.DAT' ROWS=Y COMPRESS=N STATISTICS=NONE OWNER=OWNERNAME
USERID : EXPORT를 실행시키고 있는 USERNAME/PASSWORD
BUFFER : 데이터 행들을 가져오는데 사용되는 버퍼의 크기.
FILE : 생성되는 EXPORT덤프 파일명.
FILESIZE : EXPORT덤프 파일의 최대 크기
GRANTS : 데이터베이스 객체에 대한 권한 정보의 EXPORT여부 (Y/N 플래그)
INDEXES : 테이블에 대한 INDEXES의 EXPORT여부 (Y/N 플래그)
ROWS : 행들에 대한 EXPORT여부. (Y/N 플래그)
만약, “NO”이면 데이터는 EXPORT되지않고 테이블의 정의만 EXPORT 됩니다.
CONSTRAINTS : 테이블에 대한 제약조건 정보의 EXPORT여부 (Y/N 플래그)
COMPRESS : IMPORT에 대비하여 테이블의 데이터를 한 EXTENT로 압축 할것인가의 여부 (Y/N 플래그)
FULL : 전체 데이터베이스를 EXPORT할것인가의 여부
(FULL LEVEL EXPORT) (Y/N 플래그)
OWNER : EXPORT될 데이터베이스의 소유자명 (USER LEVEL EXPORT)[OWNER=USER]
TABLES : EXPORT될 테이블의 리스트(TABLE LEVEL EXPORT)
[TABLES=(TABLE1, TABLE2, ...)]
STATISTICS=NONE : 이 옵션을 주면 통계 데이타를 만들지 않는다.
간혹 이 옵션없이 통계 데이타를 만들다가 오류가 나는 경우가 있다.
EX) EXP-00091: 불완전한 통계를 엑스포트 중입니다.
그러나 데이터에는 아무런 이상이 없다.
2. IMPORT
IMP USERID=USERID/PWD TABLES=TABLE1,TABLE2,TABLE3 IGNORE=Y
USERID : IMPORT를 실생시키는 계정의 USERNAME/PASSWORD
BUFFER : 데이터를 행들을 가져오는데 사용되는 BUFFER의 BYTES수
FILE : IMPORT될 EXPORT 덤프 파일명
SHOW : 파일 내용이 화면에 표시되어야 할 것인가를 나타냄(Y/N 플래그)
IGNORE : IMPORT중 CREATE명령을 실행할 때 만나게 되는 에러들을 무시할 것인지 결정 (Y/N 플래그)
INDEXES : 테이블 INDEX의 IMPORT여부(Y/N 플래그)
ROWS : 테이블 데이터를 IMPORT할 것인가(Y/N 플래그)
만약, "N"로 설정하면 데이터베이스 객체들에 대한 DDL만이 실행됩니다.
FULL : FULL엑스포트 덤프 파일이 IMPORT 할때 사용합니다.
TABLES : IMPORT될 테이블 리스트
COMMIT : 배열(배열의 크기는 BUFFER에 의해 설정됩니다) 단위로
COMMIT을 할것인가 결정, 기본적으로는 테이블 단위로 COMMIT을 합니다.
FROMUSER : EXPORT덤프 파일로 부터 읽혀져야 하는 객체들을 갖고 있는 테이터베이스 계정
TOUSER : EXPORT덤프 안에 있는 객체들이 IMPORT될 데이터베이스 계정
2012년 2월 17일 금요일
2012년 2월 3일 금요일
forms6i를 Windows7 64bit에서 개발하기
http://oracleforms.blogspot.com/2008/04/oracle-forms-6i-en-windows-de-64-bits.html
파일을 다운받아 oracle\bin 폴더에 넣으면 끝
파일을 다운받아 oracle\bin 폴더에 넣으면 끝
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
2011년 12월 7일 수요일
Forms에서 멀티 라인 입력하기
The idea for this sample form came to me from the Oracle Technology Network (OTN) user forums. The question was asked: Is there a way to count the number of characters a user has typed into a multi-line Text-Item and display the number of characters remaining? I knew this could be done with a timer, but had never seen it done nor had I done it myself. So, I decided it was time to figure out how to do it.
I hope you find the following sample Form useful. The code included is not specific to any version of Oracle Forms, however, I have only tested the code with Oracle Forms 10g R2 (10.1.2.0.2). The supplied sample form is written using this version, but you should be able to use the supplied code in Forms 6i or higher.
The following sample will walk you through creating a non-base table form that employs a timer to count the number of characters that have been entered in a multi-line Text-Item and display how many characters are left. It is assumed that you have a basic working knowledge of Oracle Forms
If you find bugs with the supplied form or code, please let me know.
1. Open Forms Builder and log into any database.
2. Open Create a new forms module.
3. Create a new Canvas and set the following properties in the Property Palette:
a. Name: MLINE_COUNTER or leave at default name
b. Width: 380
c. Height: 250
4. Create a new “Non-Base table” data block (do not use the Data Block Wizard to create the block) and change the following block properties in the Property Palette:
a. Name: MLINE_COUNTER or leave at default name
b. Database Data Block: No
c. Query Data Source Type: None
5. Add three Text-Items to the MLINE_COUNTER block and change the following item properties:
a. ITEM1
i. Name: MLINE_TEXT
ii. Multi-line: Yes
iii. Maximum Length: 500
iv. Database Item: No
v. Visible: Yes
vi. Canvas: MLINE_COUNTER
vii. X Position: 35
viii. Y Position: 19
ix. Width: 310
x. Height: 127
xi. Show Vertical Scroll Bar: Yes
b. ITEM2
i. Name: DSP_TEXT_COUNT
ii. Item Type: Display Item
iii. Maximum Length: 100
iv. Database Item: No
v. Visible: Yes
vi. Canvas: MLINE_COUNTER
vii. X Position: 96
viii. Y Position: 150
ix. Width: 188
x. Height: 14
xi. Bevel: None
c. ITEM3 (This item is not required, but was used during development to prove the concept. You can skip this step if you want.)
i. Name: BTN_COUNT_TEXT
ii. Item Type: Push Button
iii. Label: Count
iv. Canvas: MLINE_COUNTER
v. X Position: 156
vi. Y Position: 174
vii. Width: 68
viii. Height: 16
6. Now we will add three Program Units to encapsulate the code for the counting process. Again, it is assumed that you familiar enough with Oracle Forms to add a program unit to the Program Units node in the Object Navigator.
a. GET_ITEM_COUNT_F (Function)
i. Program Unit Text:
FUNCTION get_item_count_f (p_block_item VARCHAR2) RETURN VARCHAR2 IS nCount NUMBER := 0; nItmSize NUMBER := 0; nRemaining NUMBER := 0; vRetVal VARCHAR2(100); BEGIN nItmSize := Get_Item_Property(p_block_item, MAX_LENGTH); nCount := length(NAME_IN(p_block_item)); nRemaining := nItmSize - nvl(nCount,0); vRetVal := 'Remaining: '||nRemaining||' of '||nItmSize; RETURN vRetVal; END; |
b. MYTIMER (Package Specification)
i. Program Unit Text:
PACKAGE MyTimer IS tID TIMER; v_block_item VARCHAR2(61); PROCEDURE SET_TIMER (p_item VARCHAR2); PROCEDURE DEL_TIMER (p_item VARCHAR2); END; |
c. MYTIMER (Package Body)
i. Program Unit Text:
PACKAGE BODY MyTimer IS -- The value passed to this procedure should be the value of -- :SYSTEM.CURSOR_ITEM (if Forms version 6i, use -- :SYSTEM.CURRENT_ITEM) PROCEDURE SET_TIMER (p_item VARCHAR2) AS BEGIN IF ( p_item = v_block_item ) THEN -- tID is a Package level Variable tID := Create_Timer('MLINE_TEXT',1000,REPEAT); -- These ELSIF statements are here only to demonstrate -- how you can extend this procedure to support multiple items -- in a Form that you use this method on. They can be excluded -- if you do not want to include them. ELSIF ( p_item = 'BLOCK2.Item2' ) THEN tID := Create_Timer('ITEM2',1000,REPEAT); ELSIF ( p_item = 'BLOCK2.Item3' ) THEN tID := Create_Timer('ITEM3',1000,REPEAT); END IF; END SET_TIMER; PROCEDURE DEL_TIMER (p_item VARCHAR2) AS BEGIN IF ( p_item = v_block_item ) THEN -- tID is a Package level Variable Delete_Timer('MLINE_TEXT'); ELSIF ( p_item = 'BLOCK2.Item2' ) THEN Delete_Timer('ITEM2'); ELSIF ( p_item = 'BLOCK2.Item3' ) THEN Delete_Timer('ITEM3'); END IF; END DEL_TIMER; END; |
7. Create a Form level When-Timer-Expired trigger and add the following Trigger Text:
DECLARE vTimer VARCHAR2(30) := Get_Application_Property(TIMER_NAME); BEGIN IF ( vTimer = 'COUNTER' ) THEN :MLINE_COUNTER.DSP_TEXT_COUNT := get_item_count_f(:SYSTEM.CURSOR_ITEM); END IF; END; |
At this point, your form should look similar to the following:
You are now ready to compile and run your form. It is assumed that you are familiar enough with Oracle Forms to configure your installation to allow running Form locally. For Forms 6i, this is quite simple as the form will run in Client/Server mode Forms Runtime. If you are using Oracle Forms 9i or higher then you will need to configure your Forms installation to enable running a form from your developer installation of Oracle Forms. How to configure forms to run locally is covered in a separate document. You could also deploy this form to a Development environment to test.
Run the form and as you type, your form should look similar to the following:
Attachments (2)
피드 구독하기:
글 (Atom)