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

declarel_depnam varchar2(20) := 'testing';l_loc    varchar2(10) := 'Dubai';beginl_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;