oracle批量添加指定表前綴的索引SQL語句

需要輸入用戶名，表空間，索引個數(shù)，表名前綴，過濾的表名后綴

##需要輸入用戶名，表空間，索引個數(shù)，表名前綴，過濾的表名后綴

declare

--用戶名

userName varchar2(50) :='apps';

--表空間

tablespaceName varchar2(50) :='APPS_TS_TX_IDX';

--索引個數(shù)

rowN number :=3;

--過濾表名后綴，該表不添加索引，例如臨時表

tableLastName varchar2(10) :=upper('_temp');

tableLastName1 varchar2(10) :=upper('_te');

--表名前綴

cursor vdata is select t.table_name from user_tables t where t.TABLE_NAME like upper('SRM_POS_%')

and t.TABLE_NAME not like '%'||tableLastName

and t.TABLE_NAME not like '%'||tableLastName1

order by t.TABLE_NAME;

--聲明變量,記錄一行數(shù)

v vdata%rowtype;

n number;

--索引名，限制最大程度為30字符

indexName varchar2(30);

temp varchar2(27);

k number;

begin

--打開游標(biāo)

open vdata;

--循環(huán)遍歷取數(shù)據(jù)

loop

fetch vdata into v;

exit when vdata%NOTFOUND;

select count(*) into n from user_ind_columns t where t.TABLE_NAME=upper(v.table_name);

if n

k := 1;

--dbms_output.put_line('表名：-----'||v.table_name);

select length(v.table_name) into n from dual;

if n>27 then

temp := substr(v.table_name,0,27);

else

temp := v.table_name;

end if;

indexName := temp||'_N'||k;

for cur in (select t.column_name,t.TABLE_NAME from user_tab_columns t where t.table_name = upper(v.table_name) ---表名

and instr(lower(t.column_name), 'attribute') = 0

and instr(lower(t.COLUMN_NAME),'last_update') = 0

and instr(lower(t.COLUMN_NAME),'version_num') = 0

and instr(lower(t.COLUMN_NAME),'created_by') = 0

and instr(lower(t.COLUMN_NAME),'creation_date') = 0

and t.COLUMN_ID<=rowN) loop

--判斷該字段是否已經(jīng)創(chuàng)建索引

select count(*) into n from user_ind_columns t where t.TABLE_NAME=upper(v.table_name) and t.COLUMN_NAME=upper(cur.column_name);

--未創(chuàng)建索引

if n<1 then

--判斷索引名是否存在

select count(*) into n from user_ind_columns t where t.INDEX_NAME=upper(indexName);

while n>0 loop

k := k+1;

indexName := temp||'_N'||k;

select count(*) into n from user_ind_columns t where t.INDEX_NAME=upper(indexName);

end loop;

--打印創(chuàng)建索引語句

dbms_output.put_line('Create Index '||userName||'.'||indexName||' ON '||userName||'.'||cur.table_name||'('||cur.column_name||')'||' tablespace '||tablespaceName||';');

k := k+1;

indexName := temp||'_N'||k;

end if;

end loop;

end if;

end loop;

--關(guān)閉游標(biāo)

close vdata;

end;

總結(jié)

以上是生活随笔為你收集整理的oracle添加表的索引,oracle批量添加指定表前缀的索引SQL语句的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。