How do you move a partitioned table from one tablespace to another in Oracle 11g?
I have a partitioned table that belongs to tablespace report. I want to move it to tablespace record instead.
One possibility is to drop the table and recreate it in the new tablespace, but that is not an option for me, since there is data in the table that needs to survive the move.
I started by checking that the partitions actually belong to tablespace report with:
SELECT * FROM user_tab_partitions WH开发者_JAVA百科ERE table_name = 'REQUESTLOG';
Then I just tried:
ALTER TABLE requestLog MOVE TABLESPACE record;
But that gives me error ORA-145111 “cannot perform operation on a partitioned object”.
Then I found out that I can move individual partitions using:
ALTER TABLE requestLog MOVE PARTITION "2009-12-29" TABLESPACE report;
But since there are 60 partitions of the table (based on date), and because I may have to do this for several systems, I would like to loop over all the partition names, moving each to the new tablespace. I tried that, but couldn’t quite get the SQL to work.
Even if I move all the existing partitions to the new tablespace, there is still a problem when creating new partitions. The new partitions are still created in the old tablespace report. How do I change so that new partitions are created in the new tablespace record?
You have to consider indexes that may be invalidated as well - to cover your question about resetting the default tablespaces in addition to this, I think this is the full process that you'll want to implement:
1) Move partitions (a PL/SQL loop as per zürigschnäzlets' answer)
These are procedures I use within an anonymous block wrapper that defines a_tname, a_destTS, vTname, and vTspName - they should give you the general idea:
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) Set table default partition tablespace so new partitions are created there:
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) Set index default partition tablespace so new index partitions (if any) are created where you want them:
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) rebuild any partitioned indexes that need rebuilding and are not in the desired tablespace:
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) Rebuild any global indexes
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;
You can either do it with PL/SQL or generate the statements with sql. I decided to generate the alter table statements with simple 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';
You can execute the output from the previous statement.
Every user has a default tablespace. New database objects are created in that default tablespace if nothing else is specified on creation/alteration
The easiest way to move the data within tablespaces:
Moving all non-partitioned tables
SELECT 'ALTER TABLE '||OWNER|| '.'||TABLE_NAME||' MOVE TABLESPACE ARCHIVE;'
FROM ALL_tables
where owner = 'owner_name'
and temporary != 'Y'
and partitioned != 'YES';
Partitioned tables
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$%';
Non-partitioned indexes
SELECT 'ALTER INDEX '|| OWNER||'.'||OBJECT_NAME ||' REBUILD TABLESPACE ARCHIVE ;'
FROM ALL_OBJECTS
WHERE OBJECT_TYPE ='INDEX'
AND OWNER = 'owner_name';
Partitioned indexes
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;
If this is an option, the easiest way could be to rename the table (ALTER TABLE requestLog
RENAME TO requestLogTmp;
), create the same table with all indexes in the correct tablespace and copy the data from the old table:
INSERT INTO requestLog ( SELECT * FROM requestLogTmp )
When everything is up and running, you can drop the old table.
<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>
精彩评论