表分区维护的sql(10)

来源：未知责任编辑：责任编辑发表时间:2015-10-08 14:16　点击:次

Sqlserver Mysql Oracle DB2数据库数据库综合

values

(v_baktable, sysdate, sysdate, 'A');

commit;

end;

PROCEDURE Pro_Create_Backup_Part_Indx(v_bk_table_name in varchar2,

v_partitionflag in varchar2,

v_table_name in varchar2,

v_in_date in VARCHAR2) AS

v_temp_table varchar2(60);

v_index_name varchar2(60);

v_index_col varchar2(60);

v_index_spec varchar2(60); --存放索引的表空间，对于分区表，此字段为空

v_uniqueness varchar2(30);

v_sql varchar2(32767);

TYPE Tcur IS REF CURSOR;

cur_all_index Tcur;

cur_all_index_col Tcur;

www.2cto.com

BEGIN

v_temp_table := v_table_name;

OPEN cur_all_index FOR 'select INDEX_NAME,UNIQUEness,TABLESPACE_NAME from user_indexes US where table_name =upper(''' || v_temp_table || ''') and Partitioned=''YES''';

LOOP

FETCH cur_all_index

INTO v_index_name, v_uniqueness, v_index_spec;

EXIT WHEN cur_all_index%NOTFOUND;

begin

v_sql := NULL;

OPEN cur_all_index_col FOR 'select column_name from user_ind_columns where index_name=upper(''' || v_index_name || ''') order by column_position asc ';

LOOP

FETCH cur_all_index_col

INTO v_index_col;

EXIT WHEN cur_all_index_col%NOTFOUND;

IF v_sql IS NOT NULL THEN

v_sql := v_sql || ',';

END IF; www.2cto.com

v_sql := v_sql || v_index_col;

END LOOP;

CLOSE cur_all_index_col;