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,706
questions
0
votes
1
answer
26
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
93
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
75
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
33
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 ...
- 630
-1
votes
1
answer
76
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
52
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
184
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
32
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
20
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
104
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
33
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
89
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
82
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
92
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
46
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
165
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 ...
0
votes
0
answers
31
views
Performance issues after activating second remote Data Guard
I have a primary database that has two remote data guard. Second data guard was configured for a DC migration on new DC.
Second remote data guard was installed on the same DC (and subnet) with primary ...
-1
votes
1
answer
44
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
121
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
1
vote
0
answers
138
views
Is it possible to limit infinte recursion in an Oracle CTE?
In a DBMS like Microsoft SQL, there is a default limit of 100 recursions, in a recursive CTE, which can be adjusted with:
WITH cte AS (
…
)
SELECT * FROM cte OPTION (MAXRECURSION …);
As far as I ...
- 2,759
0
votes
1
answer
295
views
Oracle equivalent of PostgreSQL ARRAY_AGG
I'm looking for an aggregate function supported by Oracle SQL to collect values into arrays, something similar to PostgreSQL ARRAY_AGG. I already use listagg for strings, I use cast() with collect() ...
- 101
1
vote
1
answer
1k
views
How to clean sysaux table in Oracle 19c SE2
I have a problem in tablespace SYSAUX.
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
ORDER BY bytes DESC;
The issue is with ...
1
vote
0
answers
210
views
ORA-12840: cannot access a remote table after parallel/insert direct load txn After Switching To Oracle.ManagedDataAccess
We are encountering a strange issue at work.
For years we had a .NET Framework Windows service running with the Unmanaged Oracle Client (10.1.3001) with no problems at all.
Recently, we decided to ...
- 113
-1
votes
1
answer
80
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....
0
votes
0
answers
92
views
Advice on Oracle Flashback Data Archive
I am looking for some help / advice on using Flashback Data Archive.
In a test system we enabled FDA on a handful of tables. Before enabling FDA, our UNDO tablespaces were empty - 0 MB.
After ...
- 101
0
votes
0
answers
78
views
Can only use Oracle user when logged in as SYSDBA
I've just been setting up Oracle 19c on my Windows 10 Pro desktop following a hard drive failure. The install itself went well and I could log in with / as sysdba fine, but every user I seem to add ...
- 173
0
votes
0
answers
51
views
can I recovery specific table from .DBF files in oracle?
I have oracle 19c on windows10, I accidentally corrupted my System TABLESPACE file(Description). But my USERS tablespace Not damaged, Can I recovery my tables from this file on windows?
I haven't any ...
- 111
1
vote
2
answers
964
views
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 01589
I have oracle_19C. I get this error unable to extend index string.string partition string by string in tablespace string for users tablespace, then I create DATAFILE by mistake as shown as:
ALTER ...
- 111
0
votes
1
answer
97
views
Cardinality miscalculations leads to ridiculous execution plans
I'm not talking about stale statistics, or just simply "bad"/"non-optimal" plans.
We have a lot of complicated queries running in our database. Normally all works as expected. But ...
0
votes
1
answer
174
views
Rolling Upgrade (with logical standby) from 11g and 19c question
Let's say we have the following upgrade map:
server1 (11g production db) ---> server2 (11g logical standby db)
then we upgrade the 11g logical standby db to 19c
server1 (11g production db) ---> ...
- 630
0
votes
0
answers
59
views
Error with migration to OCI with ZDM
I am migrating three on premise databases to OCI with the ZDM tool. The three instances are INS1, INS2 and INS3.
With the INS1 and INS2 instances I have had no problems, the migration finishes ...
- 143
2
votes
2
answers
238
views
Why recover database request archived log from the future
It suddenly throw error when I trying to do our monthly backup verification, it failed on recover database command with :
ORA-00279: change 26378396150 generated at 05/31/2023 03:52:48 needed for
...
- 21
0
votes
2
answers
160
views
Why is an oracle query executed multiple times?
I am checking some AWR reports.
But I am also interested to know, why some queries are executed multiple times, even if some app job does one update or insert or select ?
I know we have 3 phases, ...
0
votes
1
answer
123
views
Slow count on empty table unexplained
My incremental materialized view refresh starting to become slower and slower. Each day there are millions of new records. So there are many deletes on the mv log table. After investigation I see one ...
- 513
1
vote
1
answer
67
views
Select rows with latest before and earliest after datetime
Suppose there is a table "Events" of types X and Y happened at some datetime. For example:
Type
Datetime
X
30.05.2023 10:00
X
30.05.2023 10:15
X
30.05.2023 10:30
Y
30.05.2023 10:45
X
...
- 11
1
vote
0
answers
486
views
ORA-27046: file size is not a multiple of logical block size
I have oracle19c container, running by docker-compose:
version: '3.7'
services:
oracle:
image: container-registry.oracle.com/database/enterprise:19.3.0.0
container_name: oracle19c
ports:
...
MiH
0
votes
1
answer
212
views
Oracle19c container cannot startup after turning on archive log
I'm running oracle19c container.
This is the docker-compose file:
version: '3.7'
services:
oracle:
image: container-registry.oracle.com/database/enterprise:19.3.0.0
container_name: oracle19c
...
MiH
0
votes
0
answers
64
views
Oracle :Invalid username/password; logon denied
Oracle throws invalid username/password when trying to login with a created user.
Oracle is installed locally.
SQL> show user
USER is "SYSTEM"
SQL> show con_name;
CON_NAME
------------...
- 101
0
votes
0
answers
66
views
Top reasons for temp tablespace to go offline
It was marked offline and it is fixed already.
We want to know the list of causes that marks the temp tablespace offline.
0
votes
1
answer
116
views
Why is the SYS user granted both the DBA role and the SYSDBA privilege?
If the SYSDBA privilege gives the grantee ultimate authority, then why does the SYS user also have the DBA role in addition to the SYSDBA privilege?
- 131
-1
votes
1
answer
256
views
I want to change the location of redo files on standby db
my db is generating redofiles to a certain path, lets call it 'old/path', and i want to move it to a new path, lets call it 'new/path' in oracle
can somebody help?
- 54
0
votes
0
answers
59
views
Do I need to pay for an Oracle EE license for OAS 7 RCU Installation?
I need to install OAS (Oracle Analytics Server) 7, but this version needs an Oracle RDBMS EE for the RCU. Is this free of charge, like the recovery catalog is for example, or do I need a full license?
- 1
0
votes
1
answer
2k
views
How to fix ORA-00600: internal error code, arguments: [ktsircinfo_num1], [5], [104], [2789130], [], [], [], [], [], [], [], []
I change the table from non-partitioned to partitioned using
ALTER table schema.table_name MODIFY partition by list(column_name)
automatic (partition p_null values(null))
but its corrupt and throws
...
0
votes
1
answer
39
views
How to query in the given order
select * from table where options=(10,223,43,1,23,54,323,32)
But the result is not coming in the given order of options. Options need to be changed frequently.
- 1
0
votes
0
answers
56
views
ZHS16GBK to UTF-8 converting issue
I've got an unusual issue. I am using oracle goldengate to migrate oracle database of ZHS16GBK charset to postgresql of UTF-8 charset. After working out a few issues, I encountered a charset issue ...
- 1
0
votes
0
answers
28
views
while starting oracle instance we facing LOG_ARCHIVE_DEST_1 problems
In Oracle, I have set log_archive_dest_1='LOCATION=C:\Program Files\sqldeveloper\sqldeveloper\bin\TSH1' parameter and shutdown the database. When I tried to start up the db., I got the following error:...
vamsi krishna
0
votes
1
answer
40
views
Can database administrators access and modify the database before it's opened?
I'm trying to answer the following question (asked in a previous exam and supposedly having one correct answer):
To create a DATAFILE and Assign it to a DATABASE, the instance can't be
started in ...
- 131
0
votes
0
answers
80
views
Cross Version EXPDP/IMPDP of Indexes
Our source database is 11g and target is 19c.
My question is about exporting and importing indexes.
Does the EXPDP command "exports" the actual index structure or just the "Create index&...
- 141
1
vote
2
answers
128
views
how to audit users with old password versions
I must set
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a
on all of my database servers.
How can I check if clients still use old authentication protocols?
I know I can check DBA_USERS for old password ...
- 163
-1
votes
1
answer
216
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