오라클 파티션 테이블 TRUNCATE - olakeul patisyeon teibeul TRUNCATE

## Range patition 예제

CREATE TABLE SALES
(SALES_NO VARCHAR2(10),
 CUSTOMER_NAME VARCHAR2(30),
 PRICE    NUMBER,
 SALES_DT DATE)
PARTITION BY RANGE (SALES_DT)
(
PARTITION P2019 VALUES LESS THAN (TO_DATE('20200101','YYYYMMDD')),
PARTITION P202001 VALUES LESS THAN (TO_DATE('20200201','YYYYMMDD')),
PARTITION P202002 VALUES LESS THAN (TO_DATE('20200301','YYYYMMDD')),
PARTITION P202003 VALUES LESS THAN (TO_DATE('20200401','YYYYMMDD')), 

.

.

.
PARTITION P9999 VALUES LESS THAN (MAXVALUE)
);

* MAXVALUE : 파티션 범위에 포함되지 않는 데이터가 저장됨

## Sample Data
INSERT INTO SALES (SALES_NO, CUSTOMER_NAME, PRICE, SALES_DT) VALUES('1001' , 'KIM', 15000 , TO_DATE('20190101', 'yyyymmdd'));
INSERT INTO SALES (SALES_NO, CUSTOMER_NAME, PRICE, SALES_DT) VALUES('1002' , 'HONG', 20000 , TO_DATE('20200101', 'yyyymmdd'));
INSERT INTO SALES (SALES_NO, CUSTOMER_NAME, PRICE, SALES_DT) VALUES('1003' , 'WHANG', 30000 , TO_DATE('20200201', 'yyyymmdd'));
INSERT INTO SALES (SALES_NO, CUSTOMER_NAME, PRICE, SALES_DT) VALUES('1004' , 'HA', 33000 , TO_DATE('20200201', 'yyyymmdd'));

## 해당 파티션 데이터 조회

SELECT * FROM SALES PARTITION(P202001);

## 파티션 ADD

ALTER TABLE SALES ADD PARTITION P202004 VALUES LESS THAN (TO_DATE('20200501','YYYYMMDD'));

## 파티션 DROP

ALTER TABLE SALES DROP PARTITION P2019;

## 파티션 TRUNCATE

ALTER TABLE SALES TRUNCATE PARTITION P202001;

## 파티션 SPLIT

ALTER TABLE SALES
SPLIT PARTITION P202004 AT (TO_DATE(' 2020-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO (PARTITION P9999);

## 테이블의 파티션 정보 (DBA 권한필요)

SELECT  OWNER,TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, STATUS, DEF_TABLESPACE_NAME, DEF_LOGGING, DEF_COMPRESSION, INTERVAL 
FROM    DBA_PART_TABLES       
WHERE   TABLE_NAME ='SALES';

## 관련 오라클 딕셔너리 테이블 (DBA 권한필요)

SELECT *
 FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'SALES'

## 로컬 인덱스 생성

CREATE INDEX IDX_SALES_01 ON SALES (SALES_NO) LOCAL;

## 글로벌 인덱스 생성

CREATE INDEX IDX_SALES_02 ON SALES (CUSTOMER_NAME) GLOBAL;

(* GLOBAL 키워드 생략시 디폴트로 글로벌 인덱스로 생성)

## 인덱스 REBUILD

ALTER INDEX IDX_SALES_02 REBUILD;

## 글로벌 인덱스 상태확인

SELECT INDEX_NAME, STATUS
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'SALES'
    AND OWNER = 'TEST_USER';

INDEX_NAME                     STATUS   
------------------------------ -------- 
IDX_SALES_01                     N/A     --> 파티션 인덱스는 조회불가
IDX_SALES_02                     VALID   --> INVALID / VALID

## 글로벌 인덱스 상태확인2

SELECT INDEX_NAME, STATUS
  FROM DBA_INDEXES
 WHERE STATUS = 'UNUSABLE';   

## 파티션 인덱스 상태확인

SELECT  INDEX_NAME, PARTITION_NAME, STATUS 
 FROM  DBA_IND_PARTITIONS    
WHERE  INDEX_NAME IN ( SELECT INDEX_NAME FROM DBA_PART_INDEXES WHERE TABLE_NAME ='SALES'  AND OWNER = 'TEST_USER' ) ;

[참고] 오라클 파티션 (Partition) 관리하기|작성자 Nerd

oracle

특정 USER 에게 특정 테이블 TRUNCATE 권한 부여 procdure

둥구리둥둥 2022. 7. 6. 19:18

오라클에서는 TRUNCATE 권한을 부여할 때 일반적으로 특정 테이블만 TRUNCATE 권한을 부여할수 없다.

truncate 권한 부여 방법
EX>
SQL>grant drop any table to scott;

그래서 procdure 을 이용하여 이를 해결 할수 있다.

1. 일단 두개의 테이블을 생성 한다. 
TRUNC_TABLE_HIST : 권한을 부여한 계정에 대한 truncate 의 증적을 남기는 테이블
TRUNC_TABLE_TARGET : 테이블에 계정 및 테이블 정보를 INSERT 하여 특정계정이 테이블을 TRUNCATE  가능하게 권한 부여 

<테이블 생성스크립트>
  CREATE TABLE SCOTT.TRUNC_TABLE_HIST
(
    EXEC_DATE       DATE NOT NULL,
    SESSION_USER    VARCHAR2(30) NOT NULL,
    OWNER           VARCHAR2(30) NOT NULL,
    TABLE_NAME      VARCHAR2(30) NOT NULL,
    PARTITION_NAME  VARCHAR2(30),
    MACHINE         VARCHAR2(64),
    OSUSER          VARCHAR2(30),
    TERMINAL        VARCHAR2(30),
    PROGRAM         VARCHAR2(48),
    EXEC_RESULT     VARCHAR2(2000)
)
TABLESPACE USERS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

CREATE TABLE SCOTT.TRUNC_TABLE_TARGET
(
    OWNER           VARCHAR2(30) NOT NULL,
    TABLE_NAME      VARCHAR2(30) NOT NULL,
    GRANTEE         VARCHAR2(30) NOT NULL,
    REGISTER_DATE   DATE,
    REQ_USER        VARCHAR2(30)
)
TABLESPACE USERS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

2. 프로시져 생성

CREATE OR REPLACE PROCEDURE SCOTT.TRUNC_TABLE(pOwner IN VARCHAR2, pTabName IN VARCHAR2, pPartName IN VARCHAR2 DEFAULT NULL) IS -- 파티션인경우 파티션명을 넣어주고 아닌경우 입력안해도됨
  vSessUser VARCHAR2(30);
  vMachine VARCHAR2(64);
  vOsuer VARCHAR2(30);
  vTerminal VARCHAR2(30);
  vProgram VARCHAR2(48);
  vCnt NUMBER := 0;
  vDeg NUMBER;
  vErrm VARCHAR2(1000);
BEGIN
  SELECT SYS_CONTEXT('USERENV','SESSION_USER')
    INTO vSessUser
    FROM DUAL;
  SELECT MACHINE,
         OSUSER,
         TERMINAL,
         PROGRAM
    INTO vMachine, vOsuer, vTerminal, vProgram
    FROM V$SESSION SE
 WHERE AUDSID = USERENV('SESSIONID');
  IF pPartName IS NULL THEN
    BEGIN
      FOR tab IN (SELECT DISTINCT A.OWNER, A.TABLE_NAME
                    FROM DBA_TABLES A,
                         SCOTT.TRUNC_TABLE_TARGET B
                   WHERE A.OWNER = B.OWNER
                     AND A.TABLE_NAME = B.TABLE_NAME
                     AND B.OWNER = pOwner
                     AND B.TABLE_NAME = pTabName
                     AND B.GRANTEE = vSessUser)
      LOOP
        EXECUTE IMMEDIATE 'TRUNCATE TABLE '||tab.OWNER||'.'||tab.TABLE_NAME;
        vCnt := vCnt + 1;
      END LOOP;
    END;
  ELSE
    BEGIN
      FOR tab IN (SELECT A.TABLE_OWNER, A.TABLE_NAME, A.PARTITION_NAME
                    FROM DBA_TAB_PARTITIONS A,
                         SCOTT.TRUNC_TABLE_TARGET B
                   WHERE A.TABLE_OWNER = B.OWNER
                     AND A.TABLE_NAME = B.TABLE_NAME
                     AND A.PARTITION_NAME = pPartName
                     AND B.OWNER = pOwner
                     AND B.TABLE_NAME = pTabName
                     AND B.GRANTEE = vSessUser)
      LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||tab.TABLE_OWNER||'.'||tab.TABLE_NAME||' TRUNCATE PARTITION '||tab.PARTITION_NAME;
        vCnt := vCnt + 1;
      END LOOP;
    END;
  END IF;
  IF (vCnt = 0) THEN
    INSERT INTO SCOTT.TRUNC_TABLE_HIST
    VALUES (SYSDATE,vSessUser,pOwner,pTabName,pPartName,vMachine,vOsuer,vTerminal,vProgram,'Not registered table or Not Permitted user');
    COMMIT;
    RAISE_APPLICATION_ERROR(-20001,'You are not permitted to truncate this table');
  ELSE
    INSERT INTO SCOTT.TRUNC_TABLE_HIST
    VALUES (SYSDATE,vSessUser,pOwner,pTabName,pPartName,vMachine,vOsuer,vTerminal,vProgram,NVL2(pPartName,'1 Partition Truncated successfully','1 Table Truncated successfully'));
    COMMIT;
  END IF;
  end trunc_table;
  

3.권한 부여 (프로시져를 실행 하여 테이블을 truncate 할 계정에게 권한 부여)
EX>
SQL>grant execute on scott.TRUNC_TABLE to scott;

4. 권한 부여 
SQL> insert into scott.trunc_table_target
('테이블OWNER','TRUNCATE할테이블','truncate 권한 부여할 계정',sysdate,'커멘트 넣기');
SQL> commit;
EX>
SQL> insert into scott.trunc_table_target ('scott','test111','testuser',sysdate,'홍길동에게부여');
SQL> commit;

5. 프로시져 수행 방법
SQL> exec scott.trunc_table ('OWNER명','TABLE명','PARTITION TABLE명'(생각가능));

6. 수행 여부 확인
SQL>select * from scott.trunc_table_hist;