oracle dblink change sequence script ,DML, Descricption trable

ORACLE DATABASE LINK(DBLINK) CREATE METHOD

• FIXED USER EXAMPL
• PUBLIC EXAMPLE
• DBLINK CREATE COMMAND

  CURRENT USER DBLINK CREATE COMMAND:

sql>  CREATE DATABASE LINK MYLINLK

   CONNECT TO HR

   USING 'EMPLOYEE';

FIXED USER BDLINK CREATE COMMAND

The following statement defines a fixed-user database link named MYLINK:

CREATE DATABASE LINK MYLINK

   CONNECT TO scott IDENTIFIED BY tiger

   USING 'EMPLOYEE';

Once this database link is created, you can query tables in the schema SCOTT on the remote database in this manner:

SELECT *

   FROM emp@MYLINK;

 With mylink any DML sql  to modify data on the remote database:

INSERT INTO emp@mylink(emp_no,emp_id,emp_name )

   VALUES (1, 12000,’test’);

UPDATE emp @ mylink

   SET emp_name = ‘niger’;

DELETE FROM emp@mylink

   WHERE emp_name = 'niger';

How to get sequence.nextval through  oracle database link

select schema.s_test_seq.nextval@remote from dual;

   NEXTVAL

----------

         1

DESCRIBING OBJECT WITH DBLINK
DESC S_PM_EDU_QUALIFICATIONS@MYLINK
DESC TABLE/SEQUENCE/TRIGGER/ETC@MYLINK

HOW TO ALTER/CHANGE SEQUENCE SCRIPT/INCREAMENT VALUE CHANGE FOR SQEUENCE WITH ORACLE DBLINK :
Following command is show remote sequence nextval:

  SELECT S_PM_EDU_QUALIFICATIONS.nextval@MYLINK source_seq,

  S_PM_EDU_QUALIFICATIONS.nextval S_PM_EDU_QUALIFICATIONS

FROM dual

--------------------------------------

CREATE OR REPLACE

PROCEDURE reset_sequence

AS

  l_source_sequence pls_integer;

  l_target_sequence pls_integer;

  l_sql VARCHAR2(100);

BEGIN

  SELECT S_PM_EDU_QUALIFICATIONS.nextval@MYLINK,

    PMIS.S_PM_EDU_QUALIFICATIONS.nextval

  INTO l_source_sequence,

    l_target_sequence

  FROM dual;

  l_sql :='alter sequence S_PM_EDU_QUALIFICATIONS  START WITH 368'||to_number(l_source_sequence-l_target_sequence);

  EXECUTE immediate l_sql;

  COMMIT;

END reset_sequence;

/

Sequence is modifies on remote database.

You can also access tables owned by other users on the same database. This statement assumes SCOTT has access to department table:

SELECT *

   FROM pmis.department@mylink;

The previous statement connects to the user SCOTT on the remote database and then queries pmis’s DEPARTMENT table.
YOU MAY CREATE synonym to hide the fact that SCOTT's EMP table is on a remote database. Below statement causes all future references to EMP to access a remote EMP table owned by SCOTT:

CREATE SYNONYM emp

   FOR scott.emp@mylink;

PUBLIC Example

The following statement defines a shared public fixed user dblink named ‘mylink’
that refers to user SCOTT with password TIGER on the database specified by the string service name 'employee': 

CREATE SHARED PUBLIC DATABASE LINK mylink

   CONNECT TO scott IDENTIFIED BY tiger

   AUTHENTICATED BY niger IDENTIFIED BY 123

   USING 'employee';

Use of the CREATE DATABASE LINK Command

NB: All of the following examples will use DECNET and TCP/IP format.

Let's assume we have two instances, the local instance and an instance in Newyork called New2, and there are communicating via TCP/IP. The node address in Newyork is 170.222.00.12 and the account we will connect to is LIMITED_USER with a password of NONE.

SQL> CREATE PUBLIC DATABASE LINK USA

                    CONNECT TO LIMITED_USER IDENTIFIED BY NONE

                    USING 'N: 170.222.00.12: NEW2';

USA db link IS CREATE, now access tables that the LIMITED_USER has privileges on, for example, the country table. To get information from the table we could now issue the command:

SQL> SELECT * FROM country@usa;

If you don't want the users to know they are accessing a table in the remote database, you can hide this object by synonym:

SQL> CREATE PUBLIC SYNONYM country FOR country@usa;

You may read from synonym:

SQL> SELECT * FROM country;

You can access views, tables, clusters and sequences via db links and connect strings.

Example VMS DATABASE LINK

Unless network proxies are used under DECNET for VMS, must specify a username and password for  connection string. The connect string format is:

NODE "username password " : : " TASK=ORDNsid"

The TASK variable is actually a command procedure located in either the ORA_NETCONFIG directory or in the user's top level directory. Some sites set up a non-privileged, non-interactive account to connect through, others use net proxies. What your site uses will depend on your site security policy and the policies of your system administrator. 

The entire connect string can be loaded into a logical symbol:

$define/nolog usa   usa1 " " " sqlnet sqlnet " " " : : " " " TASK=ORDNTEST1 " " "

The triple sets of " allow a single set of quotes to remain in the logical symbol. The command to create the DB LINK now becomes:

SQL> CREATE PUBLIC DATABASE LINK USA

CONNECT TO LIMITED_USER IDENTIFIED BY NONE

USING 'D:USA';

If you are using DATABROWSER some versions will not allow the use of logicals and the entire connect string definition will have to be loaded into the db link.