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,676
questions
1
vote
4
answers
24
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
...
- 11
0
votes
0
answers
6
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
22
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
30
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
-2
votes
0
answers
11
views
How to compare results that saved in table columns
I have two tables like below:
Besides that, I have two different lists of rules I need to check to make the decision:
A: I need to check rule 1, 2 in the table 1 and rule 1 in the table 2
B: I need ...
- 1
0
votes
0
answers
17
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
22
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
- 1
0
votes
0
answers
20
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
23
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
23
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 ...
0
votes
1
answer
33
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
38
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,
...
- 169
0
votes
1
answer
12
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
16
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
45
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 ...
- 54
0
votes
1
answer
20
views
Manage archive logs on DBCS
We are testing an Oracle OCI DBCS and I have enabled automatic backup. I saw that the archive logs have been accumulating since I enabled automatic backup and I would like to know if OCI also deletes ...
- 23
1
vote
1
answer
22
views
Database name in oracle 12 connection parameters
I am currently working with MS SSIS to copy data from MSSQL to Oracle 12.
I cannot figure out how to make connection string to some test database.
I found sample connection string for oracle .net ...
- 11
0
votes
0
answers
33
views
I am having trouble creating a listner in Oracle 19C. Some help will be appreciated
These are my active services:
SQL> SELECT NAME FROM V$ACTIVE_SERVICES;
NAME
----------------------------------------------------------------
pdb3
orcldb
pdb2
pdb1
and this is my listener.ora ...
- 54
-2
votes
2
answers
81
views
Which databases implement a difference between index scan and index seek?
Let's say I have a table:
CREATE TABLE transactions (
id NUMERIC(20, 0) PRIMARY_KEY,
account_id VARCHAR(512),
...
transaction_date TIMESTAMP
);
So with SQL Server, an index:
CREATE ...
- 169
0
votes
0
answers
27
views
Export query result to Oracle Server
I have written PL/SQL code to store XML into cursor.
Refer the below code
Requirement:
As of line at line 83, I am inserting the result into another table (XML_GEN) as XMLTYPE datatype.
Line: insert ...
0
votes
0
answers
48
views
Prometheus Database Exporter giving Errorpingingoracle:="missing port in address
I am using Oracle db exporter from https://github.com/iamseth/oracledb_exporter/releases linux version 5.1 and not using docker or similar for installation. When I am executing nohup ./...
-1
votes
1
answer
27
views
Displaying values in one column as multiple columns
I have a table which millions of records like this:
The desired format of the data is this:
how do i achieve this? tried a cross join but didnt help.
pivot is taking too long to execute as the ...
0
votes
1
answer
25
views
Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?
Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?
I am getting ORA-00942: table or view does not exist when tried to query the same.
- 1
0
votes
1
answer
42
views
Break down integer interval into rows
I'm a developer that uses Oracle database with .Net application. I successfully created a query to return some data that I want, and it returns something like this:
cod
charac1
charac2
items
I001
...
0
votes
1
answer
30
views
How to increase sequence by condition in Oracle Database 19.x?
We have the table as below.
CREATE TABLE "STYLES" (
"STYLE_CODE" CHAR(8) GENERATED ALWAYS AS
("COLOR"||"FEATURE"||"SIZE"||"YEAR")...
- 3
2
votes
1
answer
180
views
How to know how much memory is used by each DB user in the cache buffer?
Using this query:
SELECT
A.CACHE_BUFFER_TOTAL,
B.CACHE_BUFFER_USED,
A.CACHE_BUFFER_TOTAL - B.CACHE_BUFFER_USED AS CACHE_BUFFER_FREE
FROM
(SELECT BYTES / 1024 / 1024 ...
- 23
0
votes
1
answer
38
views
Restricting Direct Changes in Database Data
Is it possible for Oracle database administrator to make direct changes in data contained in a row, for example, changing an amount of 50 to 500 either by typing into the database cell or by executing ...
- 3
0
votes
0
answers
72
views
ORA 01918: user '...' does not exist
I am new to Oracle DB and I am currently working with Oracle Database 21c express edition.
After the installation, I created a common user "C##OLUAKIN" so that I don't have to do my work on ...
-2
votes
1
answer
14
views
Omit the time value
In an Oracle database, I need to omit the time value output from this query:
select *
from tbraccd inner join TBBDETC on TBRACCD_DETAIL_CODE=TBBDETC_DETAIL_CODE
where tbraccd.TBRACCD_EFFECTIVE_DATE &...
- 1
0
votes
0
answers
91
views
Error: RMAN-06172- Failed to recover oracle DB from RMAN backup files of another oracle DB
I'm trying to recover oracle DB (v11.2) in ubuntu server using rman backup that is taken from the database in the old host but failed with error: RMAN-06172: no AUTOBACKUP found or specified handle ...
2
votes
2
answers
54
views
Oracle 19 Scheduler not starting jobs
Our Oracle Scheduler isn't working in several of our environments but does work in some. In the environments where it is working, the following query returns 1 row
select program from v$session where ...
- 213
2
votes
2
answers
105
views
Getting wrong/misleading info related to oracle table index
Creating a table as follows:
CREATE TABLE "HOTEL3"
( "NAMEX" VARCHAR2(4000 CHAR),
"CITY" VARCHAR2(4000 CHAR),
"PRICE" NUMBER,
"ID&...
- 189
0
votes
2
answers
138
views
Installing DBD:Oracle on RedHat 8.8
I am trying to install DBD:Oracle.
I'm in the following environment:
Red Hat Enterprise Linux release 8.8 (Ootpa)
Perl v5.26.3
Our DBA installed the Oracle 19 client in this location:
/u01/app/oracle/...
- 1
1
vote
2
answers
68
views
Oracle: how to specify non-default backup location during RMAN restore
Oracle 19c on Windows.
RMAN scheduled backups go to FRA here: D:\oracle\recovery.
I have another backup in D:\backup\ which I want to restore. I need to be able to "tell" Rman to look into ...
- 113
3
votes
1
answer
132
views
Oracle database: how to change varchar length of same column in multiple tables in one go?
My company uses Oracle 19 and we have many thousands of tables which have two columns called CRBY and MODBY in them, which are varchar2(200). These columns get populated by a script called on an ...
- 37
1
vote
0
answers
13
views
Why am I getting a privilege error when creating a table from a trigger in Oracle? [duplicate]
I'm trying to create a trigger that creates a backup of a table before it is truncated. However, I'm getting a permission error when attempting to create a new table. Why?
create or replace trigger ...
- 111
0
votes
1
answer
34
views
How to connect / auth users to Oracle pluggable DB using SQL developer
I am struggling connecting to a pluggable DB for which I created local users.
I have an Oracle 21c XE on my machine.
The CDB is XE.
I logged into SID=XE with sys as sysdba.
There I created a PDB as ...
- 103
0
votes
0
answers
17
views
Oracle 18 - How can I add additional components to an existing client installation
When you first install Oracle Client, there are numerous checkboxes for various components.
Once you have installed Oracle Client, how can you go back an select additional components you didn't check ...
- 577
0
votes
0
answers
22
views
What are the options to mask data in Oracle standard edition (SE2)?
We have an Oracle Database Standard Edition 2 (SE2), and we need to mask some data. The only option I've considered is creating views only with allowed columns. Is there any other way in the standard ...
- 550
0
votes
0
answers
17
views
Move data between DBaaS with Azure pipeline
I need to move data from tst DBaaS to acc DBaaS, DBaaS are not connected as azure resource or something like that.
Im trying to select some data from DBaaS A and insert it in DBaaS B using Linux Agent....
Bartek
0
votes
1
answer
47
views
SqlDeveloper - Real Time SQL Monitor doesn't log statements even though I can see them in Sessions
I'm trying to trace an application that is running on my machine.
In SQL Developer, I go into Real Time SQL Monitor and run the tool. I can see various queries being logged, but none from the ...
- 111
0
votes
2
answers
115
views
Is there any query to find blocking sessions in the past? This is for Oracle database 12
I want to know which sessions were blocking other sessions, but views like v$sessions and v$locks only show me about active sessions, not sessions from other days.
I executed queries on v$session and ...
Omar
0
votes
1
answer
7
views
Flashback Data Archive Not Capturing client_identifier
Audit attribute client_identifier from the USERENV context is not being stored with flashback data archive records after restarting database instance. Oracle standard edition version 19.17
- 25
0
votes
1
answer
73
views
Index rebuild, on a busy table, in a 24/7 system
We have got an audit table, that has got pretty big (> 1 billion rows). It's got a number of indexes that are actually bigger than the table itself, that are the ideal case for index key ...
- 113
-1
votes
1
answer
80
views
Calling stored procedure with local custom type @ dblink
Newbie here. I am trying to execute stored procedure located at DBLINK. Here's my code:
DECLARE
TYPE ARRAY_VARCHAR2 IS TABLE OF VARCHAR2(32767);
Vparams ARRAY_VARCHAR2 := ARRAY_VARCHAR2('p1', 'p2',...
0
votes
0
answers
59
views
Password hash in apex oracle
i'm trying to hash or encrypt the password provided by the user in my apex oracle before store it in the DB table How can i do it ? i tried using DBMS_CRYPTO but it always generate an error saying
...
0
votes
1
answer
123
views
How to use UNNEST hint in Oracle Database?
I have encountered Bug 34044661 - Poor Performance Due to SQL Not Unnesting in Oracle 19C (Doc ID 34044661.8).
Oracle provides some workarounds:
use UNNEST hint
or
alter session set "...
- 550
1
vote
0
answers
18
views
Restore oracle database on a new machine with only datafiles and control files?
I lost my laptop and I had an oracle database 11g r2 in it but I still have all the data files, create control files script and nothing else now I bought new laptop and I want to get my data back with ...
1
vote
1
answer
48
views
Updating an oracle updatable view through an oracle_fdw foreign table in PostgreSQL fails with ORA-02014
I created an updatable view V1 in an Oracle 11g DB (it has INSTEAD OF triggers for UPDATE, DELETE and INSERT); it originally had a GROUP BY query in it, which I moved to another view V2. This other ...
- 406
0
votes
2
answers
25
views
Can a foreign key refer to a subset of parent table's rows? (Value domain constraint)
Our project massively uses so-called (enumerated) data value domains, or (coded) value domains, as defined by the Australian Institute of Health and Welfare, and in The Data Administration Newsletter. ...
