oracle script 积累

define_editor='"C:\Program Files\Notepad++\notepad++.exe"';
define_editor='vim';
set echo on;
set time on;
set pages 9999;
set linesize 9999;
set serveroutput on;
set timing on;
set feed 1;
col object_name format a30;
col column_name format a10
alter session set NLS_LANGUAGE='AMERICAN';

create or replace procedure find_quoter(i_tbl_name varchar2)
as
        quoter_name varchar2(100);
        cursor quoter_cur is
                SELECT u1.TABLE_NAME AS quoter
                FROM user_constraints u1,
                user_constraints u2
                WHERE u1.constraint_type='R'
                AND u1.R_CONSTRAINT_NAME  = u2.CONSTRAINT_NAME
                AND u2.table_name         = upper(i_tbl_name);
begin
        dbms_output.put_line('--------------------------------------------');
        open quoter_cur;
        loop
                fetch quoter_cur into quoter_name;
                exit when quoter_cur%NOTFOUND;
                dbms_output.put_line(quoter_name);
        end loop;
        close quoter_cur;
        dbms_output.put_line('--------------------------------------------');
exception
        when others
        then
                dbms_output.put_line('Error: ' || sqlerrm);
end;
/

select count(*) from dictionary where instr(comments, 'index')>0;

加在login.sql或glogin.sql文件的后面：

DEFINE gname = 'not connected'
COLUMN global_name new_value gname
SET termout off
SELECT USER ||'@'|| REPLACE (GLOBAL_NAME, '.WORLD', NULL) GLOBAL_NAME
FROM GLOBAL_NAME;
SET termout on
SET sqlprompt '&&gname> '

这样每次登录SQLPLUS提示符会自动更改为“当前用户名@全局数据库名> ”的形式。

10g以下版本在登录后切换用户的情况下，提示符不能自动更新。


查询主键

select  tt.column_name
  from user_constraints t, user_ind_columns tt
  where t.table_name =upper('ECCAP_USER_PROVIDER')
      and tt.table_name=t.table_name
      and t.constraint_type='P'
      and t.index_name = tt.index_name

 SELECT */*dbl.lock_type, dbl.mode_held, dbl.blocking_others, dbo.object_name, dbo.object_type*/
   FROM dba_locks dbl,
   dba_objects dbo,
   v$session v
   WHERE v.sid  = dbl.session_id
   and dbo.object_id = dbl.lock_id1
AND v.username = 'HR'

query the current executing sql

 SELECT u.sid                      ,
  SUBSTR(u.username,1,12) user_name,
  s.sql_text
   FROM v$sql s,
  v$session u
  WHERE s.hash_value = u.sql_hash_value
AND sql_text NOT LIKE '%from v$sql s, v$session u%'
ORDER BY u.sid;

query the resource that has been consumed by the session

 SELECT yt.name,
  st.value
   FROM v$sesstat st,
  v$sysstat yt      ,
  v$session vs
  WHERE vs.sid    = st.sid
AND yt.statistic# = st.statistic#
AND vs.username   ='CHANG'