Linux, Oracle 11g AND Run .sql scripts


         How do I run .sql scripts in Linux /Oracle 11g? I cannt connect to the Enterprise Manager.
Failed to Connect. Is there another way to run .sql scripts?

Who is Participating?
teixeirapConnect With a Mentor Commented:

I tried your example and recieved bash:tnsping: command not found

I believe tnsping is in the /u01/app/oracle/product/11.1.0/db_1/bin directory

check your path environment variable -> echo $PATH
if the directory above is not there then do the exports previously mentioned in first posting.
if the directory is in the path then tnsping is somewhere else and you need to search for it, should be in the directory I mentioned in this posting

tnsping can be run from anyway (as long as the PATH points to it somewhere), doesn't have to be in the directory where the script you are trying to run is in.

Next time can you paste the whole terminal session from the point that you open the window. remember environment variables only stay for the window that you run the export commands unless you edit the user's rc or .profile
I work for Oracle and here are some instructions that will help you.

1.  Place the scripts under a common directory and run the script applicable for your instance (depending on your applications version):

sqlplus apps/(apps password) @XXXX.sql

Replace the XXXX with the name of your script.

You should be able to run sqlplus from the prompt.

Check to see if you have the environment variables set correctly else set them, for example:

export ORACLE_HOME=<Oracle Home Directory>

then run sqlplus /nolog
connect <username>
then to run the script, just type @<scriptfile> and enter
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

missymadiAuthor Commented:
When I enter ORACLE_HOME is get bash: ORACLE_HOME: command not found

I set the install to point to /u01/app/oracle/product/11.1.0/db_1
missymadiAuthor Commented:
I cannot attach to the database through Foxfire either?? can not establish connection.
I connected once at the end of my install - now I cannot open the url to get to Enterprise Manager? Is this part of the problem?
missymadiAuthor Commented:
I used your example, and input
export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export Path=$path:$ORACLE_HOME/bin

and got to sql!
now how do I get to my scripts that are located on my mounted C: drive??
missymadiAuthor Commented:
I copied the .sql's to the db_1 directory. Then in SQL> CdsBuildAllTables.sql
ERROR: SP2-0310: unable to open file
missymadiAuthor Commented:
I logged out of everything and  logged back in as Oracle and now CANNOT get to sqlplus. ERROR:
bash: sqlplus: command not found.

Why can't I execute sqlplus now?
you have to use the @ in front of the script name
The environment setting only last for that console/shell window. Close the window and they are lost.

If you want to make the changes permanently (which I would do for the ORACLE_HOME and the PATH but not the ORACLE_SID) then you have to put the "export" lines into the user's .bashrc, .kshrc or .profile, depending what shell you are using.

I logged out of everything and  logged back in as Oracle and now CANNOT get to sqlplus. ERROR:
bash: sqlplus: command not found.
missymadiAuthor Commented:
I finally connected using Oracle user. I still get an error:
SP2-0310: unable to open file "CdsBuildAllTables.sql"

I gave the directory and files chmod 777 access, why can't I execute these?
Because the 'oracle' user has to be able to read this file.
chown    oracle         /u01.../the_directory
Try to read as user 'oracle' this file.
After that you will be able to open it via SQL*Plus
This is how I would execute a script:
$sqlplus /nolog
SQL> conn sys@mydb1 as sysdba
Enter password:
SQL> @grants.sql

If the script is somewhere different from the folder/directory where you are running sqlplus from then you need to put in the complete path to the file like
SQL> @thissubdirectory/thisscript.sql

If there are spaces in the directories or the script name then put it in double quotation marks:
SQL> @"this sub directory/this script.sql"

missymadiAuthor Commented:
I tried teixeirap's example and entered password and got :
ORA-12504 TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Ok one last try ...

My example was an EXAMPLE. If you have already used export ORACLE_SID you don't have to put in the SID in the connect statement, so this should work SQL> conn <username>
if you wanted to connect to another database other than the one defined in the ORACLE_SID then you would do as follows SQL> conn <username>@<YOUR_SID>

Since I'm not sure if you are following my example literal and not substituting for your situation and there isn't something else wrong ...

You need to make sure that your tnsnames.ora and listerner.ora and sqlnet.ora files are in order, you can you the configuration assistants to do that.
To verify that you can connect to the database and all is ok, try on the command line tnsping <YOUR_ORACLE_SID>
If it response with a OK and time then everything is fine, else you may have a problem with the files I mentioned.
missymadiAuthor Commented:

I tried your example and recieved bash:tnsping: command not found
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.