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

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