Hot answers tagged

100

did you try sqlplus username/password@host:port/service sqlplus x/y@c:a/b modern versions of sqlplus (version 11 or newer) understand this syntax and you don't need a tnsnames.ora file.


66

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not to wrap lines. If your viewer is less then -S the option you have to use according to https://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less . On Unix/Linux you can use head -1 output.txt to get the first ...


21

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 /etc/hosts was not set. Action: Set the host name and try again. Now, the command worked on my machine (Ubuntu laptop), but it didn't work on EC2. I ...


12

The most simple is to use tnsnames.ora file to connect to the database. For that edit it and add a new entry: This file normally resides in the $ORACLE HOME\NETWORK\ADMIN directory. myDb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = c)(Port =a)) ) (CONNECT_DATA = (SERVICE_NAME =b) ) ) and then you could connect to ...


11

This is a known bug (description is not public unfortunately): Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF The NOT NULL constraints are copied as NOVALIDATE, and you have to set them to VALIDATE state manually, e.g: ALTER TABLE t84_redefenition ENABLE VALIDATE CONSTRAINT constraint_name; The primary key ...


11

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one. Also, system as sysdba generally isn't used, not even sure if that makes sense. SYS as sysdba and just plain system are used. Enter user-name: system@ORCL Enter password: ...


10

To original poster... in the first option you missed a closing parenthesis Incorrect: (Your string) sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))' Correct: sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED))))'


9

You have to use Service Names to connect to Pluggable Databases in 12c. It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.


9

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 from db host as sysdba: without need of password: sqlplus / as sysdba password manually: sqlplus sys as sysdba password in command: sqlplus sys/Pass1234 as ...


8

You need to set the below: SET WRAP OFF


8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


7

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 coding that path. To do this in SQLPlus requires a trick - if you try to run a non-existent file, then you'll get an error message that includes the path name. ...


7

These variables are a feature of SQL*Plus. psql, the equivalent program in the PostgreSQL world, also has variables. Use \set variable 'value' and insert into mytable(mycolumn) values (:variable);


7

DROP and TRUNCATE are Data Definition Language commands and thus cannot be rolled back. However, in Oracle you can use the following technologies to recover your table: Flashback Drop Flashback Database Tablespace Point in Time Recovery Table-Level Recovery From Backups (new in 12c) Flashback Drop If the Recycle Bin is enabled in your database, you can ...


7

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file. Your entry in tnsnames.ora file should be like this: AARCER1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524)) (CONNECT_DATA = (SID = ...


7

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 28 2016 15:51:30 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage ...


7

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 to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter ...


6

Get all privileges from AAA SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','AAA') FROM DUAL; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','AAA') FROM DUAL; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','AAA') FROM DUAL; Change the DDL commands with the user 'BBB' and execute.


6

Because you are not logged in as the user system. When you execute conn system/password as sysdba, Oracle won't even check the user and password (--> from the local systeem where the db is running on). If you enter the system/password as sysdba and then execute 'show user', you'll notice you are logged in as user 'SYS'. So you are actually entering the wrong ...


6

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}' >> /etc/hosts" The following commands then all worked just fine: sqlplus system/oracle@localhost:1521/xe sqlplus system/oracle@127.0.0.1:1521/xe sqlplus ...


5

On Unix/Linux system you can use the user level configuration files to override system level entries. System-Level User-Level Configuration File Configuration File ------------------ ------------------- sqlnet.ora $HOME/.sqlnet.ora tnsnames.ora $HOME/.tnsnames.ora The system-level configuration Files can be found in the ...


5

You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already as soon as you issue some SQL. You can't really be outside of a transaction anyway for practical purposes (sure, if you've just logged in, or just committed and haven't started anything else, well, you're not in a transaction). A few ...


5

If you're using Linux/Unix, there's a way of making SQL*Plus loads friendlier on the command line - use rlwrap. It adds readline support to SQLPlus, allowing you to use the cursor keys to navigate to previous queries (it retains a history), and edit them when you've made a mistake. Launch SQL*Plus with rlwrap -i sqlplus or add alias sqlplus="rlwrap -i ...


5

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 steps Windows (open cmd) > sqlplus // connect to sqlplus, login as sysdba as / sysdba // then type your password that you set in configuration setup ...


5

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 following command : export ORACLE_HOME=/app/oracle/product/12.1.0/dbhome_1 and now it works


5

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 subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results ...


4

Use the following command in SQL*PLUS : !stty erase <backspace> but instead of the <backspace> statement, simply press the backspace button and hit return with what it gives you.


4

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, the datafiles are not always deleted, it is a known limitation. Before dropping tablespaces or datafiles, take them offline: alter tablespace ... offline; ...


4

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=/tmp ORACLE_HOSTNAME=prashant.localdomain ORACLE_UNQNAME=prod ORACLE_BASE=/u01/app/oracle ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 ORACLE_SID=prod PATH=$...


4

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 administrative privileges (the SYSDBA privilege). This is expected behavior as you are logging the Oracle Database from the OS user which belongs to DBA OS ...


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