?
创建一个转储undo块的存储过程?
create or replace procedure dump_undo_block as m_xidusn number; m_header_file_id number; m_header_block_id number; m_start_file_id number;
Oracle事务原理探究2--读书笔记五(五)
t a90
column other_tag_plus_exp format a29
column access_predicates format a80
column filter_predicates format a80
column projection format a80
column remarks format a80
column partition_start format a12
column partition_stop format a12
column partition_id format 999
column other_tag format a32
column object_alias format a24
column object_node format a13
column other format a150
column os_username format a30
column terminal format a24
column userhost format a24
column client_id format a24
column statistic_name format a35
column namespace format a20
column attribute format a20
column hint format a40
column start_time format a25
column end_time format a25
column time_now noprint new_value m_timestamp
set feedback off
select to_char(sysdate,'hh24miss') time_now from dual;
commit;
set feedback on
set timing off
set verify off
alter session set optimizer_mode = all_rows;
spool log
-- 创建表
drop table t1;
create table t1(id number, n1 number);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
commit;
create unique index t1_i1 on t1(id);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
estimate_percent => 100,
method_opt => 'for all columns size 1'
);
end;
/
--
-- For 11g - force to disc for the dump
--
alter system checkpoint;
-- 创建一个存储过程，用来转储一个表使用的第一个数据块。
create or replace procedure dump_table_block(
i_tab_name in varchar2,
i_owner in varchar2 default sys_context('userenv','session_user')
)
as
m_file_id number;
m_block number;
m_process varchar2(32);
begin
execute immediate
' select ' ||
' dbms_rowid.rowid_relative_fno(rowid), ' ||
' dbms_rowid.rowid_block_number(rowid) ' ||
' from ' ||
i_owner ||
'.' ||
i_tab_name ||
' where ' ||
' rownum = 1 '
into
m_file_id, m_block
;
execute immediate
'alter system dump datafile ' || m_file_id ||
' block ' || m_block
;
--
-- For non-MTS, work out the trace file name
--
select
spid
into
m_process
from
v$session se,
v$process pr
where
--
-- The first option is the 9.2 version for checking the SID
-- The second is a quick and dirty option for 8.1.7
-- provided SYS has made v$mystat visible (or this is the sys account)
--
-- se.sid = (select dbms_support.mysid from dual)
se.sid = (select sid from v$mystat where rownum = 1)
and pr.addr = se.paddr
;
dbms_output.new_line;
dbms_output.put_line('Trace file name includes: ' || m_process);
dbms_output.new_line;
exception
when others then
dbms_output.new_line;
dbms_output.put_line('Unspecified error.');
dbms_output.put_line('Check syntax.');
dbms_output.put_line('dump_table_block({table_name},[{owner}]');
dbms_output.new_line;
raise;
end;
.
/
show errors
drop public synonym dump_table_block;
create public synonym dump_table_block for dump_table_block;
grant execute on dump_table_block to public;