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
193
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
87
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
69
views
I am having trouble creating a listener in Oracle 19C
These are my active services:
SQL> SELECT NAME FROM V$ACTIVE_SERVICES;
NAME
----------------------------------------------------------------
pdb3
orcldb
pdb2
pdb1
and this is my listener.ora ...
- 62
-2
votes
2
answers
118
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 ...
- 179
-1
votes
1
answer
42
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
182
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.
- 13
0
votes
1
answer
47
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
141
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
784
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
89
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
1k
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
22
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
900
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
1k
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
140
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
3
answers
2k
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
2k
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
723
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
16
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
641
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
123
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
1
answer
377
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
3k
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
22
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
1k
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
1k
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',...
1
vote
0
answers
308
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
1k
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 "...
- 801
1
vote
0
answers
70
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
229
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
85
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. ...
1
vote
1
answer
62
views
Explain plan not showing most columns
I am running an explain plan for a query and it only returns the following columns:
explain plan for SELECT ....
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash ...
- 801
0
votes
1
answer
69
views
Flyway Migrate - External Table Create Throwing ORA-00922: missing or invalid option
We use flyway in our company to make changes to the various regions. The guy who set all this up is unfortunately left the company so I don't have anyone really to run this past.
I have a bit of SQL ...
- 11
0
votes
0
answers
253
views
ORA-01113 File 2 needs media recovery, but it's online
I am attempting the following:
C:\>sqlplus / as sysdba
SQL> startup
Gives me the output:
Total System Global Area 1720328192 bytes
Fixed Size 2176448 bytes
Variable Size 1073744448 bytes
...
Renma
0
votes
1
answer
215
views
Using at sign "@" in password Oracle Database/Forms
We have an application running in Oracle Forms which connects to an Oracle database (12c). We've always had issues with using the "@" sign in passwords so we always instructed our users not ...
- 1
0
votes
0
answers
42
views
Does Oracle execute all overdue jobs when changing job_queue_processes from 0 to another value?
job_queue_processes has been disabled for three days. When we enable it, it will execute all overdue jobs immediately or according to the job's scheduled time? or maybe we need to manually run all the ...
- 801
-1
votes
1
answer
191
views
Generating a new new tablespace because of error ora-00607
Windows XP,
Oracle 9i,
The new undo tablespace has been generated. It named UNDOTBS_01 (old named UNDOTBS02). But now the database could not be open.
It gives an error: ora-01092.
SQL\> show ...
- 1
0
votes
1
answer
155
views
Why can I call DBMS_SESSION subprograms without EXECUTE privilege?
I execute the following SQL to confirm I don't have privileges to DBMS_SESSION package:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'MY_USER' AND TABLE_NAME = 'DBMS_SESSION' AND GRANTOR = 'SYS';
No ...
1
vote
1
answer
366
views
Can JDBC Oracle file stream blob inserts be speeded up?
I have these four different ways by which I am inserting some file data into an Oracle database using JDBC and PreparedStatements.
Approach A
ps.setBytes(1, fileDataInByteArray);
Approach B
Blob ...
- 11
0
votes
1
answer
39
views
using variable in dynamic query
set serveroutput on
DECLARE
statement VARCHAR2(4000);
BALANCING_ID INTEGER;
VAULT_TILL_ID INTEGER;
balancingid INTEGER := 16437;
BEGIN
statement := 'SELECT EOD_BALANCING_ID, VAULT_TILL_ID
...
- 97
0
votes
1
answer
22
views
comparing a variable in when oracle sql
I want to use a variable in when clause of oracle sql like below
DEFINE balancing_id = 0--'16493'--null;
ON A.BALANCING_ID =
CASE
WHEN &balancing_id is null-- &balancing_id = ''--...
- 97
0
votes
2
answers
367
views
Oracle: what are the entries that can occur in ora_dict_obj_type
I'm looking to do some database level system triggers as I'm building a proxy access system into my database to stop my team sharing passwords. I have also granted roles, but some development needs ...
- 37
0
votes
1
answer
49
views
I want to store scientific numbers in Oracle, is BINARY_DOUBLE a good option?
I have a table where I store curve data/measurements e.g. points or polynomial constants. Those can be anything from normal numbers like 15000 to very small numbers like 123e-50. Is BINARY_DOUBLE a ...
- 123
-1
votes
1
answer
181
views
Oracle SQL Developer stored procedure
create table emp as
select 7782 empno, 'CLARK' ename from dual;
CREATE GLOBAL TEMPORARY TABLE emp
(empno CHAR(20),
ename CHAR(20))
ON COMMIT DELETE ROWS;
insert into emp
(empno,ename) ...
- 97
0
votes
1
answer
242
views
Oracle DBMS_SQLTUNE.REPORT_TUNING_TASK returns errors section with ORA-01031: insufficient privileges
I am trying to generate a SQL query tuning report. Given an SQL ID ($sql_id), I tried:
SELECT DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '$sql_id', time_limit => 500) FROM DUAL
Then, use the ...
- 101
0
votes
2
answers
274
views
oracle working with CTE with condition
DEFINE fromtable1= true;
WITH firstQuery AS (
SELECT columns FROM table
),
secondQuery AS (
SELECT columns FROM second_table
)
IF @fromtable1= true
BEGIN
SELECT *
FROM ...
- 97
0
votes
2
answers
56
views
Query a table with association to itself
General question here.
If a table e.g. Person has a foreign key to itself to model "parent" association and you had to do a query get anyone who is named "Peter" or their parent is ...
- 211
0
votes
0
answers
256
views
ORA-00600: internal error code, arguments: [psdghi:baseTblColName]
I am trying to create a 'before' trigger for each row on a view. The trigger will be based on an event that occurs on a specific column, which is an invisible column (hidden_column = YES).
I have ...
-1
votes
1
answer
70
views
How to rollback data mart update if data quality checks are not passed?
Good afternoon!
I am working with Oracle database. I have a PL/SQL procedure which is scheduled with DBMS job to update every night a table SOME_TABLE:
CREATE OR REPLACE PROCEDURE MY_PROC
IS
...
- 1
0
votes
4
answers
238
views
Choosing the index partition type for OLTP application
The ORDER_ITEMS table holds the data related to the orders.
Use case: OLTP. The rows will be added and the historical data(more than 3 years old) will be deleted periodically. Partition/Index ...
- 1