-
That did not work. It did not like that statement. Here is what I have below:
Declare
V_sql_1 varchar2(100);
V_sql_2 varchar2(100);
v_sql_3 varchar2(100);
Cursor Ind_Cur is Select index_name from all_Indexes where TABLESPACE_NAME = 'PRO_DATA' and owner in ('PRO','OHT') Order by INDEX_NAME;
Begin
For Ind_rec in Ind_Cur Loop
v_sql_1 := 'Alter index PRO.';
v_sql_2 := ind_rec.index_name;
v_sql_3 := ' rebuild tablespace pro_index';
v_sql_1 := v_sql_1||v_sql_2||v_sql_3;
execute immediate v_sql_1;
dbms_output.put_line(v_sql_1||' has been moved to PRO_INDEX');
End loop;
End;
ERROR: execute immediate v_sql_1;
*
ORA-06550: line 19, column 9:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
:= . ( @ % ;
-
add dbms_output.put_line(v_sql_1) before your execute immediate and see if the syntaxis is good
-
HELP
Same problem. I just want to write a script that rebuilds all the indexes for a specific User into a different tablespace. how can I do this.
jeremy
-
Check following problem :
1.
v_sql_1 := 'Alter index PRO.'; -- but u read from all_Indexes users -> in ('PRO','OHT') .
2. (but only may be???)
(You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or for a partition (ALTER INDEX ... MODIFY PARTITION). You can specify PCTFREE in all other forms of the ALTER INDEX statement.)
ur first(old) tablespace has different default PCTFREE parameter, then second(new) tablespace.
-
so is your syntax good? you didnt think by adding dbms_output your proc will work huh?!?
-
it works for me I just tried it
-
WHAT version of Oracle
i am running 8.0.4.0 and I am getting the error around Execute and the way I use it. Man this sucks.
THIS IS THE ERROR:
46>
execute immediate v_sql_1;
*
ORA-06550: line 13, column 21:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
:= . ( @ % ;
-
U cannot use EXECUTE IMMEDIATE in 8.0.4
u should use dbms_sql package for executing synamic sql statments.
-
How about this syntax..
Declare
V_sql_1 varchar2(100);
V_sql_2 varchar2(100);
v_sql_3 varchar2(100);
Cursor Ind_Cur is Select index_name from all_Indexes where TABLESPACE_NAME = 'PRO_DATA' and owner = 'PRO' Order by INDEX_NAME;
Begin
For Ind_rec in Ind_Cur Loop
v_sql_1 := 'Alter index PRO.';
v_sql_2 := ind_rec.index_name;
v_sql_3 := ' rebuild tablespace pro_index';
v_sql_1 := v_sql_1||v_sql_2||v_sql_3;
dbms_output.put_line(v_sql_1||' has been moved to PRO_INDEX');
dbms_sql(alter index pro.ind_rec.index_name rebuild tablespace pro_index);
End loop;
End;
-
This is little bit more difficult, then use EXECUTE IMMEDIATE.
U should:
-- open (create) cursor
-- parse statment
...
This is little example for u:
-- ----------------------------------------------
create or replace
procedure Do_Sql(p_TXT_SQL varchar2) is
temp_sql integer;
r number ;
res number ;
begin
temp_sql := dbms_sql.open_cursor;
dbms_sql.parse(temp_sql,temp_sql,DBMS_SQL.NATIVE);
r := dbms_sql.execute(temp_sql);
dbms_sql.close_cursor(temp_sql);
return;
exception
when INVALID_CURSOR then
raise_application_failure(-20199, 'Error in do_sql() - invalid statment');
when OTHERS then
raise_application_failure(-20199, 'Error in do_sql() - undefined error');
end;
/
-- --------------------------------------------
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|