Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

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

Setting Blank lines to ON before executing a SQLFILE in shell script

echo exit | sqlplus username/password@host_ip:port/Service_name @SQLFILENAME I want to execute set sqlbl on before executing The SQLFILENAME and also want that after executing SQLFILENAME it should ...
0
votes
1answer
27 views

(SQL) User tables are separated into two sections

Using SQL Plus, I create 13 tables via an .sql file, each containing primary and foreign keys. When I execute "select table_name from user_tables;" they are displayed in this partitioned fashion. The ...
1
vote
1answer
301 views

Importing Oracle12c database

i am using Oracle 12c in debian Jessie i want to import an old database exported with expdp on oracle 12c when i try to launch the commande 'impdp' i got those errors : 'ORA-39002: invalid operation' ...
0
votes
1answer
2k views

Error launching sqlplus

I try to run sqlplus on my server with the command sqlplus /as sysdba and I got this error sqlplus: error while loading shared libraries: /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so....
0
votes
1answer
3k views

Help on joining 3 tables Oracle XE 11g

I am trying to return the first name, surname, subject and mark from the following tables: SQL> desc students; Name Null? Type ------------------------ -------- --------------...
1
vote
1answer
39 views

Oracle XE/SQLPlus query display more items

Is there a setting to allow Oracle XE/SQLPlus to display more items before beginning a new header? For example: First Name SURNAME Subject MARK -------------...
3
votes
2answers
6k views

SQLPLUS: SELECT INTO prompting for variable value

Any idea why this is prompting me for the value? I'm calling the script via batch file: DECLARE precount NUMBER; nowcount NUMBER; BEGIN SELECT COUNT(*) INTO precount FROM ...
0
votes
1answer
219 views

how to run DBMS_RULE_ADM [closed]

Oracle 11.2.0.3 My instructions are DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '<schema_name>'); I tried to run them at sqlplus /as sysdba but it tells me unknown ...
2
votes
1answer
423 views

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

Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database? Here is the scenario: Every user-defined user and user-defined tablespace was deleted from an ...
1
vote
1answer
8k views

Spool query to file without output on screen

Using spool on Oracle sqlplus correctly sends the results of my queries to a file. But I also see output on the screen. Is it possible to see only the query without output on screen, and result on ...
1
vote
1answer
238 views

Should we consider “sqlplus” as an Oracle client?

I am a newbie of SQL*Plus and Oracle, and I have 2 questions about them. (1) Executing "sqlplus / as sysdba" command: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 18 ...
0
votes
1answer
213 views

I don't want to have the error when I drop a table although the table doesn't exist [closed]

I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist. I did this set echo on set serveroutput on alter session set ...
5
votes
1answer
119 views

Result on execution of any SQL is number 2

After successful execution of any sql in oracle sql command line it shows 2 like i want to see the table or description of any table it show 2 instead of showing the table. SQL> select * from dual ...
1
vote
1answer
662 views

sqlplus not connecting to Oracle XE 11g

I installed Oracle XE 11g on my Ubuntu in vmware fusion. The oracle installation is all went fine but I am not able to connect the oracle database using sqlplus. I am getting this exception: $ ...
2
votes
1answer
16k views

Database gives “ORA-01109: Database not open” error when it IS open

Running Oracle Database 12c on Windows Server 2012 Whenever I attempt to do a log switch (alter system archive log current), I am given the error in the title. Searching online shows the simple ...
1
vote
1answer
493 views

Find out where length semantics gets changed

I've code to create tables that doesn't specify length semantics: CREATE TABLE CHAR_TEST ( CHAR_TEST_ID NUMBER(*,0) NOT NULL, FOO VARCHAR2(6) NOT NULL, BAR VARCHAR2(6) NOT NULL, ...
0
votes
1answer
134 views

Can not login user SEC_MGR in SQLPLUS

I can login user SEC_MGR when I use the TOAD tool. But, It has error when i login by SQLPLUS:
4
votes
3answers
19k views

Oracle Enterprise Manager (OEM) locking SYSMAN account

Why does Oracle Enterprise Manager reset my SYSMAN account to LOCKED(TIMED) status? I've tried rebuilding Enterprise Manager, and re-installation of Enterprise Manager, which always works, until the ...
1
vote
0answers
478 views

SQLPlus TNS:protocol adapter error

i have two Oracle 12c instances. currently both are shutdown. when I try to connect with sqlplus / as sysdba it says tns protocol adapter error contents of tnsnames.ora are DB12102 = (DESCRIPTION = (...
1
vote
1answer
2k views

SQLPlus “no listener” only with a remote connection from Windows

The Oracle database is running on a linux server. The lsnrctl service is running. Locally, I can connect to database from sqlplus and sql developer. Remotely, from windows, I can't connect. sqlplus on ...
1
vote
2answers
750 views

SQL*Plus save current COLUMN settings

In SQL*Plus, is there a way to save current COLUMN settings similar to STORE which saves SET commands in a script? COLUMN can be used to list the settings, but they are not in script format.
0
votes
1answer
3k views

is there a way to connect to mysql using oracle sqlplus instant client

i am stuck while trying to connect to mysql using sqlplus client and i have already asked this question at askubuntu.com as https://askubuntu.com/questions/674871/not-able-to-connect-to-mysql-...
0
votes
0answers
56 views

Query on Sqlplus utility

I have a OS user 'test' who doesn't belong to dba group or oinstall group. I need to access the database using sqlplus but I'm unable to access the sqlplus even after setting oracle home ans path and ...
1
vote
1answer
4k views

Spool in Shell script not working for me

#!/bin/sh echo "Started at" `date +%Y/%m/%d" "%H:%M:%S` sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF 2>&1 > /dev/null SET SERVEROUTPUT ON SET FEED OFF spool ech.txt create BEGIN DBMS_OUTPUT....
1
vote
1answer
1k views

SQL Spool FILE SQLPLUS BATCH FILE

*/ SET colsep ';' SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 800 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET NUMW 20 column timecol new_value timestamp column ...
0
votes
1answer
758 views

sqlplus connect without typing the @local_SID when only have one instance

Every time I want to connect with a user in Sqlplus I have to specify the database|sid name too, SQL>connect scott/tiger@orcl I have installed another database for other purpose but I removed it. ...
5
votes
4answers
19k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
2
votes
1answer
6k views

SQLPlus Command Line In-line Set Commands

I have the need to run linux command line direct query, including SET options. The query by itself runs fine. However, when I attempt to add set commands, it fails. (my alias 'db' connects to ...
1
vote
0answers
1k views

Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python. However,...
4
votes
2answers
5k views

How does SQL*Plus connect to Oracle without a listener?

sqlplus can be used even if there is no listener in the system, or if it isn't running. How does SQL*Plus connect to Oracle without a listener? I can connect any user using SQL*plus even with no ...
3
votes
2answers
11k views

Using transactions in sqlplus

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction: dbpool.production=> begin; BEGIN dbpool.production=*>...
0
votes
0answers
1k views

WHENEVER OSERROR suppresses error messages

$ sqlplus <connstring> 2>&1 <<! WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; SET ECHO ON; @nonex.sql; ! Produces SQL*Plus: Release 11.2.0.1.0 ...
0
votes
2answers
35k views

“ORA-01017: invalid username/password” : Why is that?

What is the difference between the two : SQLPLUS / AS SYSDBA and SQLPLUS Enter user-name: SYSDBA Enter password: If I use the first command I get connected and see the prompt SQL> but upon ...
0
votes
2answers
3k views

Failed to connect to oracle SQL Plus [closed]

I tryed to connect to SQL Plus as the admin "System", I'm sure that the password I wrote was correct but the connection failed. I also tried to connect with Scott/tiger but it failed as well. What can ...
1
vote
2answers
2k views

sqlplus not working on opensuse

take a look at this: > cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$...
2
votes
2answers
758 views

SQLPlus : default define settings

My workstation is Ubuntu 14.04. I have Oracle Express 11g installed. Whenever i run the define command in SQLPlus, is see the define _editor = "ed". Is there a way to change that permanently to gedit ...
0
votes
3answers
1k views

Prevent auto commit of DROP without SET AUTOCOMMIT OFF

I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?
0
votes
0answers
4k views

How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

I have three fields in a table named table_family. One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son. Other field is "age" and it contains the ages of all the ...
1
vote
1answer
2k views

How to access apex data from sqlplus?

I have setup an oracle database in my Ubuntu machine. Now I have took the following steps to create tables 1) login to my ubuntu account 2) su oracle 3) sqlplus "sys as sysdba" //Connected to an ...
0
votes
2answers
308 views

glogin.sql supress error when Instance Idle

I have a pl/sql block in my glogin.sql file to show information from v$instance when I connect on the database server. Is there a way to suppress the ORA-01034 we would naturally get when the ...
-1
votes
2answers
8k views

Write filename, servername/IP and date+time in spool file

I'm having some problems to verify the installation/execution of some oracle scripts in different production servers. I've created a launcher script which executes all the sql scripts in sqlplus and ...
5
votes
2answers
2k views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- --...
0
votes
2answers
116 views

Filtering of sql output

I want to migrate the database. When I disable the constraints before adding the data to the output file I get extra lines: SQL> spool disable_constraints.sql; SQL> select 'alter table '||...
0
votes
0answers
46 views

sqlplus: ORA-01017 logon denied from one host but OK from another [duplicate]

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
0
votes
2answers
35 views

transaction not processing?

I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back ...
0
votes
0answers
9k views

execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

I got a shell script which connects to SQLPLUS and executes the .sql file like below: read a x=`echo $a | cut -d , -f 1` y=`echo $a | sed 's/.*,//'` c=`echo $USER | cut -c -2`"OPR"`echo $USER | ...
1
vote
1answer
2k views

Pass the unix variable to the ddl command inside a pl/sql block

I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from ...
0
votes
1answer
132 views

Using oracle easy connect does it really connect to the remote DB?

I tried connecting to remote host db using : sqlplus abc/abc@hostname1:1000/ABC And tried printing !hostname it still shows me local hostname from which I am running and not HOSTNAME1. So if I ...
0
votes
1answer
2k views

Why is it necessary to type “host” before the imp command when I'm using SQL*Plus to import a dmp file into Oracle?

In SQL*Plus, I'm connected to an Oracle 11g database. When I type imp help=y It gives me SP2-0042: unknown command "imp help=y" - rest of line ignored. But when I type host imp help=y it gives ...
1
vote
1answer
4k views

How to limit select output from sqlplus?

I stop using SQL Developer as found more convenient to use SQL Plus inside Emacs SQLi mode. SQL Developer on selects with large amount of rows show only few rows. But SQL Plus tend to dump all rows ...