Questions tagged [oracle-12c]
Oracle Database 12c. Please also tag oracle for search purposes.
901
questions
1
vote
1answer
27 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
31 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
47 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
29 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
43 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
47 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
12 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
34 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
35 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
24 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
29 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
49 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
26 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
48 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
61 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
14 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
19 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
41 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
55 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
35 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
79 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
111 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
44 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
24 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
48 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
70 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
67 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
27 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
1answer
13 views
How can I publish a SOAP Web Service that takes data from an Oracle 12c Database? [closed]
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
1answer
28 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
36 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
78 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
265 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
51 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
143 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
39 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
43 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
71 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
110 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
655 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
31 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
45 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
257 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
49 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
66 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(...
