프로시저 안 만들고 SPOOL 떠서 인덱스 REBUILD 하는 신박한 방법

개발언어/PL SQL

쿵도리 2018. 4. 10. 17:36

select 'alter index '||index_name||' rebuild;'

2 from user_indexes

3 where table_name = 'EMPLOYEES';

'ALTERINDEX'||INDEX_NAME||'REBUILD;'

---------------------------------------------------

alter index EMP_NAME_IX rebuild;

alter index EMP_MANAGER_IX rebuild;

alter index EMP_JOB_IX rebuild;

alter index EMP_DEPARTMENT_IX rebuild;

alter index EMP_EMP_ID_PK rebuild;

alter index EMP_EMAIL_UK rebuild;

6 rows selected.

HR@orcl>set heading off

HR@orcl>set feedback off

HR@orcl>spool in.sql

HR@orcl>/

alter index EMP_NAME_IX rebuild;

alter index EMP_MANAGER_IX rebuild;

alter index EMP_JOB_IX rebuild;

alter index EMP_DEPARTMENT_IX rebuild;

alter index EMP_EMP_ID_PK rebuild;

alter index EMP_EMAIL_UK rebuild;

HR@orcl>spool off

HR@orcl>set heading on

HR@orcl>set feedback on

HR@orcl>/

'ALTERINDEX'||INDEX_NAME||'REBUILD;'

---------------------------------------------------

alter index EMP_NAME_IX rebuild;

alter index EMP_MANAGER_IX rebuild;

alter index EMP_JOB_IX rebuild;

alter index EMP_DEPARTMENT_IX rebuild;

alter index EMP_EMP_ID_PK rebuild;

alter index EMP_EMAIL_UK rebuild;

6 rows selected.

HR@orcl>@in

SP2-0042: unknown command "HR@orcl>/" - rest of line ignored.

Index altered.