Ora-02429 cannot drop index used for enforcement of unique/primary key

There are multiple reasons why Ora-02429 error may happen. I encountered this problem while deleting tablespace dedicated to indexes in the product we are using.

One may want to try following options:

1. Use "cascade constraints" e.g. drop tablespace [name of ts] cascade constraints;
2. check what objects are in this tablespace and move these to some where else and drop the tablespace.
3. alter index [indexname] rebuild [tablespacename]; (this is because, rowid's will change when a table is moved/re-org)
4. If you are trying to delete tablespace which is still referenced by any user; you need to make sure either users are removed (recursively) and that there are no data related to any user in there. Delete the user (Schema) with all the objects and it should free up any references.

For me the last option worked!

Helpful SQL Scripts

There may come a time for database developers to perform bulk operations e.g. compile all the stored procedures or rebuild all the indices. This might be required esp. when you are supporting a product and have to upgrade it.

I had to do this with help of oracle XE instance with only a web interface and here is the solution I developed.

Recompiling All Procedures:

begin
declare
cursor c_proc is
select object_name from user_objects where object_type = 'PROCEDURE';
v_sql varchar2(512);

begin
for c_proc_rec in c_proc LOOP
v_sql := 'ALTER PROCEDURE 'c_proc_rec.object_name' COMPILE';
execute immediate v_sql;
END LOOP;

end;
end;

Rebuilding all indexes:
This might be required after inserting thousands of rows as part of product upgrade. Here is the script that I used.

begin
begin
declare
cursor c_idx is
select index_name from user_indexes where index_type = 'NORMAL' and uniqueness = 'NONUNIQUE';
v_sql varchar2(512);

begin
for c_idx_rec in c_idx LOOP
v_sql := 'Alter index 'c_idx_rec.index_name' rebuild';

execute immediate v_sql;
END LOOP;
end;
end;

Hope this helps!

Restarting a Windows XP when connected over Remote Desktop

If you ever have to connect to a windows machine over remote desktop and have to shut it down; it might not be straight forward to shutdown / restart the remote machine.

Here is a simple work around that I could find. Please note that the remote desktop program by design disables the shutdown option when connected.

"Shutdown" is the utility and can be invoked from command prompt. If you have access to command prompt - run the following command and you should see a dialoguebox displaying countdown to shutdown.

C:\>shutdown -s

please note that if you don't pass any arguments; the command will list all available parameters that "shutdown" utility can take as arguments.

Following command will restart the machine.

C:\>shutdown -r

Hope this helps...