Questions tagged [sqlplus]
Command-Line Utility Program for Oracle
0
votes
0answers
28 views
Oracle CLOB contains html - export to csv
I have a table that contains html formatted text in Oracle. I'm trying to export that to a csv but can't use the built in SQL Developer Export features because of character limits.
I've tried almost ...
1
vote
1answer
95 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
52 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
38 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
116 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
28 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
174 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
88 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
46 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
52 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
59 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
2answers
184 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
1answer
540 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
103 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
1answer
58 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
214 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
214 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
62 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
185 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
92 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
737 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
266 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
60 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 ...
0
votes
0answers
27 views
Add multiple rows in a single row with different columns with a date field
This question comes from another one which was solved
Question link
In this case I would like to add a new field to the table: date_reading
Read_Id Register_Id Date
011 331 ...
0
votes
0answers
13 views
how to get output in different columns for a sql select statment running through a shell script?
Currently getting all my data in one column. Formatting used in the script:
set lines 500
set pagesize 100
set serveroutput on size 1000000;
set verify off;
set wrap on;
set pagesize 1000;
set ...
2
votes
2answers
56 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
82 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
50 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
4k 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. ...
0
votes
1answer
268 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 ...
0
votes
3answers
130 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 : ...
1
vote
2answers
3k views
System account locked, insufficient privileges to log in as sysdba
I've researched the web but I could not found exactly the same situation than mine so I'm posting this message.
I'm connecting via Remote Desktop Connection to a remote server (Win2k3 Server). This ...
1
vote
2answers
536 views
Empty v$asm_diskgroup with sqlplus
I have an Oracle Database instance installed under an OL7. It is a 12c2.
Oracle GI also installed and my database has some files stored in ASM.
I am using sqlplus to connect to the database instance ...
0
votes
1answer
684 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
59 views
Difference between init.ora db_name and query in sqlplus
I have a question about the content of the file init.ora, according to oracle documentation.
DB_NAME specifies a database identifier of up to 8 characters. This parameter must be specified and must ...
0
votes
2answers
6k views
Oracle: spool data to different files depending on where condition (loop)
I am trying to get my way around something like this
declare
filename varchar2(100);
begin
for condition in (select fieldN from mytable) loop
filename := 'data_for_value'||condition.fieldN||'...
-1
votes
1answer
633 views
Create new control files using SQLPLUS
down vote
favorite
I need help with creating controlfile using sqlplus. I have used next steps, I altered spfile, shutdown database, startup nomount, used next command to create controlfile but it ...
0
votes
1answer
33 views
How to add table to database inventory in sqlplus oracle 12c
Problems
How to create database Inventory database and inside it table Items in sqlplus
oracle 12c .
Details
Im beginner on Oracle 12c release
i setup oracle and i create database Inventory
Now ...
0
votes
1answer
116 views
How to use SQL*plus from standard user in Windows Vista to run sql query to Oracle10g on my server?
i have a bach file it contain:
@echo off
sqlplus x/y@z @run.sql
for execute this code correctly i have to execute this file from administrator user from my client, for some reason i need to run ...
0
votes
2answers
1k views
Spool Unicode data in sqlplus to ISO-client without character set conversion
I'm currently facing the following problem:
We have an Oracle 11g database with character set AL32UTF8, and an sqlplus-client with NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1.
Stored in the database are ...
2
votes
1answer
3k views
How to install Oracle sqlplus, rman utilities on local Windows workstation?
I am the administrator for our organization's Oracle database. I would like to install sqlplus and rman client utilities on my local workstation so I can administer the database without having to ...
0
votes
2answers
44 views
Column names display issue in terminal
I am using SQL*Plus and connected to the HR schema.
When I do SELECT * FROM tab; it gave me three column names displayed in different lines.
Is there a way to correct it?
0
votes
1answer
170 views
limiting db user password to 3 days
Can any one help me with this query?
I want to change expiry date for db user to 3 days from current date, but I am not getting the query for the same.
I have installed Oracle Express 11g, it is ...
1
vote
1answer
1k 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 ...
0
votes
1answer
153 views
As an application what are differences between sqlplus.exe and sqlplusw.exe?
Currently i am using Oracle 10g and i can use both sqlplus and sqlplusw. But is there any difference in using them. And, is any of the is deprecated in newer versions?
-1
votes
1answer
4k views
sqlplus / as sysdba is not working in windows, ERROR: ORA-12560: TNS:protocol adapter error
I tried to login using sqlplus / as sysdba in Oracle 11g, but it is asking for a password. So I tried to login using sqlplus "/ as sysdba" since I'm using Windows. But it is showing the following ...
1
vote
1answer
3k views
How to preserve sqlplus session in shell script
I am trying to insert queries from a sql file in shell script using sqlplus.
Now, some queries can fail because of incorrect schema.
So I want to commit all transactions only if all queries succeed.
...
1
vote
1answer
667 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: ...
0
votes
2answers
452 views
Pass multiples values to script into sqlplus in Oracle
I want to execute the same sql script passing one text every time but I don't know how to it. I want to do it into sqlplus.
With this select I have the values but I don't know how to pass this ...
0
votes
1answer
47 views
Confirmation of SQL statement disappeared
Until today after every SQL statement eg. CREATE user t identified by t; I had a confirmation of the execution "User created." Now I have nothing (no confirmation) but if I check if the user exists it ...
