Linking and Accessing files among two databases on Oracle, DB2 and MSSQL

Lets assume we have two databases as 'FIRST', 'SECOND' with 'first' and 'second' are the schema's for each databases and we have a table 'SECFILE' IN 'SECOND' database. So to access the 'SECFILE' from FIRST database we follow the below steps.

Oracle :

From First database (FIRST): Create database link from first database to connect to second database (SECOND)

SQL> CREATE PUBLIC DATABASE LINK ORALINK CONNECT TO second identified by second using 'SECOND';
Database link created.

From First database : Create the synonym for the table in second database as below.

SQL>  CREATE SYNONYM TESTF FOR SECFILE@ORALINK;
Synonym created.

Now we can access the table 'SECFILE' in 'SECOND' database from 'FIRST' database by referring the synonym name like

From First database:

desc TESTF;
select * from TESTF;


DB2:

Same way we can do in DB2 as below. Assuming same database names and tables,

From 'FIRST' database:

db2 => CREATE WRAPPER drda
DB20000I  The SQL command completed successfully.
db2 => CREATE SERVER DB2LINK TYPE DB2/UDB VERSION 10.1 WRAPPER DRDA AUTHORIZATION "second" PASSWORD "second123" OPTIONS (ADD NODE 'SECOND_N', ADD DBNAME 'SECOND')
DB20000I  The SQL command completed successfully.
db2 => CREATE USER MAPPING FOR SECOND SERVER DB2LINK OPTIONS (REMOTE_AUTHID 'second', REMOTE_PASSWORD 'second123')
DB20000I  The SQL command completed successfully.
db2 => CREATE NICKNAME TESTF FOR DB2LINK.second.SECFILE
DB20000I  The SQL command completed successfully.
db2 =>

From First database:

describe table TESTF;
select * from TESTF;


MSSQL :

Create a synonym for the table in 'SECOND' database as below.

CREATE SYNONYM TESTF FOR SECOND.second.SECFILE

Additionally need to provide privileges to 'first' of 'FIRST' database to access 'SECOND' database and back and fro.

From First database:

select * from dbo.TESTF

Reclaim unused space in oracle datafiles

After dropping large number of tables or users the space occupied by tables/users will not be released in oracle.
As the allocation happened for the first time the space is occupied and it may be required in future, thus the space occupied remain reserved for future requirements.
The space occupied by data-files remain reserved unless an explicit way to resize has been performed.
One of the ways to reduce the data-file size is to know the free-size of the data-files, so as to reduce the data-file size based on the free-size.

SQL> select a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize from dba_data_files a, (select file_id,round(sum(bytes/1024/1024)) freesize from dba_free_space group by file_id) b where a.file_id=b.file_id(+);
FILE_NAME
--------------------------------------------------------------------------------
 TOTALSIZE   FREESIZE
---------- ----------
/oracle/dbs/DBORA/system01.dbf
       800          1
/oracle/dbs/DBORA/DBORAINDEX.dbf
      6274       3283
/oracle/dbs/DBORA/sysaux01.dbf
       550         36

From the above result the freesize of the datafiles for the database are listed. The free-size is listed in MB, relating with that the data-file sizes can be resized as like below.

SQL> alter  database datafile '/oracle/dbs/DBORA/DBORAINDEX.dbf' RESIZE 5G;
Database altered.
SQL> select a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize from dba_data_files a, (select file_id,round(sum(bytes/1
024/1024)) freesize from dba_free_space group by file_id) b where a.file_id=b.file_id(+);
FILE_NAME
--------------------------------------------------------------------------------
 TOTALSIZE   FREESIZE
---------- ----------
/oracle/dbs/DBORA/system01.dbf
       800          1
/oracle/dbs/DBORA/DBORAINDEX.dbf
      5120       2129
/oracle/dbs/DBORA/sysaux01.dbf
       550         36

Oracle tables starts with BIN$

DROP table puts the tables in recycle bin, and it can be removed from recycle bin by using PURGE command.
The tables in recycle bin can be viewed by 'show recyclebin';

SQL> DROP TABLE RECYCLE;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
RECYCLE          BIN$FGO4f+kYTnW0fc0rTXSvHQ==$0 TABLE        2014-09-12:19:11:48
SQL>
SQL> PURGE TABLE RECYCLE
  2  ;
Table purged.
SQL> show recyclebin;
SQL>

Detailed description can be found here

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#ADMIN01511

org.openqa.selenium.remote.SessionNotFoundException: Unexpected error launching Internet Explorer. Protected Mode settings are not the same for all zones. Enable Protected Mode must be set to the same value (enabled or disabled) for all zones. (WARNING: The server did not provide any stacktrace information)

[TestNG] Running:
  C:\Users\tester\AppData\Local\Temp\testng-eclipse-520368244\testng-customsuite.xml
Started InternetExplorerDriver server (32-bit)
2.42.0.0
Listening on port 30903
FAILED CONFIGURATION: @BeforeMethod beforeMethod
org.openqa.selenium.remote.SessionNotFoundException: Unexpected error launching Internet Explorer. Protected Mode settings are not the same for all zones. Enable Protected Mode must be set to the same value (enabled or disabled) for all zones. (WARNING: The server did not provide any stacktrace information)
Command duration or timeout: 1.69 seconds
Build info: version: '2.43.1', revision: '5163bce', time: '2014-06-10 19:27:58'

Driver info: org.openqa.selenium.ie.InternetExplorerDriver
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
.
.
.

The protected mode setting in internet explorer must be enabled for all zones listed under the security tab on Internet Options menu.
Correcting it to have the setting enables would solve the above reported issue.

ORA-01119: error in creating database file

After dropping tablespace with including contents, may end up with "ORA-01119: error in creating database file".

Reasons could be
1. Not deleting the datafile with "INCLUDING CONTENTS AND DATAFILES"
2. Provided directory path does not exist
3. Error accessing the directory (read/write permission or misspelling of directory names)
4. Datafile already existing in the current directory

Here is the case with not deleting datafile with drop tablespace,

SQL> DROP TABLESPACE DATATS INCLUDING CONTENTS;
Tablespace dropped.

SQL> CREATE TABLESPACE DATATS LOGGING DATAFILE '/home/database/DATATS1.dbf' SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01119: error in creating database file
'/home/database/DATATS1.dbf'
ORA-27038: created file already exists
Additional information: 1

SQL> DROP TABLESPACE DATATS INCLUDING CONTENTS and datafiles;
DROP TABLESPACE DATATS INCLUDING CONTENTS and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'DATATS' does not exist

$ ls -lrt
total 63169492
-rw-r----- 1 oracle dbs   359723476 Oct  6 16:59 DATATS1.dbf
-rw-r----- 1 oracle dbs   863962592 Oct  6 23:03 temp01.dbf
-rw-r----- 1 oracle dbs   831239922 Oct  7 10:24 system01.dbf
-rw-r----- 1 oracle dbs   513430411 Oct  7 10:24 sysaux01.dbf
-rw-r----- 1 oracle dbs   108758112 Oct  7 10:24 redo03a.log
-rw-r----- 1 oracle dbs   104858342 Oct  7 10:24 redo02a.log
-rw-r----- 1 oracle dbs   222677423 Oct  7 10:24 INDEXTS1.dbf
.
.
.

$ rm -r DATATS1.dbf

SQL> CREATE TABLESPACE DATATS LOGGING DATAFILE '/home/database/DATATS1.dbf' SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.