oracle批量添加指定表前缀的索引SQL语句

database

需要输入用户名,表空间,索引个数,表名前缀,过滤的表名后缀

declare

--用户名

userName varchar2(50) :="snsrm";

--表空间

tablespaceName varchar2(50) :="APPS_TS_TX_IDX";

--索引个数

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;

--声明变量,记录一行数

v vdata%rowtype;

n number;

--索引名,限制最大程度为30字符

indexName varchar2(30);

temp varchar2(27);

k number;

begin

--打开游标

open vdata;

--循环遍历取数据

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<rowN then

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.comments,t.TABLE_NAME from all_col_comments 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 rownum<=rowN) loop

--判断该字段是否已经创建索引

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);

--未创建索引

if n<1 then

--判断索引名是否存在

select count(*) into n from user_ind_columns t where t.INDEX_NAME=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=indexName;

end loop;

--打印创建索引语句

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;

--关闭游标

close vdata;

end;

 

以上是 oracle批量添加指定表前缀的索引SQL语句 的全部内容, 来源链接: utcz.com/z/533620.html

回到顶部