Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Command-Line Utility Program for Oracle

0
votes
0answers
11 views

How to SPOOL to a file in current directory

I have this problem where I want to SPOOL to a file in current directory. I want to run a .sql script to anywhere but it has to be in SPOOL to scripts/current/directory/tmp/output_file.txt. Remember ...
0
votes
1answer
33 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
47 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
32 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
34 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
50 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
63 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
61 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
134 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
32 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
25 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
55 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
61 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
0answers
360 views

Oracle 12c | Solaris | ORA-12560: TNS:protocol adapter error

I tried to login using sqlplus on 12c on solaris 11.3 but I am facing the below issue: sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 10 14:44:41 2018 Copyright (c) 1982, ...
0
votes
2answers
47 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
2k 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
160 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
83 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
2k 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
277 views

Empty v$asm_diskgroup with sqlplus

I have a Oracle Database instance installed under a OL7. It is a 12c2. Oracle GI also installed and my database have some files stored in ASM. I am using sqlplus to connect to the database instance ...
0
votes
1answer
403 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
53 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
4k 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
425 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
32 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
82 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
881 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
2k 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
42 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
112 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
786 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
127 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
2k 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
2k 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
454 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
369 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
46 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 ...
-1
votes
1answer
407 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> ...
0
votes
1answer
9k views

ORA-14400: inserted partition key does not map to any partition

I try to create table from ORACLE setup script After I run the script SQL> @home/oracle/Desktop/Setup.sql Following output appears: Table dropped. Table created. Index created. INSERT INTO tab1 ...
5
votes
4answers
9k 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 ...
0
votes
1answer
661 views

How to spool output from SQL*PLUS to appear in single line with 1 space in between them?

I have a table with 20 rows. I want to spool the rows into a text file where rows come side by side in one line and have one space in between to keep them separated. I intend to create a tar.gz file ...
0
votes
0answers
120 views

How to turn on the expanded mode in Oracle SQL*Plus?

As in the switch \x of PostgreSQL. For more information on the PostgreSQL expanded mode, fetch https://serverfault.com/questions/618642/psql-expanded-mode-equivalency-for-mysql. Both PostgreSQL and ...
0
votes
2answers
2k views

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

I'm not able to alter the SORT_AREA_SIZE in my database.
0
votes
3answers
1k views

How to check oracle error description in sqlplus?

I'm trying to find the oracle error's description in SQL*PLUS. Similar command in DB2 as ? SQLXXXX Where XXXX is the error number.
0
votes
1answer
530 views

can not connect to oracle database with ip address

I am new to oracle database, I have installed Oracle 11.g on redhat 5.6, I have a database that I can connect to it using the following command: export ORACLE_SID=test sqlplus user/passwd but not ...
0
votes
1answer
123 views

Why does SQL Developer update Oracle Data Dictionaries while SQL*Plus does not?

I have the following code stored in a script file create or replace package test_record is type t_test_rec is record( name varchar2(64) ,value ...
0
votes
2answers
318 views

How to prompt sql if there is no exception

I'm new to SQL advance methods. I'm trying to do some error handling after checking for exceptions. DECLARE value_invalid exception; BEGIN EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g'; ...
0
votes
1answer
470 views

Problems connecting remotely to a RAC DB via sqlplus

When I create a user in an 11204 single instance DB and afterwards grant the sysdba privilege to that user I can connect remotely without any problems using: sqlplus testuser/testuserpwd@testdb as ...
0
votes
1answer
9k views

ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version 11.2.0.0.0

I'm trying to mount my database but sqlplus returned the following error : SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 ...