SQL 在 Oracle 11g 中如何将分区表从一个表空间移动到另一个表空间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1998627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you move a partitioned table from one tablespace to another in Oracle 11g?
提问by Henrik Warne
I have a partitioned table that belongs to tablespace report. I want to move it to tablespace recordinstead.
我有一个属于 tablespace report的分区表。我想把它移到表空间记录。
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 WHERE 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”.
但这给了我错误 ORA-145111“无法对分区对象执行操作”。
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.
但是由于表有 60 个分区（基于日期），并且因为我可能必须为多个系统执行此操作，所以我想遍历所有分区名称，将每个分区名称移动到新表空间。我试过了，但不能完全让 SQL 工作。
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?
即使我将所有现有分区都移动到新表空间，创建新分区时仍然存在问题。新分区仍然在旧表空间报告中创建。如何更改以便在新表空间记录中创建新分区？
回答by dpbradley
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)
1) 移动分区（按照 zürigschn?zlets 的回答的 PL/SQL 循环）
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:
这些是我在定义 a_tname、a_destTS、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) Set table default partition tablespace so new partitions are created there:
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) Set index default partition tablespace so new index partitions (if any) are created where you want them:
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) rebuild any partitioned indexes that need rebuilding and are not in the desired tablespace:
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) Rebuild any global indexes
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;
回答by Thomas Aregger
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:
您可以使用 PL/SQL 执行此操作，也可以使用 sql 生成语句。我决定用简单的 SQL 生成 alter table 语句：
--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
每个用户都有一个默认的表空间。如果在创建/更改时未指定其他任何内容，则会在该默认表空间中创建新的数据库对象
回答by Imran
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';
回答by Imran
--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;
回答by Peter Lang
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:
如果这是一个选项，最简单的方法可能是重命名表 ( ALTER TABLE requestLog
RENAME TO requestLogTmp;)，使用正确表空间中的所有索引创建同一个表，然后从旧表复制数据：
INSERT INTO requestLog ( SELECT * FROM requestLogTmp )
When everything is up and running, you can drop the old table.
当一切都启动并运行时，您可以删除旧表。
回答by Vinay
<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>
