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'); insert into source values ('aa','aaa',null); set serveroutput on pages 0 feedback off echo off head off verify off numformat 99999999999.9999999999999 null '\N' colsep ' ' linesize 32767 select * from source; aa \N aa \N aa aa aa aaa \N I want to ignore the column width and show the result as ` without touching the SQL (only the commands before it or sqlplus`). So the result will look like: aa \N aa \N aa aa aa aaa \N (representing tab as two spaces)

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 insert the paragraph it stores and on runtime when check it shows just a single line.. As am new, your guidance will help me a-lot thanks. I have tried this, on runtime it only displays limited characters. I want to store one page paragraph in single row and on runtime it displays all the characters of the paragraph. Note: Using SQL*PLUS

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 is being done with Sqlplus scripting to populate the reports. Using sqlplus -S to filter out that unnecessary login message, but the security folks would also like the command with the finding details instead of just the output. With sqlplus -S, the output is

VALUE
------
FALSE

whereas we want

Select blah blah;

VALUE
------
FALSE

Set echo on doesn't fix this, even when nesting the query in a sql script and run with @. I'm assuming the S option overrides the echo setting. Wondering if there's an option I'm not familiar with or if I just need to work around it.

On https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/STARTUP.html , the document states that: > ## Startup command > ... > > where db options has the following syntax:

[FORCE] [RESTRICT] [PFILE=filename] [QUIET]  [ MOUNT [dbname] |  [ OPEN [open_db_options] [dbname] ] | NOMOUNT ]

So it seems that the if you want to use force and restrict, you have to use something like startup force restrict. But by my testing, startup restrict force also works. But the document describe the option like [FORCE] [RESTRICT], to me, this indicates an order, and force should go first, then restrict. Is my understanding correct?

I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below. However, if I use sqlplus / as sysdba (it worked, and I don't know why) I have tried this command: lsnrctl start, but again not working. In addition, I have checked my listener.ora and tnsnames.oraca I have attached my environment set up in .bash_profile Please help me this. Thank you

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 sysdba? We tried the following: SELECT h.sample_time, u.username, h.program, h.module, s.sql_text FROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT s WHERE sample_time >= SYSDATE - 1 AND h.user_id=u.user_id AND h.sql_id = s.sql_iD ORDER BY h.sample_time Also tried select * from v$sql

SET SERVEROUTPUT ON; ACCEPT redeem_id CHAR PROMPT 'Enter Redeem ID: '; ACCEPT matric_num CHAR PROMPT 'Enter Matric Number: '; ACCEPT prod_id CHAR PROMPT 'Enter Product ID: '; DECLARE v_prod_name VARCHAR2(100); v_req_points NUMBER; v_total_points NUMBER; v_remaining_points NUMBER; BEGIN SELECT prod_name INTO v_prod_name FROM cat_management WHERE prod_id = :prod_id; SELECT req_points INTO v_req_points FROM cat_management WHERE prod_id = :prod_id; SELECT total_points INTO v_total_points FROM point_calc WHERE matric_num = :matric_num; v_remaining_points := v_total_points - v_req_points; INSERT INTO prod_redemption (redeem_id, matric_num, redeem_date, redeem_time, prod_id, prod_name, req_points, remaining_points) VALUES (:redeem_id, :matric_num, SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'), :prod_id, v_prod_name, v_req_points, v_remaining_points); DBMS_OUTPUT.PUT_LINE('Redemption successfully registered.'); DBMS_OUTPUT.PUT_LINE('Redeem ID: ' :redeem_id); DBMS_OUTPUT.PUT_LINE('Matric Number: ' :matric_num); DBMS_OUTPUT.PUT_LINE('Product ID: ' :prod_id); DBMS_OUTPUT.PUT_LINE('Product Name: ' v_prod_name); DBMS_OUTPUT.PUT_LINE('Remaining Points: ' v_remaining_points); DBMS_OUTPUT.PUT_LINE('Current Date and Time: ' TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS')); END; /

I connected via sqlplus like following: sqlplus user/password@SID I have a .Net application which also connects to same DB using same user and password. But once sqlplus is connected, it was unable to run any update statements. So, the following code doesn't do anything. It just hangs and control is not returned to program if I check it using breakpoint. command.ExecuteNonQuery() Does sqlplus take exclusive session once connected?

I want to execute the same sql script passing one text every time but I don't know how to it. I want to do it into sqlplus. With this select I have the values but I don't know how to pass this values. select column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec'))) LOOP start script.sql column_name; END LOOP; I don't understand your example. I have try to use but it doesn't works. I want to pass both values to "prueba.sql" (one in each execution) cat prueba.sql prompt &1; SQL> COL column_value NEW_VALUE v_column_value SQL> select column_value column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec')); COLUMN_VALUE -------------------------------------------- Logons Per Sec Executions Per Sec SQL> @prueba.sql &&v_column_value Executions I want to execute a report inside the prueba.sql each time with one of this values as parameter. Thanks

**→ OS: Oracle Linux Server release 8.7** **→ DBMS: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production - Version 19.3.0.0.0** After cloning a virtual machine that we can connect normally on sqlplus with sys user, we cannot say the same about the clone and reconfigured machine, even change sys password and putting the right one: > oracle@somedb ~]$ sqlplus sys@somecdb as sysdba > > SQL*Plus: Release 19.0.0.0.0 - Production on Seg Set 23 09:39:55 2024 > Version 19.3.0.0.0 > > Copyright (c) 1982, 2019, Oracle. All rights reserved. > > Informe a senha: ERROR: ORA-01017: senha/nome do usu�rio inv�lido; > log-on negado But I can connect with SYSTEM user and probably with the other users, on both machines: > oracle@somedb ~]$ sqlplus system@somecdb > > SQL*Plus: Release 19.0.0.0.0 - Production on Seg Set 23 09:56:01 2024 > Version 19.3.0.0.0 > > Copyright (c) 1982, 2019, Oracle. All rights reserved. > > Informe a senha: Hor�rio do �ltimo log-in bem-sucedido: Seg Set 23 > 2024 09:03:09 -03:00 > > Conectado a: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 > - Production Version 19.3.0.0.0 > > SQL> The root connection is ok (sqlplus / as sysdba), but the CDB connection not - same problem happens trying to connect with SQL Developer. How can I solve it?

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 resolve the connect identifier specified Steps: 1. Opened command promt 2. Reach to the path of SQLPLUS (*C:\programs\Oracle\Ora11g\BIN*) 3. C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name 4. Enter Password : ERROR: ORA-12154 TNS: could not resolve the connect identifier specified 5. But when i open this with the owner of the schema ,it works for me 6. C:\programs\Oracle\Ora11g\BIN\sqlplus schema_name@schema_name 7. Enter Password: 8. Connected to :...................... So,my question is why my username is not able to connect that particular schema ,while the owner of that schema can connect . Note:I can connect to that schema with my username with Toad ,but not with sqlplus My TNSNAME.ORA is under TNS_ADMIN folder Please help me out of this .

I have been searching in internet for best way to install sqlplus in MacOs apple silicon chip ( arm bases processor). please guide me some easiest method to do that. I saw that many article about installing sqlplus in x64 intel based processor but not for the arm64 based processor. Some more details about the system Chip Apple M1 Pro Mac OS version Mac OS Sonoma 14.3.1

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 successfully: sqlplus john/john@127.0.0.1:1521/XYZ However, when ran from outside the container, the command hangs for ~60 seconds then fails and returns this error: ORA-12637: Packet receive failed **How can I log into the database from the host?** docker-compose.yml:

version: '3.8'
name: db
services:
  database:
    image: doctorkirk/oracle-19c:19.3
    volumes:
      - ~/oracle19c/data/:/opt/oracle/oradata
    ports:
      - 1521:1521
    environment:
      - ORACLE_SID=XYZ
      - ORACLE_PWD=oracle

docker ps:

user@test-db:~$ docker ps
CONTAINER ID   IMAGE                        COMMAND                  CREATED          STATUS                    PORTS                                       NAMES
29ba3a2ebe95   doctorkirk/oracle-19c:19.3   "/bin/sh -c 'exec $O…"   16 minutes ago   Up 15 minutes (healthy)   0.0.0.0:1521->1521/tcp, :::1521->1521/tcp   db-database-1

listener.ora:

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  )
)

DEDICATED_THROUGH_BROKER_LISTENER=ON
DIAG_ADR_ENABLED = off

netstat -tulpn from inside the container:

[oracle@29ba3a2ebe95 ~]$ netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:1521            0.0.0.0:*               LISTEN      17/tnslsnr
tcp        0      0 127.0.0.11:34963        0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:34633           0.0.0.0:*               LISTEN      88/ora_d000_XYZ
udp        0      0 127.0.0.11:37794        0.0.0.0:*                           -
udp        0      0 127.0.0.1:54395         0.0.0.0:*                           90/ora_s000_XYZ
udp        0      0 127.0.0.1:38515         0.0.0.0:*                           88/ora_d000_XYZ
udp        0      0 127.0.0.1:52421         0.0.0.0:*                           76/ora_lreg_XYZ

netstat -tulpn from the host:

user@test-db:~$ netstat -tulpn
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:1521            0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp6       0      0 :::80                   :::*                    LISTEN      -
tcp6       0      0 :::1521                 :::*                    LISTEN      -
tcp6       0      0 :::22                   :::*                    LISTEN      -

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='yyyy-mm-dd hh24:mi:ss'; pro....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2....+....3 select sysdate a, sysdate b, sysdate c, sysdate d, sysdate e, 'yyyyyyyyyyy' y, 'z' z from dual; select sysdate a, sysdate b, sysdate c, sysdate d, sysdate e, sysdate f, 'z' z from dual; How can this be fixed?

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 or Sql*Plus? Thanks in advance.

I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script. Unfortunately, few PDB NAMEs are split/broken in the output(for example, P DB1P016, PD B1P032 etc) so the variable is not assigning proper/correct value. Any idea to fix this?

SQL> select LISTAGG(a.name, ' ') WITHIN GROUP (ORDER BY name) as pdbs from v$pdbs a;

PDBS
-------------------------------------------------------------------------------------------------------------------------------------------------
IOC1P002 PDB$SEED PDB1P001 PDB1P002 PDB1P003 PDB1P004 PDB1P005 PDB1P006 PDB1P007 PDB1P008 PDB1P009 PDB1P010 PDB1P012 PDB1P013 PDB1P014 PDB1P015 P
DB1P016 PDB1P017 PDB1P018 PDB1P019 PDB1P020 PDB1P021 PDB1P022 PDB1P023 PDB1P024 PDB1P025 PDB1P026 PDB1P027 PDB1P028 PDB1P029 PDB1P030 PDB1P031 PD
B1P032 PDB1P033 PDB1P034 PDB1P035 PDB1P036 PDB1P037 PDB1P038 PDB1P039 PDB1P040 PDB1P041 PDB1P042 PDB1P043 PDB1P044 PDB1P045 PDB1P046 PDB1P047 PDB
1P048 PDB1P049 PDB1P050 PDB1P051 PDB1P052 PDB1P053 PDB1P054 PDB1P055 PDB1P056 PDB1P057 PDB1P058 PDB1P059 PDB1P060 PDB1P061 PDB1P062 PDB1P063 PDB1
P064 PDB1P065 PDB1P066 PDB1P067 PDB1P068 PDB1P069 PDB1P070 PDB1P071 PDB1P072 PDB1P073 PDB1P074 PDB1P075 PDB1P076 PDB1P077 PDB1P078 PDB1P079 PDB1P
080 PDBCUS12

I have database that i want export from one machine and import into another machine using sqlplus on linux terminal. I have tried nothing

> SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 13:08:07 2018 > > Copyright (c) 1982, 2016, Oracle. All rights reserved. > > Enter user-name: system > Enter password: > ERROR: > ORA-28002: the password will expire within 4 days Every time I login into my database it shows this warning message of password gonna expire in .. days. Now its just 4 days left I don't know what to do to fix this warning.

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 - Production on Mon Mar 4 16:33:30 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to an idle instance. i try SQL> STARTUP; answer: > ORA-01078: failure in processing system parameters LRM-00109: could > not open parameter file > '/oracle/impuls/19c/db_home1/dbs/initIMPDBP.ora' but when I use this command sqlplus sys@pdbname as sysdba paste password and answer is ok, database working, bud i don't anderstand what is happends lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-MAR-2024 16:42:39 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amvmor1.andoria.net)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 26-JAN-2024 19:40:33 Uptime 37 days 21 hr. 2 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/impuls/19c/db_home1/network/admin/listener.ora Listener Log File /oracle/diag/tnslsnr/amvmor1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amvmor1.andoria.net)(PORT=1521))) Services Summary... Service "02dd7d33e92ebc5fe0631801000a2bb3" has 1 instance(s). Instance "impdbt", status READY, has 1 handler(s) for this service... Service "bpsc" has 2 instance(s). Instance "bpsc", status UNKNOWN, has 1 handler(s) for this service... Instance "impdbp", status READY, has 1 handler(s) for this service... Service "e81af78f0d129774e0531801000a28a1" has 1 instance(s). Instance "impdbt", status READY, has 1 handler(s) for this service... Service "e81bb4c3318fb858e0531801000abe14" has 1 instance(s). Instance "impdbp", status READY, has 1 handler(s) for this service... Service "e81c4e8d6234d523e0531801000a4126" has 1 instance(s). Instance "impdbp", status READY, has 1 handler(s) for this service... Service "impdbp" has 1 instance(s). Instance "impdbp", status READY, has 1 handler(s) for this service... Service "impdbpXDB" has 1 instance(s). Instance "impdbp", status READY, has 1 handler(s) for this service... Service "impdbt" has 1 instance(s). Instance "impdbt", status READY, has 1 handler(s) for this service... Service "impdbtXDB" has 1 instance(s). Instance "impdbt", status READY, has 1 handler(s) for this service... Service "test" has 2 instance(s). Instance "impdbt", status READY, has 1 handler(s) for this service... Instance "test", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

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/dbhome_1)
      (SID_NAME = dr)
      (UR=A)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


tnsname.ora


DR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dr) (UR=A)
    )
  )


PDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb1)
    )
  )


PDB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb2)
    )
  )

PDB3 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb3)
    )
  )

when I start the listener, only getting this output:

lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-JAN-2024 20:38:21

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.XX.XX)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-JAN-2024 20:28:41
Uptime                    0 days 0 hr. 9 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.XX.XX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.XX.XX)(PORT=1521)))
Services Summary...
Service "dr_DGMGRL" has 1 instance(s).
  Instance "dr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully