Hi, All!
To perform the index rebuilding of a group of tables, I had created a PL/SQL procedure to generate a full list with all the index rebuild command and - next - execute each of them, indicating start/end of the index_rebuild, as show below:
FOR c_indx IN (SELECT owner, index_name, tablespace_name FROM dba_indexes
WHERE owner=c_schema.username AND table_name=application_tab.table_name AND index_type != 'LOB' ORDER BY 1)
LOOP
UTL_FILE.PUTF(fileHandler, 'Rebuilding INDEX ' || c_indx.owner || '.' || c_indx.index_name || '\n');
dbms_output.put_line ('ALTER INDEX ' || c_indx.owner || '.' || c_indx.index_name || ' REBUILD TABLESPACE ' || c_indx.tablespace_name || ';');
UTL_FILE.PUTF(fileHandler, 'Started at:' || TO_CHAR(sysdate, 'DD/MM/YYYY HH:MM:SS') || '\n');
EXECUTE IMMEDIATE 'ALTER INDEX ' || c_indx.owner || '.' || c_indx.index_name || ' REBUILD TABLESPACE ' || c_indx.tablespace_name;
UTL_FILE.PUTF(fileHandler, 'Finished at:' || TO_CHAR(sysdate, 'DD/MM/YYYY HH:MM:SS') || '\n');
END LOOP;
when I execute my PL/SQL script with the FOR above, I had noticed the most of time the log file register 0 (zero) seconds of execution, by example:
Rebuilding INDEX APPLICATION.CLIENTINDX0000000088F
Started at:24/09/2015 06:09:50
Finished at:24/09/2015 06:09:50
And - because of this - I am not sure if the related index_rebuild were sucessfully executed or not.
I would like to know if there is a way so I can save the whole output of the index_rebuild_command, so I can watch closely the status for my procedure execution.
When you talk about linux you can use the following command line to store in output file all the status of a script (including errors), by example:
# command_to_execute.sh >/tmp/output.log 2>&1 ===> If have similar for oracle, I would like to know.
Is there a way to record the completely status of the [EXECUTE IMMEDIATE 'ALTER INDEX ...'] command?
I appreciate all the tips you can provide me.
Thanks, in advance.
Luiz Cássio