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,710
questions
0
votes
1
answer
23
views
Validate "insert", "update", "delete" in the body of a trigger
I need to create a query where I can remove triggers that have "insert", "update" or "delete" in their body from the result.
I'm using the following query for this
SELECT ...
0
votes
1
answer
40
views
oracle pdb Relocation to new server
We plan to migrate the data (8 PDBS with at least 50 TB Data) of our old oracle server 19c to the new one (same version) and we must do this task with minimal or no downtime. For this reason we can ...
- 161
0
votes
0
answers
11
views
Oracle latch:shared pool waits causes application disconnection
I'm in need of help regarding some very weird behaviour on 1 of my production Oracle 19c database. This database was upgraded from 11g some time ago & after the upgrades the applications had been ...
1
vote
1
answer
42
views
Oracle spfile value is empty
I create spfile from pfie but after restart spfile value is empty
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ...
- 23
0
votes
1
answer
36
views
How do I launch sql script on every pdb using catcon with a SPECIFIC USER?
I need to launch some pl/sql on all pdbs in a cdb, loggin in as a specific user which doesn't exist on the cdb$root. I thought I could use the catcon perl script, but I tried in different ways and I ...
- 9
0
votes
2
answers
34
views
Oracle Batch Deletion
I have an Oracle table that has over 1.8 Billion rows, I need to delete up to 260 million of these rows.
I am a Microsoft SQL DBA, so not fully comfortable with the Oracle syntax and best practices ...
- 429
0
votes
0
answers
42
views
how to solve the issue of missing datafile and tablespace
how to solve the issue datafile mistakenly deleted something
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'
...
Dilip Kumar
2
votes
1
answer
50
views
How to locate a procedure in the body of a trigger
I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body
-- I'm using ...
1
vote
2
answers
51
views
How to configure the location of the Oracle password file?
When I start an Oracle instance with sqlplus the paths to the datafiles are stored in the control files, the control file name is stored in the control_files initialization parameter. The ...
- 7,664
0
votes
1
answer
21
views
Oracle Redaction and Definer's Rights Stored Procedures
0
I created a data Redaction policy for a field EMAIL in a Person Table in an ORACLE Data Base 12c.
In the expression statment of the Redaction Policy is the exception for the Data base owner user: ...
0
votes
1
answer
60
views
How to find out when Oracle table statistics became stale?
ALL_TAB_STATISTICS has a column named STALE_STATS, but is there a way to determine the date when it became stale?
- 630
0
votes
1
answer
25
views
Oracle colsep is adding newlines. Why?
So, I have a script:
set heading off;
set feedback off;
set linesize 10000;
set newpage NONE;
set colsep '~'
whenever sqlerror exit 1;
WHENEVER OSERROR EXIT 255;
select TO_CHAR(je.key_date, 'YYYY-MM-...
1
vote
1
answer
30
views
Oracle multiple PIVOT clauses
I have data with 2 calculated bucket columns I would like to summarize with a PIVOT query. This is an example set of pre-generated data. My bucket columns here are sec and pct.
select '1' id, 'a' ...
- 113
0
votes
1
answer
37
views
How to get all rows which created or updated for a specified day
I trying the count rows in Oracle database table which are created or updated for a specified day.
SELECT COUNT(*)
FROM (
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), COLUMN_A, COLUMN_B
FROM TABLE t
)...
- 131
0
votes
0
answers
35
views
How to drop a datafile of an already dropped tablespace
In an 18c Oracle database I have a tablespace that has been dropped. I need to add this tablespace anew, but when I try to do so I get this error:
ORA-01537: cannot add file 'MYTABLESPACE.DBF' - file ...
- 113
0
votes
0
answers
41
views
oracle database crashed
Fatal NI connect error 12537, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version ...
- 1
1
vote
1
answer
36
views
For a tablespace what does the value 3 mean in the online column?
I have a problematic tablespace that I cannot get rid of. When I try drop it, I get informed that it doesn't exist, but when I look into TS$ it's definitely there. The only clue I have right now, is ...
- 113
0
votes
1
answer
35
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
30
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
20
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
46
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
81
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
29
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 ...
- 630
0
votes
1
answer
31
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 ...
- 630
-1
votes
2
answers
58
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
0
answers
66
views
ORA-27369: job of type EXECUTABLE failed with exit code: 5 Login executable not setuid-root
I try to run executable job in Oracle 19c and the error occurs:
ORA-27369: job of type EXECUTABLE failed with exit code: 5 Login executable not setuid-root
ORA-06512: at "SYS.DBMS_ISCHED", ...
-1
votes
1
answer
21
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
36
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/...
- 398
1
vote
0
answers
10
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 ...
- 11
0
votes
0
answers
31
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
37
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'...
- 630
0
votes
0
answers
89
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
51
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 ...
- 197
0
votes
0
answers
17
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
15
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
39
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
56
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
37
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 ...
0
votes
3
answers
93
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 ...
- 520
0
votes
1
answer
31
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 ...
- 630
0
votes
0
answers
61
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
55
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
251
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
13
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
57
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
59
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
64
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
29
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
24
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