128 lines
3.1 KiB
SQL
128 lines
3.1 KiB
SQL
|
|
prompt
|
|
prompt 'create table perm and temp'
|
|
declare
|
|
row_count number;
|
|
row_count_temp number;
|
|
begin
|
|
select count(1) into row_count from dual where exists (select 1 from user_tables where table_name = upper('perm'));
|
|
select count(1) into row_count_temp from dual where exists (select 1 from user_tables where table_name = upper('temp'));
|
|
if row_count = 1 then
|
|
execute immediate 'drop table perm';
|
|
end if;
|
|
if row_count_temp = 1 then
|
|
execute immediate 'drop table temp';
|
|
end if;
|
|
execute immediate '
|
|
create table perm
|
|
(
|
|
x char(2000),
|
|
y char(2000),
|
|
z char(2000)
|
|
)
|
|
';
|
|
execute immediate '
|
|
create global temporary table temp
|
|
(
|
|
x char(2000),
|
|
y char(2000),
|
|
z char(2000)
|
|
)
|
|
on commit preserve rows
|
|
';
|
|
end;
|
|
/
|
|
|
|
prompt
|
|
prompt 'create procedure do_sql'
|
|
create or replace procedure eoda.do_sql(p_sql in varchar2)
|
|
as
|
|
l_start_redo number;
|
|
l_redo number;
|
|
begin
|
|
l_start_redo := get_stat_val('redo size');
|
|
execute immediate p_sql;
|
|
commit;
|
|
l_redo := get_stat_val('redo size') - l_start_redo;
|
|
dbms_output.put_line( to_char(l_redo,'99,999,999') || ' byties of redo generated for "' ||
|
|
substr( replace( p_sql,chr(10),' ' ),1,25 ) || '"...');
|
|
end;
|
|
/
|
|
|
|
prompt
|
|
prompt 'deal with perm and temp'
|
|
begin
|
|
do_sql( 'insert into perm
|
|
select 1,1,1
|
|
from all_objects
|
|
where rownum <= 500' );
|
|
|
|
do_sql( 'insert into temp
|
|
select 1,1,1
|
|
from all_objects
|
|
where rownum <= 500' );
|
|
dbms_output.new_line;
|
|
|
|
do_sql( 'update perm set x = 2' );
|
|
do_sql( 'update temp set x = 2' );
|
|
|
|
do_sql( 'delete from perm' );
|
|
do_sql( 'delete from temp' );
|
|
end;
|
|
/
|
|
|
|
prompt
|
|
prompt 'create idnex on perm and temp'
|
|
declare
|
|
perm_row_count number;
|
|
temp_row_count number;
|
|
begin
|
|
-- select 'alter system kill session '''||sid||','||serial#||''''
|
|
-- from v$session
|
|
-- where sid in (
|
|
-- select sid
|
|
-- from v$lock
|
|
-- where id1 in (
|
|
-- select object_id
|
|
-- from dba_objects
|
|
-- where object_name in (
|
|
-- select table_name
|
|
-- from dba_tables
|
|
-- where owner='EODA')
|
|
-- )
|
|
-- );
|
|
-- commit;
|
|
select count(1) into perm_row_count from dual where exists (select 1 from user_indexes where table_name = upper('perm') and index_name = upper('perm_idx') );
|
|
select count(1) into temp_row_count from dual where exists (select 1 from user_indexes where table_name = upper('temp') and index_name = upper('temp_idx') );
|
|
if perm_row_count = 0 then
|
|
execute immediate 'create index perm_idx on perm(x)';
|
|
end if;
|
|
-- 执行下面语句可能会报错 ORA-14452: attempt to create, alter or drop an index on temporary table already in use ,此时就需要将下面代码注释掉，并单独执行
|
|
--if temp_row_count = 0 then
|
|
-- execute immediate 'create index temp_idx on temp(x)';
|
|
--end if;
|
|
end;
|
|
/
|
|
|
|
prompt
|
|
prompt 'deal with perm and temp'
|
|
begin
|
|
do_sql( 'insert into perm
|
|
select 1,1,1
|
|
from all_objects
|
|
where rownum <= 500' );
|
|
|
|
do_sql( 'insert into temp
|
|
select 1,1,1
|
|
from all_objects
|
|
where rownum <= 500' );
|
|
dbms_output.new_line;
|
|
|
|
do_sql( 'update perm set x = 2' );
|
|
do_sql( 'update temp set x = 2' );
|
|
|
|
do_sql( 'delete from perm' );
|
|
do_sql( 'delete from temp' );
|
|
end;
|
|
/
|