Is it possible to connect 2 database schemes at the same time? I want to update a table from one scheme to another with the following update statement.
Update scheme1.table Set (col1, col2, col3, col4) = (select col1, col2, col3, col4 from scheme2.table);
If there is a way of doing this, I want to do this in some procedure?
My need is to grant priveleges for select on view without granting on nested tables.
Actual problem is that I can grant privileges for particular user, but can't grant privileges for user group.
I have a main application from which the user can go to different applications (through tabs).
I have authorization scheme set for the main application.
When I try to use these (main app) authorization schemes in the other sub applications, they doesn't work.
Is there any way we can globalize the authorization scheme in the main application so that they can be used by different apps ?Or share the main applications authorization schemes with the sub applications.
I would like to implement connect time fail over from my JDBC thin client. The client will try to first connect with dedicated connection mode, if the connection time crosses more than two seconds, then connection will be opened with shared mode. To implement this, I have put the following connection string in my JDBC URL. I am able to connect to database with this URL, however it seems fail over does not work. Is it because, my database listener is not configured with dynamic service registration. If I implement the dynamic service registration, is fail over going to work? Note, I want to fail over in the same database instance but with different listener.
(DESCRIPTION_LIST =
(FAILOVER = TRUE)
(LOAD_BALANCE = FALSE)
[Code].....
How connect-time failover is setup.
I am following lab instruction from my Oracle Admin Workshop book. The exercise is to create a 2nd listener to support connect-time failover. I have two listeners up and running. I connected the second listener using Static Database Registration in OEM, and then I started it.
When I looked in the listener.ora file, I was expecting to find the entries for FAILOVER=TRUE but it is not there. Both listeners are designed to connect to the same instance 'orcl'.
My questions:
1) Is the FAILOVER entry supposed to be done automatically when the second listener is created ? or is it always a manual entry ?
2) How can I test that it is working ?
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 ?
Is there a way where I can check users/IPs who connect to the database in a specific time 2 days ago?
View 5 Replies View RelatedI have been troubling with this issue for the last two weeks...tried so much, but could not able to resolve....
sqlplus username/password@IPAddress:port/ServiceName
I am able to connect to DATABASE from SQLAssistant only when I provide string like ABOVE........If I dont mention the SERVICE, m unable to connect to DB.Now the biggest issue is with SQL Developer.....m not at all able to connect to DB using SQL Developer.
I have installed oracle developer 6i. in sqlplus i want to put my username and password but nothing in the connect string field.
View 1 Replies View RelatedI have the following computer/setup:
Acer laptop, Intel Processor w/ Windows 7 Professional (64-bit)
Delphi 2010 Professional
Oracle XE (+ the Oracle XE client that comes with it)
I cannot can't to the Oracle database from Delphi. The error I keep getting is:
Alias is not currently opened. [Oracle][ODBC][Ora]ORA-12154:TNS: could not resolve the connet identifier specified.
Here's what I've done:
1. Create an ODBC connection to my Oracle database
- run C:WindowsSysWOW64odbcad32.exe to open the ODBC administrator.
- Data source name: bt_user
- TNS Server Name: localhost/xe
- user bt_user
- Clicked "test connection" -- Success.
2. Open Delphi and drop a TDatabase component on my main form.
- Set the Alias Name property to "bt_user" (the alias I set up in ODBC)
- Set database name to "bt_user".
- Checked "connected".
Alternatively, I tried using the Delphi ADO components (the dbGo components).
- Drop TADOConnection component on my form.
- Select "connection string" and open the dialog.
- Click "build string".
- Select "Microsoft OLE DB Provider for ODBC Drivers" from the selection.
- In "use data source", select "bt_user" from the drop-down list (my ODBC connection from #1)
- Click "test connection".
Again the same error.
My questions are:
1. Can Delphi work with Oracle XE (Express Edition)?
2. If so, then do I need to install the "Instant Client"?
3. Will the instant client for Oracle 10g work with Oracle 10g XE?
4. Do I need to install any additional ODBC driver(s)?
5. Why was I successful in creating the ODBC connection in Windows, but it failed in Delphi?
6. Will Visual Studio.NET (C#) work with Oracle XE, and what are the steps for setting that up?
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.
What does this exec and Fetch time is?
How to improve fetch time, (esp with sub-query) ?
I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......
I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.
This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.
The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.
. I have this query:
select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus
from
(
select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2
from cti.agentsessionlog asl1
[code]...
As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
View 1 Replies View RelatedI have an application in which time is show as . But, in the table in Oracle it is showing as 13:00. The application is taking the time from OS. OS time is 18:30 IST. Time in SYSDATE is also showing as 18:30. DBTIMEZONE is '+5:30'. what is the problem in Database and how that can be rectified to show the time as same as OS time.
View 4 Replies View RelatedWe have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.
Service Time : 15.36%
Wait Time : 84.64%
This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.
As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.
i have installed oracle 10g and toad but i don't know how to connect toad with database
View 1 Replies View RelatedI am Using Toad 9.3 and try to connect Oracle DB 11g through SYS but fail, because it is showing ORA-01031:Insufficient privileges.Although the same, I can connect through SQL * Plus. Is there any way to connect SYS (in TOAD).
View 1 Replies View RelatedFor example, I have 3 database, database 1 and database 2 connect together by database link db_link1, database 2 and database 3 connect together by database link db_link2.
I want to ask: Can I connect from database 1 to database 3 ? ( Of course don't use database link between database 1 and 3, just use database db_link1 and db_link2 )
Both db_link1 and db_link2 are public database link.
Can I restrict user access by 2 these public database link ?
I installed Oracle Express on my Windows 2003 Enterprise server. And right off the bat I cannot connect to it via command line. I have googled and read many sites, but am still getting the same error, no matter what I do. I am newbie again (was a DBA in 1990...when I used to have the same error and fix it in seconds...not sure why this problem still lingers in Oracle) so most likely I am overlooking something. Below are my tnsnames.ora and liserner.ora files.
tnsnames.ora
CODEXE =
(DESCRIPTION =
[Code]....
Had 10.2.0.3 on Win Server 2003 and ran patch set 3. Now have 10.2.0.4 but cannot login. Here is what I get along with other info.
-------
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 24 14:27:31 2011
Copyright © 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
--------
My listener service is running. Listener.ora is:
---------
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = W2K3-TX-ORA)(PORT = 1521))
)
)
-----------
Running TNSPING:
C:\oracle\product\10.2.0\db_1\network\ADMIN>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 24-MAR-2011 14:35:35
Copyright © 1997, 2007, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = W2K3-TX-ORA)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME
= ORCL)))
OK (60 msec)
I've stucked with a query. Let me explain the situation: I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
[code]...
In my sample the equality definitions is;
11 = 12
12 = 13
13 = 14
13 = 15
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
I am trying to rollup a measure of a fact for a hierarchical dimension. The 2 entities - Dimension and the fact are described as below
Dimension Table - This maintains the hierarchical structure of the dimension in a parent child relationship format
DROP TABLE tmpDimension;
CREATE TABLE tmpDimension
(
DimValVARCHAR2 (20),
ParentDimValVARCHAR2 (20)
);
[code]...
Also, is there a way to make the group By levels dynamic. I cheated my way through by using a substr which makes it very depenent on 1 letter dimVal values
I want access to the Oracle as manager. What is the user name and password. I tried:
Username: system
Password: manager
But it gives an error. I work on oracle 11g
created one user and granted connect, resource.
SQL> create user xyz identified by welcome123;
but while connecting to that user it's not connecting. saying
SQL> conn xyz/welcome123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
but i am sure i create user.i though to drop and recreate that user. But it is saying connected user can't be drop.then i checked for the connected session of xyz user. but shocked, there is no session for xyz user.
I hv no problem connecting oracle at localhost. but when connecting using another pc on the same LAN, the sqldeveloper returned an error saying
IO Error: The Network Adapter could not establish the connection on the linux where oracle installed, iptables is disable, but firewalld is enabled, so I did a
'firewall-cmd permanent zone=home --add-port=1521/tcp'
yet the remote pc still prompt the same IO error. what should I do to have oracle accept connection from the LAN and WAN ( I hv setup virtual server on router)
I am not able to connect to database due to ORA-257. So manually i deleted the archivelog files in diskgroup. however it is showing 13gb free space after deletiion. Archive log files generated due to Import job is running.
In below ways i have tried to connect to database.
1) Through SQLPLUS (Not able to Connect)
2) Through RMAN i tried to connect (Not able to connect)
3) I tried to connect impdp to stop the running job. It was also failed