SQL 在 Oracle 中禁用并稍后启用所有表索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/129046/
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
Disable and later enable all table indexes in Oracle
提问by oneself
How would I disable and later enable all indexes in a given schema/database in Oracle?
我将如何禁用并稍后启用 Oracle 中给定架构/数据库中的所有索引？
Note: This is to make sqlldr run faster.
注意：这是为了让 sqlldr 运行得更快。
采纳答案by jmc
Here's making the indexes unusable without the file:
这是使索引在没有文件的情况下无法使用的：
DECLARE
CURSOR usr_idxs IS select * from user_indexes;
cur_idx usr_idxs% ROWTYPE;
v_sql VARCHAR2(1024);
BEGIN
OPEN usr_idxs;
LOOP
FETCH usr_idxs INTO cur_idx;
EXIT WHEN NOT usr_idxs%FOUND;
v_sql:= 'ALTER INDEX ' || cur_idx.index_name || ' UNUSABLE';
EXECUTE IMMEDIATE v_sql;
END LOOP;
CLOSE usr_idxs;
END;
The rebuild would be similiar.
重建将是相似的。
回答by jmc
combining 3 answers together: (because a select statement does not execute the DDL)
将 3 个答案组合在一起：（因为 select 语句不执行 DDL）
set pagesize 0
alter session set skip_unusable_indexes = true;
spool c:\temp\disable_indexes.sql
select 'alter index ' || u.index_name || ' unusable;' from user_indexes u;
spool off
@c:\temp\disable_indexes.sql
Do import...
做进口...
select 'alter index ' || u.index_name ||
' rebuild online;' from user_indexes u;
Note this assumes that the import is going to happen in the same (sqlplus) session.
If you are calling "imp" it will run in a separate session so you would need to use "ALTER SYSTEM" instead of "ALTER SESSION" (and remember to put the parameter back the way you found it.
请注意，这假定导入将发生在同一个 (sqlplus) 会话中。
如果您正在调用“imp”，它将在单独的会话中运行，因此您需要使用“ALTER SYSTEM”而不是“ALTER SESSION”（并记住将参数放回您找到它的方式。
回答by David Aldridge
If you are using non-parallel direct path loads then consider and benchmark not dropping the indexes at all, particularly if the indexes only cover a minority of the columns. Oracle has a mechanism for efficient maintenance of indexes on direct path loads.
如果您正在使用非并行直接路径加载，那么请考虑和基准测试根本不删除索引，特别是如果索引仅覆盖少数列。Oracle 有一种机制可以有效维护直接路径负载上的索引。
Otherwise, I'd also advise making the indexes unusable instead of dropping them. Less chance of accidentally not recreating an index.
否则，我还建议使索引不可用而不是删除它们。意外不重新创建索引的可能性较小。
回答by Jason Baker
If you're on Oracle 11g, you may also want to check out dbms_index_utl.
如果您使用的是 Oracle 11g，您可能还想查看dbms_index_utl。
回答by Dmitry Khalatov
From here: http://forums.oracle.com/forums/thread.jspa?messageID=2354075
从这里：http: //forums.oracle.com/forums/thread.jspa?messageID=2354075
alter session set skip_unusable_indexes = true;
alter session set skip_unusable_indexes = true;
alter index your_index unusable;
alter index your_index unusable;
do import...
做进口...
alter index your_index rebuild [online];
alter index your_index rebuild [online];
回答by Brian Deterling
You can disable constraints in Oracle but not indexes. There's a command to make an index ununsable but you have to rebuild the index anyway, so I'd probably just write a script to drop and rebuild the indexes. You can use the user_indexes and user_ind_columns to get all the indexes for a schema or use dbms_metadata:
您可以禁用 Oracle 中的约束，但不能禁用索引。有一个命令可以使索引不可用，但无论如何您都必须重建索引，所以我可能只是编写一个脚本来删除和重建索引。您可以使用 user_indexes 和 user_ind_columns 来获取模式的所有索引或使用 dbms_metadata：
select dbms_metadata.get_ddl('INDEX', u.index_name) from user_indexes u;
回答by oneself
Combining the two answers:
结合两个答案：
First create sql to make all index unusable:
先创建sql，使所有索引不可用：
alter session set skip_unusable_indexes = true;
select 'alter index ' || u.index_name || ' unusable;' from user_indexes u;
Do import...
做进口...
select 'alter index ' || u.index_name || ' rebuild online;' from user_indexes u;
回答by Karl Bartel
You should try sqlldr's SKIP_INDEX_MAINTENANCE parameter.
您应该尝试 sqlldr 的 SKIP_INDEX_MAINTENANCE 参数。
