Disable and Later Enable All Table Indexes in Oracle

Disable and later enable all table indexes in Oracle

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.

What is the difference between Disabling and Re-enabling an index compared to Dropping and Re-creating it?

We cannot disable an index, unless it is a function-based index.

SQL> alter index i42 disable;
alter index i42 disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL>

We can make it unusable but that has a rather unfortunate side-effect:

SQL> alter index i42 unusable;

Index altered.

SQL> insert into t42 values (sysdate, 6, 'FOX IN SOCKS');
insert into t42 values (sysdate, 6, 'FOX IN SOCKS')
*
ERROR at line 1:
ORA-01502: index 'APC.I42' or partition of such index is in unusable state

SQL> 

Just to prove the point about function-based indexes:

SQL> create index f42 on t42(initcap(name))
  2  /

Index created.

SQL> alter index f42 disable;

Index altered.

SQL> insert into t42 values (sysdate, 6, 'MR KNOX')
  2  /
insert into t42 values (sysdate, 6, 'MR KNOX')
*
ERROR at line 1:
ORA-30554: function-based index APC.F42 is disabled

SQL> 

I think that rules out disabling indexes for your purposes. If you want to work on a table without indexes you need to drop them. Whether that makes sense depends on the specifics of your case, as I said in my answer to your previous question ....

---

As for constraints, we cannot drop an index which is used to enforce a unique constraint. However, we can drop a constraint but leave the index in place. In that case, if the index is a unique one, it will still enforce integrity:

SQL> create unique index i42 on t42(id);

Index created.

SQL> alter table t42 add constraint t42_pk primary key (id);

Table altered.

SQL> insert into t42 values (sysdate, 5, 'MAISIE');

1 row created.

SQL> r
  1* insert into t42 values (sysdate, 5, 'MAISIE')
insert into t42 values (sysdate, 5)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T42_PK) violated

SQL> alter table t42 drop constraint t42_pk;

Table altered.

SQL> insert into t42 values (sysdate, 5, 'MAISIE');
insert into t42 values (sysdate, 5, 'MAISIE')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.I42) violated

SQL> 

Disable and re-enable all indexes in a SQL Server database

Here is a script that will output ALTER statements for all non clustered indexes in your database. You can modify this easily to output REBUILD scripts and scripts for clustered indexes

select 'ALTER INDEX [' + I.name + '] ON [' + T.name + '] DISABLE' 
from sys.indexes I
inner join sys.tables T on I.object_id = T.object_id
where I.type_desc = 'NONCLUSTERED'
and I.name is not null

How to see the status of all indexes; enabled/disabled in Oracle database?

Seems as if you didn't look close enough.

SQL> select index_name, status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_EMP                         VALID
PK_DEPT                        VALID
<snip>

---

As of "disabled" indexes - generally speaking, you can't do that:

SQL> alter index pk_dept disable;
alter index pk_dept disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

What you can do is to make it unusable:

SQL> alter index pk_dept unusable;

Index altered.

SQL> select index_name, status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        UNUSABLE
<snip>

To make it "usable" again, you have to rebuild it:

SQL> alter index pk_dept rebuild;

Index altered.

SQL> select index_name, status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        VALID
<snip>

---

Non-generally speaking, you can disable function-based index:

SQL> create index myind on emp (to_char(hiredate, 'yyyymm'));

Index created.

SQL> alter index myind disable;

Index altered.

SQL>

Disable all table constraints in Oracle

It is better to avoid writing out temporary spool files. Use a PL/SQL block. You can run this from SQL*Plus or put this thing into a package or procedure. The join to USER_TABLES is there to avoid view constraints.

It's unlikely that you really want to disable all constraints (including NOT NULL, primary keys, etc). You should think about putting constraint_type in the WHERE clause.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

Enabling the constraints again is a bit tricker - you need to enable primary key constraints before you can reference them in a foreign key constraint. This can be done using an ORDER BY on constraint_type. 'P' = primary key, 'R' = foreign key.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
  END LOOP;
END;
/

---

Related Topics