-
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.
-
Hi,
U may try
DBA_2PC_NEIGHBORS
DBA_2PC_PENDING
-
These views show pending distributed transactions to a remote database.
I need access/sql information on the production database FROM a remote database. Thanks.
-
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
-
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.
-
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?
-
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
-
The host from v$session will be the hostname of the remote server.
Jeff Hunter
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|