Solved

Linux, Oracle 11g AND Run .sql scripts

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

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 47

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

895 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

14 Experts available now in Live!

Get 1:1 Help Now