How do I check what is accessing the database via db links?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How do I check what is accessing the database via db links?

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    How do I check what is accessing the database via db links?

    We have a remote database that is accessing our production database via a db link. How do I check when and what it is accessing? I have check V$SQLAREA but it didn't show any sql statements this remote database session was using. Thanks.

  2. #2
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Red face

    Hi,
    U may try
    DBA_2PC_NEIGHBORS
    DBA_2PC_PENDING

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    These views show pending distributed transactions to a remote database.

    I need access/sql information on the production database FROM a remote database. Thanks.

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    If you can somehow identify the session coming from the remote database, you can create a log on trigger, which enables tracing each time a connection from that machine gets established. Then u can see the SQLs in the trace file

    Regards

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Remote sessions are connecting to your database using a specific Oracle account -just check DBLink creation script-

    Is that enough of a clue?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    This specific session is using the same username as the apps.

    I have asked this question as we had a problem this morning. The application has a message table, and when these messages are processed, the msg_processed column gets updated to 'yes'. We were having a problem with the package that processes these messages, therefore we dropped the package, but the messages were still getting processed (i.e updated). I monitored active sql but nothing was running on the database, therefore we couldn't work out who or where these messages were being processed.

    It came down to a developer who 'owned up' in the end that he had a session on the 'test' database that was accessing the 'live' database message table via a dblink (for testing purposes).

    Therefore, how can you see sql statement from remote session via db links if they don't show up in v$sqlarea?

  7. #7
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    My dear friend : see, when a database runs one SQL (acessing db links or not), FOR SURE this SQl is parsed (compiled), an execution plan is mounted, the plan is executed, the SQL is registered in the SQL cache, for sure... What happens is, all that is done IN THE DATABASE where the SQLs runs, example :

    system@BD_DEV:SQL>select * from dba_db_links;

    OWNER DB_LINK USERNAME HOST CREATED
    ------ ------------------ -------- ------- -------------------
    SYSTEM FCO_BD_DEV_BD_PROD FCO bd_prod 07/11/2006 11:35:01

    ==> let´s use it :

    system@BD_DEV:SQL>select /*+ SQL_USING_DB_LINK */ * from SIA_SITES@FCO_BD_DEV_BD_PROD where rownum < 10;

    SITE_ID SITE SITE_DESCRI
    ------------ ---------- --------------------
    93633 11001 ADAMANTINA
    93634 11002 ADOLFO
    93635 11003 AGUA VERMELHA
    93636 11004 AGUAI
    93637 11005 AGUAS DA PRATA
    93638 11006 AGUAS DE LINDOIA
    93639 11007 AGUAS DE SAO PEDRO
    93640 11008 AGUDOS
    93641 11009 AJAPI

    9 linhas selecionadas.

    ==> don´t pay attention to the table and the data and sql*plus messages in another language, it´s only an example. See the SQL, it IS ** in this db ** :

    system@BD_DEV:SQL>select sql_text, sqltype, remote, command_type, module, action from v$sql where sql_text like 'select /*+ SQL_USING_DB_LINK */%';

    SQL_TEXT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQLTYPE R COMMAND_TYPE MODULE ACTION
    ------------------ - ------------------ ---------------------------------------------------------------- ----------------------------------------------------------------
    select /*+ SQL_USING_DB_LINK */ * from SIA_SITES@FCO_BD_DEV_BD_PROD where rownum < 10
    6 Y 3 SQL*Plus

    ===>> OF COURSE, this exact SQL does NOT exist in the remote db :

    e069356@BD_PROD:SQL>select sql_text, sqltype, remote, command_type, module, action from v$sql where sql_text like 'select /*+ SQL_USING_DB_LINK */%';

    não há linhas selecionadas

    ==> BUT, of course, when the origin db need data from the remote db, the data is PULLED via SQL, only the text is different from the original SQL. Always thinking in 9i databases (my version, afaik tehere is NO WAY to "see" if a SQL was send by a "local" or a "remote" source, BUT the dblink is a foreign session, you CAN find the session opened by the db link, and then find the SQLs fired by this session. See in my case :

    e069356@BD_PROD:SQL>select
    username,osuser,status,sid,serial#,machine,process,terminal,program
    from v$session
    where saddr in ( select k2gtdses from sys.x_$k2gte );

    USERNAME OSUSER STATUS SID SERIAL# MACHINE PROCESS TERMINAL PROGRAM
    -------- --------- -------- ---- ------- ------- ------- -------- -------------------------
    FCO C18277167 INACTIVE 140 34520 bd_dev 7962 oracle@bd_dev (TNS V1-V3)

    ==> oh yeah, sid 140 is the session opened by my db link, let´s see their SQLs :

    e069356@BD_PROD:SQL>Select A.User_Name, B.Disk_Reads, B.Buffer_Gets,
    2 B.Rows_Processed, C.SQL_Text
    3 From V$Open_Cursor A, V$SQLArea B, V$SQLText C
    4 Where A.SID = 140
    5 And A.Address = C.Address
    6 And A.Address = B.Address
    7 Order By A.User_Name, A.Address, C.Piece;

    USER_NAME DISK_READS BUFFER_GETS ROWS_PROCESSED SQL_TEXT
    ------------------------------ ------------------ ------------------ ------------------ ----------------------------------------------------------------
    FCO 1 5 9 SELECT /*+ */ "A1"."SITE_ID","A1"."SITE","A1"."MUNICIP
    FCO 1 5 9 IO_DESCRI","A1"."FECHA_ALTA","A1"."FECHA_ULT_MOD","A1"."USUARIO_
    FCO 1 5 9 ALTA","A1"."USUARIO_ULT_MOD","A1"."PROVINCIA","A1"."REGIAO","A1"
    FCO 1 5 9 ."SETOR" FROM "SIA_SITES" "A1" WHERE ROWNUM<10

    we found the SQLs remotely originated in the dest db, AND (of course) in the ORIGIN db the original SQL ** IS PRESENT **, yes ??

    Regards,

    Chiappa

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The host from v$session will be the hostname of the remote server.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    yeah, *** BUT *** this is true even with a normal connection (no db link) from the other machine, see :

    DEVEL!!fco:bd_dev:/traffic/fco>hostname
    bd_dev

    DEVEL!!fco:bd_dev:/traffic/fco>sqlplus fco@bd_prod

    SQL*Plus: Release 9.2.0.5.0 - Production on Qui Jul 5 14:37:10 2007

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Enter password:

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning option
    JServer Release 9.2.0.5.0 - Production

    SQL>


    ==> in the dest db :

    e069356@BD_PROD:SQL>select * from v$session where machine='bd_dev';

    SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE TERMINAL PROGRAM TYPE SQL_ADDRESS SQL_HASH_VALUE PREV_SQL_ADDR PREV_HASH_VALUE MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER
    ---------------- ---- ------- -------- ---------------- ----- ---------------- ------------------ ------------------ ---------------- ---------------- -------- --------- ------------------ ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------- ---------------- ------------------ ---------------- ------------------ ------------------------------------------------ ------------------ -------------------------------- ------------------ ---------------------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------ ------------------- ------------------ --- ------------- ---------- --- -------------------------------- -------- -------- -------- ---------------------- ----------------------------------------------------------------
    C0000000F727C1C0 49 39892 1617823 C0000000FA27ADF8 66 FCO 0 2147483644 INACTIVE DEDICATED 66 FCO fco 16566 bd_dev pts/tc sqlplus@bd_dev (TNS V1-V3) USER C000000117EC6210 4282642546 C000000117EC6210 4282642546 SQL*Plus 3669949024 4029777240 521238 -1 0 0 0 05/07/2007 14:37:17 74 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0

    ==> yes, there IS a session, but NOT from a db link :

    e069356@BD_PROD:SQL>select
    2 username,osuser,status,sid,serial#,machine,process,terminal,program
    3 from v$session
    4 where saddr in ( select k2gtdses from sys.x_$k2gte );

    não há linhas selecionadas (no rows selected)

    e069356@BD_PROD:SQL>

    []s

    Chiappa

  10. #10
    Join Date
    May 2013
    Posts
    1

    try this (enable dbms_output first)

    declare
    type linkobj is record (owner varchar2(30), linkname varchar2(128));
    type linkstab is table of linkobj;
    type rstab is table of dba_dependencies%rowtype;
    rs rstab := rstab();
    links linkstab;
    v_clob clob;
    begin
    select owner,db_link bulk collect into links from dba_db_links;
    select * bulk collect into rs from
    (select * from dba_dependencies where referenced_link_name is not null
    union
    select owner, synonym_name, 'SYNONYM', table_owner, table_name, 'TABLE', db_link, null
    from dba_synonyms where db_link is not null
    union
    select owner, mview_name, 'MVIEW', null, null, null, ltrim(master_link,'@'), null
    from dba_mviews where master_link is not null
    );
    begin
    for c in (select owner, view_name, text from dba_views)
    loop
    v_clob := upper(to_clob(c.text));
    for i in links.first..links.last
    loop
    if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.owner = links(i).owner
    then
    rs.extend;
    rs(rs.count).owner := c.owner;
    rs(rs.count).name := c.view_name;
    rs(rs.count).type := 'VIEW';
    rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
    end if;
    end loop;
    end loop;
    exception when others then null;
    end;
    begin
    for c in (select owner, trigger_name, trigger_body from dba_triggers)
    loop
    v_clob := upper(to_clob(c.trigger_body));
    for i in links.first..links.last
    loop
    if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.owner = links(i).owner
    then
    rs.extend;
    rs(rs.count).owner := c.owner;
    rs(rs.count).name := c.trigger_name;
    rs(rs.count).type := 'TRIGGER';
    rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
    end if;
    end loop;
    end loop;
    exception when others then null;
    end;
    begin
    for c in (select schema_user, job, what from dba_jobs)
    loop
    v_clob := upper(to_clob(c.what));
    for i in links.first..links.last
    loop
    if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.schema_user = links(i).owner
    then
    rs.extend;
    rs(rs.count).owner := c.schema_user;
    rs(rs.count).name := c.job;
    rs(rs.count).type := 'JOB';
    rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
    end if;
    end loop;
    end loop;
    exception when others then null;
    end;
    begin
    for c in (select owner, job_name, job_action from dba_scheduler_jobs)
    loop
    v_clob := upper(to_clob(c.job_action));
    for i in links.first..links.last
    loop
    if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.owner = links(i).owner
    then
    rs.extend;
    rs(rs.count).owner := c.owner;
    rs(rs.count).name := c.job_name;
    rs(rs.count).type := 'SCHJOB';
    rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
    end if;
    end loop;
    end loop;
    exception when others then null;
    end;
    dbms_output.put_line('owner,oname,otype,rowner,rname,rtype,dblink');
    for i in rs.first..rs.last
    loop
    dbms_output.put_line
    (
    rs(i).owner||','||
    rs(i).name||','||
    rs(i).type||','||
    rs(i).referenced_owner||','||
    rs(i).referenced_name||','||
    rs(i).referenced_type||','||
    rs(i).referenced_link_name
    );
    end loop;
    end;
    /

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