Change auto extensible from all datafiles to OFF
set serveroutput on
BEGIN
for i in (select file_name,autoextensible from dba_data_files where autoextensible = ‘YES’) loop
dbms_output.put_line(‘File name is: ‘||i.file_name||’ and autoextensible is: ‘||i.autoextensible);
execute immediate ‘alter database datafile ”’||i.file_name||”’ autoextend off’;
end loop;
END;
/
How to send an email not using xp_sendmail in SQLServer?
From the following KB article it list couple of store procedures which can be used to send email through SQLServer. One of the feature it has the “From/Sender” can be passed in a parameter where as in case xp_sendmail it can’t be.
http://support.microsoft.com/kb/312839
Using oradebug to dump call stack?
Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area
Example:
– Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc
Information in the trace file:
…
*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3′ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?
..
How to find/list the events set in a session?
Using “oradebug dump events 1″ one can find/list events set in a session.
For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';
Session altered.
SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';
Session altered.
SQL> SELECT distinct sid FROM v$mystat
SID
———-
131
In another session login as an account as sysdba
SQL> SELECT pid, spid FROM v$process where addr IN (SELECT paddr FROM V$session where sid = 131);
PID SPID
———- ——-
12 3420
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump events 1
Statement processed.
SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc
Or in 11g:
SQL> oradebug eventdump session
1410 trace name context forever, level 12
sql_trace level=12
After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..
How to suspend/resume a process using oradebug?
Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.
Resume a process
SQL> oradebug resume
Statement processed.
If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321
The Official Oracle Wiki – Oracle Wiki
The Official Oracle Wiki – Oracle Wiki.
Using oradebug to set event
The following example shows how to set event on a oracle session
To turn on event, needs to be run as sysdba
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
To turn off event
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.
List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg
Tablespace size report with auto allocation
the script below will help you to see how big auto allocation is set plus all the report for the current used and free space.
[spocoradb1.aeso.ca]/dbadmin/workspace2/alima/sql>cat db_tablespaces_report.sql
set linesize 200
set trimspool on
set pagesize 100
set feedback off
column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a30 heading “Tablespace Name”
column Kbytes_alloc format 999,999,999,999 heading “Alloc|KBytes”
column currently_used format 999,999,999,999 heading “Used|KBytes”
column kbytes_free format 999,999,999,999 heading “Free|KBytes”
column Kbytes_max format 999,999,999,999 heading “MaxPoss|Kbytes”
column pct_free format 999.99 heading “%Free”
column max_kbytes_free format 999,999,999,999 heading “MaxPoss|Free|KBytes”
column max_pct_used format 999.99 heading “%Max|Used”
column host_name format a30
spool $vfile_email
select TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) TODAY FROM dual
/
SELECT host_name, instance_name FROM v$instance
/
break on report
compute sum of Kbytes_alloc on report
compute sum of kbytes_free on report
compute sum of currently_used on report
compute sum of Kbytes_max on report
select decode(extent_management,’LOCAL’,'*’,”) ||
decode(segment_space_management,’AUTO’,'a ‘,’m ‘) ||
b.tablespace_name name,
Kbytes_alloc,
kbytes_max,
(Kbytes_alloc – NVL(kbytes_free, 0)) currently_used,
NVL(kbytes_free, 0) kbytes_free,
(NVL(kbytes_free, 0))*100/Kbytes_alloc pct_free,
(Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) max_kbytes_free,
(Kbytes_alloc – NVL(kbytes_free, 0))*100/kbytes_max max_pct_used
FROM
( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(decode(AUTOEXTENSIBLE, ‘NO’, bytes,
decode(sign(maxbytes – bytes), 1, maxbytes, -1, bytes, 0,maxbytes)))/1024 Kbytes_max,tablespace_name
from sys.dba_data_files
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name
AND c.tablespace_name = b.tablespace_name
AND b.tablespace_name NOT IN (select distinct TABLESPACE_NAME from
DBA_UNDO_EXTENTS)
— 5% free and less than 2GB
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max < 5
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) < (2*1024*1024)
ORDER BY (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max, b.tablespace_name
/
spool off
