Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
can any body explain me this query? select 'alter index '||index_name||' monitoring usage' as inde

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.
Answers
-
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......
-
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
-
thank you albertoFaenza....
-
Hi,
If you consider this question answered, please mark it as answered.
Regards.
Alberto
-
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