Finding UNUSED indexes in oracle

 First we need to start monitoring for indexes:

SQL> ALTER INDEX index_name MONITORING USAGE;
 Index altered.
 
SQL> ALTER INDEX index_name1 MONITORING USAGE;
 Index altered.

Execute the below query:

SQL> SELECT v.index_name, v.table_name,
 v.monitoring, v.used,
 start_monitoring, end_monitoring
 FROM v$object_usage v, user_indexes u
 WHERE v.index_name = u.index_name;


INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
ADD_ID_PK                      ADDRESS                        YES NO  01/26/2013 17:06:15
 

Msg 5074, Level 16, State 1, Line 1

unable to drop column

Msg 5074, Level 16, State 1, Line 1

The object ' ' is dependent on column ' '.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN   failed because one or more objects access this column.

THIS error because of you are trying to drop column , without dropping the constraint.

solution : drop constraint first then column

declare @query nvarchar(1000)
select @query = 'ALTER TABLE ' + t.name + ' drop constraint ' + d.name
from sys.tables t join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id and c.column_id = d.parent_column_id
where t.name = 'table_name' and c.name = 'column_name'
execute (@query)

  note : replace table_name and column_name with present table_name and column_name

SQL-SERVER Backup RESTORE error

when trying to restore the database i get something like this:


Restore failed for Server '\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ' ' database. (Microsoft.SqlServer.Smo)

The solution for this is simple: you need to choose "WITH REPLACE" option when restoring.

Check this option ‘Overwrite the existing database (WITH REPLACE)’.

"OK"


You might also got this error

Restore failed for Server ' \SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\scott.mdf' cannot be overwritten. It is being used by database ' '. (Microsoft.SqlServer.Smo)

to restore the database use WITH MOVE option

RESTORE DATABASE [new_dbname]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\old_dbname.bak' 
WITH FILE = 1, MOVE N'old_dbname'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\new_dbname.mdf',
MOVE N'old_dbname_log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\new_dbname.ldf',
NOUNLOAD, REPLACE, STATS = 10