Solved

Linux, Oracle 11g AND Run .sql scripts

Posted on 2009-05-08
16
1,578 Views
Last Modified: 2013-11-11
Experts,

         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?

MissyMadi
0
Comment
Question by:missymadi
16 Comments
 

Expert Comment

by:ju421019
ID: 24337792
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.

0
 
LVL 1

Expert Comment

by:teixeirap
ID: 24337863
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_SID=<SID>
export ORACLE_HOME=<Oracle Home Directory>
export PATH=$PATH:$ORACLE_HOME/bin

then run sqlplus /nolog
connect <username>
then to run the script, just type @<scriptfile> and enter
0
 

Author Comment

by:missymadi
ID: 24337974
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:missymadi
ID: 24338003
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?
0
 

Author Comment

by:missymadi
ID: 24338105
teixeirap!!
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??
0
 

Author Comment

by:missymadi
ID: 24338395
I copied the .sql's to the db_1 directory. Then in SQL> CdsBuildAllTables.sql
ERROR: SP2-0310: unable to open file
0
 

Author Comment

by:missymadi
ID: 24338783
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?
0
 
LVL 1

Expert Comment

by:teixeirap
ID: 24338834
you have to use the @ in front of the script name
0
 
LVL 1

Expert Comment

by:teixeirap
ID: 24338887
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.

missymadi:
I logged out of everything and  logged back in as Oracle and now CANNOT get to sqlplus. ERROR:
bash: sqlplus: command not found.
0
 

Author Comment

by:missymadi
ID: 24339071
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?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24339125
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
0
 
LVL 1

Expert Comment

by:teixeirap
ID: 24339128
This is how I would execute a script:
$sqlplus /nolog
SQL> conn sys@mydb1 as sysdba
Enter password:
Connected.
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"

0
 

Author Comment

by:missymadi
ID: 24339181
I tried teixeirap's example and entered password and got :
ERROR:
ORA-12504 TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
0
 
LVL 1

Expert Comment

by:teixeirap
ID: 24339310
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.
0
 

Author Comment

by:missymadi
ID: 24339510
teixeirap,

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

Accepted Solution

by:
teixeirap earned 500 total points
ID: 24340129

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
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question