tnsnames.ora not working ?
Hello, I'm trying to add description in my $ORACLE_HOME/network/admin/tnsnames.ora, but it seems that the client (ie. sqlplus) wont use it. Whenever I try to connect to the service using sqlplus, I got : $ sqlplus Enter user-name: [EMAIL PROTECTED] Enter password: * ORA-12154: TNS:could not resolve service name I tried to add the description to my ~/.tnsnames.ora too with no luck. The entry in the tnsnames.ora is: DEV_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dev_db) ) ) (note: I removed the real hostname for privacy/security reason of course) However, when I use sqlplus using the following way: $ sqlplus Enter user-name: developer@(description=(address=(protocol=tcp)(host=hostname)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = dev_db))) Enter password: * It would work, where all the information from the description is just a copy-paste from the tnsnames.ora file. Is there anything I overlook? Sorry if this is kinda a newbie question. I'm still learning my way around this. I'm using Oracle9i on Redhat Linux. Thanks for any help. Reuben D. Budiardja -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - To be a nemesis, you have to actively try to destroy something, don't you? Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. - Linus Torvalds - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tnsnames.ora not working ?
Hello, On Tuesday 20 January 2004 11:01 am, Mercadante, Thomas F wrote: Reuben, If the normal connection is throwing an error, then it stands to reason that the seond one would not connect either. You need to get a connection working first before you try something else. I can confirm that this: $ sqlplus Enter user-name: developer@(description=(address=(protocol=tcp)(host=hostname)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = dev_db))) work just fine. That why I'm confused why using tnsnames.ora by putting that entry in tnsnames.ora does not work. The entry is given to me by the DBA of the server I'm trying to connect to. The Oracle error you are getting is complaining about the service_name entry. Is this the same value for the database that you are trying to connect to? I'm not sure what you meant, but I suppose yes. The DBA gave me those entry that I copy-paste in my previous message. Is the database advertising itself as a service of dev_db? As far as I know, yes. I'm thinking the problem is more in the client side (me) rather than the server side. Am I correct ? Fix this first and get the connection to work via the normal method. And then you can try the other method. What do you mean by normal method? Is there a fundamental difference or assumption that I made that may not be true when I'm making connection by passing all the information in the user-name field and by using service name (like [EMAIL PROTECTED]) that is defined in tnsnames.ora? And, finally, why in the world do you want to do this? ummm, do what? What I want is to define that entry in tnsnames.ora so when I want to connect I can just type [EMAIL PROTECTED] as my username, rather than using the long description. Thanks for any help. RDB Tom Mercadante Oracle Certified Professional -Original Message- From: Reuben D. Budiardja [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 10:49 AM To: Multiple recipients of list ORACLE-L Subject: tnsnames.ora not working ? Hello, I'm trying to add description in my $ORACLE_HOME/network/admin/tnsnames.ora, but it seems that the client (ie. sqlplus) wont use it. Whenever I try to connect to the service using sqlplus, I got : $ sqlplus Enter user-name: [EMAIL PROTECTED] Enter password: * ORA-12154: TNS:could not resolve service name I tried to add the description to my ~/.tnsnames.ora too with no luck. The entry in the tnsnames.ora is: DEV_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dev_db) ) ) (note: I removed the real hostname for privacy/security reason of course) However, when I use sqlplus using the following way: $ sqlplus Enter user-name: developer@(description=(address=(protocol=tcp)(host=hostname)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = dev_db))) Enter password: * It would work, where all the information from the description is just a copy-paste from the tnsnames.ora file. Is there anything I overlook? Sorry if this is kinda a newbie question. I'm still learning my way around this. I'm using Oracle9i on Redhat Linux. Thanks for any help. Reuben D. Budiardja -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - To be a nemesis, you have to actively try to destroy something, don't you? Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. - Linus Torvalds - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tnsnames.ora not working ?
On Tuesday 20 January 2004 11:20 am, Scott Canaan wrote: Are you using Oracle Services? I've seen this happen before and we change SERVICE_NAME to SID and everything works fine. That does not work for me either. RDB -Original Message- Reuben D. Budiardja Sent: Tuesday, January 20, 2004 10:49 AM To: Multiple recipients of list ORACLE-L Hello, I'm trying to add description in my $ORACLE_HOME/network/admin/tnsnames.ora, but it seems that the client (ie. sqlplus) wont use it. Whenever I try to connect to the service using sqlplus, I got : $ sqlplus Enter user-name: [EMAIL PROTECTED] Enter password: * ORA-12154: TNS:could not resolve service name I tried to add the description to my ~/.tnsnames.ora too with no luck. The entry in the tnsnames.ora is: DEV_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dev_db) ) ) (note: I removed the real hostname for privacy/security reason of course) However, when I use sqlplus using the following way: $ sqlplus Enter user-name: developer@(description=(address=(protocol=tcp)(host=hostname)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = dev_db))) Enter password: * It would work, where all the information from the description is just a copy-paste from the tnsnames.ora file. Is there anything I overlook? Sorry if this is kinda a newbie question. I'm still learning my way around this. I'm using Oracle9i on Redhat Linux. Thanks for any help. Reuben D. Budiardja -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - To be a nemesis, you have to actively try to destroy something, don't you? Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. - Linus Torvalds - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
HELP: HD crashed, oracle get Error ORA-00205
Hello, I am running oracle on Linux as my development server. Somehow, the hard drive crashed last night. When I run fsck on the partition that has the oracle installation and oradata, it always failed with all kind of stuff (Bad/Duplicate block, short read block, etc), so I assume fsck cannot fully recovered my drive. I however can mount it and read some data on in. Then I tried to logon as sysdba and issues startup, and I got the following error. SQL conn /as sysdba Connected to an idle instance. SQL startup ORACLE instance started. Total System Global Area 235999352 bytes Fixed Size 450680 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes ORA-00205: error in identifying controlfile, check alert log for more info My question: is there any hope at all to recover all or partial of the data in the database? Even if I have to redo oracle installation or re-create the tables, that's fine if I can just recover the data. What can I do? Any help on this is greatly appreciated. And no, I don't have backup. This is supposed to be development server and end up becoming a semi production server. And no, we don't have a DBA either, and I'm newbie on that area (I'm supposed to be a web developer). In a way I a sorta hope this incident becomes a lesson for the boss. sigh Thanks a lot for any help. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Error Log
Is there anyway to setup oracle on the server side to log all fail and error transaction in a file or something? I mean, error/fail transaction due to, eg: Integrity Contraint violation, Check constraint, Not Null constraint, any other error. It would simply debugging since then we don't have to output / catch and send error and SQL statement on the application level. System: ORACLE 9i on Redhat Linux 7.3 Thanks. RDB -- Reuben D. Budiardja -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DB gets really slow
Hello, Sorry if this is a really newbie kinda question. I am a web developer and at this point we have a contract to develop something using Oracle. We use PHP as the front end to connect to oracle. Now, eventually all the codes will be moves to the company that outsource it to us, and they have cluster oracle and their DBA. But for development purposes, for some reason, we don't have access to their DB server. So I downloaded the demo version and install it to our own development server (Redhat Linux 7.3, 1GB RAM, 1.4GHz Athlon XP). I just do install as explained here:http://www.puschitz.com/InstallingOracle9i.html, started the database, and started doing development (creating/dropping table, insert/update/delete/select, etc). I don't have very much data, since it's only dummy data for testing purposes. So it's really a vanilla oracle-install. No customization, optimization whatsoever. At some point, after being up for a while, the whole thing starts to slowdown. I am assuming it's Oracle, since I can't really think anything else who slow it down. The same codes and tables and data using postgreSQL doesn't do this. Restarting oracle and the webserver helps, and things back to normal. My question is, is there anything critical that I miss here? Do I have to do something, like cleaning up etc., to keep the DB running well? In the early starts of development, we did a lot of dropping and re-creating tables, indexes, constraint, and updating/deleting data. Does this have any effect? Anything I can do to find the source of this problem? We don't have an Oracle DBA, and I am not very familiar with oracle from DBA point of view (only as developer), and I didn't really want to spend a lot of time customizing/learning/optimizing the development database because we needed to start development ASAP, but it's probably time to start learning. Thanks for any help. Reuben D.Budiardja -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to tell Oracle the directories of script files
Thanks all. The replies have been really helpful. Reuben D. Budiardja. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to tell Oracle the directories of script files
Hello, Suppose I have some SQL scripts in my /home/user directories, /usr/local/bin directories, etc. If I start 'sqlplus' from console in /home/user directories, I can load/execute the script by doing: SQL @scriptname But what if I don't start sqlplus from that directories? How do I tell Oracle to find the scripts in /home/user, then if it's not there in /usr/local/bin, for example? So that I can be anywhere in the filesystem when starting sqlplus and can execute my SQL script. Just like the $PATH environment variables in *nix system that tell the shell where to find executables, is there a similar thing for Oracle? Thanks for any help. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Empty String is interpreted as NULL
Thanks for all replies.
Right now I think I can afford just make the column NULL, so that's what I'll
do. But still, something doesn't feel quite right with empty string == NULL
:).
RDB
On Friday 18 July 2003 03:22 pm, Mercadante, Thomas F wrote:
Reuben,
While I agree that an empty string is not logically equal to a null, Oracle
interprets an empty string in INSERT and UPDATE statements as a NULL. So
you really do not have a choice here. If you have the need to insert an
empty string into a column, you have two choices:
- Define a character to represent an empty string and insert that character
(pretty dumb suggestion)
- Change the table to allow null values in that column, and perform the
INSERT as your example showed.
Good Luck and hope these helped.
Tom Mercadante
Oracle Certified Professional
-Original Message-
From: Reuben D. Budiardja [mailto:[EMAIL PROTECTED]
Sent: Friday, July 18, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L
Subject: Empty String is interpreted as NULL
Hello all,
Suppose I have this table
SQL DESC FRUIT
Name Null?Type
-
ORANGENOT NULL VARCHAR2(10)
APPLE NOT NULL VARCHAR2(10)
If I do this insert:
SQL /
INSERT INTO FRUIT VALUES ('hello', '')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (LIGHTCONE.FRUIT.APPLE)
I got an error cannot insert NULL. But, what if I meant is to insert empty
string '' ? Certainly empty string is NOT equal to NULL values.
So how do I get around this?
Thanks in advance for any help.
Reuben D. Budiardja
--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-
/\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reuben D. Budiardja
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Empty String is interpreted as NULL
Hello all,
Suppose I have this table
SQL DESC FRUIT
Name Null?Type
-
ORANGENOT NULL VARCHAR2(10)
APPLE NOT NULL VARCHAR2(10)
If I do this insert:
SQL /
INSERT INTO FRUIT VALUES ('hello', '')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (LIGHTCONE.FRUIT.APPLE)
I got an error cannot insert NULL. But, what if I meant is to insert empty
string '' ? Certainly empty string is NOT equal to NULL values.
So how do I get around this?
Thanks in advance for any help.
Reuben D. Budiardja
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reuben D. Budiardja
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
