Drop Linked Server in SQL Server

You have learned how to create linked server in SQL Server in Accessing Oracle From SQL Server. Sometimes you may want to drop it. I'll show you how.

First find and drop the logins associated with the linked server.
user master
go

select s.srvid, s.srvname, l.name login_name_associated
from dbo.sysxlogins x, dbo.syslogins l, dbo.sysservers s
where l.sid = x.sid and s.srvid = x.srvid and s.srvname = 'ora_test'
go
srvid srvname login_name_associated
----- --------- ------------------------
1 ora_test sa
1 ora_test analyst

Now sa and analyst need to be dropped from the linked server.

sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'analyst'
go
sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'sa'
go

Then the linked server is ready to be dropped.

sp_dropserver @server = 'ora_test'
go

The logins have to be dropped from linked server first, otherwise an error message will show:

There are still remote logins for the server 'ora_test'.

Oracle Flashback Query Using AS OF TIMESTAMP

Since Oracle 9i Release 2, you can use "AS OF SCN | TIMESTAMP" flashback query to query the "before" data of a changed table. It's useful to find the changes you made or recover errors.
I used TIMESTAMP more often and found it simple but efficient. I'll show some examples below.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

SQL> update employees set salary = salary * 1.05 where employee_id = 100;
1 row updated.

SQL> commit;

SQL> select salary from employees where employee_id = 100;
SALARY
----------
25200

Here shows the way to query the "before" data of yesterday:

SQL> select salary from employees AS OF TIMESTAMP sysdate - 1 where employee_id = 100;
SALARY
----------
24000

To show ten (10) minutes ago:

select salary from employees AS OF TIMESTAMP sysdate - 10/60/24 where employee_id = 100;

Or you can use function SYSTIMESTAMP instead of sysdate.

select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) where employee_id = 100;
select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) where employee_id = 100;

To recover the change:

SQL>update employees e1 set salary =
( select salary from employees AS OF TIMESTAMP sysdate - 1 e2
where e2.employee_id = e1.employee_id )
where employee_id = 100;
1 row updated.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

You may sometimes want to save the before data to a temporary table, then compare and reccover.

create table tmp_employees nologging as select * from employees AS OF TIMESTAMP sysdate - 1;

The SELECT ... AS OF will not work after the table structure changes or table truncation.

SQL> truncate table tmp_objects;
Table truncated.

SQL> select count(*) from tmp_objects as of timestamp sysdate - 1/24/60;

ORA-01466: unable to read data - table definition has changed