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.

Filter by
Sorted by
Tagged with
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 ...
Igor Oliveira's user avatar
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 ...
TimLer's user avatar
  • 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 ...
Jason Oon's user avatar
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 ------------------------------------ ----------- ...
AleksRous's user avatar
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 ...
trustno1's user avatar
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 ...
Stockburn's user avatar
  • 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' ...
user avatar
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 ...
Igor Oliveira's user avatar
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 ...
miracle173's user avatar
  • 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: ...
Ana Esquivel's user avatar
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?
Potter's user avatar
  • 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-...
Christian Bongiorno's user avatar
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' ...
Aaron's user avatar
  • 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 )...
BuZZ-dEE's user avatar
  • 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 ...
jahu's user avatar
  • 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 ...
Ian's user avatar
  • 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 ...
jahu's user avatar
  • 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 [...
andyinnie's user avatar
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 } ...
Jun's user avatar
  • 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 ...
2ps's user avatar
  • 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 ...
datascinalyst's user avatar
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 ...
RBERTRAND's user avatar
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 ...
Potter's user avatar
  • 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 ...
Potter's user avatar
  • 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(...
Java_Beginner's user avatar
-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", ...
kalisto-87's user avatar
-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 ...
Java_Beginner's user avatar
-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/...
mvorisek's user avatar
  • 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 ...
largecats's user avatar
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 ...
Trevor's user avatar
  • 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, ...
datascinalyst's user avatar
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'...
Potter's user avatar
  • 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 ...
Abdullah's user avatar
  • 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 ...
Matthew Moisen's user avatar
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 ...
user avatar
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 ...
datascinalyst's user avatar
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?
jammiie's user avatar
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 ...
Carlovski's user avatar
  • 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 ...
Andy DB Analyst's user avatar
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 ...
Paul W's user avatar
  • 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 ...
Potter's user avatar
  • 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 ...
user avatar
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 ...
Your Goal's user avatar
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 ...
datascinalyst's user avatar
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 ...
datascinalyst's user avatar
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. ...
baffy's user avatar
  • 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 ...
Niki's user avatar
  • 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 ...
to-find's user avatar
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') = '...
Thorsten Kettner's user avatar
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'...
Thorsten Kettner's user avatar

1
2 3 4 5
155