Pages

Sunday, November 16, 2014

How to Create a DB Link between two Databases(ORACLE)

TNS_NAME must be created between DataBase_1(Loacl) and DataBase_2(Remote) (If already have then no need)

Conn : DataBase_1

create database link "MY_DB_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM"
connect to User_Name -- User_Name of DataBase_2
identified by Password  -- Password of DataBase_2
using 'DataBase_2';      -- TNS Name Alias or TNS Name


select *
from table_name@MY_DB_LINK

NB: The following syntax is not supported for LOBs:

SELECT lobcol FROM table1@MY_DB_LINK;

INSERT INTO lobtable
SELECT type1.lobattr
FROM table1@MY_DB_LINK;

SELECT DBMS_LOB.getlength(lobcol) FROM table1@MY_DB_LINK;

(This statement produces error: ORA-22992 cannot use LOB locators selected from remote tables.)

==========================================================================
========================================================================== 

However, the following statement is not supported:

  • CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2;
  •  Clusters cannot contain LOBs, either as key or non-key columns. This produces error, ORA-02335: invalid datatype for cluster column.
  • You cannot create a VARRAY of LOBs. This produces error, ORA-02348: cannot create VARRAY column with embedded LOB.
  • You cannot specify LOB columns in the ORDER BY clause of a query, or in the GROUP BY clause of a query or in an aggregate function. This produces error, ORA-00932: inconsistent datatypes.
  • You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement or in a query that uses the UNION or MINUS set operator if the column's object type has a MAP or ORDER function defined on it.
  • You cannot specify an NCLOB as an attribute of an object type when creating a table. However, you can specify NCLOB parameters in methods.
  • You cannot specify LOB columns in ANALYZE... COMPUTE or ANALYZE... ESTIMATE statements.
  • You cannot define an UPDATE DML trigger on a LOB column.
  • You cannot specify a LOB as a primary key column.
  • You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the function of a function-based index or in the indextype specification of a domain index. In addition, Oracle Text lets you define an index on a CLOB column.

No comments:

Post a Comment