Skip to main content

Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

Filter by
Sorted by
Tagged with
107 votes
4 answers
986k views

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to connect to that database local_SID = (DESCRIPTION = (ADDRESS = ...
Louis Rhys's user avatar
  • 1,265
69 votes
7 answers
855k views

Connect with sqlplus from the command line using a connection string

Let's say I have an Oracle database. I have a username = x, password = y, database = z, port = a, SID = b, and Hostname = c. So, how do I connect correctly? I used many options like: sqlplus x/y@'(...
Edvinas's user avatar
  • 691
62 votes
3 answers
612k views

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the ...
Kshitiz Sharma's user avatar
51 votes
4 answers
98k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as an administrator using SQL Plus: sqlplus sys as sysdba sqlplus system/manager These accounts should be used for different purposes, I suppose. Which tasks ...
Lazer's user avatar
  • 3,361
21 votes
14 answers
7k views

Comfortable sqlplus interface? [closed]

I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available. What is a ...
Sebastian Roth's user avatar
17 votes
3 answers
15k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
Lazer's user avatar
  • 3,361
16 votes
4 answers
14k views

What is the significance of a semicolon at the end of SQL*Plus commands?

Some statements like create table, insert into etc take a semicolon at the end: CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name ...
Lazer's user avatar
  • 3,361
16 votes
4 answers
69k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
Leigh Riffel's user avatar
  • 23.9k
15 votes
3 answers
73k views

How to set an Oracle user's default schema?

I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?
user avatar
15 votes
3 answers
38k views

Is there any difference between Rem and -- comments for SQL*Plus?

On SQL*Plus prompt, both Rem and -- qualify as comment indicators: Rem this is a comment -- this is also a comment create table emp ( id number primary key, name cvarchar2(40)); Is there any ...
Lazer's user avatar
  • 3,361
14 votes
1 answer
14k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...
bernd_k's user avatar
  • 12.3k
13 votes
7 answers
32k views

"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

Some background: I am attempting to connect to an Oracle database. I have a username, password, IP address, port, and service name. I only have access to this database from an Ubuntu EC2 instance on ...
Karl Johnson's user avatar
11 votes
3 answers
1k views

Do I have to retype information every time I make a mistake in SQLPlus

If I make a mistake in SQLPlus while inputting an 'insert into' command, is there a way I can bring this information back up in order to edit it?
Bambi Bowman's user avatar
10 votes
4 answers
191k views

SYSDBA password in Oracle DB

I am connecting to Oracle DB with: sqlplus / as sysdba What is the default password for the sysdba user? I tried in net and found out the default password is change_on_install but it is not working ...
Learner's user avatar
  • 211
10 votes
1 answer
23k views

SQL*Plus, @, and relative paths

Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot. For example, under x:\...
René Nyffenegger's user avatar
8 votes
1 answer
68k views

How to connect to oracle 12c from sqlplus without password in command line

This may seem as weird question: If i have user called demo with password Pass1234 When connecting to oracle 11g I can run sqlplus like this with password in command line: on DB host: C:\> ...
SHR's user avatar
  • 874
7 votes
2 answers
2k views

If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?

I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database: create or replace function tail_f return varchar2_tab pipelined as n number; ...
René Nyffenegger's user avatar
7 votes
2 answers
87k views

Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

I installed Sql*PLUS on a cloud Oracle Linux Environment in order to access an remote database. I used the installation instructions provided as an answer here: https://stackoverflow.com/questions/...
beckah's user avatar
  • 201
6 votes
4 answers
21k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
Darryl Braaten's user avatar
6 votes
4 answers
138k views

sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

There are similar questions asked and answered in this network related to the error message given in the question, however this problem is a little different from each. I have installed oracle 12c in ...
pranphy's user avatar
  • 165
6 votes
1 answer
2k views

sqlplus consuming 99% CPU

Oracle's installed on one server, I'm running sqlplus on a different (application) server. I've recently encountered a problem getting sqlplus to do anything; it simply spins, consuming 99% CPU even ...
searlea's user avatar
  • 201
6 votes
3 answers
6k views

How do I execute an Oracle SQL script without sqlplus hanging on me?

For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.: If the script contains any named substitution variable ...
Martin's user avatar
  • 2,420
5 votes
2 answers
4k views

Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition

I have the following table that I want to redefine: create table tq84_redefinition ( id number primary key, ts1 timestamp not null, ts2 timestamp ); Please note the not null constraint on the ...
René Nyffenegger's user avatar
5 votes
2 answers
13k views

Calling SQL Loader From SQL Plus

I was wondering if there was a way I can execute a SQL Loader script from SQL Plus. We are using Oracle 10g.
Scott's user avatar
  • 657
5 votes
2 answers
2k views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- --...
Lazer's user avatar
  • 3,361
5 votes
1 answer
568 views

Result on execution of any SQL is number 2

After successful execution of any sql in oracle sql command line it shows 2 like i want to see the table or description of any table it show 2 instead of showing the table. SQL> select * from dual ...
piyush's user avatar
  • 61
5 votes
1 answer
108k views

SP2-0667: Message file sp1<lang>.msb not found sqlplus oracle

I've a linux RedHat which contains oracle database. I setted all environnement variables, but when I running sqlplus command I've this error : SP2-0667: Message file sp1<lang>.msb not found ...
Couim's user avatar
  • 253
5 votes
2 answers
27k views

ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS

I am using Toad to connect my database and its working fine for me . But when i try to connect with the same credential , i am getting the error message as shown below: ORA-12154 TNS: could not ...
Gaurav Soni's user avatar
5 votes
1 answer
5k views

sqlplus Setting the exit code of a script

How can I set the exit code of a script? The following doesn't seem to work WHENEVER SQLERROR EXIT 5 DECLARE retCode number := 0; BEGIN retCode := 30; END; / EXIT :retCode ; Gives the ...
Scott's user avatar
  • 657
5 votes
1 answer
914 views

Execution plan using B+ tree index, but also sorts

I'm using Oracle sqlplus. And I have the following query: SELECT fooID from foo MINUS SELECT fooID from bar; I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
John Fr.'s user avatar
5 votes
9 answers
61k views

Message file not found when starting SQLPlus

I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error: Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found SP2-...
synapse's user avatar
  • 171
4 votes
2 answers
14k views

Why can't we specify group functions in Oracle in the where clause?

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
Lazer's user avatar
  • 3,361
4 votes
1 answer
22k views

Set encoding from within SQL*Plus

I have a large set of files with SQL commands that I run with a SQL*Plus script that uses the @@ operator. I run the script on third-party computers (or even send it by e-mail) so I'd like to make it ...
Álvaro González's user avatar
4 votes
1 answer
6k views

halt on error in a sqlplus script

I am deploying pl/sql code using several sql files that are called with @@file. If a package got a compilation error the script continues to the end. Is there a way to stop on every compilation error?...
Jan's user avatar
  • 141
4 votes
3 answers
21k views

Oracle Enterprise Manager (OEM) locking SYSMAN account

Why does Oracle Enterprise Manager reset my SYSMAN account to LOCKED(TIMED) status? I've tried rebuilding Enterprise Manager, and re-installation of Enterprise Manager, which always works, until the ...
Brian Deragon's user avatar
4 votes
3 answers
28k views

How can sqlplus knows where ORACLE_SID point?

As I understand, SID is unique value to identify an Oracle database instance. And I can set default database instance sqlplus use by changing ORACLE_SID environment variable. But how can sqlplus ...
SangminKim's user avatar
4 votes
4 answers
18k views

Install Oracle 10.2.0.4 on Windows Server 2012 R2

I have problem with installing Oracle 10.2.0.4 on Windows Server 2012 R2. We are running old 10.2.0.4 instance on Windows Server 2008 R2. Now I have to migrate it to the Windows Server 2012 R2. ...
teo's user avatar
  • 401
4 votes
2 answers
7k views

How does SQL*Plus connect to Oracle without a listener?

sqlplus can be used even if there is no listener in the system, or if it isn't running. How does SQL*Plus connect to Oracle without a listener? I can connect any user using SQL*plus even with no ...
peterh's user avatar
  • 2,089
3 votes
1 answer
64k views

Cannot connect database as sys as sysdba using sqlplus

I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below. However, if I ...
Khang Mai's user avatar
3 votes
3 answers
49k views

How in Oracle can I export package that will have package and body?

I'm using putty. How in Oracle can I export package and its body to a file in present directory using spool? Thanks
catinredboots's user avatar
3 votes
2 answers
87k views

Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server

I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos. $lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production ...
Auguster's user avatar
  • 143
3 votes
2 answers
10k views

How to erase in sqlplus with backspace?

Using SQL*PLUS, I cannot use the backspace button to erase content during a line command. When I hit backspace, instead of erasing it writes ^H Is there a way to configure backspace so it works as ...
A_V's user avatar
  • 1,364
3 votes
2 answers
13k views

Using transactions in sqlplus

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction: dbpool.production=> begin; BEGIN dbpool.production=*>...
Wayne Conrad's user avatar
3 votes
1 answer
4k views

How to Substitute Variables in PostgreSQL

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL? INSERT INTO control_threshold ( threshold_id, group_name, description, sql, ...
DUnkn0wn1's user avatar
3 votes
2 answers
812 views

How to create a user with a particular user_id?

Is it possible to specify the user_id of a user while creating it? SQL> create user user1 identified by user1; User created. SQL> select user_id, username from all_users where username='USER1'...
Lazer's user avatar
  • 3,361
3 votes
1 answer
14k views

How to install Oracle's sqlplus on a Mac? [closed]

I need steps how to install Oracle's sqlplus on a Mac.
OSGI Java's user avatar
  • 193
3 votes
2 answers
19k views

Encoding problems with sqlplus, Linux

I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page. select * from ...
ivkremer's user avatar
  • 155
3 votes
2 answers
377 views

Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output?

Just again looking at some log from an oracle script, where the developer intended to comment out some part not to add documentation. I don't want that a script containing code like this /* select ...
bernd_k's user avatar
  • 12.3k
3 votes
1 answer
2k views

How to make sqlplus prompt longer than 50 chars?

I've set a prompt in my sqlplus glogin.sql a little bit long, mainly because of some internal variables and control sequence. Starting sqlplus I get the following error: String beginning "...
peterh's user avatar
  • 2,089
3 votes
1 answer
592 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...
demongolem's user avatar

1
2 3 4 5 6