create a index rebuilding script
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: create a index rebuilding script

  1. #1
    Join Date
    Jan 2008
    Posts
    17

    create a index rebuilding script

    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.

  2. #2
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    insert "select sysdate from dual;" statement after every index rebuild command.
    Rgds
    Parag

  3. #3
    Join Date
    Jan 2007
    Posts
    231
    use if u want time with date

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

  4. #4
    Join Date
    Jan 2008
    Posts
    17
    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

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    please post your index sql file here..,

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    scrool through user_objects for last_ddl time after

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by hafeezkalsekar
    ... 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
    Code:
    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"
    /
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Talking

    PHP Code:
    SET TIME ON ECHO ON 
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  9. #9
    Join Date
    Jan 2008
    Posts
    17
    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

  10. #10
    Join Date
    Jan 2008
    Posts
    17
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width