delete

Simple SQL*Plus script for rebuild of in...

In older versions of Oracle I sometimes faced problem of corupted or too fragmented indexes. In such case you need to rebuild them. But be careful this operation can be very time consuming and no one should work in database – if possible do it in restrict mode. Here is very simple script for SQL*Plus which will do it: </pre> set define off set serveroutput on spool rebuild_dsu.log prompt Starting RE-BUILD of existing indexes BEGIN FOR INDEX_RECORD IN (select owner||'.'||object_name as obj from dba_objects where object_type = 'INDEX' and owner...
delete

Check possible duplicities in indexed co...

select C.TABLE_NAME, C.COLUMN_NAME, C.COLUMN_POSITION, COUNT(*) AS POCET from all_ind_columns c where c.table_owner = 'RMS' AND C.TABLE_NAME LIKE '.....your_table_mask....%' HAVING COUNT(*)>1 GROUP BY C.TABLE_NAME, C.COLUMN_NAME, C.COLUMN_POSITION
delete

Show foreign keys without index

Oracle 9 does not create automaticaly index for foreign key. Therefore use this select to find such cases: select /*+NO_MERGE*/c.TABLE_NAME, c.CONSTRAINT_NAME, col.COLUMN_NAME from all_constraints c, all_cons_columns col where c.owner = 'RMS' and c.constraint_name not like 'SYS_%' and c.constraint_type = 'R' and c.constraint_name = col.constraint_name and not exists (select i_col.COLUMN_NAME from all_indexes i, all_ind_columns i_col where i.table_name = c.TABLE_NAME and i.owner = c.OWNER and i_col.index_nameĀ  = i.INDEX_NAME and i_col.COLUMN_NAME = col.COLUMN_NAME) order...