SQL & PL/SQL :: Getting Hostname / Systimestamp Over Database Link
Oct 26, 2013
I have a staging which connects to RAC clusters via dblink..I have a scenario to collect the hostname,systimestamp whenever i do a query from this rac clusters
So in general below would be cool
select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric would work perfect
But on dblink..
select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink.is returning local timestamp and hostname which is not intended
So i even tried subquery..
select (select host_name,systimestamp from v$instance),t UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink
this would work in certain case ...But it certain cases it would throw an error saying mutiple rows from asubquery .All ineed is to get hostname,systimestamp append to all sql i fetch from any this rac clusters through this dblinks.
View 8 Replies
ADVERTISEMENT
Apr 22, 2010
i got to find out the users hostname who are connecting to database through remote,i tried with v$session bt it is showing null values in the terminal column.how to find out ?
View 8 Replies
View Related
Jun 14, 2012
I need to change systimestamp from +01:00 to +08:00.
SYSTIMESTAMP
14-JUN-12 03.29.47.657901 PM +01:00 =====> I need to change it as +08:00
how can i change it?
I added TZ=SGT in my listener.ora file and bounced the listener. Even though systimestamp is still showing --> +01:00
View 3 Replies
View Related
Mar 27, 2013
I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.
SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE
--------------------------------------------------------- --------------------------------------------------------- ------------------------------------------------ --------------------- ----------------------------
27-MAR-13 02.31.55.041411 AM +00:00 26-MAR-13 07.31.55.041416 PM -07:00 26-MAR-13 07.31.55.041416 PM +00:00 -07:00
I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.
So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.
So questions are:
What could be the difference in setup between my db and db A?
Is there a query, logfile, or anything I should check to find out what can be the difference?
I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.
The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)
View 0 Replies
View Related
Oct 23, 2012
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg :
select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
View 4 Replies
View Related
Mar 17, 2010
i would like to import a table from another database by using database link.
impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
i created the public database link as system user
it gave error like
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
[code]....
after that i try to import as system user like
impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1
but error like
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.
when i use IMP utility
imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser
View 3 Replies
View Related
Jun 26, 2013
Is it possible to use Database authentication in APEX through database link, and how?Also is it posible to read roles from users through database link?
View 3 Replies
View Related
May 9, 2011
how can I get the form hostname that it is currently running to? I already tried the form's (forms developer 6i) built in host functionality but it doesn't return the hostname.
Our current approach is to have a shell script that will get and insert the hostname into the table and the form will just query from the table to get the hostname, but that approach has involved too many modules.
Is there an easiest way to get the hostname?
View 5 Replies
View Related
Jul 5, 2012
I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;
It Returns
Database link created.
select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
what changes I need to make to tnsnames and listener at both servers.
View 1 Replies
View Related
Apr 7, 2009
I have purchased LIVE IP on my application server to access it anywhere on the internet.I am calling application on the internet as URL>...And its working perfectly.
I have configured Sigle Sign On and using this code on "When-Button-Pressed" to call report
WEB.show_document('/reports/rwservlet?server='||:PARAMETER.reps
||'&ssoconn=default/oracledb/userid'
||'&report='||repid
||'&desformat=PDF'
||'&destype=CACHE'
||'&pyear='||:PARAMETER.pyear
||'&branch='||:PARAMETER.branch_id
||'&bk='||:transactions.book
||'&sno='||:transactions.srno
||'¶mform=no'
,'_blank');
From Client side When i press button "sigle sign on authentication page" of Internet Directory is not coming. Because URL is accessing the application server using the HOSTNAME of application server instead of IP ADDRESS like this URL....
How can i configure it to use only IP address.
View 25 Replies
View Related
Feb 28, 2013
Is it possible to change the Hostname/IP's nodes on installed GI11.2.0.3? Exist some notes for do this action on "GI standalone" but no for GI.
View 4 Replies
View Related
May 15, 2013
I am newbie to oracle database.
I am using oracle 9i version and in EMCA satndalone application . I have created database links.
The database links have been created, and three databases are local.
When i am pressing the test button in emca . It gives notification database link not active.
What should i do?
View 3 Replies
View Related
May 12, 2010
A procedure in a package uses a database link. The database link is defined without a username and a password. The user that uses the database link is supposed to be present in the opposite database as wel.
When the procedure is started in SQL*Plus or TOAD it runs perfect. But the procedure has to run in a scheduled job. And that doesn't work. The procedure fails because of: ORA-01017 invalid username/password. The user that is used to run the scheduled job is the right one.
View 5 Replies
View Related
Jan 28, 2013
I have installed oracle database 11gr2 in laptop1 and installed oracle database 11gr2 in laptop2.Both the laptop's are connected to same internet connection through wifi. So my question is can i create a database link between these two databases using this wifi? give some steps in creation of a database link.
View 13 Replies
View Related
Jul 10, 2013
i need access to a view running on another database server. i configured my db link and tried to compile the view. but the following message occurs: ora-12154: TNS: Connection Identifier..
View 16 Replies
View Related
Aug 11, 2010
I've got a oracle install [non production, but devel] that is a tad screwed up. We moved the box and as a result changed the hostname to match the new naming scheme. Ever since then OracleEM has been somewhat confused. In anycase, I don't want OEM anyways now. Plan is to learn SQLplus.
That being said I've used emctl to shut down dbconsole, but it seems there is something somewhere that keeps restarting 2 processes that like to sit around and take up 100% cpu. I can kill them, they stay dead for a few hours then crop up again.I was able to find this out about them:
[jmacdonald@devoracle ~]$ ps auxwww|grep 2033
oracle 20334 84.1 12.3 994052 255824 ? Rs Aug10 1740:43 ora_j000_orcl
oracle 20336 80.9 14.1 998140 294288 ? Ss Aug10 1674:18 ora_j001_orcl
And then this, which caused me to conlucde its OracleEM:
SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.process in (20334,20336)
[code]...
View 6 Replies
View Related
May 20, 2009
In Oracle 10gR2.If a materialized view uses a database link for the query in order to create a snapshot of data on a remote instance, does the name of the database link have to be an entry in the tnsnames.ora file?
The following link suggests not, but is not version specific: Materialized View - Oracle Wiki FAQ
However, the following 11g documentation suggests that the database link name must be the same as the global name of the target database.URL..
I can't any info specific to 10gR2.
We have three instances. Our application metadata is stored in a schema in B.METADATA. There is a shell schema (B.METADATASHLL) that provides access to the tables to remote applications.
On instance A, we have a shell schema (A.METADATASHLL) that creates materialized views as follows:
CREATE DATABASE LINK METADATA_PRIME
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';
Our materialized view is created using the query SELECT * FROM METADATA.APPLICATIONS@METADATA_PRIME WHERE Application = 'A';
The query works, but the materialized view does not.I'm being told that the database link has to be named B as follows
CREATE DATABASE LINK B
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';
Which means that I can only have one public link to instance B, and I'm in a pickle if I create a second application (which I have) using the same model).
View 6 Replies
View Related
Jan 9, 2012
I found a link to a topic concerning this issue (click here), but the question mentions that the poster was looking for a "band-aid" fix...if this method will work for a more long-term solution
View 1 Replies
View Related
Jun 18, 2013
Usually where should we create the database link? In the production or development? create public database link dblinkname connect to user_in_db2 identified by password_in_db2 using 'tnsentry name in db2'; In production or development?
View 2 Replies
View Related
Jul 5, 2012
I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;
It Returns
Database link created.
select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
what changes I need to make to tnsnames and listener at both servers.
View 8 Replies
View Related
Jun 7, 2011
Is it possible create dynamically database link within procedure.
It give me the following error
ORA-01031: insufficient privileges
But i can drop database link within procrdure.
Is there any way to do this?
View 4 Replies
View Related
Jun 19, 2012
in a 9.2.0 db I create a db link but does not function :
CREATE DATABASE LINK "ONEDB"
CONNECT TO "user1" IDENTIFIED BY "****" USING 'ONEDB';
SQL> select * from dual@"ONEDB";
select * from dual@"ONEDB"
*
ERROR : ORA-12154: TNS:could not resolve service nameBut on the same server I can connect to 11g in sqlplus :
Z:>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mar. Juin 19 10:13:45 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect user1@ONEDB
password :
Connected.Of cours the other DB links toward 9.2.0 databases work well.
View 4 Replies
View Related
May 20, 2010
How I can find the hostname for an oracle session id?
View 4 Replies
View Related
Jun 17, 2009
the procedure to link oracle 9i database server withi 10i.
View 1 Replies
View Related
Dec 8, 2008
Here is the information about database I need to connect from my view, as the underlying table for my view is in this database
Database instance : orcl
Machine name : contentm (not sure, if this needs to be used)
so in my schema, I tried the following
CREATE DATABASE LINK product_lnk2 USING 'orcl'
select * from cont20.V_FB_PRODUCT_NM@product_lnk2
where cont20 is the schema on orcl
But it gives me this error ORA-12154: TNS:could not resolve the connect identifier specified
View 4 Replies
View Related
Jun 6, 2013
I would like to create a view on the Oracle which points to SQLserver tables.
oracle db version : 10g
sql server version :sql server 2008 R2
View 2 Replies
View Related
Oct 13, 2010
how to encrypt 9i database-link passwords? In 10g when we browse the link it show **** but in 9i it shows the actual password.
View 5 Replies
View Related
Aug 29, 2012
I want to create a database trigger which will test the database link, if it is ok then it will use dblink and do its work.
If it fails then it will send the data into its own server logfile.
I Wrote:
CREATE OR REPLACE TRIGGER PERMIT.TESTTRG
AFTER INSERT OR UPDATE
ON PERMIT.TR_LP_M_H_COMPANY_25072012
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
Declare
l number; nIgn PLS_INTEGER; nRows PLS_INTEGER := 0;
[code]....
When I execute it it is giving error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PLS-00201: identifier 'EXEC_SQL.CONNTYPE' must be declared
4/11 PL/SQL: Item ignored
8/2 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
[code]....
View 4 Replies
View Related
Jul 26, 2011
I am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
View 6 Replies
View Related
Feb 15, 2011
How to call a procedure by passing the db link dynamically.
View 1 Replies
View Related