How can I kill all sessions connecting to my oracle database?
I need an sqlplus script to quickly kill off all external sessions connecting to my oracle database without the supervision of and administrator. ...
sqlplus
SQLPlus settings to generate tab-separated data file
Anyone have a good set of sqlplus configuration directives to help transform a given sql query into nicely tab separated output for pulling into a spreadsheet or further processing? ...
How do I ignore ampersands in a SQL script running from SQL Plus?
I have a SQL script that creates a package with a comment containing an ampersand (&). When I run the script from SQL Plus, I am prompted to enter a substitute value for the string starting with &. How do I disable this feature so that SQL Plus ignores the ampersand? ...
SQLplus script to format text as numeric
I am selecting rows from a table, but some of the columns are a text type, but they always have numeric data in them. How can I format them as numbers? e.g. column quantity heading 'Quantity' format 999,999 However, since the column in the table is text, the numeric formatting is ignored. ...
As System in Sqlplus, how do I query another user's table?
According to select name from system_privilege_map System has been granted: SELECT ANY TABLE (and lots of other * ANY TABLES) Plainly running SQL>select * from the_table; select * from the_table; * ERROR at line 1: ORA-00942: table or view does not exist nets the given response. I can log in as that user and run the s...
How do I display the full content of LOB column in Oracle SQL*Plus?
When I try to display the contents of a LOB (large object) column in SQL*Plus, it is truncated. How do I display the whole thing? ...
How to insert a string which contains an "&"
How can I write an insert statement which includes the & character? For example, if I wanted to insert "J&J Construction" into a column in the database. I'm not sure if it makes a difference, but I'm using Oracle 9i. ...
How do I format my oracle queries so the columns don't wrap?
I've tried this, but it doesn't work: col * format a20000 Do I really have to list every column specifically? That is a huge pain in the arse. ...
How do I Suppress "PL/SQL procedure successfully completed" message in sqlplus?
Is there a way that you can have SERVEROUTPUT set to ON in sqlplus but somehow repress the message "PL/SQL procedure successfully completed" that is automatically generated upon completed execution of a plsql procedure? ...
SQL select statement in UNIX inside/within IF..THEN statement.
Hi - I just want ask the steps when trying to create a simple SQL select statement in UNIX inside/within IF..THEN..FI statement. I know how to use the 'select' and 'if..then' statements in SQL*Plus, but I'm having a difficulties having a UNIX script to point to variables: If 'ABC' to 'Select...' Example: if [ "$?" = 'ABC' ] then ...
Is there a good alternative to SQL*PLUS for Oracle?
I am not a fan of using SQL*PLUS as an interface to Oracle. I usually use yasql, but it hasn't been updated since 2005 and can do with some improvements. A quick Google search shows yasql and SQLPal. I am using linux, so SQLPal is not an option. Are there any alternatives out there, or am I stuck with an interface that I do not lik...
Does SQLDeveloper support executing scripts?
I was trying to follow some instructions today, and it starts with the comment REM In SQLPlus I manually copy in each line and execute it. That's nice, I don't have SQLPlus, I have SQLDeveloper. The lines that were pasted in were of the type: @\\server\dir\dir\dir\commandfile1.txt; COMMIT; ...etc. It didn't like it when I tri...
Need for troubleshooting in UNIX w/ shell script program and IF..THEN command.
Since you guys are having lots of questions or not enough info to work with, below is proprietary information and as such be careful as you can. The question – (please refer to if [$7 -eq "AAA"]...then statement - in bold ) has a problem with an output, where I can only output/print count of 'AAA' regardless the variable coming in eith...
connecting to remote oracle via cygwin sqlplus
I'm trying to use cygwin sqlplus to connect to a remote oracle installation located at myserver.mycompany.com port 1530. When I try sqlplus [email protected]:1530/orcl I get the error: ORA-12154: TNS:could not resolve the connect identifier specified When I set ORACLE_HOME to /cygdrive/c/oracle/product/10.2.0/client_...
In SQL*Plus, how do I change the prompt to show the connected user and database?
To show, for example.... USER@SID > I thought this was potentially helpful to a few people so I'm going to answer it too! ...
Using a batch file to loop through Oracle SQL scripts
I have two SQL scripts which get called within a loop that accept a number parameter. Here is what I'm currently using: for /l %%i in (1, 1, 51) do ( sqlplus u/p@name @script.sql a%%i.html %%i sqlplus u/p@name @script.sql b%%i.html %%i ) Everything works fine, but it seems like a waste of time and resources to connect twice fo...
sqlplus access and email access using shell script..
I am fairly beginner level at shell scripts and following are the details.. Am looking for the best way to fire sql queries and and carry out some logic based on that data. I've used the following snippet.. shellvariable=sqlplus $user/$passwd <<END select count(1) from table1; end EOF if[$shellvariable -ne 0] then <> fi Is there a be...
combining and joining two tables with different no. of columns and same column name
I tried to combined tables which is fus_shift and root table into a new table which is final table but it outputs like "ERROR at line 2: ORA-01789: query block has incorrect number of result columns". I tried also joining table as my alternative but it also outputs "ERROR at line 3: ORA-00918: column ambiguously defined". Is there anoth...
Preventing sqlplus truncation of column names, without individual column formatting
By default sqlplus truncates column names to the length of the underlying data type. Many of the column names in our database are prefixed by the table name, and therefore look identical when truncated. I need to specify select * queries to remote DBAs in a locked down production environment, and drag back spooled results for diagnosis....
SQL Plus change current directory
How does one change the current directory in SQL Plus under windows. I am trying to write a script with several "@ filename" commands. I know that one can open a script with the File --> Open command, which will change the current directory, but I am looking for a way to do this automatically unattended. Resolution Based on Plasme...