4000 character limit in SSIS
Opinions, experiences and information about SQL and the databases that process it, from a guy who's been at it for over 20 years.
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
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;
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;
Subscribe to:
Posts (Atom)