231020 Oracle SQL TABLE, INDEX, COLUMN_ 이름, 제약조건명 수정

■ 테이블명 수정 
A. 방법1. RENAME 기존이름 TO 새로운 이름;
# 오류발생 : ORA-01765: specifying owner''s name of the table is not allowed
소유자만 변경 가능. 소유자 이름은 사용불가.

RENAME hr.emp TO hr.emp_new;

# 소유자 없이 테이블명 변경가능

RENAME emp TO emp_new;

# 확인

SELECT * FROM tab;
SELECT * FROM hr.emp_new;

# 'EMP_NEW' 제약조건

SELECT * FROM user_constraints WHERE table_name = 'EMP_NEW' ;
SELECT * FROM user_cons_columns WHERE table_name = 'EMP_NEW' ;

# 'EMP_NEW' INDEX 제약조건

SELECT * FROM user_indexes WHERE table_name = 'EMP_NEW' ;
SELECT * FROM user_ind_columns WHERE table_name = 'EMP_NEW' ;

B. 방법2. ALTER TABLE 기존이름 RENAME TO 새로운 이름;
# 소유자 없이 테이블명 변경

ALTER TABLE emp_new RENAME TO emp;

# 확인

SELECT * FROM tab;
SELECT * FROM hr.emp;

# 'EMP' 제약조건

SELECT * FROM user_constraints WHERE table_name = 'EMP' ;
SELECT * FROM user_cons_columns WHERE table_name = 'EMP' ;

# 'EMP' INDEX 제약조건

SELECT * FROM user_indexes WHERE table_name = 'EMP' ;
SELECT * FROM user_ind_columns WHERE table_name = 'EMP' ;

■ 컬럼명 수정 
# ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼 TO 새로운 컬럼;

DESC hr.emp
ALTER TABLE hr.emp RENAME COLUMN id TO emp_id;

# 컬럼명 수정

DESC hr.emp

SELECT * FROM tab;
SELECT * FROM hr.emp;

# 'EMP' 제약조건

SELECT * FROM user_constraints WHERE table_name = 'EMP' ;
SELECT * FROM user_cons_columns WHERE table_name = 'EMP' ;

# 'EMP' INDEX 제약조건

SELECT * FROM user_indexes WHERE table_name = 'EMP' ;
SELECT * FROM user_ind_columns WHERE table_name = 'EMP' ;

■ 제약조건 이름 수정 
# ALTER TABLE 테이블명 RENAME CONSTRAINT 기존제약조건이름 TO 새로운 제약조건이름;

ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO emp_empid_pk;

# 'EMP' 제약조건

SELECT * FROM user_constraints WHERE table_name = 'EMP' ;
SELECT * FROM user_cons_columns WHERE table_name = 'EMP' ;

■ index 이름 수정 
# ALTER INDEX 인덱스이름 RENAME TO 새로운인덱스이름;

ALTER INDEX emp_id_pk RENAME TO emp_id_idx;

# 'EMP' INDEX 제약조건

SELECT * FROM user_indexes WHERE table_name = 'EMP' ;
SELECT * FROM user_ind_columns WHERE table_name = 'EMP' ;