All Questions
240 questions
0
votes
1
answer
26
views
sqlplus column header misaligned
In wide sqlplus reports, column headers get misaligned from the data
The code that led to this output was:
set linesize 32767
set trimspool on
col z format a1
alter session set nls_date_format='...
- 101
0
votes
0
answers
51
views
sqlplus connects to my Oracle server only on "second try"
My Oracle DB used to work fine but lately I have the following problem:
When I try to connect with sqlplus with a non-interactive command as the following, from the very machine I deployed my DB on,
...
0
votes
1
answer
1k
views
sqlplus / as sysdba Connected to an idle instance
anyone can Me help uderstand what is happends?
i have oraenv corectly, use su oracle to change user, use this command to conncet as sysdba -
sqlplus / as sysdba
answer is:
SQL*Plus: Release 19.0.0.0....
0
votes
0
answers
162
views
listener issues in oracle 19c
These are my listener.ora and tnsnames.ora files
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dr_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/...
0
votes
1
answer
53
views
Oracle colsep is adding newlines. Why?
So, I have a script:
set heading off;
set feedback off;
set linesize 10000;
set newpage NONE;
set colsep '~'
whenever sqlerror exit 1;
WHENEVER OSERROR EXIT 255;
select TO_CHAR(je.key_date, 'YYYY-MM-...
0
votes
2
answers
1k
views
Unable to connect to Oracle user in pluggable database
for some reason, i am unable to connect with any user that I create. their account status is also unlocked.
I want to mention one thing; the pdbs are clone of the main pdb. (as shown by their name ...
- 62
-1
votes
1
answer
191
views
How to make sqlplus parse stdin?
I have the data in the following form:
10334,99.4,4241
14354,99.5,6018
16807,93.9,2279
22042,97.5,70341
22117,96.4,70335
22138,95.7,70332
23869,94.4,135649
23937,97.5,135642
23982,96.6,135638
24055,95....
-1
votes
2
answers
867
views
How to export and Import entire oracle database using sqlplus on terminal linux machine?
I have database that i want export from one machine and import into another machine using sqlplus on linux terminal.
I have tried nothing
Bruce Wayne
0
votes
2
answers
603
views
Oracle sqlplus doesn't connect to remote DB, only local
I would appriciate some help with an issue that I'm facing.
I have two Oracle DB servers with same schemas. I want to run sqlplus from one server to another. I've added the tns_names entries in ...
0
votes
1
answer
72
views
How to silent the login.sql output
[nir@dba etl]$ cat login.sql
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set markup csv on delimiter "&TAB" quote off
set heading off termout off echo off feedback off ...
- 533
1
vote
1
answer
828
views
Can't log into Oracle 19c database running inside a container from the host
I have a server running Oracle 19c inside a Docker container, and I would like the ability to log into the database from the host using sqlplus.
Inside the container, I am able to run this command ...
- 121
0
votes
2
answers
3k
views
How do I set tab as a delimiter in sqlplus markup
I want to return sqlplus output as tsv. For that I use set markup csv. but it does not accept tab as delimiter, not \t and not actual tab.
error is
SP2-1660: Invalid option. Only a single character be ...
- 533
0
votes
2
answers
193
views
ignore column width in sqlplus
I'm getting the follow
SQLPLUS> create table source(aa nvarchar2(2), bb nvarchar2(100), cc nvarchar2(10));
insert into source values ('aa',null,'aa');
insert into source values (null,'aa','aa');
...
- 533
0
votes
1
answer
85
views
How to return SQLPLUS Null as \N
I have a script that moves that from one DB to another by a SQL.
I'm querying from one side and load it into another. TSV style.
I'm expanding that script now to support Oracle as a source.
For that ...
- 533
0
votes
0
answers
157
views
What’s wrong with provided apex instance administrator password
1
I have installed Oracle Database 21c Enterprise Edition on Oracle windows 10 I am now trying to install Apex 22.2 .
Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance ...
0
votes
0
answers
304
views
problem with services in automatic restart
I am working on Oracle 19c. I have a problem with the services I created via:
alter system set service_names=' exemple1, exemple2' scope=both;
I have many services to create and I am limited to 255 ...
- 1
1
vote
1
answer
244
views
My subdirectories under $ORACLE_HOME are missing
Im using oracle 11g. I cannot access the software products since they are missing. When I cd to $ORACLE_HOME, there are no files under it. They are missing. I cannot access the sqlplus. What to do in ...
- 11
0
votes
1
answer
729
views
How to we get history of sql statements of sysdba?
We have been running a list of commands in cli: sqlplus / as sysdba
We did not have "history on" and we logged out of the session. How do we get the list of sql statements we ran before as ...
0
votes
1
answer
272
views
How to store 30000 plus characters in a particular text field, which datatype should be use for it? sql*plus
Here, I have a problem in which I want to store 30000 plus characters (paragraphs) in an individual field instance.
Moreover, I tried the CLOB datatype for a column, but when I am crossing the ...
0
votes
1
answer
204
views
How to use CLOB datatype for storing limitless characters in column each rows?
I want to use CLOB datatype instead of varchar2(4000) and long, in which I want store limitless characters . However, its easy to declare the string based column to CLOB but the main problem is when I ...
1
vote
1
answer
2k
views
Oracle ODBC client configuration missing variable
I'm trying to configure an ODBC connection for an Oracle DB using unixODBC drivers.
For that I followed this Oracle's guide.
First, I successfully installed the ODBC client and driver.
# dnf install ...
- 131
0
votes
0
answers
1k
views
sqlplus is not working, it is frozen
When I type
sqlplus / as sysdba
It simply gets frozen, the cursor stays there, and I can type more character but after pressing enter nothing happens. Neither CTRL + C. My pmon service is running, ...
- 101
3
votes
1
answer
2k
views
How to Avoid SQLPlus output truncating
I have the following column in oracle database table.
Column name : objectdata
Data type : LONG RAW
When I query this column via sqlplus output is like below.
SQL> select objectdata from tableA ...
- 273
0
votes
1
answer
623
views
Cannot connect to a pluggable database via IDE
I have an Oracle XE database on computer1.
When I try to connect to it via sqlplus from computer2, it works fine, for both cdb and pdb connections;
e.g.:
rlwrap sqlplus timon@//192.168.0.115:9121/...
0
votes
0
answers
387
views
How to store data in loop and after assign data set to out ref cursor in Oracle
I tried to store date in loop and after that dataset set to OUT SYS_REFCURSOR. In this time I declared array variable and I tried to store data in loop by each iteration. and after, I want to assign ...
- 1
-1
votes
1
answer
2k
views
Sql plus equivalent in psql
I'm tasked of migrating some code from sqlplus to psql
In oracle, I have a bash script that excutes a stored procedure and based on the result it calls a sqlplus that spools multiple files to dump ...
- 111
1
vote
1
answer
584
views
The expected read speed of SQL*PLUS
The issue is many-fold but it boils down to this simple scenario: I have a Oracle Database Version 19.4.0.0.0 and SQL*PLUS Version 21.3.0.0.0 running on the same Red Hat Linux (I actually tried both ...
user238532
0
votes
2
answers
7k
views
Oracle: Simple way to compare all objects of a schema in two databases?
I'm attempting to compare all objects of two schemas located in two different databases. I know there may be ways to do this with TOAD and SQL Developer, but is there another way to do this by script ...
- 11
0
votes
2
answers
1k
views
Insert query in Orcale database - not showing the row in a table
I am inserting a row in the Oracle database table. The query looks like as below -
INSERT INTO <schema_name>.<Table_name> (SID, LABEL, DESCR, GTI_EXPTYP, GTI_MODUS, GTI_NAME) VALUES (22, '...
- 11
0
votes
0
answers
274
views
Is it neccesary to shutdown the oracle database before uninstalling
I have a requirement of deleting/uninstalling a installed oracle 11g database on a Linux machine and I follow the below steps to uninstall the database.
Kill all processes runs with user oracle.
...
- 101
0
votes
1
answer
2k
views
ORA-03114 Unable to connect to remote Oracle database
I have an Oracle Database installed on Oracle Linux Server which I'm trying to connect to from my PC. I have configured the listener in the following way
[oracle@ol8ngs1 ~]$ lsnrctl status
LSNRCTL ...
0
votes
1
answer
118
views
How can I make this DECLARE PROCEDURE working?
I'm new to Oracle DB, coming from MS SQL Server.
I created this script:
DEFINE USER_NAME = &1
DEFINE PASSWORD = &2
DEFINE TABLESPACE = &3
CONNECT &DB_ADMIN/&DB_PWD@//localhost:...
- 133
0
votes
1
answer
248
views
Silence Sqlplus Login Message but Retain Echo
Running a series of automated checks on our databases for the security folks checking our STIG compliance. Mostly simple stuff like certain parameters turned off, etc. We're in the stone ages, so this ...
- 325
-1
votes
1
answer
528
views
import .dmp in Toad : Invalid format of Import utility name Verify that ORACLE_HOME is properly set
When I tried to import a DMP file using TOAD (9.0.1) Import Utility Wizard I get an error
Invalid format of Import utility name Verify that ORACLE_HOME is
properly set
Any suggestions please?
I ...
0
votes
1
answer
620
views
Designing User & its Sub User Database Diagram Problem [duplicate]
There are some business category e.g. Travel Agency, Hotel, Car Rental etc.
Roles:
Management rolesSuper Admin, Staff
Customer side roles are Owner, admin and sales counter
May be etc. customer side ...
- 5
0
votes
1
answer
940
views
Data deleted with sqlplus still showing up in SQL Developer [duplicate]
I wrote a script to run with SQLPlus which deletes the previous seven days of data in my Oracle XE database. I logged into the database with SQLPlus, ran the script, and it showed me the correct rows ...
- 3
2
votes
0
answers
703
views
SQL*Plus fail instead of prompting for substitution variable
Imagine a file test.sql with the following content
SELECT '&myvar' AS greeting FROM dual;
When we run that file in sqlplus. We will be prompted for the value of the variable &myvar since it's ...
- 121
0
votes
1
answer
431
views
Cannot login oracle database using other users
I'm currently using oracle xe c18. I can login using sqlplus / AS SYSDBA as oracle user. However, the command does not work for other users. It always shows an error ORA-01017: invalid username/...
- 135
0
votes
1
answer
182
views
set autotrace on is just estimate plan?
I've made a test:
SQL> set autotrace on; << autotrace on
SQL> select * from danilo.testepart where nome = 'danilo3';
ID NOME
---------- -------------------------------------------...
- 801
1
vote
0
answers
209
views
oracle sqlcl and sqlplus - seperate login.sql on linux
I feel I'm probably missing something obvious here, but I can't see an easy way to have a separate login.sql for sqlplus and sqlcl
This is problematic for me as the errors thrown up by sqlplus for the ...
- 135
0
votes
2
answers
709
views
SQLPlus configuration problem or firewall issue
I need to connect to a remote Oracle DB, so I requested to open the firewall on the port 1521 to the DB and the replica.
[root@ansible-test ~]$ telnet <oracle_db_active> 1521
Trying <...
- 101
0
votes
2
answers
208
views
Creating two databases in Oracle 11g
I am a student. I have assignments and projects simultaneously.
I want my project database and the other simple databases separate.
I have no option to use MySQL or others as my lecturer suggested to ...
- 11
3
votes
1
answer
14k
views
How to install Oracle's sqlplus on a Mac? [closed]
I need steps how to install Oracle's sqlplus on a Mac.
- 193
0
votes
1
answer
149
views
How to quote a SQL*Plus identifier in a bash script?
I get "ORA-00904: "MEMORY_TARGET": invalid identifier" no matter how I try to quote the identifier in the following command:
MEMORY_TARGET=$(su - orat01 -c " echo 'select ...
1
vote
1
answer
493
views
Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?
I am trying to have my entire remote DB backup made to my local server.
Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ ...
- 13
0
votes
1
answer
1k
views
Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"
All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
-1
votes
1
answer
2k
views
Database not opening, after messing with datafiles [closed]
I was exercising some database recovery exercises
First I had made a backup of my datafiles, then I moved them to another directory to test if I could recover them or not.
When I was recovering them ...
1
vote
0
answers
346
views
How to trim trailing spaces from headers in Oracle 12.1 SQLPLUS
I am creating a pipe-delimited file from a SQLPLUS query (Oracle 12.1), and the recipients are requesting that I trim the trailing spaces from the header entries. That is, the current output file ...
- 51
0
votes
1
answer
430
views
Scheduled Job Does not work in oracle SQL*PLUS
I am just a beginner in Oracle and can't seem to get this job running :-
I want to delete old rows from my database. And created a job as follows :-
CREATE OR REPLACE PROCEDURE delete_trips
AS
...
- 103
1
vote
1
answer
50
views
The full contents of the bind-variable are not displayed in SQL Plus
I wrote a big query in bind-variable. I want to print it in SQL Plus, but the text doesn't show, only small part of it.
- 31