오라클 데이터베이스 사용자 ORA_USER 관리 및 테이블스페이스 설정 가이드

오라클 데이터베이스를 운영하다 보면 특정 사용자를 삭제하거나 다시 생성해야 하는 상황이 발생할 수 있습니다. 이 글에서는 사용자 ORA_USER(사용자가 지정하는 계정명)를 삭제하고, 새로 생성하며, 테이블스페이스와 관련된 설정을 확인 및 수정하는 방법을 단계별로 설명합니다. 또한, 테이블에 저장된 행(row) 수를 확인하는 방법도 포함되어 있습니다.

상황 요약

1. 사용자 삭제: 기존에 생성된 사용자 ORA_USER를 삭제하려고 할 때, 활성 세션이 존재하거나 종속된 객체가 있는 경우 삭제가 제한될 수 있습니다.
2. 사용자 생성: 삭제한 사용자를 동일한 이름으로 다시 생성하고, 필요한 권한과 테이블스페이스 설정을 부여합니다.
3. 테이블스페이스 관리: 사용자의 기본 테이블스페이스를 확인하고, 할당량(Quota)을 설정합니다.
4. 테이블 데이터 확인: 사용자가 소유한 테이블에 저장된 행(row) 수를 확인합니다.

1. 사용자 세션 확인 및 강제 종료

사용자를 삭제하기 전에 해당 사용자가 현재 연결된 세션이 있는지 확인하고, 필요 시 세션을 강제로 종료해야 합니다.

SQL 스크립트

-- 현재 연결된 세션 확인 
SELECT sid, serial#, username FROM v$session WHERE username = 'ORA_USER'; 

-- 세션 강제 종료 
ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL#>' IMMEDIATE; 

설명

• v$session 뷰를 통해 ORA_USER 계정으로 연결된 세션의 SID와 SERIAL#을 조회합니다.
• ALTER SYSTEM DISCONNECT SESSION 명령으로 해당 세션을 강제로 종료합니다.
• <SID>와 <SERIAL#> 값을 실제 조회 결과로 대체해야 합니다.

2. 사용자 삭제

세션이 종료되면 사용자를 삭제할 수 있습니다. 사용자가 소유한 모든 객체(테이블, 뷰, 시퀀스 등)를 함께 삭제하려면 CASCADE 옵션을 사용합니다.

SQL 스크립트

-- 사용자 삭제 
ALTER SESSION SET "_oracle_script"=TRUE; 
DROP USER ORA_USER CASCADE; 

ALTER SESSION SET “_oracle_script”=TRUE; 를 사용하는 이유

• _oracle_script는 오라클의 내부 히든 파라미터로, 시스템 계정이나 특정 작업(예: 멀티테넌트 환경에서 공통 사용자 또는 내부적으로 생성된 객체)을 처리할 때 필요합니다.
• 이 설정은 주로 오라클 12c 이상에서 멀티테넌트 아키텍처(CDB/PDB)를 사용하는 경우에 필요하며, 일반적인 사용자 작업에서는 사용되지 않습니다.
• 위 명령은 시스템 계정이나 특별히 보호되는 객체와 관련된 작업을 허용하기 위해 필요합니다.

3. 기본 테이블스페이스 확인 및 변경

사용자의 기본 테이블스페이스를 확인하고 필요 시 변경합니다.

SQL 스크립트

-- 기본 테이블스페이스 확인 
SELECT username, default_tablespace  FROM dba_users  WHERE username = 'ORA_USER'; 

-- 기본 테이블스페이스 변경 
ALTER USER ORA_USER DEFAULT TABLESPACE USERS; 

설명

• dba_users 뷰에서 사용자의 기본 테이블스페이스를 조회합니다.
• 기본 테이블스페이스를 USERS로 변경하려면 ALTER USER ... DEFAULT TABLESPACE 명령을 사용합니다.

4. 사용자 재생성 및 권한 부여

삭제한 사용자 ORA_USER를 동일한 이름으로 다시 생성하고 필요한 권한을 부여합니다.

SQL 스크립트

-- 사용자 생성 
ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE; 
CREATE USER ORA_USER IDENTIFIED BY "user_password_text"; 
GRANT CREATE SESSION TO ORA_USER; 
GRANT UNLIMITED TABLESPACE TO ORA_USER; 
GRANT CONNECT, RESOURCE TO ORA_USER; 

설명

• CREATE USER: 새 사용자를 생성하며 비밀번호를 설정합니다.
• GRANT CREATE SESSION: 데이터베이스 접속 권한 부여.
• GRANT UNLIMITED TABLESPACE: 모든 테이블스페이스에 대해 무제한 저장소 할당.
• GRANT CONNECT, RESOURCE: 기본적인 객체 생성 권한 부여.

5. 테이블스페이스 할당량 확인

사용자가 특정 테이블스페이스에서 사용할 수 있는 저장소 할당량(Quota)을 확인합니다.

SQL 스크립트

-- 할당량 확인 
SELECT tablespace_name, bytes/1024/1024 AS used_mb, max_bytes/1024/1024 AS max_mb, username FROM dba_ts_quotas; 

설명

• dba_ts_quotas 뷰는 각 사용자별로 테이블스페이스의 사용량 및 최대 할당량 정보를 제공합니다.
• 필요 시 ALTER USER ... QUOTA ... ON ... 명령으로 할당량을 조정할 수 있습니다.

6. 테이블에 저장된 행(row) 수 확인

사용자가 소유한 모든 테이블의 행 수를 조회하거나 총합을 계산합니다.

SQL 스크립트

-- 각 테이블의 행 수 조회 
SELECT table_name, num_rows FROM user_tables ORDER BY table_name; 

-- 전체 행 수 합계 계산 
SELECT SUM(num_rows) AS total_rows  FROM user_tables; 

설명

• user_tables: 현재 사용자가 소유한 모든 테이블 정보를 제공합니다.
• num_rows: 통계 정보에 기반한 각 테이블의 행 수(최신 통계가 없으면 값이 비어 있을 수 있음).
• 정확한 값을 원한다면 통계를 갱신해야 합니다:
• sqlEXEC DBMS_STATS.GATHER_SCHEMA_STATS('ORA_USER');

7. 테이블스페이스 상태 확인

테이블스페이스가 정상적으로 사용할 수 있는 상태인지 점검합니다.

SQL 스크립트

-- 테이블스페이스 상태 확인 
SELECT tablespace_name, status  FROM dba_tablespaces; 

설명

• dba_tablespaces: 데이터베이스에 존재하는 모든 테이블스페이스와 상태 정보를 제공합니다.
• 상태가 ONLINE이어야 정상적으로 사용할 수 있습니다.

결론

위 스크립트를 통해 오라클 데이터베이스에서 특정 사용자(ORA_USER)를 관리하는 방법과 관련 작업(세션 종료, 사용자 생성/삭제, 테이블스페이스 설정 등)을 수행할 수 있습니다. 이 가이드는 실무에서 자주 발생하는 문제를 해결하는 데 유용하며, 특히 데이터베이스 관리자의 업무 효율성을 높이는 데 기여할 것입니다.

주의사항:

1. 작업 전 반드시 데이터를 백업하세요.
2. 관리자 계정(SYS, SYSTEM)으로 작업할 때는 신중히 수행하세요.
3. 통계 정보(num_rows)는 최신 상태로 유지해야 정확한 결과를 얻을 수 있습니다.