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.)
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