How to close database link
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to close database link

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Unhappy

    HI,
    Is it possible to close database link?
    Thanks

  2. #2
    Join Date
    Feb 2001
    Posts
    75

    Close DB Link

    Hi,

    I think it opens with username/passwd & service name implicitely and closes after statement execution is over automatically.

    Correct me if I am wrong.

    Kailash Pareek

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Re: Close DB Link

    As I can see it doesn't close automaticaly and I get error:
    (That is package, which takes max values of sequence from 2 servers, if one go down sequence must be taken from another one server)
    SQL> select log_id.next$val from dual;
    NEXT$VAL
    ---------
    30
    SQL> /
    NEXT$VAL
    ---------
    31
    SQL> /
    select log_id.next$val from dual
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    SQL> /
    select log_id.next$val from dual
    *
    ERROR at line 1:
    ORA-01041: internal error. hostdef extension doesn't exist
    +++++++++++++++++++++++++++++++
    ONLY when I reconnect everything goes well:
    SQL> select log_id.next$val from dual;
    NEXT$VAL
    ---------
    32

    [Edited by kgb on 07-13-2001 at 12:43 PM]

  4. #4
    Join Date
    Feb 2001
    Posts
    75

    close DB link

    Hi,

    Are you connecting as username/passwd@service? If you are then, use disconnect.

    However if you are using some thing like

    select x.nextval from dual@dblink then I think it will automatically close the link. You may still get the next sequence number if it is cached.

    Kailash Pareek

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Good idea!
    Thank you!
    I will try

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    No, that is wrong, it doen't work.
    As I need that:

    CREATE OR REPLACE PACKAGE log_id AS
    FUNCTION next$val RETURN NUMBER;
    END log_id;
    -------------------------------------------------
    CREATE OR REPLACE PACKAGE BODY log_id AS
    function next$val return number is
    first number := 0;
    second number := 0;
    gap number;
    i number;
    begin
    BEGIN -- block1
    select log_id_seq.nextval@to_rcat into first from dual;
    EXCEPTION WHEN OTHERS THEN --NOT_LOGGED_ON
    BEGIN -- block2
    IF first=0 THEN begin -- block3
    select log_id_seq.nextval into second from dual;
    return second;
    end; -- block3
    END IF;
    END; -- block2
    END; -- block1

    BEGIN -- block4
    select log_id_seq.nextval into second from dual;
    EXCEPTION WHEN OTHERS THEN
    BEGIN -- block5
    IF second=0 THEN begin -- block6
    select log_id_seq.nextval@to_rcat into first from dual;
    return first;
    end; -- block6
    END IF;
    END; -- block5
    END; -- block4

    IF first > second THEN BEGIN -- block7
    gap:=first-second;
    FOR i IN 1..gap LOOP
    select log_id_seq.nextval into second from dual;
    END LOOP;
    return first;
    EXCEPTION WHEN OTHERS THEN NULL;
    END; -- block7
    END IF;

    IF second > first THEN BEGIN -- block8
    gap:=second-first;
    FOR i IN 1..gap LOOP
    select log_id_seq.nextval@to_rcat into second from dual;
    END LOOP;
    return second;
    EXCEPTION WHEN OTHERS THEN NULL;
    END; -- block9
    END IF;

    IF first = second THEN
    return first;
    END IF;

    EXCEPTION WHEN OTHERS THEN BEGIN
    RAISE_APPLICATION_ERROR(-22222,'Both Servers were DOWNED! This message must not be displayed never!');
    Return 0;
    END;
    END next$val;
    END log_id;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width