File System re-organization: Oracle Internal Error

File System re-organization: Targeted: Internal Error 600 (For Below Post)

Oracle Internal Error:

As a Oracle database administrator, we must keep the file system perfect, to make accessible for end users. Well this augments my issue for the internal error which kept me on toe. So I started to work on this to try with this and almost absolute and reasonable.

Unused columns (Why disorganized & what creates them to drop):- Refer my previous post

SQL> Alter table set unused column
SQL>Alter table drop unused columns
Before SQL Statement sets the columns and the next statement drops the columns, but make sure about the constraints on those columns – well why don’t we use cascade option to do this.
SQL>Alter table drop unused column/columns cascade constraints
And one more important thing must me noted - when you are doing these things, the table is not available to the users.

To avoid this, use continue option

SQL>Alter table drop unused column/columns continue

***And one more thing is to note that when you drop the columns the table will be marked as invalid in general. Because it may have reference to other objects like stored procedures, triggers etc. So, it marks as invalid.

So, if use the CONTINUE option for the drop columns, (even during the dropping before completion, if instance crashes, the table will be marked as invalid in general. But when you use the continue option, u will be get rid of this problem and marked as valid) then the table will be marked as valid upon completion.

You can try this to monitor index usage – may be it will help you
SQL>Alter index index_name monitoring usage;

And to stop this – why to stop – well it causes performance degradation
SQL>Alter index index_name nomonitoring usage;

The check with the view
SQL>Select * from v$object_usage

Well its not an end – I need to check all the Index usage, the unused columns etc etc lots of stuff.

Oracle 9.2 on Microsoft Windows 2003 (32 Bit OS)