Change to Default Setting for FORMS_RESTRICT_ENTER_QUERY Environment Variable
The environment setting FORMS_RESTRICT_ENTER_QUERY has a default value of TRUE. This setting disallows users from using QUERY-WHERE functionality until it is set to FALSE in the default.env file.
Monday, July 12, 2010
Saturday, July 10, 2010
Rhombus Design
*
***
*****
***
*
SET serveroutput ON;
DECLARE
i NUMBER := 6;
j NUMBER := 0;
BEGIN
i := 2;
j := 2;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i + 2;
j := j - 1;
EXIT WHEN i >= 6;
END LOOP;
i := 6;
j := 0;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i - 2;
j := j + 1;
EXIT WHEN i <= 1;
END LOOP;
END;
/
*****
***
*
***
*****
SET serveroutput ON;
DECLARE
i NUMBER := 6;
j NUMBER := 0;
BEGIN
i := 6;
j := 0;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i - 2;
j := j + 1;
EXIT WHEN i <= 1;
END LOOP;
i := 4;
j := 1;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i + 2;
j := j - 1;
EXIT WHEN i >= 7;
END LOOP;
END;
/
***
*****
***
*
SET serveroutput ON;
DECLARE
i NUMBER := 6;
j NUMBER := 0;
BEGIN
i := 2;
j := 2;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i + 2;
j := j - 1;
EXIT WHEN i >= 6;
END LOOP;
i := 6;
j := 0;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i - 2;
j := j + 1;
EXIT WHEN i <= 1;
END LOOP;
END;
/
*****
***
*
***
*****
SET serveroutput ON;
DECLARE
i NUMBER := 6;
j NUMBER := 0;
BEGIN
i := 6;
j := 0;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i - 2;
j := j + 1;
EXIT WHEN i <= 1;
END LOOP;
i := 4;
j := 1;
LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i + 2;
j := j - 1;
EXIT WHEN i >= 7;
END LOOP;
END;
/
Wednesday, July 7, 2010
access windows shared folder from linux
mkdir -p /home/oracle/xls
mount -t cifs //192.168.0.28/sh -o username=os_username,password=os_password /home/oracle/xls
mount -t cifs //192.168.0.28/sh -o username=
Sunday, July 4, 2010
Dump_xml
CREATE
OR
REPLACE
PROCEDURE rmver721.dump_xml
(
p_query VARCHAR2,
p_file VARCHAR2
DEFAULT
'TEMP',
p_dir VARCHAR2
DEFAULT
'XLS_DIR'
)
IS
a
CLOB;
ctx DBMS_XMLGEN.ctxhandle;
BEGIN
ctx :=
DBMS_XMLGEN.newcontext
(p_query);
DBMS_XMLGEN.setrowsettag
(ctx,
'ROWSET');
DBMS_XMLGEN.setrowtag
(ctx,
'ROW');
a
:=
DBMS_XMLGEN.getxml
(ctx);
dpr_clobtofile
(p_file || '.xml', p_dir,
a);
END;
/
CREATE
OR
REPLACE
PROCEDURE rmver721.dpr_clobtofile
(
p_filename IN
VARCHAR2,
p_dir IN
VARCHAR2,
p_clob IN
CLOB
)
IS
c_amount CONSTANT
BINARY_INTEGER
:=
32767;
l_buffer VARCHAR2
(32767);
l_chr10 PLS_INTEGER;
l_cloblen PLS_INTEGER;
l_fhandler UTL_FILE.file_type;
l_pos PLS_INTEGER
:=
1;
BEGIN
l_cloblen :=
DBMS_LOB.getlength
(p_clob);
l_fhandler := UTL_FILE.fopen (p_dir, p_filename,
'W', c_amount);
WHILE l_pos < l_cloblen
LOOP
l_buffer :=
DBMS_LOB.SUBSTR
(p_clob, c_amount, l_pos);
EXIT
WHEN l_buffer IS
NULL;
l_chr10 :=
INSTR
(l_buffer,
CHR
(10),
-1);
IF l_chr10 !=
0
THEN
l_buffer :=
SUBSTR
(l_buffer,
1, l_chr10 -
1);
END
IF;
UTL_FILE.put_line (l_fhandler, l_buffer,
TRUE);
l_pos := l_pos +
LEAST
(LENGTH
(l_buffer)
+
1, c_amount);
END
LOOP;
UTL_FILE.fclose (l_fhandler);
EXCEPTION
WHEN
OTHERS
THEN
IF UTL_FILE.is_open (l_fhandler)
THEN
UTL_FILE.fclose (l_fhandler);
END
IF;
RAISE;
END;
/
Top SQL
Top SQL
By buffer gets
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by buffer_gets desc nulls last;
By buffer gets/rows proc
select substr(sql_text,1,500) "SQL",
round((cpu_time/1000000),3) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by (buffer_gets/nvl(replace(rows_processed,0,1),1)) desc nulls last;
By CPU
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by cpu_time desc nulls last;
By disk reads
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by disk_reads desc nulls last;
By Executions
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by executions desc nulls last;
By waits
select INST_ID,
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
--round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
substr(sql_text,1,500) "SQL",
module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
(select sql_id
, CASE WHEN session_state = 'ON CPU' THEN 'CPU'
WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
ELSE 'WAIT' END state
from gv$active_session_history
where session_type IN ( 'FOREGROUND')
and sample_time between trunc(sysdate,'MI') - :minutes/24/60 and trunc(sysdate,'MI') )
group by sql_id, state),
ranked_sqls AS
(select sql_id, sum(occur) sql_occur , rank () over (order by sum(occur)desc) xrank
from sql_class
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
--and rs.xrank <= :top_n
order by xrank, sql_id, state ))
order by elapsed_time desc nulls last;
Friday, July 2, 2010
Thursday, July 1, 2010
Blocking locks by user
select /*+ ORDERED */
blocker.sid blocker_sid
, waiting.sid waiting_sid
, TRUNC(waiting.ctime/60) min_waiting
, waiting.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock waiting
where waiting.type='TX'
and waiting.block = 0
and waiting.id1 = blocker.id1
blocker.sid blocker_sid
, waiting.sid waiting_sid
, TRUNC(waiting.ctime/60) min_waiting
, waiting.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock waiting
where waiting.type='TX'
and waiting.block = 0
and waiting.id1 = blocker.id1
Locks by user
SELECT p.username ,
p.pid ,
s.sid ,
s.serial#,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
--AND u.name = 'GME'
AND (:USER_NAME is null or s.username LIKE upper(:USER_NAME))
ORDER BY 1, 2, 5, 6, 7
p.pid ,
s.sid ,
s.serial#,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
--AND u.name = 'GME'
AND (:USER_NAME is null or s.username LIKE upper(:USER_NAME))
ORDER BY 1, 2, 5, 6, 7
Subscribe to:
Posts (Atom)