running CENTOS 6 and oracle DB 11.2.0 and when i try to launch sqlplus i get the following error:
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
problem is, it's telling me to set ORACLE_HOME but I'm not sure where this is to set.
I have created oracle 11 XE..Once it is created it was running fine.I have worked on that 2 days.
Problem is occured when I restart my machine.I m getting below error:
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
I have checked the forum in which it is mentioned that ORACLE_HOME is not properly set.I tried that one alot of time.Even now environment variable shows proper value.
after connecting to database using username and password , i get below error message connection is established and able to work asusal,
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
select 'Welcome to database: ' || name from v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist
In my database server i am not able to login to the database via sqlplus and exp/imp also having error as below. But i am having permission to the this executable. I am falling under other user catagary
export ORACLE_SID=TEST
export ORACLE_HOME=/dboracle/orabase/product/10.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin
Error:sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
Permission details.
testuser@sgh3001:/dboracle/orabase/product/10.2.0.3/bin> ls -lrt sqlplus
-rwxr-x--x 1 oracle dba 12924 2007-03-27 10:21 sqlplus
I have a .sql file that is used as a wrapper file that when executes within sqlplus (9.2.0.1.0), executes a bunch of .sql files within it. Example below:
WRAPPER.SQL
START D:ScriptsA.sql "'04-01-2012 00:00:00'"
START D:ScriptsB.sql "'04-01-2012 00:00:00'"
START D:ScriptsC.sql "'04-01-2012 00:00:00'"
START D:ScriptsD.sql "'04-01-2012 00:00:00'"
START D:ScriptsE.sql "'04-01-2012 00:00:00'"
EXIT;
Each of the .sql file (A,B,C,D,E) Spools individual output of sql statment within them. Each of the indv .sql file queries different tables with different filters(where) clause.
I would like to capture any error (OS,SQL,DB) into indv error file (A_ERROR.log). The reason being is because later in the process we need to validate if there were any errors before processing and loading the data into our SQL database
tried installing oracle 10g in ubuntu. i am successful with the oracle installation.i am able to login to sqlplus using oracle OEM,but when i try to login through command prompt , i get the following error:
rakesh@ubuntu:/$ sqlplus "/ as sysdba"
bash: sqlplus: command not found
[color="#000000"][/color]
rakesh@ubuntu:/$ sqlplus
bash: sqlplus: command not found
i have also tried to set the oracle_home, oracle path & oracle SID.
find the below configuration parameters which i have set to.
root@ubuntu:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin# more oracle_env.sh
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID
#NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
[code]....
it seems to me that the file tnsnames.ora is not read when i execute sqlplus.whit the command: sqlplus username / password @ servicename i receive the error ORA-12154, while if i pass the whole connection string i can connect without problems
sqlplus username/password@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oradev10)(PORT = 10520))(CONNECT_DATA = (SERVICE_NAME = D10)))
i set the ORACLE_HOME environment variable correctly... what more i have to do? following is my tnsnames.ora
TOTEMPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ttf19.cern.ch)(PORT = 1521))
[code]...
How to get the client machine IP in Forms 10g without using webutil? I know the function Webutil_clientinfo.get_ip_address. But Webutil is not working in when new form instance. And I need get client ip address in trigger PRE-FORM.
sys_context('userenv', 'IP_ADDRESS') return address of server, not client.
OR
How to make the trigger WHEN-CUSTOM-ITEM-EVENT in Webutil starts before triggers "WHEN-NEW-FORM-INSTANCE" and "PRE-FORM" ?? To use webutil_ clientinfo.get_ip_address
I'm having some minor trouble with my sqlplus in Windows. Don't know which feature I accidentally edited by cosole seems to be in debug mode. Everything is ok, except that it print the old and new values of my variables. How can't I restore it to it's original behavior.
View 3 Replies View Relatedwhen i enter a sql * plus session an do a rollback; (without performing anything), always a "rollback complete" message appears even if there is nothing to rollback.
View 4 Replies View RelatedI'm running a C++ program to call sqlplus to execute some sql scirpts. The program will accept arguments from users at the command line, and execute the script which the name will also be passed in as an argument to the program eg:
./MY_PROG.sh ABC.sql 23
(MY_PROG will call ABC.sql script and pass 23 as an argument to ABC.sql)
it works fine when the argument is passed in correctly, and my ABC.sql scirpt is able to execute correctly, however when the argument is not passed in, the program just hangs there, waiting for an input from the users. This is because "&1" in my script is expecting some value from users.
Because this program will be scheduled to run, and no one will be there to monitor the job, if so happens arguments were missing, the program will wait there for the entire night. Is there any way that I could make the program or the script to exit when there is no input to be found? I can't use timeout as I do not know how long does the script take to finish it operations, I could be updating millions of record from a script.
We have two database instances on the same server. One was left at 9.2.0.7 and one was upgraded to 10.2.0.3. Connecting externally (sqlplus '/as sysdba') to the 9.2.0.7 database is lightning fast. Connecting externally to the 10.2.0.3 database is very slow, comparatively speakiing. This is on an IBM AIX-5L (64-bit) machine. We are using "tnsnames".
View 10 Replies View Relatedi am trying to copy the data from a remote database which has Oracle 8i thru Sqlplus copy command to the local database which has Oracle 11gR2.
copy from abc/abc@remote
insert emp_new
using
select *
from emp_old
where rownum < 1001;
is it possible to copy the remote database thru sqlplus copy command ?
I'm having issues with users logging into Oracle. I installed it on Ubuntu 12.04 running Oracle 11.2 XE. If i am root, i can run sqlplus just fine and log in. But when I use a regular user account and run sqlplus, it will just stay blank. No error messages or any feedback. I echo $ORACLE_HOME and echo $ORACLE_SID and they come back exactly as they do under the root account. My path is set up just like root has it.
It almost seems like a permission issue but even when i try sqlplus /nolog it stays blank.
[URL]
I have installed oracle 10g on opensuse. After i logon as normal user and type sqlplus, it was display an error message as below:-
login: nv2am
Password for nv2am:
login: Configuration file does not specify default realm when parsing name nv2am
Last login: Tue Aug 3 16:33:19 from 10.69.20.25
Have a lot of fun...
Directory: /home/nv2am
Tue Aug 3 17:36:23 MYT 2010
nv2am@ARMSDEV:/home/nv2am> sqlplus
-ksh: sqlplus: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am> sqlplus "/ as sysdba"
-ksh: sqlplus: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am> set ORACLE_SID="ARMSDEV"
nv2am@ARMSDEV:/home/nv2am> svrmgrl
-ksh: svrmgrl: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am>
I want to add a value from sqlplus.
My scenario is like that i want to add the following value
select count(*) x from tab1
i need to connect several database, and each time i need to add with previous one. like that
connect a/a@db1;
select count(*) x from tab1;
then when i connect another db
connect b/b@db2;
select count(*) x from tab1;
x will add with previous one.
Here is a simple sqlplus line command with & substitution run time variable.
set verify off;
SQL> SELECT * FROM emp where sal >&SAL;
Enter value for sal:
Now as you see, oracle asks to Enter Value for substitution variable in line just below the SQL>. I wonder if there is one or more line could be left there so to improve the readability. So it should look like:
set verify off;
SQL> SELECT * FROM emp where sal >&SAL;
Enter value for sal:
how to restrict sqlplus as sysdba
C:> Set Oracle_sid=Mydb
C:>sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
I don't want to log in any one as sqlplus / as sysdba.If they can able to login as sqlplus / as sysdba then they can see others schema in the database.Say if i set
SQLNET.AUTHENTICATION_SERVICES=none
no one can log in as sysdba were as later some one changes to
SQLNET.AUTHENTICATION_SERVICES=NTS
After they changes to NTS they can log in and access other user details right ? how to restrict
Oracle version: Oracle 11 g
OS Version :Win 7
My database in on 10.2.0.3 and facing hang problem when I am connecting to sqlplus.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 29 00:54:03 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01075: you are currently logged on
As per Metalink note(215858.1) suggested in forums I have followed steps and produced results below.
bash-3.00$ sqlplus -prelim
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 28 23:28:02 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
SYS on test>oradebug help
SYS on test>oradebug setmypid
Statement processed.
SYS on test>oradebug hanganalyze 12
[code]...
Now I have got struck how to analyze above results.
"C:UsersKarthikeyan>sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 7 17:42:37 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR:ORA-12560: TNS:protocol adapter error Enter user-name:"
this is the first time i tried to open sql*plus and i didn't create any database connection before....so i don't have any username and password.......
I'm trying to create a new table which has a date field (date of birth) and I want to include a constraint which will not allow me to add values if they are over 21 years old. I'm using SQLPlus .
View 3 Replies View RelatedI'm trying to perform a SELECT INTO in a SQL*Plus script.the script i'm working towards to, looks something like this;
set term on
set echo off
set serveroutput off
set verify off
ACCEPT ReportName PROMPT 'Please enter a report name; '
PROMPT
PROMPT retrieving current settings for &ReportName:
select STARTDATE, ENDDATE, SUFFIX
into pStart, pEnd, pSuffix
from MyTable
where NAME = '&ReportName';
[code]....
I'm basically trying to perform a select into based in user input and then put the retrieved data back to the console with a prompt. Is this possible within a sql script running on SQL*Plus?
I have two identical tables from different DB's and I need to copy a row from db1 and insert into db2 if it does not exist or update it if it does(on the tables pk).
This is what i was trying until i realized replace drops the whole table where i thought it dropped the row and inserted the new.
copy
from db1
to db2
replace table2
using
select *
from table1
where x = blah
we have a program that uses SQLPlus to run PLSQL scripts - this program then checks the return code of SQLPlus to determine whether the script ran successfully. Our PLSQL scripts often call other PLSQL scripts via "@@OtherScript.sql" - one problem we have is that if (due to an engineering mistake) the OtherScript.sql file is missing, SQLPlus throws an SP2 error stating that the file was not found. However, the return code of SQLPlus does not indicate any problem. I have found, by searching the internet, that I can simply add a line "whenever OS error exit failure" to our script which then allows our program to detect the failure... however, the error message is not as informative:
CODESQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 19 10:28:45 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[code]....
is it possible to get BOTH types of error messages? With "oserror exit" set it does not tell you which file was not found.. which is useful information. I would like the SQL Plus session to return failure but also print out what file it did not find.
I have a series of sql files that I need to execute via sqlplus. What I do need is to immediately exit the sqlplus instance upon completion of the script. Now, I am not using a batch file, nor a file containing script files where I can add the EXIT command.
I have to effectively run each script from the command line as such:
sqlplus user/pass@server @c: empsomedirsomeScript.sql
Now this works, but once it runs the SQLPlus window is still open. Now I have an event handler (in a c# wrapper) that is supposed to fire once the process terminates...but of course, this even never fires since the sqlplus process doesn't terminate after running the script.
Is there any any any any way to get sqlplus to run this script and then self-exit??
I have no control of the sql script and cannot edit them to include an EXIT command. I need to run them as is.
I have some Oracle Databases and sometimes for me is easier to access to the databases with SQLPLUS instead SQLDEVELOPER or TOAD.
But i can't reach to a good configuration of command line prompt to do queries against the database, normaly if it returns a lot o rows and columns it is unreadable.
How can I put an Oracle sqlplus query output into file?
SQL> select myfield from userDB where IMSI like '22803%' and rownum < 11;
I would like to put the output in a file, say '/tmp/mydata.dat'
I am trying to create a batch file which will be executed with windows scheduled task. This batch file will have sqlplus script running Oracle query. I can run this query from the command prompt, no problem,
This is the sqlplus scipt to run query:
C:\Documents and Settings\testuser>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 19:54:25 2010
Copyright © 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: TEST1
Enter password:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
TEST1@DB> spool testuserfile
here goes my query
TEST1@DB> spool off
TEST1@DB> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
C:\Documents and Settings\testuser>
How do I put this in a batch file. I was able to figure how to logon, but I get stuck at the spool command. My batch file starts like this:
cd C:\Documents and Settings\testuser
sqlplus TEST1/Password
When I run sqlplus and try to loging as system/<admin passwd> I get information:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
I understand, that this is a common question and the common answer is don't have trailing slash in ORACLE_HOME and set ORACLE_SID. But I did this.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=XE
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
When I try to remotely connect to an Oracle schema using SQLplus, I consistantly get this error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Yet I can connect to the same machine, from my own machine just fine using SQLDeveloper.Here are the details I have collected so far
- I am using the instantclient for OS X
- export DYLD_LIBRARY_PATH=/Users/jeff/instantclient
- export ORACLE_HOME=/Users/jeff/instantclient
- export TNS_ADMIN=/Users/jeff/instantclient/tns
The command line i ran : ./sqlplus seam_classoft/seam_classoft@//oracle11gtest.terida.net:1521/orcl
Its behind a VPN, so I'm not worried about posting my info.
My /Users/jeff/instantclient/tns/tnsnames.ora that I copied directly from the server looks like this:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle11gtest.terida.net)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.terida.net)
)
)
I've tried changing SERVICE_NAME to SID = orcl, no change inbehavior. My original guess was that the database wasn't registered with the listener, but that would mean sqlDeveloper should fail as well, but it works.Here is out put of lsnctrl status:
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 07-JUN-2010 09:16:40
Uptime 42 days 0 hr. 58 min. 59 sec
Trace Level off
[code]....
