23 votes

"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

After two hours, I figured it out. Oracle's documentation for the error didn't seem terribly descriptive at first: ORA-24454: client host name is not set Cause: The network host name in files like /...
Karl Johnson's user avatar
12 votes
Accepted

How to connect to oracle 12c from sqlplus without password in command line

This answer based on a1ex07 comment: given the folowing detailes: user name: demo password: Pass1234 ip: 1.2.3.4 listener port: 1521 oracle SID: orcl pdb service name: pdborcl connect to core db ...
SHR's user avatar
  • 874
10 votes
Accepted

Cannot connect database as sys as sysdba using sqlplus

If the password contains special characters such as @, enter the password between double quotes: $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 11 15:41:40 2019 Version ...
Balazs Papp's user avatar
  • 40.7k
8 votes
Accepted

SQL*Plus, @, and relative paths

Yup, this is Bug 2391334 which has been around for long time, and probably will not be fixed in the near future. One way of working around this is "know" the path for scripts without actually hard ...
Connor McDonald's user avatar
7 votes
Accepted

Not able to login to oracle using sqlplus utility because username and password contain space

C:\Users\balaz>sqlplus bp/bp@orcl SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 28 15:51:40 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Sep ...
Balazs Papp's user avatar
  • 40.7k
7 votes
Accepted

SP2-0667: Message file sp1<lang>.msb not found sqlplus oracle

I found the solution ! As the oracle documentation said about ORACLE_HOME, My env variables was setted in the /etc/profilefile, but not visible from env | grep ORACLE_HOME or dbhomeso I executed the ...
Couim's user avatar
  • 253
7 votes

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

12.41.61 SET TERM[OUT] {ON | OFF} Controls the display of output generated by commands in a script that is executed with @, @@ or START. OFF suppresses the display so that you can spool output ...
Balazs Papp's user avatar
  • 40.7k
6 votes

"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

It's been mentioned in a comment above, but this line fixed this issue for me on my MacBook Pro, running docker wnameless/oracle-xe-11g on port 1521. sudo /bin/bash -c "echo '127.0.0.1 ${HOSTNAME}' &...
turiyag's user avatar
  • 161
5 votes

sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

I had the same problem. And I solved it by using SERVICE NAME (an alias) instead of SID: 1. Open file tnsnames.ora from your ORACLE_HOME folder. In case you don't know its location, do the following ...
Bien Do's user avatar
  • 51
5 votes
Accepted

System account locked, insufficient privileges to log in as sysdba

The windows user account belongs to Administrator and Ora_DBA Windows groups. Double check this, because you get the above error, when you are not a member of the ora_dba group. Check %ORACLE_HOME%...
Balazs Papp's user avatar
  • 40.7k
5 votes
Accepted

Can sqlplus fetch more than one row at a time?

SET ARRAYSIZE has a default of 15 "Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and ...
Gary's user avatar
  • 1,959
5 votes

How to install Oracle's sqlplus on a Mac?

Download Basic Package and SQL*Plus Package zip files from Oracle Add a local repository to brew from where the Oracle packages will be installed: brew tap InstantClientTap/instantclient Copy both ...
OSGI Java's user avatar
  • 193
4 votes

Connect with sqlplus from the command line using a connection string

SQL*Plus 19.9.0.0 shows this syntax Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]] where <logon> ::= <username>[/<...
T.S.'s user avatar
  • 216
4 votes
Accepted

Global command to destroy all disconnected data files in Oracle 11g

Based on your description of this behaviour and your other comment, I guess your database runs on Windows. The command you used should suffice and delete the datafiles. Except on Windows. On Windows, ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

Spool query to file without output on screen

You can use SQL> SET TERMOUT OFF command to get the result. Here are some useful links collected from comments: Oracle SqlPlus - saving output in a file but don't show on screen sqlplus, how to ...
atokpas's user avatar
  • 8,634
4 votes

How in Oracle can I export package that will have package and body?

There are two ways to achieve this Way 1 : SET HEAD OFF SET ECHO OFF SET FEED OFF SET TERM OFF SET LINE 1500 SET NEWPAGE NONE set pagesize 0 SPOOL C:\temp\FILE_NAME.SQL PROMPT CREATE OR REPLACE ...
Bibhuti Bhusan Padhi's user avatar
4 votes
Accepted

I am not able to use the sqlplus after intalling 11g on redhat 6.7

This should sort you out. Remember to login & out again once edited, or do a . .bash_profile to source it. Also, make sure it's executable (chmod u+x ~/.bash_profile ). PATH=$PATH:$HOME/bin TMP=/...
Philᵀᴹ's user avatar
  • 31.8k
4 votes
Accepted

Oracle DB Security Issue: Anyone can login using SQL*PLUS, with any username, and any wrong passphrase

The OSDBA group (typically, dba) You must create this group the first time you install Oracle Database software on the system. This group identifies operating system user accounts that have database ...
atokpas's user avatar
  • 8,634
4 votes

"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

This was due to a VPC setting which the EC2 was created under, was set to no: Under Your VPCs -> Summary tab, DNS hostnames: no In order to avoid manual resolution as answered by others, set the ...
Arif Basri's user avatar
4 votes

ORA-01304 not available

Please ensure you use the correct names for your PFILE and SPFILE. Backup both files before you start playing around with them Check Instance Start SQL*Plus (Windows) or sqlplus (*nix): SQL*Plus: ...
John K. N.'s user avatar
  • 17.7k
4 votes

limiting db user password to 3 days

First, create a password policy (profile) that captures your time period. Then assign that profile to the user. CREATE PROFILE SHORT_LIFE_PROFILE LIMIT PASSWORD_LIFE_TIME 3 ...
CaM's user avatar
  • 2,183
4 votes
Accepted

sqlplus SP2-0341 (line overflow during variable substitution)

SQL*Plus is not a data loading tool. Use the proper tools. SQL Developer can create CSV files. Instead of SQL*Plus, use SQL*Loader to load the data. SQL*Plus Limits
Balazs Papp's user avatar
  • 40.7k
4 votes

Long query causes problems

DON'T DO THAT That code is horrible. You should place ALL of the values in a Global Temporary Table (GTT) and JOIN to that table. Call commit or rollback to auto-clear the GTT.
Michael Kutz's user avatar
  • 4,829
4 votes
Accepted

SQL*Plus - interactive mode on windows

That is a matter of Windows/batch, it is not specific to SQL*Plus. You can do this: (echo connect bp/[email protected]/min19_o71 echo select * from dual; echo exit ) | sqlplus -S /nolog Result: C:\...
Balazs Papp's user avatar
  • 40.7k
3 votes

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

tnsnames.ora doesn't "come with" anything. The key is not that file, as it is nothing but a names resolution file. And sqlplus is nothing but a CLI application. If you want to connect to an Oracle ...
EdStevens's user avatar
  • 2,024
3 votes
Accepted

how to run DBMS_RULE_ADM

You need to use EXEC[UTE] or BEGIN ... END ;. EXEC DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, ''); Or: BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM....
Balazs Papp's user avatar
  • 40.7k
3 votes

SYSDBA password in Oracle DB

Oracle stores password for SYS and internal user's password in PWD###.ora file, where ### is your DB service Name/SID. I found a nice tutorial on orapwd utility to change password for SYS: Lost ...
Saurabh Srivastava's user avatar
3 votes
Accepted

Why does the scipt executes only once the sqlscript

As you haven't shown us the sql files I'm going to take a guess that you haven't included the exit statement. This means that SQL*Plus will be invoked, run the commands/sql in the first file and then ...
BriteSponge's user avatar
  • 1,510
3 votes
Accepted

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

The problem was that I had too many instant clients and Oracle clients on my machine (one oracle client 'x86'). The instant clients were too old. The only working instant client for me is the '12.1....
Cavva79's user avatar
  • 151
3 votes

How in Oracle can I export package that will have package and body?

Use SYS.DBMS_METADATA.GET_DDL to get a CLOB with the code: declare myPackageSpec clob; myPackageBody clob; BEGIN select dbms_metadata.get_ddl('PACKAGE_BODY', 'PACKAGE_NAME', 'SCHEMA') ...
vercelli's user avatar
  • 756

Only top scored, non community-wiki answers of a minimum length are eligible