Questions tagged [sqlplus]
Command-Line Utility Program for Oracle
235
questions
-1
votes
1answer
35 views
Database not opening, after messing with datafiles [closed]
I was exercising some database recovery exercises
First I had made a backup of my datafiles, then I moved them to another directory to test if I could recover them or not.
When I was recovering them ...
1
vote
0answers
59 views
How to trim trailing spaces from headers in Oracle 12.1 SQLPLUS
I am creating a pipe-delimited file from a SQLPLUS query (Oracle 12.1), and the recipients are requesting that I trim the trailing spaces from the header entries. That is, the current output file ...
0
votes
1answer
31 views
Scheduled Job Does not work in oracle SQL*PLUS
I am just a beginner in Oracle and can't seem to get this job running :-
I want to delete old rows from my database. And created a job as follows :-
CREATE OR REPLACE PROCEDURE delete_trips
AS
...
1
vote
1answer
16 views
The full contents of the bind-variable are not displayed in SQL Plus
I wrote a big query in bind-variable. I want to print it in SQL Plus, but the text doesn't show, only small part of it.
1
vote
1answer
28 views
Big string in SQLPlus
I need to write a Select query in bind-variable and this query has more than 2500 symbols.
Variable sql_txt clob;
Exec :sql_txt := 'Select .....';
But SQL Plus support only <2499 symbols.
How ...
0
votes
1answer
105 views
Oracle: Connects with sqlplus but not from perl
I need to connect to an Oracle database from a perl script, it works in my tests and when using sqlplus, but not in production. This is a summary of the situation:
Oracle 11g XE (11.2.0.2.0) : (Test ...
0
votes
0answers
56 views
How to mount current control file in ORACLE 12C1 RAC
I am trying to take a backup of my ORACLE 12C1 RAC setup and faced following error:
RMAN> BACKUP CURRENT CONTROLFILE FORMAT '+FRA/RMAN/cold/ORCL/cntrl_%s_%p_%t';
Starting backup at 16-DEC-19
...
1
vote
1answer
1k 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 ...
-1
votes
1answer
170 views
Oracle database - how to switch instance
I'm totaly new in oracle, so sorry for my probably stupid question... I have two instances on oracle server, when I'm connecting to the server via sqlplus in default opens connection to instance X but ...
0
votes
2answers
67 views
How can the TNS connect identifier passed to SQL*Plus be accessed from the run script for reconnecting?
I wonder how the TNS identifier can be used within a SQL script. In this example
$ sqlplus MY_USER/pswd@TNSID @script.sql par1 par2
I'd like to get TNSID as a string in order to use it from script....
1
vote
2answers
123 views
Can sqlplus fetch more than one row at a time?
We have a task running every day dumping results of a query into a CSV-file -- as the stdout of sqlplus running a query.
The number of records is many millions (don't ask), and the job takes over two ...
1
vote
1answer
53 views
How to avoid split in output in sqlplus
I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script.
Unfortunately, few PDB NAMEs are split/broken in the output(for example,...
1
vote
0answers
27 views
Equivalent in SQL*Plus of sqlite3's “line mode”
One of the output display modes supported by the default sqlite3 text client is the so-called "line mode". In this mode, the output of SELECT queries is displayed "one value per line". Here's an ...
0
votes
2answers
111 views
SQL*Plus - interactive mode on windows
How can I issue commands through SQL*Plus on Windows?
In Linux is like this:
sqlplus -s /nolog<<EOF
conn username/password@database
EXECUTE some_proc;
EXIT;
EOF
And Windows?
0
votes
0answers
41 views
What gets executed immediately after logging into an Oracle database?
When I login with SQL*Plus, without executing a command, I always get the following error:
$ sqlplus db_user/db_pass@localhost/XE
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Aug 7 10:33:03 2019
...
1
vote
0answers
26 views
How does Oracle authenticate a user?
I'm learning Oracle and below is my testing of starting up an Oracle instance and make the database available.
oracle:[~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 5 09:29:30 ...
0
votes
1answer
601 views
Oracle user password with space character in it
I connected to an Oracle 19c database and executed the following SQL:
alter user sys identified by "a b" container=all;
It executed successfully. However, after that I can't connect to Oracle using ...
2
votes
0answers
121 views
In SQLPlus, why the first run of the command doesn't work?
I use SQLPlus connecting to an Oracle database. See below snapshot. Why the first run of the command show con_name doesn't work and the second run works?
Edit
I have to admit that I typed the ...
0
votes
0answers
91 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 ...
1
vote
3answers
649 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
2answers
739 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:\...
0
votes
1answer
141 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
99 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 ...
2
votes
1answer
879 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
2k 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
118 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
515 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
86 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 ...
0
votes
0answers
1k 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> ...
1
vote
2answers
305 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
2answers
56 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
396 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
108 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
3answers
1k views
Leading zeros are truncated when I spool to CSV
I have spooled a file to CSV using sqlplus. Everything works great but there is one issue. I have mobile numbers which start with 0, for example 04589 and 02586.
After the spooling is complete, 0s ...
0
votes
3answers
4k views
sqlplus SP2-0341 (line overflow during variable substitution)
I want to export an Oracle database to a file and i'm using SQLDeveloper on Windows for that (the resulting SQL text file is about 35MB). This results in very long lines.
I hit a snag when importing ...
0
votes
1answer
591 views
SQLplus run without admin account
I have a weird problem.
I login to a pc using non admin user. Then I installed sqlclient 64 bit for oracle 11.2. Installation went fine, no errors.
Then i tried to login using sqlplus from command ...
0
votes
2answers
359 views
sqlldr not recognizing new line/row
Our organization switched to a new tool and now our text files are generated with only a trailing CR instead of a CR & LF. Now sqlldr thinks all of the data in the file is 1 row. How can I fix ...
0
votes
2answers
725 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 ...
0
votes
1answer
942 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
139 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 ...
0
votes
1answer
639 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
272 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
3k 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 ...
-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 ...
0
votes
1answer
127 views
Change of NLS_LANG
Until not long ago, the NLS_CHARACTERSET and the NLS_LANG were both ISO88591. We deliver to production some scripts encoded in ISO88591.
Since they changed their NLS_LANG in utf8, they have a problem ...
2
votes
2answers
71 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
149 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
62 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 ...
1
vote
2answers
11k 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. ...
1
vote
1answer
470 views
How can I be specific about which CDB I want to connect to wrapping a PDB?
I'm quite new to Oracle. I created a CDB like cdb1 and then a PDB within like pdb1. Now I can connect to my PDB using something like:
sqlplus user/password@server:port/pdb1
But can't I have two PDBs ...
