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
87 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
46 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
63 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
83 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
344 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,662
0 votes
1 answer
39 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
219 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?
Astora's user avatar
  • 703
0 votes
1 answer
36 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
75 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
53 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
76 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
1 vote
1 answer
45 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
209 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
41 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
62 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
122 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
83 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
39 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 ...
Astora's user avatar
  • 703
0 votes
1 answer
54 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 ...
Astora's user avatar
  • 703
-1 votes
2 answers
64 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
1 answer
28 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
39 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
  • 418
2 votes
0 answers
25 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
48 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
52 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'...
Astora's user avatar
  • 703
0 votes
0 answers
276 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
225 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
59 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
21 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
42 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
74 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
56 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
218 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
  • 578
0 votes
1 answer
36 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 ...
Astora's user avatar
  • 703
0 votes
0 answers
104 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
97 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
740 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
16 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
147 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
125 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
68 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
59 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
25 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 votes
1 answer
63 views

In Oracle I want to insert data into a table in a db service from another db service without a database link.Both db services on the same db instance

In Oracle I want to insert data into a table in a db service from another db service without using database link.Both db services are on the same database instance. insert into dm2m.iotCharge@...
to-find's user avatar
0 votes
1 answer
33 views

Oracle MAX gives different result than ORDER BY desc

When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting: with some_data as ( select '123A55' as hex from dual union all ...
Andy DB Analyst's user avatar
0 votes
1 answer
138 views

Oracle DB - can it run without JDK, JRE that is installed together with?

Our client is scanning for outdated JDKs or JREs and found those in Oracle DB instance files, installed files. Is it safe for Oracle DB to remove JDK, JRE? Name="Java(TM) SE Runtime Environment&...
Jakub P's user avatar
  • 157
0 votes
2 answers
142 views

SYS password has expired and cannot be changed due to ORA-00257: Archiver error

In the past : When I had once the error ORA-00257: Archiver error, I had resolved it using the RMAN command: rman target sys/1234 delete archivelog all; This time , when I try to connect to rman ...
eathan's user avatar
  • 23
1 vote
4 answers
79 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 ...
Rellek's user avatar
  • 13
0 votes
0 answers
13 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 ...
user avatar