Oracle 11g에서 분할 테이블을 한 테이블스페이스에서 다른 테이블스페이스로 이동하려면 어떻게 해야 합니까?

Oracle 11g에서 분할 테이블을 한 테이블스페이스에서 다른 테이블스페이스로 이동하려면 어떻게 해야 합니까?

저는 테이블스페이스 리포트에 속한 구획된 테이블을 가지고 있습니다.대신 테이블 스페이스 레코드로 옮기고 싶습니다.

한 가지 가능성은 테이블을 떨어뜨려 새로운 테이블 공간에 다시 만드는 것이지만, 테이블에는 이동에서 살아남아야 하는 데이터가 있기 때문에 저에게는 선택 사항이 아닙니다.

먼저 파티션이 실제로 테이블스페이스 보고서에 속하는지 확인하는 것으로 시작했습니다.

SELECT * FROM user_tab_partitions WHERE table_name = 'REQUESTLOG';

그 다음엔 그냥 시도해봤어요.

ALTER TABLE requestLog MOVE TABLESPACE record;

그러나 ORA-145111은 "분할된 개체에 대해 작업을 수행할 수 없습니다."라는 오류를 제공합니다.

그런 다음 다음을 사용하여 개별 파티션을 이동할 수 있다는 사실을(를)

ALTER TABLE requestLog MOVE PARTITION "2009-12-29" TABLESPACE report;

그러나 테이블의 파티션이 60개(날짜 기준)이고, 여러 시스템에 대해 이 작업을 수행해야 할 수도 있기 때문에 모든 파티션 이름을 루프오버하여 새 테이블스페이스로 이동하고자 합니다.시도해 보았지만 SQL이 제대로 작동하지 않았습니다.

기존 파티션을 모두 새 테이블스페이스로 옮겨도 새 파티션을 만들 때 문제가 발생합니다.새 파티션이 이전 테이블스페이스 보고서에 여전히 생성됩니다.새 테이블스페이스 레코드에 새 파티션이 생성되도록 변경하려면 어떻게 해야 합니까?

무효화될 수도 있는 인덱스도 고려해야 합니다. 기본 테이블스페이스 재설정에 대한 질문과 이에 더해 구현하고자 하는 전체 프로세스는 다음과 같습니다.

1) 파티션 이동(zürigschnäzlets의 답변에 따라 PL/SQL 루프)

이것들은 a_tname, a_dest를 정의하는 익명의 블록 포장지 내에서 사용하는 프로시저입니다.TS, vTName 및 vTspName - 일반적인 아이디어는 다음과 같습니다.

procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) is
cursor pCur(vTname varchar2, vTspName varchar2) is
  select table_name, partition_name
  from user_tab_partitions
  where table_name = vTname
      and tablespace_name not like vTspName
  order by partition_position desc;
begin
for pRow in pCur(a_tname, a_destTS) loop
 sqlStmnt := 'alter table '||pRow.table_name||
             ' move partition '||pRow.partition_name||
             ' tablespace '||a_destTS;
execute immediate sqlStmnt;
end loop;
end mvTabPart;

2) 새 파티션이 생성되도록 테이블 기본 파티션 테이블스페이스를 설정합니다.

    procedure setDefTabPart (a_tname in varchar2, a_destTS in varchar2) is
    cursor tCur(vTname varchar2) is
      select table_name
      from user_part_tables
      where table_name = vTname;
    begin
    for tRow in tCur(a_tname) loop
     sqlStmnt := 'alter table '||tRow.table_name||
                 ' modify default attributes '||
                 ' tablespace '||a_destTS;
    execute immediate sqlStmnt;
    end loop;
end setDefNdxPart;

3) 원하는 곳에 새 인덱스 파티션(있는 경우)이 생성되도록 인덱스 기본 파티션 테이블스페이스를 설정합니다.

procedure setDefNdxPart (a_tname in varchar2, a_destTS in varchar2) is
cursor iCur(vTname varchar2) is
  select index_name
  from user_part_indexes
  where index_name in (select index_name
             from user_indexes where table_name = vTname);
begin
for iRow in iCur(a_tname) loop
 sqlStmnt := 'alter index '||iRow.index_name||
             ' modify default attributes '||
             ' tablespace '||a_destTS;
execute immediate sqlStmnt;
end loop;

end setDefNdxPart;

4) 재구성이 필요하고 원하는 테이블스페이스에 없는 분할 인덱스를 다시 작성합니다.

procedure mvNdxPart (a_tname in varchar2, a_destTS in varchar2) is
cursor ndxCur(vTname varchar2, vTspName varchar2) is
select i.index_name index_name, ip.partition_name partition_name
  from user_ind_partitions ip, user_indexes i
  where i.index_name = ip.index_name
     and i.table_name = vTname
     and i.partitioned = 'YES'
     and (ip.tablespace_name not like vTspName or ip.status not like 'USABLE')
  order by index_name, partition_name ;
begin
for ndxRow in ndxCur(a_tname, a_destTS) loop
 sqlStmnt := 'alter index '||ndxRow.index_name||
             ' rebuild partition '||ndxRow.partition_name||
             ' tablespace '||a_destTS;
execute immediate sqlStmnt ;
end loop;
end mvNdxPart;

5) 전역 인덱스 다시 만들기

procedure mvNdx (a_tname in varchar2, a_destTS in varchar2) is
cursor ndxCur(vTname varchar2, vTspName varchar2) is
  select index_name
  from user_indexes
  where table_name = vTname
       and partitioned = 'NO'
       and (tablespace_name not like vTspName or status like 'UNUSABLE')
  order by index_name ;
begin
for ndxRow in ndxCur(a_tname, a_destTS) loop
 sqlStmnt := 'alter index '||ndxRow.index_name||
             ' rebuild tablespace '||a_destTS;
execute immediate sqlStmnt ;
end loop;
end mvNdx;

PL/SQL을 사용하거나 sql을 사용하여 문을 생성할 수 있습니다.간단한 SQL을 사용하여 변경 가능한 문을 생성하기로 결정했습니다.

--set linesize
set lines 100

--This Query generates the alter table statements:
SELECT 'ALTER TABLE '
       ||table_name
       ||' MOVE PARTITION '
       ||partition_name
       ||' TABLESPACE REPORT;'
FROM   all_tab_partitions
WHERE  table_name = 'requestLog'; 

이전 문장의 출력을 실행할 수 있습니다.

모든 사용자는 기본 테이블스페이스를 가집니다.생성/변경 시 다른 내용이 지정되지 않은 경우 해당 기본 테이블스페이스에 새 데이터베이스 개체가 생성됨

테이블 영역 내에서 데이터를 이동하는 가장 쉬운 방법:

파티셔닝되지 않은 모든 테이블 이동

SELECT 'ALTER TABLE '||OWNER|| '.'||TABLE_NAME||' MOVE TABLESPACE ARCHIVE;'  
FROM ALL_tables 
where owner = 'owner_name' 
and temporary != 'Y'
and partitioned != 'YES';

구획화된 표

SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' ||  PARTITION_NAME||  ' TABLESPACE ARCHIVE;'  FROM ALL_tab_partitions 
WHERE TABLE_OWNER = 'owner_name' 
AND table_NAME NOT LIKE 'BIN$%';

비파티션 인덱스

SELECT 'ALTER INDEX '|| OWNER||'.'||OBJECT_NAME ||' REBUILD TABLESPACE ARCHIVE ;' 
FROM ALL_OBJECTS 
WHERE OBJECT_TYPE ='INDEX'
AND OWNER = 'owner_name';

분할 인덱스

SELECT  'ALTER INDEX '||I.INDEX_NAME||'REBUILD PARITION'|| S.PARTITION_NAME || ' TABLESPACE  ARCHIVE ' 
                   FROM  DBA_INDEXES I,    DBA_SEGMENTS S
                  WHERE  I.INDEX_NAME = S.SEGMENT_NAME
                    AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP')
                    AND I.OWNER = 'owner_name'; 

--MOVING ALL TABLES FROM USER  
BEGIN
  FOR i IN (
    SELECT * FROM ALL_tables where owner = :owner 
      and (tablespace_name is null or tablespace_name != :tbs)
      and temporary != 'Y'
      and partitioned != 'YES'
    ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs;
  END LOOP; 
END;


--MOVING ALL INDEX

 BEGIN
  FOR i IN (
    SELECT * FROM ALL_tab_partitions 
    WHERE table_owner = :owner and tablespace_name != :tbs
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' 
      || i.table_name || ' MOVE PARTITION '
      || i.partition_name ||' TABLESPACE '|| :tbs;
  END LOOP;
END;


--MOVING ALL PARTATION TABLES FROM USER  

BEGIN
  FOR i IN (
    SELECT * FROM ALL_tables where owner = :owner and partitioned = 'YES'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '
      || i.table_name || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :tbs;
  END LOOP;
END;

옵션인 경우 테이블 이름을 변경하는 것이 가장 쉬운 방법이 될 수 있습니다.ALTER TABLE requestLog RENAME TO requestLogTmp;), 올바른 테이블 공간에 모든 인덱스가 있는 동일한 테이블을 만들고 이전 테이블에서 데이터를 복사합니다.

INSERT INTO requestLog ( SELECT * FROM requestLogTmp )

모든 것이 정상적으로 작동할 때, 당신은 오래된 테이블을 떨어뜨릴 수 있습니다.

    <pre><code>PROCEDURE P_ALTER_TABLE_SPACE(
        A_TNAME         IN VARCHAR2,
        A_DESTTS        IN VARCHAR2,
        A_PATITION_TYPE IN VARCHAR2)
    IS
      CURSOR PCUR(VTNAME VARCHAR2, VTSPNAME VARCHAR2)
      IS
        SELECT TABLE_NAME,
          PARTITION_NAME
        FROM USER_TAB_PARTITIONS
        WHERE TABLE_NAME = VTNAME
        AND TABLESPACE_NAME NOT LIKE VTSPNAME
        ORDER BY PARTITION_POSITION DESC;

      CURSOR PCURR(VTNAME VARCHAR2, VTSPNAME VARCHAR2)
      IS
        SELECT TABLE_NAME,
          SUBPARTITION_NAME
        FROM USER_TAB_SUBPARTITIONS
        WHERE TABLE_NAME = VTNAME
        AND TABLESPACE_NAME NOT LIKE VTSPNAME
        ORDER BY SUBPARTITION_POSITION DESC;
    BEGIN
      IF A_PATITION_TYPE = 'PARTITION' THEN
        FOR PROW IN PCUR(A_TNAME, A_DESTTS)
        LOOP
          SQLSTMNT := 'ALTER TABLE '||PROW.TABLE_NAME|| ' MOVE PARTITION '||PROW.PARTITION_NAME|| ' TABLESPACE '||A_DESTTS;
          EXECUTE IMMEDIATE SQLSTMNT;
        END LOOP;
      ELSE
        FOR PROW IN PCURR(A_TNAME, A_DESTTS)
        LOOP
          SQLSTMNT := 'ALTER TABLE '||PROW.TABLE_NAME|| ' MOVE SUBPARTITION '||PROW.SUBPARTITION_NAME|| ' TABLESPACE '||A_DESTTS;
          EXECUTE IMMEDIATE SQLSTMNT;
        END LOOP;
      END IF;
    END P_ALTER_TABLE_SPACE;
    </code></pre>

언급URL : https://stackoverflow.com/questions/1998627/how-do-you-move-a-partitioned-table-from-one-tablespace-to-another-in-oracle-11g