필요없는 tablespace 삭제

SELECT * FROM dba_data_files;
SELECT * FROM dba_temp_files;
SELECT * FROM dba_tablespaces;

DROP TABLESPACE INSA_TBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE INSA_TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE AUDIT_AUX INCLUDING CONTENTS AND DATAFILES;

테이블 이관할 테이블스페이스 생성

CREATE TABLESPACE EXAMPLE
DATAFILE '/u01/app/oracle/oradata/ORA19C/example01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- undo segment 확인
SELECT * FROM dba_rollback_segs;

-- default permanent tablespace, DEFAULT_TEMP_TABLESPACE 확인
SELECT * FROM database_properties ORDER BY 1;

--sysaux에서 가지고 있는 기능프로그램 확인
SELECT * FROM v$sysaux_occupants;

기존 table, index 확인

SELECT *
FROM dba_segments
WHERE owner = 'HR'
AND segment_type = 'TABLE';

SELECT *
FROM dba_segments
WHERE owner = 'HR'
AND segment_type = 'INDEX';

-- 인덱스 status가 valid 확인
SELECT owner, index_name, status 
FROM dba_indexes 
WHERE owner = 'HR';

tablespace 이동

--테이블스페이스 이동 명령어
ALTER TABLE hr.employees MOVE TABLESPACE EXAMPLE; 

--리터럴 문자로 이동 명령어 생성
SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE EXAMPLE;' FROM dba_tables WHERE owner = 'HR'; 

-- sysaux 테이블스페이스에서 EXAMPLE 테이블스페이스로 이동
ALTER TABLE HR.REGIONS MOVE TABLESPACE EXAMPLE;
ALTER TABLE HR.COUNTRIES MOVE TABLESPACE EXAMPLE;
ALTER TABLE HR.LOCATIONS MOVE TABLESPACE EXAMPLE;
ALTER TABLE HR.DEPARTMENTS MOVE TABLESPACE EXAMPLE;
ALTER TABLE HR.JOBS MOVE TABLESPACE EXAMPLE;
ALTER TABLE HR.EMPLOYEES MOVE TABLESPACE EXAMPLE;
ALTER TABLE HR.JOB_HISTORY MOVE TABLESPACE EXAMPLE;

table, index 확인

-- 테이블스페이스가 변경된걸 확인할 수 있다.
SELECT *
FROM dba_segments
WHERE owner = 'HR'
AND segment_type = 'TABLE';

-- index가 unusable 상태가 되어서 조회되지 않는다
SELECT *
FROM dba_segments
WHERE owner = 'HR'
AND segment_type = 'INDEX';

SELECT *
FROM dba_tables
WHERE owner = 'HR';

--데이터파일이 변경되어서 rowid가 변경된다
--rowid가 변경되면서 이전 rowid를 가리키고 있던 index가 깨져 상태가 unusuable로 된다
SELECT owner, index_name, status 
FROM dba_indexes 
WHERE owner = 'HR';

-- 인덱스가 깨져서 기존 index scan 이 full table scan으로 변경된다.
SELECT * FROM hr.employees WHERE employee_id = 100;

index 이관작업

-- ONLINE 옵션은 DB운영중에 옮길수 있다.
ALTER INDEX hr.emp_emp_id_pk REBUILD TABLESPACE EXAMPLE ONLINE; 

-- 인덱스 이관 리터럴문자 만들기
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE SYSAUX ONLINE;' 
FROM dba_indexes
WHERE owner = 'HR';

-- 전체 인덱스이관 작업
ALTER INDEX HR.REG_ID_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.COUNTRY_C_ID_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.LOC_ID_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.LOC_CITY_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.LOC_STATE_PROVINCE_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.LOC_COUNTRY_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.DEPT_ID_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.DEPT_LOCATION_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.JOB_ID_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.EMP_EMAIL_UK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.EMP_EMP_ID_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.EMP_DEPARTMENT_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.EMP_JOB_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.EMP_MANAGER_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.EMP_NAME_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.JHIST_EMP_ID_ST_DATE_PK REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.JHIST_JOB_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.JHIST_EMPLOYEE_IX REBUILD TABLESPACE EXAMPLE ONLINE;
ALTER INDEX HR.JHIST_DEPARTMENT_IX REBUILD TABLESPACE EXAMPLE ONLINE;


-- 인덱스 이관 후에는 INDEX SCAN을 이용하여 데이터를 조회한다.
SELECT * FROM hr.employees WHERE employee_id = 100;

-- 이전에는 보이지 않던 INDEX_TYPE 세그먼트가 조회된다
SELECT *
FROM dba_segments
WHERE owner = 'HR'
AND segment_type = 'INDEX';

-- 인덱스 상태가 전부 VALID 되었다.
SELECT owner, index_name, status
FROM dba_indexes 
WHERE owner = 'HR';

control file, log file 확인

-- 컨트롤파일 파라미터 확인
show parameter control_files;
-- 컨트롤파일 위치 확인
-- is_recovery_dest_file은 컨트롤 파일이 db_recovery_file_dest 파라미터에 이중화가 되었는지를 확인해줘서 
-- 원래되로라면 yes가 나와야 하는데 오류로 no로 나옴
SELECT * FROM v$controlfile;

show parameter db_recovery;

-- 이중화되어있는 컨트롤 파일을 단일로 변경
ALTER SYSTEM SET control_files = '/u01/app/oracle/oradata/ORA19C/control01.ctl' SCOPE = SPFILE;

-- redo log 그룹 확인
SELECT * FROM v$log;
SELECT * FROM v$logfile;