How to disable all unique index and constraints in Oracle

bulkcopyindexoracleunique-constraint

I have been trying to do OracleBulkCopy on .NET, however, Oracle throws me errors. What specifically does OracleBulkCopy do, and how can I optimize its performance? (This was the post helped me alot) However, I do not know how to disable all unique index and constraints in the entire database.

Thanks in advance.

Best Answer

Community Wiki answer generated from question comments by Justin Cave

You can use user_indexes and user_constraints (or all_indexes and all_constraints or dba_indexes and dba_constraints) to iterate through all the indexes and constraints that you own/ have access to/ exist in the database.

I can't imagine that you'd really want to try to disable every constraint in the database. Maybe all those in your schema. More likely, you want to disable those that are on a particular set of tables. As you iterate through them, you could construct the appropriate DDL statement to do whatever you want.

You can't disable an index (well, other than a function-based index). You could make the index unusable but that's unlikely to be what you want. You can drop the index but that presumably implies that you're going to save off the definition of the index so you can re-create it.

Related Question