Menu
How would I disable and later enable all indexes in a given schema/database in Oracle?
Note: This is to make sqlldr run faster.
920
Click the plus sign to expand the table on which you want to disable an index. Click the plus sign to expand the Indexes folder. Right-click the index you want to disable and select Disable. In the Disable Indexes dialog box, verify that the correct index is in the Indexes to disable grid and click OK.
You can use the ALTER TABLE statement to enable, disable, modify, or drop a constraint. When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise.
There are multiple ways to disable constraints in Oracle. constraint_name; Another way to enable and disable constraints in Oracle would be to either use a plsql block or write a script. Execute Immediate 'alter table '||:tab_name||' disable constraint '||tabCons(numCount);
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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
