Forum Stats

  • 3,768,297 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

can any body explain me this query? select 'alter index '||index_name||' monitoring usage' as inde

kbdevelopers
kbdevelopers Member Posts: 7
edited Aug 19, 2013 10:37AM in SQL & PL/SQL

Initially i've a put an index of emp table in monitoring state..

later i've got this query

select 'alter index '||index_name||' monitoring usage' as index_monitor from user_indexes where index_name='EMP';

can any body explain me what is the meaning of this query...

and how do that query in the strings of projection area works....

i'm totally confused

thank you in advance.

Tagged:
kbdevelopers

Answers

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy

    The result of this query is giving the string

    ALTER INDEX EMP MONITORING USAGE;
    

    which is actually a command that run in SqlPlus or in Dynamic SQL.

    About monitoring the index usage you can check this link:Altering Indexes

    Regards.

    Al

  • Thank you for ur reply alberto...

    my question is the query is printing the string which contains another query...

    how does that output query/string will get executed......

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited Aug 19, 2013 8:54AM

    The answer is simple: if this is the only statement it doesn't.

    You have to use the output in an SQL session or save the variable and pass it to an EXECUTE IMMEDIATE statement in a PL/SQL block.

    The only thing that this statement is doing is to select a string. Nothing else.

    I forgot to mention that if in user_indexes you don't have an index called EMP, then this query does not return any row.

    Regards.

    Al

    kbdevelopers
  • thank you albertoFaenza....

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy

    Hi,

    If you consider this question answered, please mark it as answered.

    Regards.

    Alberto

  • marcusafs
    marcusafs Member Posts: 294
    edited Aug 19, 2013 10:38AM

    This is referred to as SQL generating SQL.  It is most useful when you  need to generate SQL statements for all indexes or indexes for one user.  After generating the text you then would execute it in SQLPlus or the tool of your choice.  If you combine this with a spool statement, SPOOL MyGeneratedScript.SQL you can run it with @MyGeneratedScript in SQLPlus.  I combine this into a script which generates the file, calls the editor so you can look it over and then executes it.  Here is an example of moving indexes to a different tablespace.

    -- UTL_Move_Indexs_To_New_TableSpace_Script.SQL
    /* This script will grab all the tables out of the specified source tablespace
    ** and move them into the specified target tablespace.  Ensure the the target
    ** exists and has sufficient room.  You must be logged in as the table owner.
    */
    ACCEPT v_source_tablespace_name PROMPT 'Enter source tablespace name: '
    ACCEPT v_target_tablespace_name PROMPT 'Enter target tablespace name: '
    SET HEADING OFF
    SET FEEDBACK OFF
    SET TERMOUT OFF
    SET ECHO OFF
    SPOOL _move_index.SQL
    
    SELECT    'ALTER INDEX '
           || ndx.owner
           || '.'
           || ndx.index_name
           || CHR (10)
           || 'REBUILD '
           || CHR (10)
           || 'TABLESPACE '
           || UPPER ('&v_target_tablespace_name')
           || ';'
             sql_statement
    FROM   dba_indexes ndx
    WHERE      ndx.tablespace_name = UPPER ('&v_source_tablespace_name')
           AND ndx.index_type <> 'LOB'
    ORDER BY ndx.owner,
             ndx.index_name;
    SPOOL OFF
    
    --Edit the move script
    EDIT _move_index
    --*****************
    --Reset parameters.
    SET TERMOUT ON
    PAUSE Hit Ctrl+C to ABORT, any key to CONTINUE,
    -- Run the move script
    SPOOL  Move_Indexs_To_New_TableSpace.LOG
    _move_index
    SPOOL OFF
    
    --*****************
    --Reset parameters.
    SET TERMOUT on
    SET FEEDBACK on
    SET HEADING on
    
    

    Marcus Baocn

This discussion has been closed.