Questions tagged [oracle-12c]
Oracle Database 12c. Please also tag oracle for search purposes.
913
questions
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 ...
