Oracle Alter Table, Index, LOB Script Collections

These are some of the scripts that will create another script that will be executed. The idea behind these scripts is that DBA won’t have to execute these alter table or index or lob one by one, when there thousand of lines of scripts.

The first script is to move lob column from every table that is owned by a schema

set wrap off
set linesize 1000
set feedback off
set pagesize 0
set verify off
set termout off

spool ALTER_LOB_MOVE.sql;
prompt set linesize 1000
prompt set pagesize 0
prompt spool LOG_ALTER_LOB_MOVE.txt

PROMPT —————— START FROM HERE —————

prompt —————— Second Script —————–

select ‘ALTER TABLE ‘||OWNER||’.'||TABLE_NAME||’ MOVE LOB(‘||COLUMN_NAME||’) STORE AS (TABLESPACE TABLESPACE-NAME);’
from all_lobs where owner like ‘SCHEMA-NAME%’;

PROMPT —————— END OF SCRIPT ———————-

prompt commit;
PROMPT /

PROMPT ——————- END OF SCRIPT ———————-

prompt spool off
SPOOL OFF;
@@ALTER_LOB_MOVE.sql

The second script is to move tables from one tablespace to another tablespace

set wrap off
set linesize 1000
set feedback off
set pagesize 0
set verify off
set termout off

spool ALTER_TABLE_MOVE.sql;
prompt set linesize 1000
prompt set pagesize 0
prompt spool LOG_ALTER_TABLE_MOVE.txt

PROMPT —————— START FROM HERE —————

prompt —————— Second Script —————–

select ‘ALTER TABLE ‘||OWNER||’.'||TABLE_NAME||’ MOVE TABLESPACE NEW-TABLESPACE-NAME;’
from all_tables where owner = ‘SCHEMA-NAME‘ and tablespace_name = ‘OLD-TABLESPACE-NAME‘;

PROMPT —————— END OF SCRIPT ———————-

prompt commit;
PROMPT /

PROMPT ——————- END OF SCRIPT ———————-

prompt spool off
SPOOL OFF;
@@ALTER_TABLE_MOVE.sql

The third script is to move index from one tablespace to another tablespace

set wrap off
set linesize 1000
set feedback off
set pagesize 0
set verify off
set termout off

spool ALTER_INDEX_REBUILD.sql;
prompt set linesize 1000
prompt set pagesize 0
prompt spool LOG_ALTER_INDEX_REBUILD.txt

PROMPT —————— START FROM HERE —————

prompt —————— Second Script —————–

select ‘ALTER INDEX ‘||OWNER||’.'||INDEX_NAME||’ REBUILD TABLESPACE NEW-TABLESPACE-NAME;’
from all_indexes where owner = ‘SCHEMA-NAME’ and tablespace_name = ‘OLD-TABLESPACE-NAME‘;

PROMPT —————— END OF SCRIPT ———————-

prompt commit;
PROMPT /

PROMPT ——————- END OF SCRIPT ———————-

prompt spool off
SPOOL OFF;
@@ALTER_INDEX_REBUILD.sql

The fourth script is to alter index of a certain schema to be monitored.

set wrap off
set linesize 1000
set feedback off
set pagesize 0
set verify off
set termout off

spool MONITOR_INDEX.sql;
prompt set linesize 1000
prompt set pagesize 0
prompt spool LOG_MONITOR_INDEX.txt

prompt —————— MONITORING INDEX ——————–

prompt —————— Second Script ———————–

select ‘ALTER INDEX ‘||OWNER||’.'||INDEX_NAME||’ MONITORING USAGE;’
from all_indexes where owner like ‘SCHEMA-NAME%’;

PROMPT —————— END OF SCRIPT ———————-

prompt commit;
PROMPT /

PROMPT ——————- END OF SCRIPT ———————-

prompt spool off
SPOOL OFF;
@@MONITOR_INDEX.sql

The Fifth script is when indexes will be renamed

set wrap off
set linesize 1000
set feedback off
set pagesize 0
set verify off
set termout off

spool RENAMING_INDEX.sql;
prompt set linesize 1000
prompt set pagesize 0
prompt spool LOG_RENAMING_INDEX.txt

prompt —————— RENAMING INDEX ——————–

prompt —————— Second Script —————–

select ‘ALTER INDEX ‘||owner||’.'||INDEX_NAME||’ RENAME TO ‘||OWNER||’.PK_’||TABLE_NAME||’;’
from all_indexes where owner like ‘SCHEMA-NAME%’ and uniqueness=’UNIQUE’ and index_name like ‘SYS%’;

PROMPT —————— END OF SCRIPT ———————-

prompt commit;
PROMPT /

PROMPT ——————- END OF SCRIPT ———————-

prompt spool off
SPOOL OFF;
@@RENAMING_INDEX.sql

Please change and modify them according your needs.