Is there any way to control the timeout before connecting via dblink. the idea is to test the dblink before calling functions over it, the problem it when i try 'select 1 from dual@dblink' in case if the remote server is down it takes too long time 5-7 min. which causing hang up the session,
So is there any parameter to reduce the waiting time say after 3 sec stop and return the timeout exception.I try the SQLNET.INBOUND_CONNECT_TIMEOUT parameter but not working, also try the tnsping but it alse take long time when the server down.
is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.
Oracle 11g on linuxdestination : ms sql server 2005 i need to create the db link from oracle to ms sql server to view the tables , is it posibble without configuring the gateways( as looks like it is licenced product)
I have this box that runs a dataguard observer in a test lab.
Is there a way to configure connect time failover and connection timeout in the connect identifier used by DGMGRL? It does not seem to respect the parameters I supply.
This is what I used for TNSNAMES connection string:
DB= (DESCRIPTION = (ADDRESS_LIST = (FAILOVER=ON)
[Code].....
When the hosts are up, if the instance is not running, I really get a quick reply from DGMGRL. However, when some host is not available, it can take quite a while to get an answer, minutes..
= I though I could use (TYPE=select) (METHOD=basic) (RETRIES=5) #(DELAY=1) # defaults to 1 when retries is set
in the connect_data section, but this does not seem to have an effect = I also tried ' (CONNECT_TIMEOUT=5)' in the ADDRESS_LIST section =
So assuming that Oracle NET services are not available on the hosts. What can I use to make 'dgmgrl' report that it can't connect to the host quickly ? Should I look into the TCP timeout parameters ?
I have one procedure which calls a function. My requirement is if that function didnt return value in 1 minute then stop the exection of that function and call next statement.
Begin (1)......... (2)....... (3) get_func_val:= function(1,2); (4)...... (5).... end;
My requirement is, if (3) doesn't return value in 1 minute, stop the execution of (3) and start executing (4).
[#|2010-03-17T19:00:26.689+0530|SEVERE|glassfishv3.0|tcplistener.ModuleHandler|_ThreadID=30;_ThreadName=Thread-1;|The log message is null. java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:168) at java.net.SocketInputStream.read(SocketInputStream.java:182) at java.io.FilterInputStream.read(FilterInputStream.java:66) at tcplistener.ByteWrapper.unwrapFromStream(ByteWrapper.java:136) at tcplistener.ModuleHandler.run(ModuleHandler.java:106) at java.lang.Thread.run(Thread.java:619) |#]
When I connect to a Oracle database(remote) using TOAD client, because of the settings of timeout done at the server end(in Oracle), the TOAD session gets time out in 5 minutes.
I want to know how I can increase the timeout interval or if I can keep on pinging the database server so that the TOAD connection does not gets timeout?
PS: When we connect to a unix server using putty, we can keep on pining the server at regular timeout interval by going to Change Settings->Connection->Set KeepAlive
We have implemented a approval notification for our customer which on a high level is as follows
requester -> Approver 1-> Approver 2
Once a request is submitted, the 'approval' notification goes to Approver 1 followed by Approver 2. The request is approved when both approves approve the request. If no action is taken by the approvers, the original notifications are cancelled and escalation notification is sent to supervisor of approver 2.
We have a timeout of 48 hours set for the initial approval notifications sent to the approvers (1 & 2). In case Approver 2 requests for more information using the 'Request information' button, the TIMEOUT clock does not seem to reset and the notification is cancelled after 48 hrs even if the notification is in the queue of the requester.
This is causing the escalation notification to be supervisor of approver 2.
The question here is why does the timeout clock not reset when the notification goes back to the requester for additional information. Is this how the standard oracle workflow notification behaves ? Is there a way to customize and achieve this ?
We have a daily batch job executing a oracle-plsql function. Actually the quartz scheduler invokes a java program which makes a call to the oracle-plsql function. This oracle plsql function deletes data (which is more than 6 months) from 4 tables and then commits the transaction.
This batch job was running successfully in the test environment but started failing when new data was dumped to the tables which happened 2 weeks ago (The code is supposed to go into production this week). Earlier the number of rows in each table was not more than 0.1 million. But now it is 1 million in 3 tables and 2.4 million in the other table.
After running for 3 hours, we are getting a error in java (written in the log file) "...Connection reset; nested exception is java.sql.SQLException: Io exception: Connection reset....". When the row-counts on the tables were checked, it was clear that no record was deleted from any of the tables.
Is it possible in oracle database, for the plsql procedure/function to be automatically terminated/killed when the connection is timed out and the invoking session is no longer active?
Background: The gateway could not receive a timely response from the website you are trying to access, a DNS server, or another gateway server. This might indicate that the network is congested or that the website is experiencing technical difficulties.
I got a database query which takes about 4-5 sec to run, but since it seems to take too long time something happens, timeout(?) and returns a white page.
What would be the proper way to debug this? I only got access to the web console. Sometimes on the second try it loads the webpage, and I guess it has cached it then and can load it. So maybe weblogic timesout my request?
When i try to compile a package, im getting below error.
ERROR at line 1:ORA-04021: timeout occurred while waiting to lock object
i gave below query and found the sid of object.
select * from v$access where object='THINKING_PKG'
From v$session view,i found sid and serial# and at same time i queried dba_jobs and saw one jobs is running at this time.Now how to compile this object.
Any way to setup Data Pump Export timeout on resumable error (like with classique export). Seems that default timeout is 2h (7200s).I have tried to set system parameter 'resumable_timeout' from 0 to 60 but no change.
I would like to script an export, but I just want that the script exits on errors like this one:
ORA-39171: Le travail se heurte à une attente avec possibilité de reprise. ORA-01691: impossible d'étendre le segment LOB SYS.SYS_LOB0000145352C00039$$ de 128 dans le tablespace SYSTEM
Actually, the script have to wait 2h for expdp timeout.
i am using 11g and pl sqlk developper, here is the script snippet
MERGE INTO GDIEXP.OEM_SCHEMAS@OX10CC30_GDI.LOTO_QUEBEC.COM O USING ( SELECT T.GRANTEE, I.INSTANCE_NAME FROM DBA_ROLE_PRIVS T, V$INSTANCE I WHERE T.GRANTED_ROLE = 'PRIVPROPRIETAIRES') DRP
[code]...
An oracle 02070 is popping up, i don't what's the real reason behind it, i only know it works when i don't use the db link. What can i do ?
I just created a dblink from our Oracle 9i database to a SQL Server 2008 R2 Server using oracle heterogeneous services. The dblink seems to be OK and I can query the remote SQL Server database but I only get results for numeric columns.
Say the remote SQL Server table was created like:
CREATE TABLE [MITLOC] ( [MLCONO] [numeric](3, 0) NOT NULL, [MLWHLO] [nchar](3) NOT NULL, [MLITNO] [nchar](15) NOT NULL, [MLWHSL] [nchar](10) NOT NULL, [MLBANO] [nvarchar](20) NOT NULL, [MLCAMU] [nvarchar](20) NOT NULL, [MLFACI] [nchar](3) NOT NULL, [MLWHLT] [nchar](2) NOT NULL, [MLSTQT] [numeric](15, 6) NOT NULL)
The remote dblink is named after M3PREP.
Then if I run:
CODEselect MLCONO, MLSTQT from MITLOC@M3PREP => this works OK and I get the two columns.
If I run:
CODEselect * from MITLOC@M3PREP => I get the same results as in the previous query and all nchar, nvarchar type columns are missing, I only get the same two columns.
If I run:
CODEselect MLCONO, MLWHLO from MITLOC@M3PREP=> I get ORA-00904 - the column name entered is either missing or invalid.
I set up my .ora init file like this, I suspect there is something missing here, is it related to the NLS_LANG or so?
There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.
I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.
The data should be refreshed every day, the data changes moderately, there are more or less 500 records.
I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
iN MY DB SERVER (CALLED A),THERE IS A DBLINK TO ANOTHER DB SERVER (CALLED B). DBLINK'S IS LINKB. ---------- BY USING LINKB,I CAN CREATE,DROP TABLE;DELETE,UPDATE,INSERT DATAS IN SERVER B.
Now,i want to create procedure on server b(but i can only deal with b using dblink linkb).
I want to create a private DBLINK in 10g and grant access to use this to limited (another 2/3) users. Is that possible ?I don't want to create PUBLIC DBLINK because the users to use it are limited.
create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT IS /* Criteria to be followed to Order Archival
* Order Status should be 'Cancelled' or 'Complete' * Order Closed date should be 6 months before * -- main Cursor to spool the Orders to be archived based on criteria
I have got a materialized view which is created over a dblink as below:
CREATE MATERIALIZED VIEW CRIMEREPORTODSV TABLESPACE ODS_DATA BUILD IMMEDIATE REFRESH FAST AS (SELECT * FROM CRIME_INT@CRISP);
This is all fine and works apart from any changes to the source table (CRIME_INT) isn't reflected unless I perform a refresh. Whereas I want any changes to be reflected straight away. I have had to use a Materialized view in this case as one of the columns in the source table is a CLOB and Oracle won't allow creation of a view with a CLOB field over DBLINK.
