-- Shorten ALL your index names to 24 chars + generate RENAME scripts
-- Paste your index names below (one per line, schema.index_name)
SET LINESIZE 6000
SET PAGESIZE 0
SET FEEDBACK OFF
SPOOL rename_long_indexes.sql
WITH your_indexes (owner, index_name) AS (
SELECT 'SCHEMA1', 'VERY_LONG_INDEX_NAME_PART1_2025' FROM DUAL UNION ALL
SELECT 'SCHEMA1', 'ANOTHER_SUPER_LONG_INDEX_NAME_2024' FROM DUAL UNION ALL
SELECT 'SCHEMA2', 'INDEX_WITH_TOO_MANY_CHARS_FOR_ORACLE' FROM DUAL UNION ALL
-- PASTE YOUR FULL LIST BELOW (schema.index_name)
-- Example:
-- SELECT 'SCOTT', 'MY_INDEX_THAT_IS_WAY_TOO_LONG_FOR_24_CHARS' FROM DUAL UNION ALL
-- SELECT 'HR', 'EMPLOYEE_PERFORMANCE_INDEX_2025_Q4' FROM DUAL
)
SELECT
'Found: ' || owner || '.' || index_name ||
' (len=' || LENGTH(index_name) || ') -> ' || SUBSTR(index_name,1,24) AS info,
'ALTER INDEX ' || owner || '.' || index_name ||
' RENAME TO ' || SUBSTR(index_name,1,24) || ';' AS rename_sql
FROM your_indexes
WHERE LENGTH(index_name) > 24
ORDER BY LENGTH(index_name) DESC;
SPOOL OFF
-- Count
SELECT COUNT(*) AS indexes_to_shorten
FROM your_indexes
WHERE LENGTH(index_name) > 24;
Find long indexes:
-- Script: Shorten index names > 24 chars to 24 and generate RENAME statements
-- Works in Toad, SQL*Plus, SQL Developer
-- Safe for Oracle RDS
SET LINESIZE 6000
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL shorten_index_names.sql
SELECT
'Found long index: ' || owner || '.' || index_name || ' (length=' || LENGTH(index_name) || ')' AS info,
'ALTER INDEX ' || owner || '.' || index_name ||
' RENAME TO ' ||
SUBSTR(index_name, 1, 24) ||
';' AS rename_sql
FROM all_indexes
WHERE LENGTH(index_name) > 24
AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'GSMADMIN_INTERNAL')
ORDER BY LENGTH(index_name) DESC, owner, index_name;
SPOOL OFF
-- Optional: Show count
SELECT COUNT(*) AS long_indexes_found
FROM all_indexes
WHERE LENGTH(index_name) > 24
AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'GSMADMIN_INTERNAL');
SET FEEDBACK ON
=======
SET FEEDBACK ON
======
SET LINESIZE 6000
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
DECLARE
------------------------------------------------------------------
-- YOUR INDEX SCRIPT – copy-paste it between the q'[...]' delimiters
------------------------------------------------------------------
v_script CLOB := q'[
ALTER INDEX SCOTT.MY_IDX REBUILD ONLINE PARALLEL 4;
ALTER INDEX HR.EMP_IDX REBUILD ONLINE;
ALTER INDEX SALES.ORD_IDX REBUILD ONLINE NOLOGGING;
-- add as many lines as you need – 6000 chars per line is fine
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','MY_TABLE');
]';
BEGIN
------------------------------------------------------------------
-- Create the scheduler job
------------------------------------------------------------------
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'REBUILD_INDEXES_BG',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
l_sql CLOB := :1; -- bind the script
l_stmt VARCHAR2(32767);
BEGIN
FOR rec IN (
SELECT REGEXP_SUBSTR(l_sql,
'[^;]+;', -- everything up to the next ;
1, LEVEL) AS stmt
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(l_sql, ';')
) LOOP
l_stmt := TRIM(REPLACE(rec.stmt, CHR(10), ' '));
BEGIN
EXECUTE IMMEDIATE l_stmt;
DBMS_OUTPUT.PUT_LINE('OK: '||SUBSTR(l_stmt,1,200));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM||' --> '||SUBSTR(l_stmt,1,200));
END;
END LOOP;
END;
]',
number_of_arguments => 1,
enabled => FALSE,
auto_drop => TRUE, -- delete after it finishes
comments => 'Background index rebuild – one shot'
);
------------------------------------------------------------------
-- Bind the script (CLOB) to argument 1
------------------------------------------------------------------
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'REBUILD_INDEXES_BG',
argument_position => 1,
argument_value => v_script
);
------------------------------------------------------------------
-- Fire it
------------------------------------------------------------------
DBMS_SCHEDULER.ENABLE('REBUILD_INDEXES_BG');
END;
/
No comments:
Post a Comment