Wednesday, January 19, 2011

Once again...

Once again I'm thwarted slowed down by a database limitation that, to me, seems silly and arbitrary.

4000 character limit in SSIS

Friday, January 14, 2011

Disabling/enabling indexes (Oracle)

I whipped up the following two procedures for disabling and enabling all indexes on a specified table. They do not handle every kind of index just yet. Technically it is marking indexes as unusable then rebuilding them. Unique/Primary Key indexes are not disabled/enabled in these functions. There may be a few other types of indexes not yet supported.

SQL Developer made the caps wacky - need to find the setting. :)

create or replace procedure DISABLE_INDEXES_ON_TABLE(P_OWNER_NAME in varchar2, P_TABLE_NAME in varchar2) as

DISABLE_STR long;

begin

for REC in (select * from ALL_INDEXES where OWNER=P_OWNER_NAME and TABLE_NAME=P_TABLE_NAME and UNIQUENESS='NONUNIQUE') LOOP

if REC.INDEX_TYPE in ('NORMAL', 'NORMAL/REV', 'FUNCTION-BASED DOMAIN', 'FUNCTION-BASED NORMAL') then

DISABLE_STR := 'ALTER INDEX ' || REC.INDEX_NAME || ' UNUSABLE';

end if;

execute immediate DISABLE_STR;

end loop;

end;





create or replace procedure ENABLE_INDEXES_ON_TABLE(P_OWNER_NAME in varchar2, P_TABLE_NAME in varchar2) as

REBUILD_STR long;

begin

for REC in (select * from ALL_INDEXES where OWNER=P_OWNER_NAME and TABLE_NAME=P_TABLE_NAME and UNIQUENESS='NONUNIQUE') LOOP

if REC.INDEX_TYPE in ('NORMAL', 'NORMAL/REV', 'FUNCTION-BASED DOMAIN', 'FUNCTION-BASED NORMAL') then

REBUILD_STR := 'ALTER INDEX ' || REC.INDEX_NAME || ' REBUILD';

end if;

execute immediate REBUILD_STR;

end LOOP;

end;