create a index rebuilding script

[hafeezkalsekar]

hi all,
i want to make a index rebuilding script and get in spool file at what time which index was getting rebuilded.
i tried set time on , but that doesnt work in spool file.
i used set timi on . that is working working fine but that only gives how much tiime taken by an index to rebuild but does not show at what time(sysdate).

is there any option by which i can get time(eg. 1march 12:30:00) in spool file to come to know at what time which index was getting rebuilded.

[paragp1981]

insert "select sysdate from dual;" statement after every index rebuild command.

[ams-jamali]

use if u want time with date

select to_char(sysdate,'dd-Mon-yyyy- hh24:mi:ss') from dual;

[hafeezkalsekar]

hi there,
thats right but when we create a sql file with select 'alter index '||owner||'.'||index_name||' rebuild online;' from dba_indexes
where owner='FCCUAT21'

and we create a spool file for it ,
its not possible to get sysdate after every alter index rebuild comannd.

plz try ouput before refering. and let know actuall stuff

[ams-jamali]

please post your index sql file here..,

[davey23uk]

scrool through user_objects for last_ddl time after

[PAVB]

... its not possible to get sysdate after every alter index rebuild comannd.

I really hate when some of my guys comes with a "its not possible"... look

select  "CMDLINE"
from
(
select  index_name                                                              "SORT1",
        '1'                                                                     "SORT2",
        'alter index '||owner||'.'||index_name||' rebuild online;'              "CMDLINE" 
from    dba_indexes
where   owner = 'FCCUAT21'
union
select  index_name                                                              "SORT1",
        '2'                                                                     "SORT2",
        'select to_char(sysdate,'||'''dd-Mon-yyyy hh24:mi:ss'''||') from dual;' "CMDLINE"
from    dba_indexes
where   owner = 'FCCUAT21'
)
order by "SORT1", "SORT2"
/

[LKBrwn_DBA]

SET TIME ON ECHO ON 

[hafeezkalsekar]

hi there
special thanks to PAVB and LKBrwn_DBA for giving the solutions.
friend both of ur replies meet my requirement.
special thanks to pavb for sharing a great script .

if any 1 needs other scripts like rman. automation of dbverify and other plz mail me on no_contact_emails@here.please

[hafeezkalsekar]

hi,
i am using below query

set timi on
SET HEADING OFF
SET FEEDBACK OFF
set PAGESIZE 3000
SPOOL C:\hafeez\scripts\index\fcatc_ind.sql
SET HEADING OFF
select "CMDLINE"
from
(
select index_name "SORT1",
'1' "SORT2",
'alter index '||owner||'.'||index_name||' rebuild online;' "CMDLINE"
from dba_indexes
where owner = 'SCOTT'
union
select index_name "SORT1",
'2' "SORT2",
'select to_char(sysdate,'||'''dd-Mon-yyyy hh24:mi:ss'''||') from dual;' "CMDLINE"
from dba_indexes
where owner = 'SCOTT'
)
order by "SORT1", "SORT2"
/
spool off
spool C:\hafeez\scripts\index\fcatc_ind.log
select '-- Rebuilding FCATC schema kindly wait......' from dual;
select to_char(sysdate, 'DD-MON-YY HH24:MI:SS') from dual;
set feedback on
@C:\hafeez\scripts\index\fcatc_ind.sql
set feedback off
select '-- Rebuilding FCATC schema completed......' from dual;
select to_char(sysdate, 'DD-MON-YY HH24:MI:SS') from dual;
spool off
exit


its give me out put like

Index altered.

Elapsed: 00:00:00.00

12-Jun-2008 18:00:05

can i have something through which i can get index name as well which is getting rebuilded