We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

Filter by
Sorted by
Tagged with
81
votes
4answers
726k 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 conenct to that database local_SID = (DESCRIPTION = (ADDRESS =...
47
votes
3answers
461k 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 ...
41
votes
9answers
537k views

Connect to sql plus from command line using connection string

Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c. So how do I need to connect correctly? I used many options like:...
21
votes
14answers
6k views

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 ...
16
votes
3answers
14k 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 ...
16
votes
4answers
12k 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 ...
15
votes
4answers
62k 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 ...
14
votes
1answer
13k 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, ...
12
votes
3answers
32k 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 ...
11
votes
2answers
52k 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?
11
votes
3answers
993 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?
9
votes
1answer
16k 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:\...
7
votes
2answers
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; ...
7
votes
4answers
16k 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 ...
6
votes
4answers
71k 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 ...
6
votes
2answers
47k 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/...
6
votes
1answer
1k 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 ...
6
votes
3answers
5k 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 ...
5
votes
4answers
18k 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 ...
5
votes
2answers
3k 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 ...
5
votes
2answers
11k 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.
5
votes
2answers
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 ----------------------------------------- -------- --...
5
votes
1answer
118 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 ...
5
votes
7answers
53k 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-...
5
votes
1answer
4k 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 ...
5
votes
1answer
842 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 ...
4
votes
2answers
13k views

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

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
4
votes
1answer
18k 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 ...
4
votes
2answers
25k 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 ...
4
votes
3answers
19k 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 ...
4
votes
4answers
15k 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. ...
4
votes
2answers
4k 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 ...
3
votes
2answers
6k 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 ...
3
votes
3answers
112k views

SYSDBA password in Oracle DB

I am connecting to Oracle DB using sysdba: sqlplus / as sysdba What is the default password for sysdba user. I tried in net and found out the default password is change_on_install but it is not ...
3
votes
1answer
33k 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:\> ...
3
votes
2answers
9k 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=*>...
3
votes
2answers
80k 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 ...
3
votes
1answer
2k 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, ...
3
votes
2answers
728 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'...
3
votes
1answer
309 views

Oracle DB Security Issue: Anyone can login using SQL*PLUS, with any username, and any wrong passphrase

I'm using Oracle DB 11g-R2 on CentOS 6.4, and unfortunately there is a terrible issue freaking me out. Anyone can login using SQL*PLUS, with any username, and any wrong passphrase. Consider somebody ...
3
votes
1answer
5k 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?...
3
votes
2answers
16k 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 ...
3
votes
2answers
365 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 ...
3
votes
1answer
569 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 ...
3
votes
3answers
19k 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 ...
3
votes
3answers
40k views

sqlplus: ORA-01017 logon denied from one host but OK from another

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
3
votes
1answer
1k 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 "...
3
votes
3answers
7k views

Login Problem as SYSDBA ORA-01031

I have the following problem: I can connect to an Orcale database as normal user or as sysdba: sqlplus myuser/myPw@myDB sqlplus "sys/sysPw@myDB as sysdba" But when another (Windows-User) is logged ...
3
votes
2answers
10k views

How to create database/tablespace/schema and users for quickstart developers on Oracle 12?

Background I need to create a contained development space for a new application. This requires a user account to connect with all permissions, such as create/drop tables and stored procedures. ...
3
votes
2answers
63k views

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

I am not a professional, sorry. But perhaps you can help me anyway ... I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus spool,...