例如:分析日志文件
alter system dump logfile logfilename;SQL> alter system dump logfile 'C:\oracle-data\oracle\redo02.log';
System altered但是我如何能看到dump后的log文件什么样?
C:\oracle\product\10.2.0\admin\oracle\udump路径下的trace文件我看了,都是如下的内容;并没有看到log文件的内容是什么样?譬如log文件的block是什么样的怎么查看?SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval,
'/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type =
"dynamic_sampling"]'), extractvalue(xmlval, '/*/info[@type = "index_size"]')
, extractvalue(xmlval,'/*/info[@type = "plan_hash"]')
from
(select xmltype(:v_other_xml) xmlval from dual)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.03 0.03 0 759 0 0
Fetch 2 0.04 0.06 0 42 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.07 0.09 0 801 0 2
alter system dump logfile logfilename;SQL> alter system dump logfile 'C:\oracle-data\oracle\redo02.log';
System altered但是我如何能看到dump后的log文件什么样?
C:\oracle\product\10.2.0\admin\oracle\udump路径下的trace文件我看了,都是如下的内容;并没有看到log文件的内容是什么样?譬如log文件的block是什么样的怎么查看?SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval,
'/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type =
"dynamic_sampling"]'), extractvalue(xmlval, '/*/info[@type = "index_size"]')
, extractvalue(xmlval,'/*/info[@type = "plan_hash"]')
from
(select xmltype(:v_other_xml) xmlval from dual)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.03 0.03 0 759 0 0
Fetch 2 0.04 0.06 0 42 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.07 0.09 0 801 0 2
last-recid= 6, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
(name #3) E:\ORADATA\JWTEST\REDO01.LOG
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x4000 seq: 0x0000000b hws: 0x5 bsz: 512 nab: 0x24ab flg: 0x0 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.002e5dd7
Low scn: 0x0000.002e78b4 11/05/2008 06:46:14
C:\oracle\product\10.2.0\admin\oracle\udump这个路径下?
如果是的话,用tkprof格式化后的输出就是下面这样的SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval,
'/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type =
"dynamic_sampling"]'), extractvalue(xmlval, '/*/info[@type = "index_size"]')
, extractvalue(xmlval,'/*/info[@type = "plan_hash"]')
from
(select xmltype(:v_other_xml) xmlval from dual)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.03 0.03 0 759 0 0
Fetch 2 0.04 0.06 0 42 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.07 0.09 0 801 0 2 而不是 下面这样的(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 6, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
(name #3) E:\ORADATA\JWTEST\REDO01.LOG
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x4000 seq: 0x0000000b hws: 0x5 bsz: 512 nab: 0x24ab flg: 0x0 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.002e5dd7
Low scn: 0x0000.002e78b4 11/05/2008 06:46:14
Parameter type
String
Syntax
MAX_DUMP_FILE_SIZE = {integer [K | M] | UNLIMITED}
Default value
UNLIMITED
Parameter class
Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values
0 to unlimited, or UNLIMITED
MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.
The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits. 2、还有就是文件名称得命名怎么来的?
System altered不过C:\oracle\product\10.2.0\admin\oracle\udump路径下的文件还是能大于100k?为什么?
还有1点,你修改成100k后,你的dump文件生成的大小达到100k就不再DUMP后面的内容了。并不是说一个dump操作可以生成多个文件,然后每个文件的大小能由你指定。 问题2:文件名:SID_ORA_SPID.ora
由以下脚本获取当前会话DUMP文件路径:
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
如果是想获取别的会话的DUMP文件路径,则修改SID即可。