Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

SQL*Plus provides basic command-line interface to Oracle instances. Programmers and DBAs commonly use it as the default available fundamental interface in almost any Oracle software installation, which includes running SQL and PL/SQL commands interactively or from a script.

Commands run from sqlplus fall under three(3) categories:

  • SQL Statements
  • PL/SQL Blocks
  • SQL*Plus internal commands

For more information see the 11g SQL*Plus User's Guide and Reference (pdf).

284 questions
109
votes
4 answers

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 = (PROTOCOL= TCP)(Host= hostname.network)(Port=…
Louis Rhys
  • 1,285
  • 4
  • 11
  • 8
71
votes
7 answers

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…
Edvinas
  • 711
  • 1
  • 6
  • 3
63
votes
3 answers

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 output to less I run following statements in SQLPlus…
Kshitiz Sharma
  • 3,357
  • 9
  • 33
  • 35
51
votes
4 answers

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 are these two schemas meant for? When should I…
Lazer
  • 3,361
  • 15
  • 43
  • 53
21
votes
14 answers

Comfortable sqlplus interface?

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 good replacement / extension for sqlplus? Could be…
Sebastian Roth
  • 1,356
  • 3
  • 15
  • 23
17
votes
3 answers

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 loop, is it not SQL anymore? PL/SQL is an extension…
Lazer
  • 3,361
  • 15
  • 43
  • 53
16
votes
4 answers

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 VARCHAR2(25) CONSTRAINT emp_last_name_nn_demo…
Lazer
  • 3,361
  • 15
  • 43
  • 53
16
votes
3 answers

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?
Will K
16
votes
4 answers

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 statements each with blank lines, semicolons,…
Leigh Riffel
  • 23,884
  • 17
  • 80
  • 155
15
votes
3 answers

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 difference at all between the two commenting techniques?
Lazer
  • 3,361
  • 15
  • 43
  • 53
14
votes
1 answer

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, Item2 NUMBER ); For some reason I have to add…
bernd_k
  • 12,369
  • 24
  • 79
  • 111
13
votes
7 answers

"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 AWS because the Oracle database is located inside…
Karl Johnson
  • 371
  • 1
  • 2
  • 6
11
votes
3 answers

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
  • 111
  • 3
10
votes
4 answers

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 for me. Does Oracle DB maintain some file or DB…
Learner
  • 211
  • 1
  • 3
  • 5
10
votes
1 answer

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:\some\where I have the following directory…
René Nyffenegger
  • 3,763
  • 7
  • 32
  • 39
1
2 3
…
18 19