For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
I am using ubuntu and have oracle SQL, java 14 how can I connect JDBC to IntelliJ please provide a link to download JDBC driver and suggest how to connect
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.
thank you albertoFaenza....
Hi,
If you consider this question answered, please mark it as answered.
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
-- 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 '
|| '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
SET TERMOUT on
SET FEEDBACK on
SET HEADING on
Marcus Baocn