select status from all_objects where object_name='OBJECTNAME' and object_type='OBJECTTYPE'提示: 未选定行dblink以创建并测试通过,现在本机不在局域网上,是否有关系? 以前的做法是 select count(*) from test@remote1 ,如果远程网络断开,则显示 tns超时,但这种方法很笨,并很慢,请各位大侠指教!
SQL> select distinct object_type from dba_objects;OBJECT_TYPE ------------------------------ CLUSTER DATABASE LINK FUNCTION INDEX LIBRARY LOB PACKAGE PACKAGE BODY PROCEDURE SEQUENCE SYNONYMOBJECT_TYPE ------------------------------ TABLE TYPE VIEW14 rows selected.SQL> select status from dba_objects where object_type='DATABASE LINK';STATUS -------------- VALID
怎样测试 dblink "remote1" 是否 valid 并且 Active? 另外,我的机器未联网,但连到其他机器上的dblink也是 select status from dba_objects where object_type='DATABASE LINK';STATUS ------- VALID VALID
Another way is to check v$sessions view v$sessions view 在哪里可以找到? 我的目的是如果网络不通,我就停止对远端数据库进行DML操作!
字段status只是代表这个对象是可用的。并不代表他是否正在被使用。 不知道我理解你的意思对否。
网络不通和DBLINK是否处于活动(active)状态是两回事。begin select count(*) from tab@db_link where rownum< 2; exception when others then ........ end;
v$session时数据字典里的一个视图是属于sys用户的。SQL> desc v$session Name Null? Type ----------------------------------------- -------- ------------------- SADDR RAW(8) SID NUMBER SERIAL# NUMBER AUDSID NUMBER PADDR RAW(8) USER# NUMBER USERNAME VARCHAR2(30) COMMAND NUMBER OWNERID NUMBER TADDR VARCHAR2(16) LOCKWAIT VARCHAR2(16) STATUS VARCHAR2(8) SERVER VARCHAR2(9) SCHEMA# NUMBER SCHEMANAME VARCHAR2(30) OSUSER VARCHAR2(15) PROCESS VARCHAR2(9) MACHINE VARCHAR2(64) TERMINAL VARCHAR2(10) PROGRAM VARCHAR2(48) TYPE VARCHAR2(10) SQL_ADDRESS RAW(8) SQL_HASH_VALUE NUMBER PREV_SQL_ADDR RAW(8) PREV_HASH_VALUE NUMBER MODULE VARCHAR2(48) MODULE_HASH NUMBER ACTION VARCHAR2(32) ACTION_HASH NUMBER CLIENT_INFO VARCHAR2(64) FIXED_TABLE_SEQUENCE NUMBER ROW_WAIT_OBJ# NUMBER ROW_WAIT_FILE# NUMBER ROW_WAIT_BLOCK# NUMBER ROW_WAIT_ROW# NUMBER LOGON_TIME DATE LAST_CALL_ET NUMBER PDML_ENABLED VARCHAR2(3) FAILOVER_TYPE VARCHAR2(13) FAILOVER_METHOD VARCHAR2(10) FAILED_OVER VARCHAR2(3)
未选定行dblink以创建并测试通过,现在本机不在局域网上,是否有关系?
以前的做法是 select count(*) from test@remote1 ,如果远程网络断开,则显示 tns超时,但这种方法很笨,并很慢,请各位大侠指教!
------------------------------
CLUSTER
DATABASE LINK
FUNCTION
INDEX
LIBRARY
LOB
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYMOBJECT_TYPE
------------------------------
TABLE
TYPE
VIEW14 rows selected.SQL> select status from dba_objects where object_type='DATABASE LINK';STATUS
--------------
VALID
另外,我的机器未联网,但连到其他机器上的dblink也是
select status from dba_objects where object_type='DATABASE LINK';STATUS
-------
VALID
VALID
v$sessions view 在哪里可以找到?
我的目的是如果网络不通,我就停止对远端数据库进行DML操作!
不知道我理解你的意思对否。
select count(*) from tab@db_link where rownum< 2;
exception when others then
........
end;
Name Null? Type
----------------------------------------- -------- -------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(15)
PROCESS VARCHAR2(9)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(10)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
但如果用 select count(*) from test@dblink,则速度很慢!
怎样得到返回值?