오라클 테이블스페이스 관리(실전)

DBA_DATA_FILES에서 DATAFILE 파일 조회

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 AS "SIZE(MB)",
    AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES';

DBA_TEMP_FILES에서 TEMPFILE 파일 조회

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 AS "SIZE(MB)",
    AUTOEXTENSIBLE
FROM DBA_TEMP_FILES
WHERE AUTOEXTENSIBLE = 'YES';

데이터 파일과 템프 파일 테이블 합쳐서 AUTOEXTENSIBLE이 YES인 것만 출력하기

SELECT 
    'ALTER DATABASE DATAFILE '''||FILE_NAME||''' AUTOEXTEND OFF;'
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES'
UNION ALL
SELECT 
    'ALTER DATABASE TEMPFILE '''||FILE_NAME||''' AUTOEXTEND OFF;'
FROM DBA_TEMP_FILES
WHERE AUTOEXTENSIBLE = 'YES';

데이터 파일 템프 파일 AUTOEXTEND 설정 끄기

 

ALTER DATABASE DATAFILE '경로' AUTOEXTEND OFF;

 

파라미터 변경

cd $ORACLE_HOME/dbs

-- 파라미터 백업
cp spfileSTR.ora spfileSTR.ora_20220706

ORACLE INSTANCE 가 START UP 시에 SPFILE과 PFILE을 읽어 들어 파라미터 값을 적용시킨다. SPFILE이 있으면 SPFILE을 읽게 되며, SPFILE가 존재하지 않으면 PFILE을 읽게 된다. 기본적으로 pfile은 initdXXXX.ora로 정의되어 있고 spfile은 spfileXXXX.ora로 지정되어 있다. 여기서 XXXX는 SID값이다.

-- pfile 생성 
create pfile from spfile;

spfile을 기본으로 pfile을 생성했다. 여기서 주의할 점은 pfile은 아스키 값이고 spfile은 바이너리 파일이다. 그래서 pfile은 직접 편집기로 수정해도 되지만, spfile은 명령어로 값을 변경하던지 pfile을 변경한 뒤 spfile로 만들어야 한다.

show parameter db_files
alter system set db_files=2000 scope=spfile;

디비를 내렸다 다시 올려야 db_files 파라미터 변경된다. 운영 시스템에서 절때 하면 안 된다!!!

SELECT 
    NAME,
    VALUE, 
    ISSYS_MODIFIABLE 
FROM V$PARAMETER
WHERE NAME = 'db_files';

V$PARAMETER 테이블에서도 db_files 파라미터가 변경된 것을 확인할 수 있다.

테이블 스페이스 구성 작업 후 PROFILE 설정 꼭 해줘야 한다. 설정하지 않으면 비밀번호 틀렸다고 시스템 계정이 접속이 안될 수도 있다.

SELECT 
    USERNAME, 
    ACCOUNT_STATUS, 
    DEFAULT_TABLESPACE, 
    TEMPORARY_TABLESPACE,
    PROFILE
FROM DBA_USERS;

디비 처음 설치했을 때 생성되는 계정들과 PROFILE이 DEFAULT값으로 설정돼있는 것을 확인할 수 있다.

SELECT 
    * 
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT'
AND LIMIT <> 'UNLIMITED';

위의 테이블을 조회하면 DEFAULT 값이 상세하게 나온다.

ALTER PROFILE DEFAULT 
LIMIT 
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME    UNLIMITED
PASSWORD_LOCK_TIME    UNLIMITED
PASSWORD_GRACE_TIME   UNLIMITED;

UNLIMITED가 아닌 5개의 값이 나오는데 4개의 값만 UNLIMITED로 바꾼다. 엑셀 파일로 백업을 진행하고 설정을 변경한다. 항상 백업을 진행하고 설정을 바꿔야 한다.

• FAILED_LOGIN_ATTEMPTS 부정확한 password 허용 횟수 변경
• PASSWORD_LIFE_TIME 패스워드 유효기간
• PASSWORD_LOCK_TIME 암호 잠김 시간
• PASSWORD_GRACE_TIME 암호 변경 메시지 출력 일자

테이블 스페이스 생성

CREATE TABLESPACE TS_STR_D
DATAFILE '/oracle/app/oracle/oradata/STR/TS_STR_D_001.DBF' SIZE 1G
AUTOEXTEND OFF;

CREATE TABLESPACE TS_STR_I
DATAFILE '/oracle/app/oracle/oradata/STR/TS_STR_I_001.DBF' SIZE 1G
AUTOEXTEND OFF;

이름 규칙은 보통 테이블 스페이스명(TBS, TS) + DB 계정명 + 테이블(D), 인덱스(I)로 보통 사용한다.

DB계정 생성과 DB계정 권한

-- DB계정 생성
CREATE USER STR
IDENTIFIED BY "oracle"
DEFAULT TABLESPACE TS_STR_D;

-- DB계정 권한
GRANT CONNECT, RESOURCE TO STR;

DB계정 상태 & 권한 확인

-- DB계정 상태
SELECT 
    USERNAME, 
    ACCOUNT_STATUS, 
    DEFAULT_TABLESPACE, 
    PROFILE
FROM DBA_USERS
WHERE USERNAME = 'STR';

-- 권한 확인
SELECT 
   * 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'STR';

테이블 생성 예시

-- 샘플 테이블 생성
CREATE TABLE STR.CUST
(
  CUST_NAME   VARCHAR2(20),
  MOB_NUMBER  VARCHAR2(20)
)
TABLESPACE TS_STR_D;

테이블, DB 계정, 테이블 스페이스 삭제

-- 테이블 삭제
DROP TABLE STR.CUST;

-- 휴지통 비우기(완전 삭제)
PURGE DBA_RECYCLEBIN;

-- DB계정 삭제
DROP USER STR CASCADE;

-- 테이블스페이스 삭제
DROP TABLESPACE TS_STR_D INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TS_STR_I INCLUDING CONTENTS AND DATAFILES;

참고

https://www.youtube.com/watch?v=bIRjupXHTvw&t=1041s