Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

Filter by
Sorted by
Tagged with
1
vote
3answers
894 views

How can I disable the @ symbol in SQL scripts for sqlplus?

In Oracle's sqlplus tool, when the script contains an @ symbol, it loads an SQL script with that name and executes it. I'm inserting a VARCHAR value which contains some CSS code, so when it encounters ...
0
votes
0answers
104 views

Don't echo a single command in SQL*Plus

If I have a script like this... SET ECHO ON SELECT 'I want the person reading the spool to see this whole query.' FROM DUAL WHERE interesting_logic_ = 'DEBUGGABLE'; SET ECHO OFF PROMPT I only ...
0
votes
2answers
1k views

sqlplus, command line parameters and ORACLE_HOME with spaces

I have noticed strange interaction between ORACLE_HOME and parameter substitution. Here is my test.sql prompt &1 prompt &2 prompt &3 prompt &4 exit The expected behavior: C:\...
6
votes
4answers
91k 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 ...
87
votes
4answers
798k 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 =...
15
votes
3answers
34k 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 ...
0
votes
1answer
201 views

UTL_RAW “invalid identifier” error message

When I execute the following: select utl_raw.cast_to_raw('8') from dual; I receive the error message below: invalid identifier ...while in other PC with the same Oracle version, i.e., 11g, the ...
0
votes
1answer
150 views

How do I know what my username/password is for SQL PLUS? [closed]

I am trying to set up an Oracle SOA Suite environment with docker containers according to this website -> https://svgonugu.com/2018/03/12/docker-containers-for-oracle-soa-suite/ I got to a point ...
11
votes
3answers
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?
5
votes
1answer
41k 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:\> ...
2
votes
1answer
1k views

ORA-48108 / ORA-48140 / ORA-48187 - diagnostic_dest value error(s) - init files / control files

This is all testing / training - none of this is related to a production env Using ORACLE DB 12c + UNIX/LINUX 64-bit on an VM. . Hi Guys! I am running in the below error(s) and seem unable to ...
0
votes
2answers
3k views

Special Character in password

I'm Trying to connect to a remote DB using Sqlplus, but the password contains a special character (!). The SID is working fine, but Sqlplus is complaining about it when I use the ! in the password /...
0
votes
1answer
158 views

Sqlplus not using NLS_LANG env variable

I am trying to login to an Oracle 10g database vía sqlplus. In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish. In the database server my NLS_LANG is ...
1
vote
1answer
619 views

connect to Oracle Cloud from SQLPlus and Instant Client

I want to try to connect with SQLPlus to Oracle Cloud. for regular oracle I can connect from remote machine as follows: IP = 1.2.3.4 PORT = 1521 ServiceName = "ORCL1" Username = "username" Password ...
-1
votes
1answer
182 views

Error while updating a date column using SQL*Plus variable

I am not able to update the date time which I have already stored in a variable and I am trying to update the another record with date time present in that variable. last_update_time=`sqlplus -s user/...
-1
votes
1answer
104 views

How to use mariadb instead of sqlplus using sqlnet configuration files

currently I can access a database on a remote server using Oracle's instant client "sqlplus" The connection is dependent on a couple of configuration files in: $ORACLE_HOME/network/admin which are ...
1
vote
2answers
389 views

Create dynamic folder using SQL*Plus

I need to create directory in Linux with date stamp from SQL*Plus. Is there a command that can be run in *.sql file to create a directory with current date?
0
votes
0answers
2k views

Import .dmp file into Oracle Database

Guys. When I tried to import a .dmp file into a fresh oracle database (sqlplus), I got this error. dump file "exp_tbs_gxplt01282019.dmp" may be an Data Pump export dump file My step is: SQL> ...
0
votes
2answers
59 views

Long query causes problems

I have a huge sql query which causes problems: select distinct subjectnr from proces where (produkttypenr = 1 or produkttypenr = 3 or produkttypenr = 4 or produkttypenr = 5 or produkttypenr = 9 or ...
0
votes
1answer
520 views

When is the best time to use RMAN vs when to use Data Pump? [duplicate]

And also... if I backup a database with RMAN, is it not possible to use impdp to restore small parts of that database?
0
votes
2answers
120 views

Unable to connect oracle 12 database remotely while local connection works

I have two oracle test nodes. On node 2 as root I am able to connect without credential with the command, su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba" Output: SQL*Plus: Release 12.1.0....
0
votes
1answer
2k views

Oracle: Save PDB state for all instances in RAC environment

I found out the following two contradicting statements in the Oracle DBA guide. For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the ...
0
votes
1answer
646 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
0
votes
2answers
856 views

Oracle db impdp command syntax error

I'm trying to run impdp system/password dumpfile=FILE.dmp fromuser=USER1 touser=USER1 directory=$DIRECTORY on oracle server but keep getting following error: LRM-00118: syntax error at '=' at the ...
2
votes
3answers
10k views

using tnsnames.ora and sqlplus client without installing oracle 11g server on linux client [closed]

I want to create a connection to remote Oracle server using SQLplus client and tnsnames.ora without installing Oracle 11g server on the Linux client. I used debian and bash script to generate reports ...
0
votes
1answer
1k views

bind variable and shell scripting with sqlplus

I wish to execute DMLs using bind variable with shell scripts. For example, something like: #!/bin/bash SH_NUM=10 sqlplus -S test_user/test_pass <<EOD var a number; a:=${SH_NUM} insert into ...
0
votes
1answer
158 views

Optimize Oracle 11g SQL*Plus connection for large remote SELECTs [closed]

Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a ...
5
votes
3answers
125k 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 ...
0
votes
1answer
758 views

Spool query output to file (SQLPlus)

This is not a duplicate of "Spool query to file without output on screen" Imagine I am spooling the below in a query: SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "...
0
votes
5answers
331 views

How could I fix this warning?

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 13:08:07 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter user-name: system Enter password: ERROR: ORA-28002: the password will ...
0
votes
1answer
4k views

sqlplus command not found after installing oracle 12c

I have going at it for the last 12 hours... I installed Oracle 12c on rhel7, the first installation didn’t go well so Installed a second one without changing the first installation; I created a new ...
1
vote
1answer
856 views

Check Oracle Service is Started from Batch Command

Uses: Oracle9i Standard Ed; I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem. After the System had rebooted, I have ...
2
votes
2answers
13k views

Set TERMOUT OFF is not working when I am using sqlplus in command prompt?

I am using the sql statement listed below in my spool sql script, and I am executing this script from sqlplus. But in terminal query output is still coming and I am using sqlplus in command prompt. ...
-2
votes
1answer
1k views

“ORA-02270: no matching unique or primary key for this column-list” on table creation [closed]

The error is in the Items table. What could be the reason, how to fix it? CREATE TABLE Users ( userid NUMBER(6) NOT NULL, username VARCHAR2(150) NOT NULL UNIQUE, email VARCHAR2(200) NOT ...
3
votes
3answers
30k 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
0
votes
2answers
1k views

Create tablespace “error in creating database file, access denied (OS 5)”

I try to create a database file in this location I've checked the route and everything, I've tried several times to create the tablespace like this... ...but as you can see, I was just lead to ...
-1
votes
1answer
626 views

ORA-01304 not available

I have been getting this error since I tried to the following command: alter system set sga_target = 1512m scope=spfile; I get the following message when I try type any command in sqlplus: SQL> ...
55
votes
3answers
518k 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 ...
2
votes
5answers
71k views

ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins

I've a strange behaviour of Sql Developer with OS Authentication, 4.1.3 32/64bit, gives ORA-01017 while sqlplus is logging in without any problem. This is the example answer sqlplus gives me: ~ ...
2
votes
2answers
75 views

Add multiple rows in a single row with different columns

I want to join two tables (they are called Registers and Reads). As a result I would like to obtain 4 columns, corresponding to Registers ID. According to the next example, by joining the tables, I ...
0
votes
1answer
181 views

XML parsing failed in the sqlplus

I get following error when executing the query Invalid token in '[count(../[name(../)=name(.)])=count(../) and count(../*)>1' However when installing the Itstar_xml_util package, it asks for two ...
0
votes
2answers
63 views

Does the Oracle database username exist for each Linux account?

Does the Oracle database username exist for each Linux account? The former employee left the company and can not find the details at this time. I can log in to the Linux root account and run a DB ...
47
votes
9answers
621k 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:...
1
vote
1answer
1k views

Login into Oracle Database Error

I had installed Oracle 12 to my Linux box. I tried to login using sqlplus but it prompt out this error at below. How to solve it? Enter user-name: SYSDBA Enter password: ERROR: ORA-01034: ...
1
vote
1answer
3k views

SQL*Plus TNS:protocol adapter error [closed]

I am using this system on which everything was already configured. I want to connect to local db on SQL Plus. When I enter user-name scott and password tiger, it gives ORA-12560:TNS:protocol adapter ...
6
votes
2answers
56k 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/...
0
votes
3answers
204 views

Error while exporting data to flat file from database using sqlplus in UNIX

Trying to export data from a table to .dat file in a unix script using the SPOOL command. However on execution only ~17k data is exported to file out of 91K data with an error in end of the file : ...
3
votes
2answers
8k 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 ...
2
votes
2answers
4k views

Oracle 12c: Password File Questions

For one of my assignments I have been attempting to demonstrate password file functionality. However, I have run into some unexpected behaviors. I have tried searching around for answers but can't ...
3
votes
3answers
23k 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 ...