如何在 oracle 11g 中将表从一个表空间移动到另一个表空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18327671/
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 to move table from one tablespace to another in oracle 11g
提问by user2698625
I run oracle 11gand need to move table (tbl1)from one tablespace (tblspc1)to another (tblspc2). What is the easiest way to do that?
我跑oracle 11g，需要table (tbl1)从一个移动tablespace (tblspc1)到另一个(tblspc2)。最简单的方法是什么？
回答by Rahul Tripathi
Try this:-
尝试这个：-
ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>
Very nice suggestion from IVANin comments so thought to add in my answer
IVAN在评论中提出了非常好的建议，所以想添加我的答案
Note: this will invalidate all table's indexes. So this command is usually followed by
注意：这将使所有表的索引无效。所以这个命令后面通常是
alter index <owner>."<index_name>" rebuild;
回答by user3025076
Use sqlfrom sql:
使用sql自sql：
spool output of this to a file:
将这个输出到一个文件：
select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';
spoolfile will have something like this:
spoolfile 将具有如下内容：
alter index OWNER.PK_INDEX rebuild tablespace CORRECT_TS_NAME;
回答by Rooie3000
Moving tables:
移动桌：
First run:
第一次运行：
SELECT 'ALTER TABLE <schema_name>.' || OBJECT_NAME ||' MOVE TABLESPACE '||' <tablespace_name>; '
FROM ALL_OBJECTS
WHERE OWNER = '<schema_name>'
AND OBJECT_TYPE = 'TABLE' <> '<TABLESPACE_NAME>';
-- Or suggested in the comments (did not test it myself)
-- 或者在评论中建议（没有自己测试过）
SELECT 'ALTER TABLE <SCHEMA>.' || TABLE_NAME ||' MOVE TABLESPACE '||' TABLESPACE_NAME>; '
FROM dba_tables
WHERE OWNER = '<SCHEMA>'
AND TABLESPACE_NAME <> '<TABLESPACE_NAME>
Where <schema_name>is the name of the user.
And <tablespace_name>is the destination tablespace.
<schema_name>用户名在哪里。并且<tablespace_name>是目标表空间。
As a result you get lines like:
结果，您会得到如下几行：
ALTER TABLE SCOT.PARTS MOVE TABLESPACE USERS;
ALTER TABLE SCOT.PARTS 移动表空间用户；
Paste the results in a script or in a oracle sql developer like application and run it.
将结果粘贴到脚本或 oracle sql 开发人员（如应用程序）中并运行它。
Moving indexes:
移动索引：
First run:
第一次运行：
SELECT 'ALTER INDEX <schema_name>.'||INDEX_NAME||' REBUILD TABLESPACE <tablespace_name>;'
FROM ALL_INDEXES
WHERE OWNER = '<schema_name>'
AND TABLESPACE_NAME NOT LIKE '<tablespace_name>';
The last line in this code could save you a lot of time because it filters out the indexes which are already in the correct tablespace.
这段代码的最后一行可以为您节省大量时间，因为它过滤掉了已经在正确表空间中的索引。
As a result you should get something like:
结果你应该得到类似的东西：
ALTER INDEX SCOT.PARTS_NO_PK REBUILD TABLESPACE USERS;
ALTER INDEX SCOT.PARTS_NO_PK 重建表空间用户；
Paste the results in a script or in a oracle sql developer like application and run it.
将结果粘贴到脚本或 oracle sql 开发人员（如应用程序）中并运行它。
Last but not least, moving LOBs:
最后但并非最不重要的是，移动 LOB：
First run:
第一次运行：
SELECT 'ALTER TABLE <schema_name>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <table_space>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<schema_name>' AND DATA_TYPE like '%LOB%';
This moves the LOB objects to the other tablespace.
这会将 LOB 对象移动到另一个表空间。
As a result you should get something like:
结果你应该得到类似的东西：
ALTER TABLE SCOT.bin$6t926o3phqjgqkjabaetqg==$0 MOVE LOB(calendar) STORE AS (TABLESPACE USERS);
ALTER TABLE SCOT.bin$6t926o3phqjgqkjabaetqg==$0 MOVE LOB(calendar) STORE AS (TABLESPACE USERS);
Paste the results in a script or in a oracle sql developer like application and run it.
将结果粘贴到脚本或 oracle sql 开发人员（如应用程序）中并运行它。
O and there is one more thing:
哦，还有一件事：
For some reason I wasn't able to move 'DOMAIN' type indexes. As a work around I dropped the index. changed the default tablespace of the user into de desired tablespace. and then recreate the index again. There is propably a better way but it worked for me.
出于某种原因，我无法移动“域”类型的索引。作为一种解决方法，我删除了索引。将用户的默认表空间更改为所需的表空间。然后再次重新创建索引。可能有更好的方法，但它对我有用。
回答by Dba
Try this to move your table (tbl1) to tablespace (tblspc2).
试试这个把你的表 (tbl1) 移动到表空间 (tblspc2)。
alter table tb11 move tablespace tblspc2;
回答by santosh tiwary
I tried many scripts but they didn't work for all objects. You can't move clustered objects from one tablespaceto another. For that you will have to use expdp, so I will suggest expdp is the best option to move all objects to a different tablespace.
我尝试了很多脚本，但它们并不适用于所有对象。您不能将集群对象从一个表空间移动到另一个表空间。为此，您将不得不使用 expdp，因此我建议 expdp 是将所有对象移动到不同表空间的最佳选择。
Below is the command:
下面是命令：
nohup expdp \"/ as sysdba\" DIRECTORY=test_dir DUMPFILE=users.dmp LOGFILE=users.log TABLESPACES=USERS &
You can check this linkfor details.
您可以查看此链接了解详情。
