Command-Line Utility Program for Oracle

learn more… | top users | synonyms

0
votes
2answers
3k views

sqlplus startup - failure in processing system parameters

I am trying to do the startup command this is what I have done set oracle_sid=ORCL sqlplus /nolog SQL> conn /as sysdba startup It shows this error message, failure in processing system ...
0
votes
1answer
1k views

ORA-12899 when inserting from sqlplus, but ok with jdbc

Inserting 'ééééé' into a column defined as varchar(5) runs fine from squirrel sql client (jdbc). But doing the same insert from sqlplus fails with ORA-12899 (too long value). NLS_CHARACTERSET is set ...
-1
votes
2answers
2k 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 ...
3
votes
1answer
752 views

Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition

I have the following table that I want to redefine: create table tq84_redefinition ( id number primary key, ts1 timestamp not null, ts2 timestamp ); Please note the not null constraint on the ...
1
vote
1answer
3k views

Why can't I log in to SYSTEM without AS SYSDBA

When loging in to sqlplus as SYSTEM, why does the login fail with an invalid username/password error if I do not include "AS SYSDBA" C:\Windows\System32>sqlplus /nolog SQL*Plus: Release ...
5
votes
3answers
69k views

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the ...
0
votes
1answer
594 views

SQL output formatting

I want this: "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" But I get ...
0
votes
1answer
114 views

Execute a script with SQLPlus containing html entity mappings

When executing a script with SQL PLus, I was prompted to enter value for lt, gt, and apos. Here are some code examples in this script. v_qry := replace(v_qry, '&lt;', '<'); v_qry := ...
3
votes
2answers
640 views

SQL*Plus default output format

I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query. Database 1: C:\Oracle\instantclient_10_2>sqlplus ...
0
votes
3answers
632 views

Oracle won't start

While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error: SQL> Connected to an idle instance. SQL> ORA-01081: ...
1
vote
5answers
16k views

Message file not found when starting SQLPlus

I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error: Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found ...
1
vote
0answers
504 views

SQLPlus times out when connecting to an Oracle XE database on another PC

I'm having the strangest issue. I have a very basic (I think) setup of an Oracle 11G database on one Hyper-V VM, and the Oracle 11G client on another VM. Although I'm able to access the database on ...
6
votes
1answer
720 views

sqlplus consuming 99% CPU

Oracle's installed on one server, I'm running sqlplus on a different (application) server. I've recently encountered a problem getting sqlplus to do anything; it simply spins, consuming 99% CPU even ...
1
vote
1answer
2k 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 ...
2
votes
4answers
971 views

Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?

I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I ...
1
vote
1answer
138 views

Appending data from one program to the other using batch commands

I have a database which sometimes crashes. I want to create a batch program that does this: Runs SQL*Plus. Makes SQL*Plus run a script from a plain text file. Gets the results back to the main batch ...
0
votes
1answer
1k views

SQLPlus: Cmd vs PowerShell

From cmd.exe, when I run: C:\oracle\client\BIN\sqlplus.exe username@server then type my password, I get in. However, if I run the same command from PowerShell and type the same password I get: ...
5
votes
2answers
1k views

How do I execute an Oracle SQL script without sqlplus hanging on me?

For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.: If the script contains any named substitution variable ...
0
votes
1answer
21k views

SYSDBA password in Oracle DB

I am connecting to Oracle DB using sysdba: sqlplus / as sysdba What is the default password for sysdba user. I tried in net and found out the default password is change_on_install but it is not ...
5
votes
2answers
50k views

How to resolve ORA-01031: insufficient priviledges error on Windows installation?

I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database ...
0
votes
2answers
16k 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
1answer
7k views

Set encoding from within SQL*Plus

I have a large set of files with SQL commands that I run with a SQL*Plus script that uses the @@ operator. I run the script on third-party computers (or even send it by e-mail) so I'd like to make it ...
2
votes
2answers
12k views

ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS

I am using Toad to connect my database and its working fine for me . But when i try to connect with the same credential , i am getting the error message as shown below: ORA-12154 TNS: could not ...
2
votes
1answer
2k views

Can't return data from SQL/Plus and Toad but can from SQL Developer

This one has me stumped. We are running an Oracle 11g on Amazon EC2 and I can connect and query fine with SQL Developer but with Toad and SQL/Plus I can connect but when I try to do a simple select ...
2
votes
1answer
625 views

Invalid File Name on login to SQLPLUS

The moment I log into SQLPLUS I get an SP2-0556 error. I recently moved OS but am connecting to the same database so I am not sure if it's because I'm using my old TNSNames or what. I can connect and ...
1
vote
1answer
91 views

There is an equivalent of login.sql on PostgresSQL?

When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed. It's usefull to set preferences... There is an equivalent ...
0
votes
1answer
1k views

Where to put login.sql in Windows environment to configure SQL Plus?

I installed Oracle 10g XE on my Windows Server 2008 R2. Now I would like to configure SQL Plus to use login.sql for login settings. I created a login.sql file and put it into a C:\users\myname ...
8
votes
3answers
386 views

Do I have to retype information every time I make a mistake in SQLPlus

If I make a mistake in SQLPlus while inputting an 'insert into' command, is there a way I can bring this information back up in order to edit it?
3
votes
1answer
2k views

halt on error in a sqlplus script

I am deploying pl/sql code using several sql files that are called with @@file. If a package got a compilation error the script continues to the end. Is there a way to stop on every compilation ...
2
votes
1answer
289 views

Oracle start database with incorrect password

I have a question regarding starting oracle database with in correct sys password. I have provided incorrect password for sys user and I am still able to start database and execute statements. So ...
3
votes
1answer
3k views

sql*plus 11.2 client connection to oracle timing out on Linux

One of our DBAs is seeing connection issues with his sql*plus client. Whenever he connects to the Oracle v10g server, the connection seems to time out for no apparent reason. We tried checking ...
4
votes
2answers
19k views

How to set an Oracle user's default schema?

I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?
2
votes
1answer
4k views

Oracle not available error with Oracle XE 11.2.0-1.0

I have downloaded Oracle XE 11.2.0-1.0 and installed it on a CentOS 6.3 virtual machine with 1.5GB memory and 3GB of swap. SYS and SYSTEM set to have password 123456. I have set ...
2
votes
1answer
2k views

Simple Oracle query hangs depending on WHERE clause

Not used to using Oracle, but we have a large database where a non-unique query like SELECT * FROM employees where department = 'HR' is working, results list up no problem. But when I do ...
2
votes
2answers
4k views

Encoding problems with sqlplus, Linux

I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page. select * from ...
1
vote
1answer
4k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
1
vote
2answers
1k views

Remote to a remote oracle server and get running instances?

I need to make a shell script that takes a list of servers and and check the running instances then run sql script in each instance! now I do not want it to ask me for a password! it's redhat and ...
2
votes
0answers
4k views

SQL*Plus, @, and relative paths

Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot. For example, under ...
1
vote
2answers
1k 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 ...
7
votes
2answers
1k views

If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?

I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database: create or replace function tail_f return varchar2_tab pipelined as n number; ...
7
votes
4answers
25k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
30
votes
4answers
249k views

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database local_SID = (DESCRIPTION = (ADDRESS ...
2
votes
2answers
15k views

Oracle11.2g post-installation problem on Oracle Linux kernel

[question copied from stackoverflow] After installation of Oracle 11.2g in silent mode on the Oracle Linux kernel, I have the following error: [u01/app/oracle/product/11/bin] $ sqlplus sqlplus ...
5
votes
1answer
679 views

Execution plan using B+ tree index, but also sorts

I'm using Oracle sqlplus. And I have the following query: SELECT fooID from foo MINUS SELECT fooID from bar; I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
1
vote
1answer
5k views

Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

I have following example query. variable pStartDateBegin VARCHAR2(10); variable pEndDateFinish VARCHAR2(10); begin select '01-01-2000', '30-11-2011' into ...
3
votes
2answers
11k 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 ...
3
votes
3answers
5k views

Login Problem as SYSDBA ORA-01031

I have the following problem: I can connect to an Orcale database as normal user or as sysdba: sqlplus myuser/myPw@myDB sqlplus "sys/sysPw@myDB as sysdba" But when another (Windows-User) is logged ...
3
votes
1answer
386 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...
2
votes
2answers
6k views

Why can't we specify group functions in Oracle in the where clause?

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
2
votes
2answers
293 views

Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output?

Just again looking at some log from an oracle script, where the developer intended to comment out some part not to add documentation. I don't want that a script containing code like this /* select ...