Questions tagged [oracle-12c]

Oracle Database 12c. Please also tag oracle for search purposes.

Filter by
Sorted by
Tagged with
0
votes
1answer
40 views

Oracle simple count(*) SQL query takes "forever"

I'm encountering an "issue" (at least a strange/slow behavior) with a new installation of Oracle 12c on Windows Server 2019, with this query which takes at least 5 minutes to complete: ...
0
votes
2answers
15 views

What do I need to put in pfile to change NLS_CHARACTERSET?

Im not an expert in databases. I am using docker Oracle 12c database for local dev environment. Its an official image from Oracle but they dont give any option to change the NLS_CHARARACTERSET during ...
0
votes
1answer
23 views

How can i disable opatch job?

I have a docker 12.2 oracle db for localhoat dev. I don't care if it's patched regularly. I'm seeing constant errors In qopatch queries. I think its triggered by some batch job at startup and maybe at ...
0
votes
1answer
26 views

Different response time because it does not use the indexes created with the same query and same tables from the web server to the Oracle database

I have a database with Oracle 12c (12.1), where when making a query locally and even from TOAD or another remote tool, it returns the response in milliseconds, reviewing the execution plan I can see ...
-3
votes
1answer
28 views

I tried running this script, but i kept getting not a group by error [duplicate]

select a.*, b.Start_Date, b.End_date, sum(b.cost) as total_Cost from Ess.TZeewrld a left join Ess.Wrldest b on a.account_number, = b.account_number, group by b.Start_Date, b.End_date;
0
votes
0answers
16 views

Query xml multinode syntax

I have a table containing an XMLTYPE column that I have to parse and display contained values one per line: Example 1 : <c2>VLAN1</c2><c2 m="2">VLAN2</c2><c2 m=&...
0
votes
1answer
18 views

Can't the Password File In Oracle 12.2 C

I intend to to make password file, spfile and control files as part of my backup script, but i however i have been trying to get find the password file in the $ORACLE_HOME/dbs location but its not ...
0
votes
0answers
23 views

SQL query to fetch data between 2 date columns from 2 different tables

I am trying to fetch data from two different tables which contain two date columns: ENCODINGDATETIME and CREATEDON. I need those records where days gap between the ENCODINGDATETIME and CREATEDON ...
0
votes
1answer
16 views

Combining conditional copies and literals in an INSERT INTO statement

I have a table with a primary key and two foreign keys. I am trying to copy data from the source of one of the foreign keys conditionally, and use a literal for the other foreign key, somewhat like ...
0
votes
2answers
30 views

How to Escape dot(.) In a substitution variable

I have a variable called ERROR_DETAIL and inside my file file.sql I have this line: SELECT 'error detail : &ERROR_DETAIL ' FROM DUAL; Once executed sqlplus complains with the following error ...
0
votes
2answers
54 views

How can I configure Automatic Memory Management for certain target values?

I am a little confused about the Oracle Database configuration. My server: Operating system: Windows Server 2012 R2 64bit RAM: 40GB Oracle: Oracle Database 12c (12.1.0.2.0) 64bit My aim: Oracle ...
0
votes
1answer
31 views

Regex replace to ignore certain pattern at the beginning if exists and format the rest

How to use regex replace to ignore country code if it exists, I tried to use select REGEXP_REPLACE(phone_number,'^/(?:011.44.)([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})$', '\1-\2-\3') ...
-1
votes
2answers
34 views

How do i recover oracle sys password for oracle 12c when login as sqlplus / as sysdba

I have got a DB Oracle 12c R2 and i am able to login as: $sqlplus / as sysdba But however my problem is that i dont even know what is the sys password used there just in case I need to do db recovery. ...
1
vote
1answer
36 views

Oracle gather stats autotask being interrupted before maintenance window ends

a few months ago I managed to enlarge the maintenance windows for autotask on a pluggable database, this way: BEGIN dbms_scheduler.disable( name => 'WINDOW_NAME'); dbms_scheduler....
3
votes
2answers
62 views

Getting "ORA-04030: out of process memory" on medium insert with large pga and sga

Running on Oracle 12.1.0.2 AIX Power9 (yes, I know it's 2021, but that's the customer I've got) Executing a plsql block that includes 1000 inserts as plain text and commit in the end. begin insert ...
0
votes
0answers
26 views

Problem whit instalatión of GridInsfrastructure DNS/NIS name service

When trying to install the Grid Infrastructure 12, I have the following problem: This is the description of error: DNS/NIS name service - This test verifies that the Name Service lookups for the ...
1
vote
1answer
38 views

To trim particular value in a column in Oracle sql

I have a regular expression select statement like below : SELECT REGEXP_REPLACE(nvl(l.text_1, l.text),'^'||21810||'=|\|' ||21810||'=', '|'||21810||'='||'B1') ...
0
votes
1answer
37 views

Oracle database 12C restore script

i'm trying to create backup restore script . in short my backup script creating new directory every day from the operating system level and then backup the database using the directory created i tried ...
-1
votes
1answer
48 views

Oracle Execution Plan - Outline Question

I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies ...
0
votes
0answers
160 views

UTL_HTTP.get_response is not able to read special character in Supplier name

I'm making a call to JSON based REST API from PL/SQL. Whenever there is special character in Supplier name, UTL_HTTP.get_response gives this error: ORA-29268: HTTP client error 400 - Bad Request For ...
0
votes
1answer
92 views

Data Guard 12.2 without Force Logging

I would like to know your opinion and experience in a specific setup I want to apply to a Production Database that is configured in Oracle Data Guard with one Standby. It is not an active data guard ...
0
votes
0answers
104 views

Closed Connection java.sql.SQLRecoverableException

During installation of an application, got following error for Oracle database - Closed Connection java.sql.SQLRecoverableException: Closed Connection at oracle.jdbc.driver.PhysicalConnection....
0
votes
2answers
34 views

Is the content of each datafile in the same tablespace the same in Oracle database?

On Oracle 12c, is the content of each datafile belonging to a single tablespace the same? If yes, is it because of performance or backup purpose thus recommanding us to store each datafile on ...
0
votes
0answers
20 views

Oracle SQL grant privilege to fetch xml from a website

I need to fetch data from a website in XML format, I added the website's certificate to the wallet. the XML for example can be found here: https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?...
1
vote
1answer
44 views

Oracle Linux 6.10 - Changing the Oracle user's ID & GID

OS: Oracle Linux 6.10 DB vers: 12cR1 I will backup and restore from NFS shared disk for a preprod server. However, the ID and GID of the oracle user on the production server (shared disk on ...
0
votes
0answers
40 views

How to easily keep versioned data in an Oracle table

I currently have some data stored in a table in an Oracle 12c database. To simplify, it has a K column (the PK) and a D column (some data). I would like to store versioned data : Add a V column to ...
0
votes
1answer
40 views

Oracle Fine-Grained Access (FGA) auditing excluding one source

I want to audit DML statements coming from other places than application user "tomcat" (OS_USER attribute). I want to focus only on developers or support team changing data in any way. DML ...
0
votes
1answer
31 views

Connect two remote oracle databases as a schemas on each other

This Question is about Oracle Database. I have two remote databases DB_1 and DB_2, and I have two schemas on each database: Schema DB_1 and Schema DB_2 on database DB_1, and Schema DB_1 and Schema ...
0
votes
1answer
143 views

Oracle 12: Using compound trigger to update data in same table on each insert/update

I need some help fixing the code of a compound trigger. I have a table with two columns that are used for auditing (upd_by and upd_time) and I'm trying to create a trigger that would update these ...
0
votes
0answers
33 views

Privileges to select any table in Oracle

I'm using a Oracle database client application that connects to 1521 port to do "select" commands over every table in a Oracle db 12.1. I told dba to create this role with these commands: ...
0
votes
0answers
55 views

ORA-01652 - How to reduce TEMP segment usage?

In production we have a failing batch with an error: ORA-01652: unable to extend temp segment by 128 in tablespace MYTABLESPACE The TEMP segment has already been increased to its maximum tolerable ...
0
votes
2answers
286 views

Ora 01017 Invalid Username Password; with correct data

I am new to oracle (enterprise:12.2.0.1) and after installation I logged in with connect sys as sysdba. I also created a new user with: alter session set "_ORACLE_SCRIPT"=true; create user ...
0
votes
1answer
17 views

Estimate Oracle tablespace required before loading data

I have files ranging from 450 to 750 million rows, which I need to load into our Oracle environment. The DBA asked me to give an estimate on how much tablespace I would need. How to estimate the ...
0
votes
0answers
30 views

Plsql query to split data based on startdate and end date

I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly) For example if the Startdate is 2015/10/02 and the Enddate is 2015/12/22 and my date range is ...
0
votes
0answers
16 views

rails 6 activerecord-oracle_enhanced-adapter drops support for primary key trigger with Oracle 12c database

I have a stable Rails 5 application with models configured with the :autogenerated sequence name parameter allowing Oracle to execute database triggers on insert. I am attempting to migrate the ...
0
votes
1answer
81 views

Convert String date format in Jalali to Gregorian

Hello I have The data table in oracle SQL 12c with 2 col one is thetime , and another is theh the data stored in cols are varchar and format like this : THETIME | THEH 14000207| 1245 14000207| 1230 ...
0
votes
2answers
84 views

Bigfile in oracle 12 is full with 120G

Hello I have The tablespace with 8192 and According with this Link From Oracle BigFile I can have 32,768 GB In my tablespace but one of my table have over 1 billion records that make datafile over the ...
0
votes
1answer
63 views

Copy a billion records from one table to another

I have Oracle database 12 with tablespace with bigfile Yes I have limited to 120Gb file data so I need change my tablespace to bigfile no and create multi datafile for my tablespace. I need to export ...
0
votes
2answers
160 views

Can I change the profile of oracle sys user?

Are there side effects of changing the profile of sys to any other than default? Our monitoring keeps telling us the password of sys has expired. The "Default“ profile has a standard limit of 180 ...
0
votes
1answer
285 views

Starting oracle rac node2 error: ORA-29701: unable to connect to Cluster Synchronization Service

I have a oracle RAC /12c/. I got these error when I try start the base from node2. ```ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/mydborcl/...
0
votes
0answers
34 views

INNER JOIN taking so long for even simple query

My inner queries when executed independently works fast(<2 sec). I have then applied INNER JOIN on two sub query results and final output takes around 25 minutes. SELECT DISTINCT tableA.consumer, ...
0
votes
1answer
55 views

Oracle 12cR1 - Duplicate Database With No Resetlogs

Database version: Oracle 12cR1 Operating system: Oracle Linux 7.x ASM: yes Duplicate script: cat duplicatetodisaster.sh ORACLE_UNQNAME=xxx ORACLE_SID=xxx ORACLE_HOME=xxx rman target sys/***@PROD ...
1
vote
1answer
47 views

What is the difference between start and enable in the Oracle RAC srvctl?

In the Oracle 12c documentation it was not clear what is the difference between the srvctl start and srvctl enable commands for services associated with a database. In the view dba_service a database ...
0
votes
0answers
52 views

Oracle 17410(connection reset) when using count(*) on cross join cte with json_table

Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details. Illustrating artificial query is: with data as ( select 5 as some_value from ...
0
votes
1answer
157 views

ORACLE execute error: ORA-01950: no privileges on tablespace 'PDATA'

I'm new in Oracle, so maybe my question could be stupid. I use Oracle only for data storage. I have made some research but I'm blocked. I use Oracle 12c. I created a PDB with admin user PEEI_SYS like ...
0
votes
0answers
116 views

ORA-7445 kills PMON instance

I restored an Oracle 12.1.0.2 database using RMAN full backup from a RAC+ASM enviroment to single instance enviroment with less memory and cpu (for test purposes) using this document Doc ID 1338193.1. ...
0
votes
1answer
43 views

How to audit a specific query in Oracle

I want to audit a specific SQL query in Oracle. However I think we can audit only users or table level. For example I want to audit 'audit statements', such as who run the "AUDIT CREATE SESSION ...
1
vote
1answer
52 views

ODI remove auto lock when object / datastore is opened

I was being stupid and enabled automatically lock object during edit / opened when I was asked the first time. Now I want to remove the automatically lock from GUI but I couldn't seem to find it. I ...
0
votes
0answers
51 views

Oracle 12cR2 - Upgrade Catalog Command Taking Too Much Time

I patched my database (Windows database bundle patch 12.2.0.1.201020 (31654782)). There are 6 instances on the server. After the patch, the UPGRADE CATALOG command I ran on RMAN for the first 5 ...
0
votes
1answer
20 views

SQL - Get the values of another column for values in a given column

I am working with a large data table. I want to check the individual items with their destination and the count of destinations if there are more than 1 destination for a given item. So in the below ...

1
2 3 4 5
19