oracle小记

--根据pid查询sql

select sql_text from v$session s,v$process p,v$sql s1 where spid=&pid and p.addr=s.paddr and s.sql_hash_value=s1.HASH_VALUE ;

--重建组合索引

BEGIN
  FOR cur IN (select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions  where index_name='IDX_xxxx')
    LOOP
      BEGIN 
          EXECUTE IMMEDIATE 'ALTER INDEX IDX_xxxx REBUILD  subpartition '||cur.subpartition_name||' online';
          exception WHEN OTHERS THEN NULL;
      END;
    END LOOP;
END;

--bang

declare
l_depnam varchar2(20) := 'testing';
l_loc    varchar2(10) := 'Dubai';
begin

l_depnam  := 'mengl';
l_loc  := 'liang';  
 
    execute immediate 'insert into ml_test 
    select :1,:2,:3 from dual '
      using 52, l_depnam, l_loc;
commit;
end;