Solved

Linux, Oracle 11g AND Run .sql scripts

Posted on 2009-05-08
16
1,555 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:missymadi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
you have to use the @ in front of the script name
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Expert Comment

by:teixeirap
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
teixeirap,

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

Accepted Solution

by:
teixeirap earned 500 total points
Comment Utility

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now