fulltemplate.blogg.se

1. #Sqlplus get sql prompt insert mode how to#
2. #Sqlplus get sql prompt insert mode mac os x#
3. #Sqlplus get sql prompt insert mode code#
4. #Sqlplus get sql prompt insert mode password#

Armed with these techniques you can start developing some very elaborate automation scripts in your Oracle environment. Select inst_id,event,p1raw,max(seconds_in_Wait) max_wait,Īs soon as the script is finished the exec_sql.sh will send us an email with a subject “ mysql_script done date” and pipe the log file generated by the script in the email body for our review.Īnd there you have it – we just went over my favorite ways to utilize sqlplus in shell scripting.

And while the above script is executing we can open another sqlplus session to the same database and execute the following sql to monitor what the script is doing or waiting for: set lines 132 To change the SQL prompt to show the user ID and database (instance) name, Oracle provides a special SQLPlus file called glogin.sql. We are executing a sql script mysql_script.sql and piping it’s output to mysql_script.hn which we then start viewing “live” using tail -f. The solution to this problem is to enhance the standard SQLPlus prompt of 'SQL>' to include the database name. Mailx -s "$ done `date`" $DBA mysql_script.hn & I use this when calling sqlplus from a shell script directly on the Oracle database server because it allows me make a connection using connect / as sysdba and then pass sqlplus some quick commands to process.įor example here’s a quick way to dump an Oracle systemstate in order to Find which Session is Holding a Particular Library Cache Lock ( the example below works on 11g and above): sqlplus /nolog $sqlparams The /nolog tells sqlplus to skip the login and go directly to the sqlplus prompt where you can make a connection.

#Sqlplus get sql prompt insert mode code#

If you save the above code in a script called login.sql and then place this script in the directory where you start sqlplus from – you’ll get the same result. I use a special script to accomplish this – it’s called login.sql and here are it’s contents: set timing on Note that my prompt is setup to display username and TNS_ALIAS that were used to make the connection with – this makes it very convenient when you have multiple terminal sessions opened to different databases and often switch between them.

#Sqlplus get sql prompt insert mode password#

I simply save my very long password in a TextExpander snippet and supply it when sqlplus prompts me for it:

#Sqlplus get sql prompt insert mode mac os x#

This is the method I use to connect to a remote Oracle database from my personal Mac OS X workstation. sqlplus we lost the password and made our connection much more secure because no-one can sniff our password by running ps -ef | grep sqlplus from their terminal. And for this reason I don’t recommend using this format to connect to an Oracle database from a machine that is open to other users/processes. This is also the most insecure way of making a connection because anyone with access to your server where you are executing this command will be able to see the values of your username and password by simply running a ps -ef | grep sqlplus from their terminal.

Now lets dig deep! sqlplus is the most basic way to start sqlplus and connect to a database defined by the TNS_ALIAS in the tnsnames.ora file. I will also assume that you already have sqlplus installed and the basic connectivity to your database is configured using tnsnames.ora file. I will focus on Linux / Unix because this is where my expertise is and where Oracle put most of it’s focus and support (Oracle EL and Solaris). And to make it easier – I’ll go over these use-cases in the context of the different ways we can invoke the sqlplus utility:

#Sqlplus get sql prompt insert mode how to#

These are all very powerful and useful techniques and I’ll show you how to use them in this post. I also wrap the sqlplus script output in a shell function and pipe it’s output to egrep filtering. I often put the output of sqlplus -s in a shell variable and use it for further processing. SELECT MAX(column_id) INTO MaxCol FROM sys.dba_tab_columnsĭbms_output.put_line('CREATE TABLE '||TabRec.table_name) įOR ColRec in ColCur(TabRec.table_name) LOOPĭbms_output.put_line('TABLESPACE '||TabRec.tablespace_name) ĭbms_output.put_line('PCTFREE '||TabRec.pct_free) ĭbms_output.put_line('PCTUSED '||TabRec.pct_used) ĭbms_output.put_line(' INITIAL '||TabRec.initial_extent) ĭbms_output.put_line(' NEXT '||TabRec.next_extent) ĭbms_output.put_line(' PCTINCREASE '||TabRec.pct_increase) ĭbms_output.put_line('PARALLEL '||TabRec.My #1 Oracle DBA tool is sqlplus – I use it to automate DBA tasks directly on the Oracle Server from shell scripts. Use: SYSTEM, SYS or user having SELECT ANY TABLE system privilege %Purpose: Generate 'CREATE TABLE' Script for an existing Table in the database Same as above but generic script found here gen_create_table_script.sql - #