tag:blogger.com,1999:blog-55848176662977893372024-03-14T09:07:37.705+02:00Zukus and OracleZukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.comBlogger41125tag:blogger.com,1999:blog-5584817666297789337.post-30680690363945235352024-01-17T23:23:00.000+02:002024-01-17T23:23:12.584+02:00How to convert seconds to HH24:MI:SS<p> First example is convert seconds to HH24:MI with using to_char:</p>
<pre class="prettyprint linenums lang-sql">with t as (select 800000000 sec from dual)
select nvl(trim(to_char(trunc(round(sec/3600, 2)), 'FM9999999900')), '00')
|| ':'
|| nvl(to_char(mod(trunc(round(sec/60, 2)), 60), 'FM00'), '00') in_hh_mi
from t
;
</pre>
<pre class="prettyprint lang-sql">
IN_HH_MI
---------------
222222:13
1 row selected.
</pre>
<p> Next example is convert seconds to HH24:MI:SS with using NUMTODSINTERVAL:</p>
<pre class="prettyprint linenums lang-sql">
with t as(select NUMTODSINTERVAL(nvl(800000000, 0) , 'second') ds_int from dual)
select ds_int
,to_char(extract(day from ds_int) * 24
+ extract(hour from ds_int), 'FM9999999900')
|| ':' || to_char(extract(minute from ds_int), 'FM00')
|| ':' || to_char(extract(second from ds_int), 'FM00') in_hh_mi_ss
from t;
</pre>
<pre class="prettyprint lang-sql">
DS_INT IN_HH_MI_SS
-------------------------------------------------- -------------------
+9259 06:13:20.000000 222222:13:20
1 row selected.
</pre>
<blockquote>Thanks to forum.oracle.com topic <a href="https://forums.oracle.com/ords/apexds/post/convert-from-numberic-seconds-to-hh-mm-ss-0433" target="_blank" rel="nofollow">convert from numberic seconds to HH:MM:SS</a> answer of <a href="https://forums.oracle.com/ords/apexds/user/michaelrozar17" target="_blank" rel="nofollow">michaelrozar17</a></blockquote>
<p> Last example is pl/sql function <b>seconds_to_hh24_mi_ss</b> to convert seconds to HH24:MI:SS with using NUMTODSINTERVAL:</p>
<pre class="prettyprint linenums lang-sql">
declare
l_var varchar2(50);
function seconds_to_hh24_mi_ss(p_seconds number) return varchar2
is
l_ds_int INTERVAL DAY(9) TO SECOND;
begin
l_ds_int := NUMTODSINTERVAL(nvl(p_seconds, 0), 'second');
return to_char(extract(day from l_ds_int) * 24
+ extract(hour from l_ds_int), 'FM9999999900') -- hh
|| ':' || to_char(extract(minute from l_ds_int), 'FM00') -- mi
|| ':' || to_char(extract(second from l_ds_int), 'FM00') -- ss
;
end;
begin
l_var := seconds_to_hh24_mi_ss(800000000);
dbms_output.put_line(l_var);
end;
</pre>
<pre class="prettyprint lang-sql">
PL/SQL procedure successfully completed.
222222:13:20
</pre>Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-13526498722207440892018-05-03T19:00:00.001+03:002018-05-03T19:00:34.677+03:00Getting information about table size, lob segment and lob index segment size which belongs to table<div dir="ltr" style="text-align: left;" trbidi="on">
<pre class="prettyprint linenums lang-sql">
set linesize 256
col owner for a30
col table_name for a30
col segment_name for a90
col LOB_SECURED for a11
col LOB_COMPRESSION for a15
with t_tab as (
select -- Information about table size
t.OWNER,
t.TABLE_NAME,
t.COMPRESSION as TABLE_COMPRESSION,
t.COMPRESS_FOR as TABLE_COMPRESS_FOR
from DBA_TABLES t
where 1 = 1
and t.TABLE_NAME = NVL(UPPER('&s_table_name'), t.TABLE_NAME)
and t.OWNER = NVL(UPPER('&s_owner'), t.OWNER)
and t.OWNER not in ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'XDB')
)
select -- Information about table size
t.OWNER,
t.TABLE_NAME,
t.TABLE_COMPRESSION,
t.TABLE_COMPRESS_FOR,
ts.SEGMENT_NAME as SEGMENT_NAME,
cast(null as varchar2(30)) as LOB_SECURED,
cast(null as varchar2(30)) as LOB_COMPRESSION,
ROUND(ts.bytes/(1024*1024),2) SPACE_ALOCATED_MB
from t_tab t
join DBA_SEGMENTS ts
on ts.SEGMENT_NAME = t.TABLE_NAME
where 1 = 1
union all
select -- Information about lob columns size for table
t.OWNER,
t.TABLE_NAME,
t.TABLE_COMPRESSION,
t.TABLE_COMPRESS_FOR,
ls.segment_type || ' "' || dl.segment_name || '" for table "' || dl.table_name || '"' as SEGMENT_NAME,
dl.securefile as LOB_SECURED,
dl.compression as LOB_COMPRESSION,
ROUND(ls.bytes/(1024*1024),2) SPACE_ALOCATED_MB
from t_tab t
join DBA_LOBS dl
on dl.table_name = t.TABLE_NAME
join DBA_SEGMENTS ls
on ls.SEGMENT_NAME = dl.segment_name
where 1 = 1
union all
select -- Information about lob index for lob column
t.OWNER,
t.TABLE_NAME,
t.TABLE_COMPRESSION,
t.TABLE_COMPRESS_FOR,
lis.segment_type || ' "' || dl.index_name || '" for LOBSEGMENT "' || dl.segment_name || '"' as SEGMENT_NAME,
dl.securefile as LOB_SECURED,
dl.compression as LOB_COMPRESSION,
ROUND(lis.bytes/(1024*1024),2) SPACE_ALOCATED_MB
from t_tab t
join DBA_LOBS dl
on dl.table_name = t.TABLE_NAME
join DBA_SEGMENTS lis
on lis.SEGMENT_NAME = dl.index_name
where 1 = 1
ORDER BY SPACE_ALOCATED_MB desc, OWNER, TABLE_NAME, SEGMENT_NAME
;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-22583667139510189512018-01-12T21:18:00.003+02:002018-01-12T21:24:35.919+02:00Processes and Sessions Utilization<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
Sessions utilization
</h2>
<pre class="prettyprint linenums lang-sql">SQL> select (100*current_utilization / limit_value) as session_percent
from v$resource_limit
where resource_name = 'sessions'
;
</pre>
<br />
The limit_value can be checked by:
<br />
<pre class="prettyprint linenums lang-sql">SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 772
</pre>
<br />
<h2 style="text-align: left;">
Processes utilization
</h2>
<pre class="prettyprint linenums lang-sql">SQL> select (100*current_utilization / limit_value) as process_percent
from v$resource_limit
where resource_name = 'processes'
;
</pre>
<br />
The limit_value can be checked by:
<br />
<pre class="prettyprint linenums lang-sql">SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 500
</pre>
<br /></div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-23334185641086112332018-01-11T20:14:00.000+02:002018-01-12T20:29:14.019+02:00Determine sessions count from machine, username using v$session<div dir="ltr" style="text-align: left;" trbidi="on">
<pre class="prettyprint linenums lang-sql">set linesize 256
col machine format a50
select machine, username, count(*) cnt
FROM v$session
WHERE 1 = 1
group by machine, username
order by username, cnt desc
;
</pre>
<br />
Output:
<br />
<pre class="code"><code>
MACHINE USERNAME CNT
-------------------------------------------------- ------------------------------ ----------
...
...
</code>
</pre>
<br /></div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-76029328472691863072018-01-11T20:05:00.000+02:002019-09-18T15:35:01.465+03:00How to find the SQL statements with hard parses<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
Find similar SQL statements</h2>
1. Find similar SQL statements using gv$open_cursor
<br />
<pre class="prettyprint linenums lang-sql">select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor
where sid in
(select sid from v$open_cursor
group by sid having count(*) > &threshold);
</pre>
<br />
Added sql_id and sort by sql_text to find similar sql's<br />
<pre class="prettyprint linenums lang-sql">--
-- Added sql_id and sort by sql_text to find similar sql's
--
select sql_id, sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_id, sql_text, user_name
order by sql_text desc, count(*) desc;
</pre>
<br />
Find SQL_FULL_TEXT by SQL_ID<br />
<pre class="prettyprint linenums lang-sql">--
-- Find SQL_FULL_TEXT by SQL_ID
--
select s.sql_id, s.sql_fulltext, s.sql_text
/*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
from v$sql s
where s.sql_id = '&s_sql_id'
</pre>
<br />
2. Find similar SQL statements by first 80 characters in SQL_TEXT
<br />
<pre class="prettyprint linenums lang-sql">SELECT substr(sql_text, 1, 80), COUNT(1)
, round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
FROM v$sql
WHERE 1 = 1
--and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
--and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
GROUP BY substr(sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2 desc
</pre>
<br />
2.1. Find similar SQL statements by first 80 characters in SQL_TEXT which included specified tables:
<br />
<pre class="prettyprint linenums lang-sql">SELECT substr(s.sql_text, 1, 80), COUNT(1)
, round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
FROM v$sql s
join (
select owner, object_name from dba_objects
where owner = 'SCOTT'
and object_type = 'TABLE'
and (
object_name like upper('emp%')
or
object_name like upper('sal%')
or
object_name in ('TAB1', 'TAB2', 'TAB3')
)
) o
on o.owner = s.parsing_schema_name
WHERE 1 = 1
and upper(sql_fulltext) like '%' || upper(o.object_name) || '%'
and parsing_schema_name = 'SCOTT'
--and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
--and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
GROUP BY substr(s.sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2 desc
</pre>
<br />
2.3. Find similar SQL statements that have only one execution to see whether they are similar using V$SQLSTATS:
<br />
<pre class="prettyprint linenums lang-sql">SELECT SUBSTR(SQL_TEXT, 1, 80), COUNT(*)
FROM V$SQLSTATS
WHERE EXECUTIONS < 4
GROUP BY SUBSTR(SQL_TEXT, 1, 80)
HAVING COUNT(*) > 1
ORDER BY 2 DESC;
</pre>
<br />
3. Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT<br />
<pre class="prettyprint linenums lang-sql">--
-- Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT
--
select s.sql_id,
round(s.sharable_mem / 1024 / 1024, 2) as sharable_mem,
round(s.persistent_mem / 1024 / 1024, 2) as persistent_mem,
round(s.runtime_mem / 1024 / 1024, 2) as runtime_mem,
substr(sql_text, 1, 80), s.sql_fulltext
/*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
from v$sql s
where s.sql_text like '%' || '&s_sql_text' || '%'
order by dbms_lob.getlength(s.sql_fulltext) desc
</pre>
<br />
<h2 style="text-align: left;">
Find the SQL statements with hard parses</h2>
<blockquote class="tr_bq">
Script copied from forum topic <a href="http://www.orafaq.com/forum/mv/msg/181700/557669/#msg_557669" target="_blank">www.orafaq.com - how to find the SQL statement which have many hard parse</a> and modified for personal needs.</blockquote>
If you want to find top sql's with literals, then you can do that with the following script:<br />
<pre class="prettyprint linenums lang-sql">-- E. Nossova, Product TuTool : www.tutool.de
set pagesize 0
set feedback off
set verify off
set linesize 180
col nline print newline
col force_match_sig format 9999999999999999999999999
col pct format 99990.99
/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
format,
default: trunc(sysdate) 00:00:00,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
format,
default: sysdate,
top_n - the number of the top sql's
(default: 10) */
define min_first_load_time='&min_first_load_time'
define max_first_load_time='&max_first_load_time'
define top_n='&top_n'
select 'Force Matching Signature='||t.force_match_sig||', Count='||max(t.cnt)||', PCT='||max(t.pct)||'%, Min. Username='||min(s.username)||', Max. Username='||max(s.username)||', Min. First Load Time='||max(min_first_load_time)||', Max. First Load Time='||max(max_first_load_time), max(sql_text) nline from
(select u.username, a.force_matching_signature force_match_sig, a.sql_text
from v$sql a, dba_users u
where
a.parsing_user_id = u.user_id and
to_date(a.first_load_time,'yyyy-mm-dd/hh24:mi:ss') between
to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
a.force_matching_signature != 0 and
a.exact_matching_signature != 0 and
a.force_matching_signature != a.exact_matching_signature ) s,
(select * from
(select
force_matching_signature force_match_sig,
count(*) cnt,
min(first_load_time) min_first_load_time,
max(first_load_time) max_first_load_time,
round((ratio_to_report(count(*)) over ())*100, 2) pct
from v$sql
where
to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss') between
to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
force_matching_signature != 0 and
exact_matching_signature != 0 and
force_matching_signature != exact_matching_signature
-- Excluded from report SQL Tuning statements
and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
group by force_matching_signature
order by 2 desc
)
where
rownum <= nvl(abs('&top_n'),10)) t
where
s.force_match_sig = t.force_match_sig
group by t.force_match_sig
order by max(t.cnt) desc
/
undefine min_first_load_time
undefine max_first_load_time
undefine top_n
set feedback on
set verify on
set linesize 80
</pre>
<br />
Input substitution variables:<br />
<pre class="prettyprint linenums lang-sql">/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
format,
default: trunc(sysdate) 00:00:00,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
format,
default: sysdate,
top_n - the number of the top sql's
(default: 10) */
Enter value for min_first_load_time: 11.01.2018 00:00:00
Enter value for max_first_load_time: 11.01.2018 23:59:59
Enter value for top_n: 10
</pre>
<br />
Output:
<br />
<pre class="prettyprint linenums lang-sql">Force Matching Signature=15954354564733902021, Count=317, PCT=14.43%, Min. Username=SCOTT, Max. Username=SCOTT, Min. First Load Time=2018-01-11/01:00:04, Max. First Load Ti
me=2018-01-11/09:00:14
select count(*) from user_sequences where upper(sequence_name) = upper('SQz6zSqA41Lx0AAAE7MhJ30bj4')
...
</pre>
<br />
Get all sql_id versions of matching SQLs by "Force Matching Signature":
<br />
<pre class="prettyprint linenums lang-sql">set pagesize 0
set feedback on
set verify on
select s.sql_id, s.sql_fulltext, s.sql_text, s.sharable_mem, s.persistent_mem, s.runtime_mem
from v$sql s
where s.force_matching_signature = '&force_matching_signature'
order by dbms_lob.getlength(s.sql_fulltext) desc;
</pre>
<br />
Input substitution variables:<br />
<pre class="prettyprint linenums lang-sql">Enter value for force_matching_signature: 15954354564733902021
</pre>
<br />
<br /></div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-60954289885292251022017-07-31T17:12:00.002+03:002018-01-11T20:09:12.269+02:00Determine How Long a Session has been Idle using v$session<div dir="ltr" style="text-align: left;" trbidi="on">
Following SQL using information from LAST_CALL_ET:<br />
<pre class="prettyprint linenums lang-sql" style="text-align: left;">select s.*
--select 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' kill_serial_sid
from (
SELECT s.sid,s.serial#,s.username
,s.status
,substr(s.machine,1,10)
,s.osuser,s.module
,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
-- idle time
,to_dsinterval(
-- days separately
substr('0'||trunc(last_call_et/86400),-2,2) || ' ' ||
-- hours
substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)
) idle_time
FROM v$session s, v$process p
WHERE s.username IS NOT NULL
-- use outer join to show sniped sessions in
-- v$session that don''t have an OS process
AND p.addr(+) = s.paddr
) s
where 1 = 1
and s.status = 'INACTIVE'
and s.idle_time >= to_dsinterval('1 00:00:00')
ORDER BY idle_time desc;
</pre>
<b>Note</b>: LAST_CALL_ET - If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.<br />
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.<br />
<h2 style="text-align: left;">
Search Idle Sessions using filter in Enterprise Manager</h2>
You can specify search criteria using WHERE clause<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-lWC_dEw4kUc/WX86wV7sHEI/AAAAAAAAC-E/7mLoanO1vsQbJPeEb9YHPthmJv7VX_nrgCLcBGAs/s1600/2017-07-31_1657_search_idle_sessions.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="457" data-original-width="1036" src="https://4.bp.blogspot.com/-lWC_dEw4kUc/WX86wV7sHEI/AAAAAAAAC-E/7mLoanO1vsQbJPeEb9YHPthmJv7VX_nrgCLcBGAs/s1600/2017-07-31_1657_search_idle_sessions.png" /></a></div>
<br />
as following:
<br />
<pre class="prettyprint linenums lang-sql">1 = 1
and username IS NOT NULL
and status = 'INACTIVE'
and to_dsinterval(
-- days separately
substr('0'||trunc(last_call_et/86400),-2,2) || ' ' ||
-- hours
substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)
) >= to_dsinterval('1 00:00:00')
</pre>
<br /></div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-30315241509644746442017-06-07T14:34:00.000+03:002017-11-08T17:47:33.699+02:00Search values in all columns of all tables<div dir="ltr" style="text-align: left;" trbidi="on">
Улучшил процедуру <span style="font-family: "courier new" , "courier" , monospace;">whereIsValue</span> из статьи <a href="https://habrahabr.ru/sandbox/25460/" target="_blank"> SQL: Поиск значения по всем колонкам всех таблиц</a>:<br />
<br />
<ul style="text-align: left;">
<li>Добавил параметр schemaName для поиска в конкретной схеме</li>
<li>Заменил user_ views на all_ чтобы была возможность поиска по любой схеме</li>
<li>Обернул в кавычки <span style="font-family: "courier new" , "courier" , monospace;">"' || columnName || '"</span><span style="font-family: inherit;"> так как столкнулся с ситуацией когда колонка называлась зарезервированным словом "DATE" </span></li>
</ul>
<div>
<pre class="prettyprint linenums lang-sql">create or replace procedure whereIsValue(p_schemaName varchar2, p_searchValue varchar2)
AS
TYPE VALCUR IS REF CURSOR;
cursor tabl(c_schemaName varchar2) is select table_name from all_tables where owner = c_schemaName order by table_name;
cursor col (c_schemaName varchar2, c_tablename varchar2) is select column_name from all_tab_columns where owner = c_schemaName and table_name like c_tableName;
valueCursor VALCUR;
tableName varchar2(50);
columnName varchar2(50);
columnValue varchar2(500);
begin
open tabl(p_schemaName);
LOOP
fetch tabl into tableName;
EXIT WHEN tabl%NOTFOUND;
--dbms_output.put_line('Search in table - ' || tableName);
OPEN col(p_schemaName, tableName);
LOOP
fetch col into columnName;
EXIT WHEN col%NOTFOUND;
--dbms_output.put_line('Search in column - ' || columnName);
OPEN valueCursor for 'select "' || columnName || '" from ' || p_schemaName || '."' || tableName || '"';
LOOP
BEGIN
fetch valueCursor into columnValue;
EXIT WHEN valueCursor%NOTFOUND;
if (columnValue like p_searchValue) then
dbms_output.put_line('Found in table - ' || tableName || ' and column - ' || columnName);
exit;
end if;
EXCEPTION
WHEN OTHERS then
NULL;
END;
END LOOP;
CLOSE valueCursor;
END LOOP;
CLOSE col;
END LOOP;
end;
/
</pre>
Примеры вызова:
<br />
<pre class="prettyprint linenums lang-sql">sqlplus /nolog
conn system
SET SERVEROUTPUT ON
exec whereIsValue('SCOTT', '1496765408852');
exec whereIsValue('SCOTT', 'CLERK');
exec whereIsValue('SCOTT', '%JONES%');
</pre>
</div>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-29047185614648525202016-07-19T18:43:00.000+03:002016-08-15T18:36:02.311+03:00My personal cycling results on veloviewer.com<div dir="ltr" style="text-align: left;" trbidi="on">
<a class="rwgps-widget" data-rwgps-activities-count="3" data-rwgps-include="year" data-rwgps-metric="1" data-rwgps-user-id="768380" data-rwgps-width="300" href="https://ridewithgps.com/users/768380">Activities for Zukus</a>
<script>
(function(d,s) {
if(!d.getElementById('rwgps-sdk')) {
var el = d.getElementsByTagName(s)[0],
js = d.createElement(s);
js.id = 'rwgps-sdk';
js.src = "//ridewithgps.com/javascripts/sdk.js?1471275037736";
el.parentNode.insertBefore(js, el);
}
})(document, 'script');
</script>
<br />
<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://veloviewer.com/athlete/15889065/"><img src="https://veloviewer.com/SigImage/f2a2e2/4/3/M/n/odefaghmzz.png" /></a>
<iframe allowtransparency="true" frameborder="0" height="405" scrolling="no" src="https://www.strava.com/activities/671259809/embed/22e0093a3c0668703615a2b756d796efcd6402d9" width="590"></iframe>
</div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://veloviewer.com/athlete/15889065/"><img src="https://veloviewer.com/SigImage/f2a2e2/4/3/M/n/odefaghmzz.png" /></a>
</div>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-88553376358357757302016-06-22T17:31:00.003+03:002017-10-11T19:44:08.001+03:00TEMP tablespace usage history<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
V$TEMPSEG_USAGE</h2>
<div style="text-align: left;">
<pre class="prettyprint linenums lang-sql">SELECT username,
session_addr,
session_num,
sqladdr,
sqlhash,
sql_id,
contents,
segtype,
extents,
blocks
FROM v$tempseg_usage
ORDER BY username;
</pre>
</div>
<h2 style="text-align: left;">
Script to display the recent activity</h2>
<div style="text-align: left;">
<pre class="prettyprint linenums lang-sql">select distinct
c.username "user",
c.osuser ,
c.sid,
c.serial#,
'alter system kill session ''' || c.sid || ',' || c.serial# || ''' immediate;' kill_session_cmd,
b.spid "unix_pid",
c.machine,
c.program "program",
a.blocks * e.block_size/1024/1024 mb_temp_used ,
a.tablespace,
d.sql_text
from
v$tempseg_usage a, /* v$sort_usage */
v$process b,
v$session c,
v$sqlarea d,
dba_tablespaces e
where c.saddr=a.session_addr
and b.addr=c.paddr
and a.sqladdr=d.address(+)
and a.tablespace = e.tablespace_name
order by mb_temp_used desc;
</pre>
</div>
<h2 style="text-align: left;">
TEMP tablespace usage history</h2>
A views <strong>V$ACTIVE_SESSION_HISTORY</strong> and <strong>DBA_HIST_ACTIVE_SESS_HISTORY</strong> include temp_space_allocated field.<br />
Script below showing last 7 days top 5 SQLs for each day of temp tablespace consumers:<br />
<pre class="prettyprint linenums lang-sql">select t.sample_time, t.sql_id, t.temp_mb, t.temp_diff
,s.sql_text
from (
select --session_id,session_serial#,
--'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
, row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
from (
select sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb,
temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
--from dba_hist_active_sess_history
from v$active_session_history
where 1 = 1
-- session_id=&1
-- and session_serial#=&2
)
group by --session_id,session_serial#,
trunc(sample_time),
sql_id
) t
left join v$sqlarea s
on s.sql_id = t.sql_id
where 1 = 1
and rn <=5
and sample_time >= trunc(sysdate) - 7
order by sample_time desc, temp_mb desc
</pre>
To release TEMP space usage before <a href="http://zukus.blogspot.com/2012/03/shrinking-temporary-tablespace-in.html">shrinking temporary tablespace</a> use:
<br />
<pre class="prettyprint linenums lang-sql">select distinct
session_id,session_serial#,
'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd
from v$active_session_history
where 1 = 1
and sql_id='&3'
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-84652140185465655052016-02-25T13:49:00.001+02:002016-02-25T13:52:07.450+02:00Monitoring the recovery progress of Standard/Standby database using v$recovery_process<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Here it is script to track recovery progress of your standard/standby database:<br />
<pre class="prettyprint linenums lang-sql">set linesize 255
set pagesize 60
col type format a30
col sofar_formatted format a20
col total_formatted format a15
col comments format a30
select to_char(start_time, 'dd-mon-rr hh24:mi:ss') start_time, type, item, units,
sofar,
case units
when 'KB/sec' then round(sofar/1024,2) || ' MB/Sec'
when 'Megabytes' then round(sofar/1024,2) || ' G'
when 'Seconds' then to_char(cast(numtodsinterval(sofar, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
else to_char(sofar)
end sofar_formatted,
total,
case units
when 'KB/sec' then round(total/1024,2) || ' MB/Sec'
when 'Megabytes' then round(total/1024,2) || ' G'
when 'Seconds' then to_char(cast(numtodsinterval(total, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
else to_char(total)
end total_formatted,
comments
from v$recovery_progress;
</pre>
<h3 style="text-align: left;">
Output:</h3>
<pre class="prettyprint linenums lang-sql">set linesize 255
START_TIME TYPE ITEM UNITS SOFAR SOFAR_FORMATTED TOTAL TOTAL_FORMATTED COMMENTS
--------------------------- ------------------------------ -------------------------------- -------------------------------- ---------- -------------------- ---------- --------------- ------------------------------
11-feb-16 22:50:00 Media Recovery Log Files Files 8 8 8 8
11-feb-16 22:50:00 Media Recovery Active Apply Rate KB/sec 3316 3.24 MB/Sec 3316 3.24 MB/Sec
11-feb-16 22:50:00 Media Recovery Average Apply Rate KB/sec 762 .74 MB/Sec 762 .74 MB/Sec
11-feb-16 22:50:00 Media Recovery Maximum Apply Rate KB/sec 9617 9.39 MB/Sec 9617 9.39 MB/Sec
11-feb-16 22:50:00 Media Recovery Redo Applied Megabytes 1144 1.12 G 1144 1.12 G
11-feb-16 22:50:00 Media Recovery Last Applied Redo SCN+Time 0 0 0 0 SCN: 6296684740127
11-feb-16 22:50:00 Media Recovery Active Time Seconds 214 +00 00:03:34.00 214 +00 00:03:34.00
11-feb-16 22:50:00 Media Recovery Apply Time per Log Seconds 22 +00 00:00:22.00 22 +00 00:00:22.00
11-feb-16 22:50:00 Media Recovery Checkpoint Time per Log Seconds 5 +00 00:00:05.00 5 +00 00:00:05.00
11-feb-16 22:50:00 Media Recovery Elapsed Time Seconds 1537 +00 00:25:37.00 1537 +00 00:25:37.00
</pre>
<h2 style="text-align: left;">
References</h2>
<ul style="text-align: left;">
<li><a href="http://www.datadisk.co.uk/html_docs/oracle_dg/redo.htm" target="_blank">Redo Processing</a></li>
<li><a href="http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2128.htm#REFRN30199" target="_blank">V$RECOVERY_PROGRESS</a>
</li>
</ul>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-6260039703379141482016-01-20T14:08:00.001+02:002016-01-20T14:08:32.166+02:00Index Rebuild vs. Coalesce vs. Shrink Space<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/" target="_blank">Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones)</a> contains demo <a href="https://richardfoote.files.wordpress.com/2007/12/when-to-use-rebuild-vs-coalesce-vs-shrink-space.pdf" target="_blank">Differences between a Coalesce, Shrink Space and Rebuild</a><br />
<pre class="prettyprint linenums lang-sql">ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;
SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;
SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';
ALTER INDEX bowie_stuff_i COALESCE;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE COMPACT;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE;
-- or
ALTER INDEX bowie_stuff_i REBUILD;
-- or
ALTER INDEX bowie_stuff_i REBUILD ONLINE NOLOGGING;
SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';
ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;
SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-14741069724458926282015-01-20T17:12:00.000+02:002015-01-20T17:12:07.768+02:00Restart QMon process<div dir="ltr" style="text-align: left;" trbidi="on">
<h2>
Event "Streams AQ: waiting for time management or cleanup tasks" in running state</h2>
<pre class="prettyprint linenums lang-sql">SELECT event, state, COUNT(*) AS cnt, sum(seconds_in_wait) AS wait_in_seconds
FROM v$session_wait
WHERE event LIKE 'Streams AQ%'
GROUP BY event, state;
select * from GV$QMON_TASKS
select * from GV$QMON_SERVER_STATS
select isdefault from v$parameter where name='aq_tm_processes';
</pre>
<h2>
Restart QMon process</h2>
<pre class="prettyprint linenums lang-sql">show parameter aq_tm_processes
alter system set aq_tm_processes = 0;
alter system set aq_tm_processes = 1;
show parameter aq_tm_processes
alter system reset aq_tm_processes scope=spfile;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-64236191206769102842015-01-20T17:08:00.001+02:002015-12-30T14:39:28.630+02:00Shrinking undo tablespace<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
Useful links</h2>
<div style="text-align: left;">
<a href="http://oracle-base.com/articles/misc/reclaiming-unused-space.php" target="_blank">Reclaiming Unused Space in Datafiles</a></div>
<br />
<h2>
Undo tablespace</h2>
The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace:<br />
<pre class="prettyprint linenums lang-sql">CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
</pre>
<h2>Undo segments in DBA_ROLLBACK_SEGS view</h2>
<pre class="prettyprint linenums lang-sql">
select owner, segment_name, tablespace_name, status
--select *
from dba_rollback_segs
where 1 = 1
--and tablespace_name='UNDOTBS1'
--and status='ONLINE'
--and segment_name ='_SYSSMU51_2438556163$'
;
</pre>
<h2>Who holds UNDO tablespace</h2>
<pre class="prettyprint linenums lang-sql">select
substr(a.os_user_name,1,15) "OS User",
substr(a.oracle_username,1,8) "DB User",
substr(b.owner,1,8) "Schema",
substr(b.object_name,1,20) "Object Name",
substr(b.object_type,1,10) "Type",
substr(c.segment_name,1,15) "RBS",
substr(d.used_urec,1,12) "# of Records",
e.sid,
e.serial#
from v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
where
a.object_id = b.object_id
and
a.xidusn = c.segment_id
and
a.xidusn = d.xidusn
and
a.xidslot = d.xidslot
and
d.addr = e.taddr;
</pre>
<h2>Issue: Drop UNDO including datafiles does not delete files at OS level</h2>
Find oracle process which hold file :
<br />
<pre class="prettyprint linenums lang-sql">[root@hostname][/]:lsof | grep undo | grep deleted
oracle 3818 oracle 258u REG 8,65 32421978112 17089 /u01/app/oracle/oradata/DB11G/undotbs01.dbf (deleted)
sqlplus / as sysdba
SQL> SELECT sid, serial#, process, terminal, program, event FROM v$session WHERE process in ('3818')
</pre>
Look at this SID and it event, and find solution how to release hold of file by this session, for example I have issue with <a href="http://zukus.blogspot.com/2015/01/restart-qmon-process.html" target="_blank">Restart QMon process</a></div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-90430889744992324482015-01-09T18:30:00.001+02:002015-01-23T16:06:30.103+02:00Solving event I/O and Cluster wait and freeing wasted space in LOBs<div dir="ltr" style="text-align: left;" trbidi="on">
<h2>
Issue: I/O and Cluster wait for LOBs</h2>
Sometimes in ADDM Report you can finding in "Top SQL Statements" following action:<br />
<pre class="prettyprint linenums lang-sql">Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"2adh0x2quv6uh". Additionally, investigate this statement for possible
performance improvements. You can supplement the information given here
with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 2adh0x2quv6uh.
INSERT INTO USERINFO (SESSIONID, USERID, DICTIONARY, EXPIRES)
VALUES(:1 , :2 , :3 , :4)
Rationale
I/O and Cluster wait for LOB "SCOTT.SYS_LOB0000155472C00008$$" with
object ID 154473 consumed 60% of the database time spent on this SQL
statement.
</pre>
Segment Advisor recommend shrink table SESSIONINFORMATION for reclaim space, but shrinking does not work for LOBs::<br />
<pre class="prettyprint linenums lang-sql">ALTER TABLE "SCOTT"."USERINFO" ENABLE ROW MOVEMENT
ALTER TABLE "SCOTT"."USERINFO" SHRINK SPACE
</pre>
<h2>
Solution</h2>
Reclaiming Wasted Space:<br />
<ol>
<li>Use CASCADE option for shrink a table and all of its dependent segments (including LOB segments).<br /><br />When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows<br />but postpones the resetting of the high water mark and the deallocation of the space<br />until you reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete<br />
<pre class="prettyprint linenums lang-sql">ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE COMPACT;
ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;
</pre>
</li>
<li>Check free unused bytes, returns information about the position of the
high water mark and the amount of unused space in a segment.
<pre class="prettyprint linenums lang-sql">ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE lastextf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'USERINFO', 'TABLE', :total_blocks, -
:total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
:last_extb, :lastusedblock)
print
</pre>
</li>
<li>Reissue the <code>SHRINK SPACE</code> clause without the <code>COMPACT</code> clause during off-peak hours to complete.<br />
<pre class="prettyprint linenums lang-sql">ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE;
ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;
</pre>
</li>
<li>Check unused bytes again, script from step 2.</li>
<li>Issue deallocate unused space. Prior to deallocation, you can run the <code>UNUSED_SPACE</code> procedure, script from step 2.<br />
<pre class="prettyprint linenums lang-sql">ALTER TABLE SCOTT.USERINFO DEALLOCATE UNUSED;
</pre>
</li>
<li>Check unused bytes again, script from step 2</li>
</ol>
The LOBSEGMENT SYS_LOB0000155472C00008$$ has been reclaimed wasted space.<br />
<h3>
Resources</h3>
<a href="http://www.remote-dba.net/oracle_10g_new_features/unused_space.htm" target="_blank">Oracle 10g New Features: Oracle Reclaiming Unused Space</a><br />
<a href="http://oracle-base.com/articles/10g/space-object-transaction-management-10g.php#online_segment_shrink" target="_blank">Space, Object, and Transaction Management in Oracle Database 10g: Online Segment Shrink</a><br />
<ol>
</ol>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-83165147161678139842014-11-25T16:44:00.001+02:002014-11-25T16:44:27.480+02:00Extract duplicate keywords from string<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="text-align: left;">
SQL example using wm_concat function:</h3>
<br />
<pre class="prettyprint linenums lang-sql">select wm_concat(distinct keyword) keywords
from (
SELECT LEVEL AS keyword_no
, REGEXP_SUBSTR(keywords, '[^,]+', 1, LEVEL) AS keyword
--, str
FROM (
SELECT ROWNUM AS id
, 'SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT' as keywords
FROM dual
)
CONNECT BY
-- INSTR for compatibility with Oracle 10g
--INSTR(keywords, ',', 1, LEVEL-1) > 0
-- From Oracle 11g may used regexp_count
LEVEL <= regexp_count(keywords,',') + 1
AND id = PRIOR id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
)
;
KEYWORDS
--------------------------------------------
12345,ALLEN,KING,SCOTT
1 row selected.
</pre>
<br />
<h3 style="text-align: left;">
SQL example using listagg function:</h3>
<br />
<pre class="prettyprint linenums lang-sql">select listagg(keyword, ',') WITHIN GROUP (ORDER BY keyword) keywords
from (
select distinct keyword
from (
SELECT LEVEL AS keyword_no
, REGEXP_SUBSTR(keywords, '[^,]+', 1, LEVEL) AS keyword
--, str
FROM (
SELECT ROWNUM AS id
, 'SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT' as keywords
FROM dual
)
CONNECT BY
-- INSTR for compatibility with Oracle 10g
--INSTR(keywords, ',', 1, LEVEL-1) > 0
-- From Oracle 11g may used regexp_count
LEVEL <= regexp_count(keywords,',') + 1
AND id = PRIOR id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
)
)
;
KEYWORDS
--------------------------------------------------------------------------------
12345,ALLEN,KING,SCOTT
1 row selected.
</pre>
<br />
<h3 style="text-align: left;">
Using PL/SQL function example:</h3>
<br />
<pre class="prettyprint linenums lang-sql">DECLARE
l_keywords varchar2(32767) := '10,20,30,40, 10, 20,SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT';
function extract_duplicate_keywords(p_keywords varchar2) return varchar2
is
l_result varchar2(32767);
cnt pls_integer;
--l_str varchar2(4000);
TYPE nested_nbr_type IS TABLE OF varchar2(4000);
nnt2 nested_nbr_type := nested_nbr_type();
--nnt3 nested_nbr_type;
answer nested_nbr_type;
j pls_integer;
BEGIN
nnt2 := nested_nbr_type();
cnt := regexp_count(p_keywords, '[^,]+');
--dbms_output.put_line('Count: ' || cnt);
for i in 1 .. cnt + 1
loop
nnt2.extend;
nnt2(nnt2.last) := trim(REGEXP_SUBSTR(p_keywords,'[^,]+', 1, i));
--dbms_output.put_line(i || ': ' || l_str);
end loop;
--nnt3 := nnt2;
answer := nnt2 MULTISET INTERSECT DISTINCT nnt2;
j := answer.first;
while j is not null
loop
if answer(j) is not null then
l_result := l_result || ',' ||answer(j);
--dbms_output.put_line(answer(j));
end if;
j := answer.next(j);
end loop;
return ltrim(l_result, ',');
END;
BEGIN
l_keywords := extract_duplicate_keywords(l_keywords);
dbms_output.put_line(l_keywords);
END;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-14872511284686893712014-02-18T17:58:00.003+02:002014-02-18T18:23:59.468+02:00How to connect SQLPlus without tnsnames.ora<div dir="ltr" style="text-align: left;" trbidi="on">
Connect using one of service_name:<br />
<pre class="prettyprint linenums lang-sql">sqlplus username/password@//host[:port][/service_name]
</pre>
<br />
where <strong>service_name</strong> can be one of simple name or domain name specified in service_names parameter:<br />
<pre class="prettyprint linenums lang-sql">show parameter service_names
ALTER SYSTEM SET service_names = 'ORCL', 'us.acme.com' COMMENT='Add new services' SCOPE=BOTH;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-9726417316004986022014-02-17T15:07:00.005+02:002019-04-19T14:06:58.051+03:00Procedure to write to alert.log<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
Link to original article <a href="http://www.dbaexpert.com/blog/stored-proc-to-write-to-database-alert-logs/" target="_blank">Stored Proc to write to database alert logs</a> author <a href="http://www.dbaexpert.com/blog/about-charles-kim/" target="_blank">Charles Kim</a>.<br />
<br />
From the application or from database triggers, if I want to record
anything to the alert log, I can simply invoke this stored procedure and
write error messages to the alert log.<br />
<br />
Most of the DBAs have implemented alert log scanners and will send
alert notifications if new ORA- error messages pop up in the alert log
file.<br />
</div>
<pre class="prettyprint linenums lang-sql">create or replace procedure write_msg_to_alert_log (
p_message varchar2,
p_oranum number default 20001
)
is
/*
create procedure under system user
*/
begin
sys.dbms_system.ksdddt;
sys.dbms_system.ksdwrt(2, 'ORA-' || to_char(p_oranum) || ': ' || p_message);
end;
/
--grant execute on write_msg_to_alert_log to public;
grant execute on write_msg_to_alert_log to scott;
create public synonym write_msg_to_alert_log for system.write_msg_to_alert_log;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-22249228862403250982013-10-09T19:53:00.001+03:002013-10-09T19:57:17.020+03:00Recovery using the SWITCH DATABASE TO COPY command<div dir="ltr" style="text-align: left;" trbidi="on">
<h2>
Recover database from copy</h2>
<pre class="prettyprint linenums lang-sql">rm users*.dbf
rman target /
RMAN> shutdown abort
# Recover database from copy
RMAN> startup mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
SQL> select name,status from v$datafile;
</pre>
<h2>
Recover tablespace from copy</h2>
<pre class="prettyprint linenums lang-sql">rm users*.dbf
rman target /
# Recover tablespace from copy
RMAN> sql 'alter tablespace users offline immediate';
RMAN> switch tablespace users to copy;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';
SQL> select file_name from dba_data_files where tablespace_name='USERS';
NAME
----------------------------
/data/users01.dbf
/backup/users02.dbf
# Move datafile from /backup/$ORACLE_SID to /data/$ORACLE_SID directory
# while the Database is online
SQL> alter tablespace users offline;
# Move the file /db_backup/ptdb1/users02.dbf using operating system command
cp /backup/users02.dbf /data/users02.dbf
# Update the database data dictionary
SQL> alter database rename file '/backup/users02.dbf' to '/data/users02.dbf';
# Take tablespace USERS online:
SQL> alter tablespace users online;
# Delete file from old location using operating system command
rm /backup/users02.dbf
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-91073149560039706232013-09-06T21:18:00.003+03:002018-05-03T18:57:00.083+03:00Check tablespace size usage<div dir="ltr" style="text-align: left;" trbidi="on">
Rewritten version from <a href="https://communities.bmc.com/docs/DOC-9942#tablespace" target="_blank">Top DBA Shell Scripts for Monitoring the Database</a> based on MAXBYTES of each file within tablespace:<br />
<pre class="prettyprint linenums lang-sql">SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.ALLOCATED_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR (T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE), '999,999') "FREE (MB)",
TO_CHAR ((ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND(SUM (MAXBYTES/1048576)) TOTAL_SPACE,
ROUND (SUM (BYTES/1048576)) ALLOCATED_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
--AND ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100) < 10;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-28218200130188230162013-09-06T21:05:00.003+03:002013-09-06T23:26:01.325+03:00Restore archivelog from backup by logseq/scn<div dir="ltr" style="text-align: left;" trbidi="on">
See details in <a href="http://www.fadalti.com/oracle/database/rman_effective_use.htm">RMAN effective use</a>
<br />
<pre class="prettyprint linenums">
set archivelog destination to '/disk1/oracle/temp_restore';
#restore archivelog from scn=460779 until scn =506141
restore archivelog from logseq 1 until logseq 10;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-50468839906174717182013-08-16T18:33:00.004+03:002016-12-20T18:29:23.358+02:00ADRCI usage examples<div dir="ltr" style="text-align: left;" trbidi="on">
Usage example 1:
<br />
<pre class="prettyprint linenums lang-sql">[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
# Output with enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'"
# Output to termnal without enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'" -term
# Show all 'ORA-%' and 'TNS-%' messages
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')" -term
# Show all 'ORA-%' and 'TNS-%' messages with exclude from output specified
show alert -p "ORIGINATING_TIMESTAMP > '2013-08-16 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%') AND (MESSAGE_TEXT NOT LIKE '%TNS-12502%' AND MESSAGE_TEXT NOT LIKE '%TNS-12537%' AND MESSAGE_TEXT NOT LIKE '%ORA-609%')" -term
show alert -tail -f
exit
</pre>
Usage example 2:
<br />
<pre class="prettyprint linenums lang-sql">[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
show alert -tail -f
exit
</pre>
Incident & Problem
<br />
<pre class="prettyprint linenums lang-sql">adrci>
show problem
show incident
show incident -mode detail -p "incident_id=6201"
show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
</pre>
Creation of Packages & ZIP files to send to Oracle Support
<br />
<pre class="prettyprint linenums lang-sql">adrci>
ips create package problem 1 correlate all
ips generate package 2 in "/home/oracle"
</pre>
Managing, especially purging of tracefiles
<br />
<pre class="prettyprint linenums lang-bsh">adrci>
show tracefile -rt
show control
set control (SHORTP_POLICY = 360)
set control (LONGP_POLICY = 2190)
show control
# Purge tracefiles manually
purge -age 2880 -type trace
show tracefile -rt
# Purge log directories for listener ADRCI Home
for i in `adrci exec="show homes"|grep listener`;do
# Purge ADRCI Homes log directories older 60 days ago
#for i in `adrci exec="show homes"|sed '1d'`;do
du -hs $ORACLE_BASE/$i | sort -rh
du -hs $ORACLE_BASE/$i/* | sort -rh
du -hs $ORACLE_BASE/$i/trace | sort -rh
# Purge listener log directory older 60 days ago
# (60 days * 24h * 60 mins = 86400 mins)
# -age <mins> - The data older than <mins> ago will be purged
echo "adrci exec=\"set home $i;purge -age 86400\""
adrci exec="set home $i;purge -age 86400";
adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";
du -hs $ORACLE_BASE/$i | sort -rh
du -hs $ORACLE_BASE/$i/* | sort -rh
du -hs $ORACLE_BASE/$i/trace | sort -rh
done
# Check ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
adrci exec="set home $i;show control;";
done
# Set ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";
done
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-83695097802226944862013-04-01T13:51:00.002+03:002013-09-06T23:27:01.208+03:00Read data from clobRead data from clob:<br />
<pre class="prettyprint linenums">
declare
v_body clob;
offset number := 1;
v_str varchar(32767);
amount number := 32767;
begin
v_body := rpad('Big clob data ', 32767 * 4, 'Big clob data ');
while(offset <= dbms_lob.getlength(v_body)) loop
dbms_lob.read(v_body, amount, offset, v_str);
-- Operate with v_str:
--dbms_output.put_line(v_str);
offset := offset + amount;
end loop;
end;
</pre>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-41032650181102362352012-10-23T20:27:00.000+03:002014-01-09T15:19:18.582+02:00Capture any failed SQL queries using database trigger in Oracle<div dir="ltr" style="text-align: left;" trbidi="on">
Создаем таблицу куда будем записывать запросы с ошибками:<br />
<pre class="prettyprint linenums lang-sql">CONNECT SYSTEM;
DROP TABLE servererror_log;
CREATE TABLE servererror_log (
error_datetime TIMESTAMP,
error_user VARCHAR2(30),
db_name VARCHAR2(30),
error_stack VARCHAR2(2000),
call_stack VARCHAR2(2000),
SID NUMBER,
SQL_ID VARCHAR2 (13),
sql_statement VARCHAR2(2000),
sql_statement_all CLOB,
CLIENT_INFO VARCHAR2(256 BYTE),
CURR_SCHEMA VARCHAR2(256 BYTE),
CURR_USER VARCHAR2(256 BYTE),
CURR_DB_NAME VARCHAR2(256 BYTE),
HOST VARCHAR2(256 BYTE),
IP VARCHAR2(256 BYTE),
OSUSER VARCHAR2(256 BYTE),
SESSID VARCHAR2(256 BYTE),
SESS_USER VARCHAR2(256 BYTE),
TERMINAL VARCHAR2(256 BYTE)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTEM.SERVERERROR_LOG TO SCOTT;
</pre>
Выдаем явный грант на V_$SESSION пользователю SYSTEM для того чтобы триггер log_server_errors указанный ниже скомпилировался.
<br />
<pre class="prettyprint linenums lang-sql">
CONNECT SYS AS SYSDBA;
GRANT SELECT ON V_$SESSION TO SYSTEM;
</pre>
Триггер базы данных на SERVERERROR(в условии WHEN можем исключить ошибки которые не нужно логгировать, в данном примере - это <code>ORA-25254</code>):<br />
<pre class="prettyprint linenums lang-sql">CONNECT SYSTEM;
DROP TRIGGER log_server_errors;
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE WHEN (
ORA_SERVER_ERROR(1)<>25254 /*ORA-25254: time-out in LISTEN while waiting for a message*/
)
DECLARE
v_sql_statement VARCHAR2(32767);
sql_text ora_name_list_t;
n pls_integer;
cursor c_user is
select vs.sid sid,
vs.sql_id sql_id,
sys_context('USERENV','CLIENT_INFO') client_info,
sys_context('USERENV','CURRENT_SCHEMA') curr_schema,
sys_context('USERENV','CURRENT_USER') curr_user,
sys_context('USERENV','DB_NAME') db_name,
sys_context('USERENV','HOST') host,
sys_context('USERENV','IP_ADDRESS') ip,
sys_context('USERENV','OS_USER') osuser,
sys_context('USERENV','SESSIONID') sessid,
sys_context('USERENV','SESSION_USER') sess_user,
sys_context('USERENV','TERMINAL') terminal
from dual
cross join v$session vs
where 1 = 1
and sys_context('USERENV','SESSIONID') = audsid
;
user_rec c_user%rowtype;
BEGIN
open c_user;
fetch c_user into user_rec;
close c_user;
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
v_sql_statement := v_sql_statement || sql_text(i);
END LOOP;
INSERT INTO servererror_log(
error_datetime, error_user, db_name,
error_stack, call_stack,
SID, SQL_ID,
sql_statement, sql_statement_all,
CLIENT_INFO, CURR_SCHEMA, CURR_USER, CURR_DB_NAME,
HOST, IP, OSUSER, SESSID,
SESS_USER, TERMINAL
) VALUES(
systimestamp, sys.login_user, sys.database_name,
ora_server_error_msg(1)/*dbms_utility.format_error_stack*/,
dbms_utility.format_call_stack,
user_rec.sid, user_rec.sql_id,
substrb(v_sql_statement, 1, 2000),
v_sql_statement,
user_rec.client_info, user_rec.curr_schema, user_rec.curr_user,
user_rec.db_name, user_rec.host, user_rec.ip,
user_rec.osuser, user_rec.sessid, user_rec.sess_user,
user_rec.terminal
);
--commit;
END;
/
</pre>
Выбираем запросы с ошибками(см. поля <code>SID, SQL_STATEMENT, SQL_STATEMENT_ALL</code>):
<br />
<pre class="prettyprint linenums lang-sql">CONNECT SCOTT;
select * from system.servererror_log
--where ERROR_USER = 'SCOTT'
order by error_datetime desc
;
</pre>
Дополнительные команды которые возможно пригодятся для трассировки сессии:
<br />
<pre class="prettyprint linenums lang-sql">CONNECT SYSTEM;
ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
...
select * from system.servererror_log;
...
ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;
--After Logon Trigger for Tracing on Schema:
CONNECT SCOTT;
CREATE OR REPLACE TRIGGER SCOTT_AFTER_LOGON_TRG_00
AFTER LOGON ON SCOTT.SCHEMA
DECLARE
sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
IF (USER = 'SCOTT') THEN
execute immediate sqlstr;
END IF;
END;
/
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-1127604357611752432012-09-19T21:28:00.000+03:002016-12-27T19:18:39.580+02:00How to viewing Oracle bind variable values?<div dir="ltr" style="text-align: left;" trbidi="on">
View <b>V$SQL_BIND_CAPTURE</b> has been introduced to report information on bind variables used by SQL cursors.<br />
<br />
Following query uses the sql_id address that should be specified for each unique SQL statement:
<br />
<pre class="code"><code>
set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off
col KILL_SERIAL_SID format a52
col sql_text format a50 wrap
col name format a15 wrap
col datatype_string format a30
col value_string format a30 wrap
SELECT
ss.serial#,
ss.sid,
--ALTER SYSTEM KILL SESSION '573,31517' IMMEDIATE
'ALTER SYSTEM KILL SESSION ''' || ss.sid || ',' || ss.serial# || ''' IMMEDIATE;' kill_serial_sid,
b.name,
b.position,
b.datatype_string,
b.value_string,
a.sql_text
FROM
v$sql_bind_capture b,
v$sqlarea a,
v$sql s,
v$session ss
WHERE 1 = 1
AND s.sql_id = a.sql_id
AND b.sql_id = a.sql_id
-- Join v$session to v$sqlarea
--AND ss.sql_address = a.address
--AND ss.sql_hash_value = a.hash_value
--
-- Join v$session to v$sql
--AND ss.SQL_ADDRESS = s.ADDRESS
-- joining on SQL_ID is much more efficient
AND ss.sql_id = s.sql_id
-- To capture only active session with specified SQL_ID
--AND ss.STATUS = 'ACTIVE'
AND b.sql_id = '&sql_id' --'4453zzgmqssxr'
;
</code></pre>
<br />
You can get the SQL ID from the v$sql view:
<br />
<pre class="code"><code>
set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off
col sql_text format a130 wrap
col bind_name format a35 wrap
col bind_string format a50 wrap
select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
and sql_id='&sql_id' --'f8pavn1bvsj7t'
;
</code></pre>
<br />
<b>How to viewing in SQL Monitor of Enterprise Manager 11g</b><br />
<a href="http://download.oracle.com/technology/products/oem/screenwatches/DB_AR/DB_AR.html" target="_blank">Offline Analysis of Long Running SQL using SQL Monitoring Active Reports</a><br />
<br />
<b>Using 10046 trace for tracing bind variable values</b><br />
You can run a 10046 level 4 trace to debug code with bind variables and see the bind variable values as it steps through the code:
<br />
<pre class="code"><code>
EXECUTE sys.dbms_system.set_ev ('||SID||','||SERIAL#||',10046,4,'''')
</code></pre>
<br />
<b>Bind Capture History</b>
<br />
<pre class="code"><code>
set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off
col name format a50 wrap
col value_string format a50 wrap
VARIABLE sql_id VARCHAR2(50)
EXEC :sql_id := '4453zzgmqssxr'
select snap_id, name, position, value_string, last_captured
from dba_hist_sqlbind
where sql_id = :sql_id
order by snap_id desc;
</code>
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0tag:blogger.com,1999:blog-5584817666297789337.post-80651085788853066422012-04-11T18:19:00.000+03:002016-09-20T18:43:51.792+03:00Reading alert log via SQL in Oracle 11g<div dir="ltr" style="text-align: left;" trbidi="on">
X$DBGALERTEXT показывает последний log.xml<br />
<br />
X$DIAG_ALERT_EXT показывает объединенное содержимое для файлов log_XX.xml, включая log.xml.<br />
<br />
<pre class="prettyprint linenums lang-sql">
connect sys as sysdba
select * from X$DBGALERTEXT where rownum <= 20;
set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170
SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
and originating_timestamp >= sysdate - 30
and message_text like 'ALTER SYSTEM%'
and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;
</pre>
<br />
Select ORA-XXXXX and TNS-XXXXX errors last 10 minutes:
<br />
<pre class="prettyprint linenums lang-sql">
set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170
SELECT inst_id, originating_timestamp, MESSAGE_TEXT
FROM TABLE (
gv$ (
CURSOR (
SELECT inst_id, originating_timestamp, MESSAGE_TEXT
FROM v$diag_alert_ext
WHERE originating_timestamp > (SYSDATE - 10/1440)
AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')
AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
)
)
)
ORDER BY inst_id, originating_timestamp;
</pre>
</div>
Zukushttp://www.blogger.com/profile/00469484835655880345noreply@blogger.com0