Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

Filter by
Sorted by
Tagged with
0
votes
1answer
27 views

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

I need steps how to install Oracle's sqlplus on a Mac.
0
votes
1answer
32 views

How to quote a SQL*Plus identifier in a bash script?

I get "ORA-00904: "MEMORY_TARGET": invalid identifier" no matter how I try to quote the identifier in the following command: MEMORY_TARGET=$(su - orat01 -c " echo 'select ...
1
vote
1answer
34 views

Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?

I am trying to have my entire remote DB backup made to my local server. Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ ...
0
votes
1answer
80 views

Direct, simple SQL query results in “ORA-01406: fetched column value was truncated”

All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
-1
votes
1answer
38 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
62 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
32 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
18 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
30 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
126 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
72 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
2k 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
233 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
70 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
141 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
59 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
32 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
125 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
57 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
761 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
146 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 command ...
0
votes
0answers
94 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
734 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
863 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
167 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
114 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
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
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
132 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
554 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
93 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
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> ...
1
vote
2answers
327 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
58 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
440 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
111 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
5k 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
2answers
648 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
418 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
765 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
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
147 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
699 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
297 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
73 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 ...

1
2 3 4 5