create or replace function get_obj_name (v_id number) return VARCHAR2 is v_name varchar2(100); cursor c(o_id number) is select object_name from dba_objects where object_id=o_id; begin open c(v_id); fetch c into v_name; if c%notfound then v_name:='not found'; end if; close c; return v_name; end; / select get_obj_name(124) from dual; select get_obj_name(124334343) from dual;
CREATE OR REPLACE PROCEDURE list_free_space (v_name varchar2, ncol number,used varchar2,freed varchar2) is cursor c(t_name varchar2) is select * from dba_free_space where tablespace_name=t_name order by file_id,block_id; cursor c2(t1_name varchar2) is select blocks from dba_data_files where tablespace_name=t1_name; j number:=1; k number:=1; m number:=1; begin for x in c(v_name) loop for i in j..x.block_id-1 loop dbms_output.put(used); k:=k+1; if k>=ncol then dbms_output.put_line('|'); k:=1; end if; end loop; for i in 1..x.BLOCKS loop dbms_output.put(freed); k:=k+1; if k>=ncol then dbms_output.put_line('|'); k:=1; end if; end loop; j:=x.block_id+x.BLOCKS; end loop; open c2(v_name); fetch c2 into m; m:=1; for i in 1..m-j loop dbms_output.put(used); k:=k+1; if k>=ncol then dbms_output.put_line('|'); k:=1; end if; end loop; close c2; close c; dbms_output.put_line('END'); NULL; end; /
select blocks from dba_data_files where tablespace_name='UNDOTBS1'; set serveroutput on size 50000 set linesize 100 exec list_free_space('UNDOTBS1',30,'*','_');
3、需要经常统计每个会话的逻辑读和物理读信息,直接使用SQL很不方便,设计一个程序, 简化查看某个会话逻辑读、物理读的过程 CREATE OR REPLACE PACKAGE my_stat IS TYPE stat IS RECORD( value number, name varchar2(100)); type var_stat is table of stat index by binary_integer; n_stat var_stat; n_stat_tmp number; PROCEDURE get_stat(v_sid number,v_name varchar2); END my_stat; /
CREATE OR REPLACE PACKAGE BODY my_stat IS PROCEDURE get_stat(v_sid number,v_name varchar2) is cursor c1(c_sid number,c_name varchar2) is select a.name,b.value from v$statname a,v$sesstat b where a.STATISTIC#=b.STATISTIC# and a.name=c_name and b.sid=c_sid; i number; begin if n_stat_tmp = 0 then n_stat_tmp:=1; n_stat(n_stat_tmp).value:=0; n_stat(n_stat_tmp).name:=v_name; else i:=1; while i<=n_stat.count loop if n_stat(i).name = v_name then n_stat_tmp:=i; goto EXIT_LOOP; end if; i:=i+1; end loop; if i=n_stat.count+1 then n_stat(i).value:=0; n_stat(i).name:=v_name; n_stat_tmp:=i; end if; end if; <<EXIT_LOOP>> for x in c1(v_sid,v_name) loop dbms_output.put_line(x.value-n_stat(n_stat_tmp).value ); n_stat(n_stat_tmp).value:=x.value; end loop; end get_stat; begin n_stat_tmp:=0; END my_stat;