Questions tagged [oracle]
All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.
7,810
questions
0
votes
1
answer
849
views
Connection rejected based on ACL filtering, but the ACL is disabled?
I'm trying to connect to an Oracle Cloud database through DataGrip (with JetBrains's instructions) but I'm getting an error:
DBMS: Oracle (no ver.)
Case sensitivity: plain=mixed, delimited=exact
[...
0
votes
0
answers
64
views
oracle disconnect sometime
Oracle sql version is 19c. I have encountered one issue that Oracle sql sometime will disconnect and then auto connect. How can I find the cause?
To get error, so write below.
try { OracleConnection } ...
- 1
1
vote
1
answer
184
views
oracle 19c rac cluster nodes show in different time zones
working on an oracle 19c 2-node rac cluster. we were running awr reports when we noted in sys.dba_hist_snapshot that the cluster nodes were in different time zones. We have validated that our ...
- 113
1
vote
1
answer
342
views
RMAN backup restoration
I am trying to restore a RMAN backup to a different server. I needed some clarifications/guidance.
This is the location for datafiles in database 1:
/u01/app/oracle/oradata/db1/datafile
This is the ...
0
votes
1
answer
86
views
Having trouble writing a query to pull 1st record from a table
I'm using Oracle SQL.
I'm attempting to write a query that queries a table that has employee timecard approval data. The problem I'm having is on one pay period, an employee has 2 timecard approvals ...
0
votes
1
answer
49
views
Is there any Oracle processes that exists only when database is in open mode?
I need to monitor whether the Oracle database is in open mode through Linux. Are there any processes that mandatorily exist only when the database is open? (not exists when mounted, or restricted, or ...
- 801
0
votes
1
answer
96
views
How to bypass ORA-01033 ORACLE initialization or shutdown in progress for a regular user?
I have a Zabbix user that can't connect to the database in mount status, I need to bypass it. Is there any workarround? it must monitor if the instance is in mount status and trigger some alert, but ...
- 801
-1
votes
2
answers
71
views
SQL breaking my query where there is NULL value
Here is my sql statement and the error I am facing:
SQL> INSERT INTO COUNTRY(COUNTRY_CODE)
select unique(country_code) from AIRPORTDEMODATA where country_code IS NOT NULL;
2 INSERT INTO COUNTRY(...
-1
votes
1
answer
94
views
Expecting "," or ")", found "nvarchar2"
Here is my SQL*Loader: Release 11.2.0.2.0 - Production on Fri Nov 10 00:39:56 2023
OS=UBUNTU 20
I am trying to load data from csv file with the help of following code:
OPTIONS (SKIP=1)
LOAD DATA
...
-1
votes
1
answer
41
views
Why does Oracle Database rounds numbers smaller than `1e-130`
Is this behaviour documented somewhere and can full double precision be enabled?
repro queries:
select 1e-130; /* 0.0...1 result */
select 1e-131; /* 0 result */
online repro: https://dbfiddle.uk/...
- 418
2
votes
0
answers
72
views
Error when creating policy in Oracle Label Security
I ran into a "failure to create trigger" error when creating policy in Oracle Label Security. Here's my code:
-- step3: Creating a Labeling Function for a Policy
CREATE OR REPLACE FUNCTION ...
- 21
0
votes
0
answers
94
views
Discrepancy Between ORACLE_UNQNAME, db_unique_name, and v$database Causing OEM Startup Issues
I am experiencing an issue with Oracle 11g where the ORACLE_UNQNAME, db_unique_name, and the name from v$database are not aligned, causing problems with Oracle Enterprise Manager (OEM) startup and ...
- 101
0
votes
0
answers
117
views
one to one relationship in SQL Modeler
USING ORACLE SQL MODELER
I am trying to make Relationship between 2 entities (Roles and Users). The Users table has role_id as a foreign key. The Relationship between Roles(1) and Users(Many) is 1:N, ...
3
votes
1
answer
1k
views
How can I determine if Oracle is installed on Microsoft Windows or Unix using SQL?
Is there a view in which I can check it? I need to create a script using the SUBSTR function by / (Linux) or \ (Windows).
SUBSTR('anything', 1, INSTR('anything', '/', 1, 1)) -- Linux
SUBSTR('anything'...
- 801
0
votes
0
answers
1k
views
how to solve error cannot communicate with CRS daemon?
I have oracle 10g 2 nodes RAC database ,
operating system HP UNIX
Electricity shut down 1 hour and database servers also shutdown after start the servers database not running and show the error when ...
- 111
1
vote
1
answer
696
views
Oracle SELECT FOR UPDATE on a single row with ORDER BY: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc
I have a control table and want to be able to select for update a single row in this control table.
However, using an ORDER BY and WHERE rownum = 1 along with a FOR UPDATE results in ORA-02014: cannot ...
- 207
0
votes
0
answers
166
views
AWS ORACLE to AWS RDS -- Error ORA-01406 even with the InlineLobMaxSize parameter set to 63
I am making a full load of a table with a 9 million rows with a lob column BLOB.
And is about 100G in space, with AWS DMS
With a Full LOB mode it takes an eterniy (More than 2 days).
I tried Inline ...
Centas
0
votes
0
answers
54
views
Cascading DR - Switchover issue in Oracle
I have 3 databases:A, B, C.
A is the primary database, and the log_arhive_dest_2 parameter is set accordingly:
log_arhive_dest_2=service='A' ASYNC valid_for=online_logfile,PRIMARY_ROLE
B is the ...
0
votes
2
answers
48
views
in the school database course they teaches us with oracle database & i have a macbook what is the best replacement database to use?
in the school database course they teaches us with oracle database & i have a macbook what is the best replacement database to use?
- 1
0
votes
1
answer
173
views
19c database suddenly using CBO on a certain query
We have had a performance problem on a particular APEX screen, ever since we had to perform a Dataguard failover 10 days ago. I'd put it down to general system load until I investigated more deeply ...
- 113
1
vote
1
answer
105
views
Wrong cardinality estimation after gathering statistics ORACLE
We have a large table range partitioned by month. Incremental statistics turned on. After scheduled statistics gathering cardinality estimation become weird, like
select count(*) from my_table where ...
- 110
0
votes
3
answers
844
views
How to prevent Oracle accounts from locking due to bad passwords by a particular source?
We're having trouble with certain clients repeatedly attempting to logon to important service accounts that they shouldn't be using with old/bad passwords, causing the account to lock(timed) and thus ...
- 653
0
votes
1
answer
63
views
Is there a way to mount Oracle DBFS on the client side?
Is there a way to mount DBFS on the client side, or can it only be mounted on the database server side?
I've searched for the dbfs_client binary in the Oracle Instant Client zip, but it appears that ...
- 801
0
votes
0
answers
141
views
How to migrate a database from Oracle to MySQL using the command line?
I am trying to migrate a database from Oracle to MySQL, with exp .dmp but I run into the problem that I must convert the .dmp file to .sql so that mysql recognizes it, for this conversion I cannot ...
Zuriel_Owsla
0
votes
0
answers
153
views
Why is my Oracle database link to MSSQL not working after successful creation?
I am trying to establish a database link between Oracle, which is installed on a Linux server, and MSSQL, which is installed on a separate Windows server. I have already:
Successfully tested the DSN
...
0
votes
1
answer
2k
views
ORA-19914: unable to encrypt backup and ORA-28365: wallet is not open
I have been taking backup of my database daily, and for some reason, I suddenly got this error
ORA-19914: unable to encrypt backup ORA-28365: wallet is not open
this is my databases's current ...
0
votes
0
answers
22
views
Restoring PDBs on a new server
I am trying to restore a 19c database on a new server using RMAN. What are the prerequisites? Do I have to first create PDBs which are supposed to be restored? Or should I just restore from ...
0
votes
0
answers
281
views
How to restore backup of Oracle Database unto same server where original database is running (and not allowed to shutdown original database)
I'm using RMAN, it's Oracle 19c and non-CDB (stand-alone). The problem is that the restored database has to have a different name, since the original database is already running on that server. ...
- 1
0
votes
1
answer
305
views
Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL
I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
- 1
0
votes
1
answer
75
views
Looking for a better design approach while migrating to Oracle from SQL Server having too many databases
We are in the process of migrating from Microsoft SQL Server to Oracle. In SQL Server we have 60 databases(like XMLDB_1,XMLDB_2,XMLDB_3, ...... , XMLDB_60) to insert data after reading 60 XML files at ...
- 5
2
votes
0
answers
97
views
Can I audit all actions of a database role?
I know from AskTom that we can audit all actions of a particular user account, e.g.:
create audit policy test_audit_policy
actions all
when q'!sys_context('userenv', 'session_user') = '...
- 193
1
vote
0
answers
25
views
Can I actively renounce roles given to me?
I am a database developer. Over the many years working in the same company I have been granted many database roles. Many more than I should actually have.
I know I could make a list of the roles I don'...
- 193
-1
votes
1
answer
102
views
In Oracle I want to insert data into a table in a db service from another db service without a database link.Both db services on the same db instance
In Oracle I want to insert data into a table in a db service from another db service without using database link.Both db services are on the same database instance.
insert into dm2m.iotCharge@...
- 5
0
votes
1
answer
46
views
Oracle MAX gives different result than ORDER BY desc
When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting:
with some_data as
(
select '123A55' as hex
from dual
union all
...
- 110
0
votes
1
answer
584
views
Oracle DB - can it run without JDK, JRE that is installed together with?
Our client is scanning for outdated JDKs or JREs and found those in Oracle DB instance files, installed files.
Is it safe for Oracle DB to remove JDK, JRE?
Name="Java(TM) SE Runtime Environment&...
- 157
0
votes
2
answers
324
views
SYS password has expired and cannot be changed due to ORA-00257: Archiver error
In the past : When I had once the error ORA-00257: Archiver error, I had resolved it using the RMAN command:
rman target sys/1234
delete archivelog all;
This time , when I try to connect to rman ...
- 23
1
vote
4
answers
112
views
Loop through 30 columns in Oracle DB to check if a value exists
I have a table which contains an ID in column one and that ID can have up to 30 different codes and dates associated with it.
ID | CODE_1 | CODE_1_DATE | CODE_2 | CODE_2_DATE | CODE_3 | CODE_3_DATE
...
- 13
0
votes
0
answers
15
views
Noarchivelog mode in logical standby
We are having one Logical standby for reporting purpose. Due to space issue I am planning to change the logical to NOARCHIVELOG mode. If i do that what will be the impact on the logical side.
I havent ...
FahimAJ
0
votes
0
answers
78
views
Problem to start sqlldr with parfile=parfile.par
If have installed Oracle Client 21.11 under Ubuntu. Sqlplus is working fine so far. Only when I try to use sqlldr in combination with parfile it is not working.
when I call
sqlldr user.../pw...@...
Joachim Bönnen
0
votes
1
answer
170
views
column size differing when querying from all_tab_columns
We create a table as follows:
CREATE TABLE "CHANGEWORKFLOWREQUEST" (
"DATA" CLOB CONSTRAINT "ChangeWorkflowRequest_data" CHECK ("DATA" IS JSON FORMAT JSON) ...
- 189
0
votes
0
answers
236
views
I can't create tablespace in Oracle
I have deployed an oracle database running on docker and use navicat to connect to the oracle database. But when I tried to created a tablespace, I failed to make it.
sql : Create tablespace tbs ...
CodeRabbit
0
votes
1
answer
27
views
Is that possible to count number of specific columns in Oracle
I have a table like this:
Is that possible to return the number of rules that pass or fail based on the no?
Thanks,
T
- 3
0
votes
0
answers
94
views
find Sqldeveloper 19.2 download link
Im trying to find a link to download and install this version but it impossible. Dont appear on oracle.
Someone knows any link or place to install it?
thanks!!
0
votes
1
answer
255
views
Configure Oracle 23c docker image in 19c compatibility mode
I need to build an Oracle 19c development environment using Docker; however there is no dev version of Oracle 19c with Partitioning available, whereas 23c does have a free version for developers with ...
1
vote
0
answers
34
views
Possible explanation for why "SELECT" query executed from JDBC returning just the first row in the table
I installed OracleDB(XE - free version) and I tried to use it in Java. I have this code:
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521/XE";
static final String ...
- 111
0
votes
1
answer
144
views
Is this enough/proper data to restore an Oracle database?
I was handed some data and was told it was a backup of an Oracle database created by RMAN and stored using Avamar. The owner would like access to the data. It is a folder named like a date, and it ...
- 101
0
votes
1
answer
143
views
Oracle query is fast from DataGrip, extremely slow within application
I have the following table(s):
CREATE TABLE items (
id NUMBER(20, 0) NOT NULL PRIMARY KEY,
assigned_to NUMBER(20, 0) DEFAULT NULL NULL,
revision NUMBER(10, 0) NOT NULL,
...
- 179
0
votes
2
answers
20
views
Regarding creating the index
there are two db we migrated the data from db1 to db2 now the db2 have some indexes missing in it and we need to create all those index in db 2 but we only have the indexes name nothing else is known
...
Suraj Kumar
0
votes
1
answer
97
views
Error when creating trigger to insert values
I am new to databases and I know this is a simple question but I cannot find an answer anywhere, so I appreciate the help.
I have 2 tables, each with the same structure.
Table1
CaseNum Number(9,0)
...
- 103
0
votes
2
answers
819
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