Questions tagged [oracle-12c]
Oracle Database 12c. Please also tag oracle for search purposes.
885
questions
0
votes
0answers
3 views
Run query every 5 minutes
Please find the below query:
select count(*) from v$session;
This has to be run on the database every 5 minutes. What is the right way to achieve this?
Database/Server details:
SQL> select * from ...
0
votes
0answers
15 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
0answers
34 views
the query runs longer time than expected for unknown reasons
The query executed on 4 different tables containing just ~ 6k rows running longer time than expected.(~15 min). I'm unable to figure out what really went wrong here -
Query:
SELECT DISTINCT
tableA....
0
votes
1answer
8 views
Adding underscore(_) in a record in Oracle table [closed]
I want to add underscore (_) for around 4000 records in one of table. How can I achieve this??
Note : Records are present in table like this "I am Yash" and I want to change it like this &...
0
votes
1answer
34 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
13 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
40 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
21 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
22 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
23 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 ...
0
votes
0answers
7 views
How can I publish a SOAP Web Service that takes data from an Oracle 12c Database?
I have an Oracle 12c Database. I want to extract data from this database and publish them via SOAP web services. I know to do this with REST web services (via ORDS and APEX utilities) but I find it a ...
1
vote
0answers
11 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
24 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
19 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 ...
0
votes
1answer
29 views
drop datafile in RECOVER status
I have a datafile in a recover state:
SQL> select status from v$datafile where file#=201;
STATUS
---------------------
RECOVER
I'm not really sure how it was damaged, looks like something went ...
0
votes
2answers
42 views
The listener supports no services - 2 databases on oracle linux 7
I'm running two 12c R2 databases on linux oracle 7.9
I've installed the first database (CDB) and also a listener and both of them were running fine. Then, I've installed another 12c R2 database (CDB2) ...
0
votes
0answers
34 views
Index not used in a remote query when values are supplied from a PL/SQL collection
I have a table with primary key column on a remote database:
create table tab (
id primary key,
pad
) nologging as
select level id, lpad('x', 800) pad
from dual
connect by level <= 100000;
If ...
2
votes
3answers
106 views
How can I get all data from a given table since my last run?
In querying a table in Oracle Database, is there any way to reliably and precisely get all rows that have changed (via inserts or updates) since the last time I checked?
My tables typically have a ...
0
votes
1answer
33 views
Oracle SQL- special characters delimited strings
I have a following string in my table in the following format:
APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED - 25%; GRAPES- GREEN - 20%
I want a output as:
Output
APPLE - 65%
Which is the sum of %...
0
votes
0answers
7 views
Oracle netservice name cpu utilization
Is there a way to check cpu utilization of services running.
Setup is a two node rac 12c and there are more services running in one node than the other.
Is there a way to find the cpu utilization of ...
2
votes
1answer
41 views
Where can I find SQL Developer Database examples (scripts) to add data to practice?
Where can I find SQL Developer Database examples (scripts) to add data to practice?
I require SQL scripts that will add tables and data on those tables in order to practice with Power Bi.
0
votes
0answers
50 views
Oracle 12cR2 / ORA-02800 ORA-06512
When we run database jobs, we get the following error:
ORA-02800: Requests timed out
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
For 12 ...
0
votes
1answer
74 views
How to access an Oracle database from linux
We have a legacy Oracle database hosting all of the information for a now shutdown website, we need to keep the data on the DB available for a period of time due to GDPR etc. We have had some requests ...
0
votes
1answer
357 views
Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries)
Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this:
"ORA-04031: unable to allocate 56 bytes of shared memory ("shared
pool", "unknown object", &...
0
votes
1answer
30 views
oracle sql joins returns multiple row than expected
I wanted to join table zz_value with zz_gg.
table zz_value looks like:
nach_id ins_id pos_id value data_id gg_id
2416 647415 176959 266355 166991 9863
2416 647421 176959 266355 166991 ...
0
votes
1answer
42 views
Oracle 12cR2 - Image Copy Backup Scenario
Backup type: Image copy.
Scenario: I want to 1 full db + 6 incremental backup and keep last 7 days backup on storage.
Here is my script:
RUN {
allocate channel c1 device type disk format '/path/%U';
...
0
votes
1answer
176 views
Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)
I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...
2
votes
1answer
47 views
Can I make PL/SQL functions more strongly typed?
I like to use overloading to speed up development, and I want to structure my code to make future changes easier. I have a function that accepts a NUMBER or a VARCHAR2, but I want it to throw a ...
0
votes
1answer
55 views
Learning COMPUTED BY - what right parenthesis does it need? [closed]
I am reading this document on Automatic columns.
The first snippet in it creates a COMPUTED BY column:
create table PERSON (
(employee_id integer,
employee_id_disp computed by
SUBSTRING (CAST(...
0
votes
1answer
29 views
Is this a valid query that could allow to detect whether compression is supported or not?
I am developing against a demo Oracle instance and have no access to compression. My code has to detect whether compression is available or not. My thought was to run this statement in a try/catch ...
0
votes
1answer
39 views
How do I interpolate months in PLSQL
I'm trying to find a way to include all months in a result set
my query looks like this:
select to_char(changedate, 'YY-Month') Mon,
sum(downtime) dt
from (select * from assetstatus
where assetnum ...
0
votes
2answers
154 views
Oracle 12cR1 - RMAN-11003, ORA-00354, ORA-00353 - Corrupt Redo Log
Oracle 12cR1, disaster database.
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 8 change 80357235718 time
11/01/2020 ...
0
votes
1answer
111 views
Oracle 12cR2 RMAN - ORA-01547, ORA-01194, RMAN-06054
Oracle 12cR2.
Error:
channel default: deleting archived log(s)
archived log file name=+RECO/.../thread_1_seq_166200.26582.1054942673
RECID=62 STAMP=1054943183
archived log file name=+RECO/.../...
0
votes
1answer
26 views
Read explain results from oracle database
hi currently i have query like this on oracle database 12
Query Detail because script is too long if input here
the query is too long execute time
from explain results i got this
PLAN_TABLE_OUTPUT
...
0
votes
1answer
39 views
Enabling TLS and TLS-MA simultaneously for different clients of a Oracle DB
We have a requirement where we want to enable TLS-MA for some of the clients connecting to a specific Oracle database while the other clients can continue to use TLS with server certificate.
We are ...
0
votes
1answer
27 views
Can't create view using a role
I am learning Oracle and have to do roles. I created an c##admin from a sys, granted him all grants. Then I created a c##manager_role and c##manager. I granted this role to c##manager. After that I ...
0
votes
0answers
135 views
How can I call a SQL Server stored procedure from Oracle and retrieve a scalar return value?
I'm developing a PL/SQL solution in Oracle Database Enterprise Edition 12.1.0.2. I'm calling stored procedures in Microsoft SQL Server 2014.
I am using DG4ODBC with the DBMS_HS_PASSTHROUGH package to ...
1
vote
0answers
125 views
Anomalous performance of mass INSERT in Oracle
Can you please help me diagnose, explain, and possibly solve the
problem of unstable peformance of mass insertions in Oracle?
I need periodically to load large amounts of data into an Oracle
database. ...
0
votes
1answer
124 views
When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY'
I am working on a database migration from Oracle 12c to Postgresql 12.
We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed.
...
0
votes
1answer
445 views
ORA-01017: invalid username/password; cannot login with other users except sys user, Oracle Database 12c?
I have setup Oracle Database 12c on my local machine. It was working fine initially but now I cannot login to users' account except the sys user. I have altered users' passwords and tried logging in ...
0
votes
0answers
16 views
Impossible to create an SQL Server function that calls an Oracle function?
I have been trying too many hours to create an SQL Server Scalar Function that calls an Oracle function.
Imagine you have a function in oracle named "Stock_Orcl" that returns the stock for a ...
0
votes
1answer
30 views
Understanding Logging Priority & Options in Oracle DB
From my understanding once we switch on database logging, the entire DB goes into logging mode and generates redo logs.
I want to categorically exclude/include some tables/tablespaces from this ...
0
votes
1answer
30 views
How do I exclude unchanged fields in Flashback query resultset?
Is it possible to exclude unchanged fields in Flashback query resultset?
Consider I have following table
create table first_table
(
id int generated as identity,
name NVARCHAR2(1024),
age ...
0
votes
0answers
35 views
Changes in OraOleDB v18
I use OraOleDB (Oracle's OleDB implementation) in a C++ application on Windows to create tables and insert rows. In the application it is also possible to connect with Oracle using ODBC through OleDB.
...
0
votes
1answer
2k views
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
I am trying to SUBSTR the first 3 character in use this query -> CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE ...
1
vote
1answer
37 views
Selecting a record based off conditions CLOB
I have a table with a column storing JSON data as a CLOB. The JSON has a 'miscData' field that is made up of an array of JSON objects. Each object has 2 keys which are always the same, but the values ...
0
votes
2answers
24 views
Recursive multiplication
How can I get ASSY_EXT_QTY to be the quantity of all the path?
IE:
LVL QTY ASSY_EXT_QTY
1 1 1
2 3 3 1*3
3 2 6 1*3*2
...
I thought making a path but for the quantities and ...
0
votes
1answer
132 views
How to enable “only” drop partition DDL for Oracle Golden Gate?
I'm struggling with how to do this, and hoping somebody could assist with some ideas.
Oracle Golden Gate - general question. Version shouldn't be specific, however, I am currently running on:
Golden ...
-1
votes
1answer
92 views
CREATE VIEW WITH LEFT JOIN - FILTER RIGHT DEPENDING ON LEFT
I am using Oracle 12.1.
I have the following tables, simplified for the question :
(Note that the XXX for entity ID does represent the same ID being repeated)
Events
date_event | entity_id | used
-----...
0
votes
0answers
260 views
Dbca hangs at 59%
Im creating database in oracle 12.2.0.1 on windows 2012 r2 64 bit and Dbca hangs at 59% of completion in db clone step.
I've checked the dbca logs and it's stuck in datapatch.bat step.
When I googled ...
