Questions tagged [oracle-19c]
Oracle Database 19c. Please also tag oracle for search purposes
126
questions
-1
votes
0
answers
6
views
Generating XSD from a query that generates XML?
We have a lot of existing SQL/XML queries that take normal relational data and generate XML from an Oracle db, like so (note that real queries are much more complex than this, involving nested ...
- 99
0
votes
0
answers
13
views
Create oracle scheduler fails
I want to create a scheduler that runs everyday at certain time, but it is never created. Is this correct :
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'LOG_MEMORY_USAGE',
job_type => '...
- 41
0
votes
0
answers
7
views
Oracle Reference Partition migration to Postgresql
We are in process of migrating from Oracle 19c to Postgresql v13. We have a table in Oracle which has numerical primary key and is range partitioned based on a date column. This table has 3 child ...
- 101
0
votes
1
answer
25
views
applications connect to oracle scan ip
I have an Oracle 19c RAC cluster that contains two nodes (say, Node A and Node B). My applications are connected to the SCAN IP along with the service handled by Node A. Is this a good practice, or ...
- 3
0
votes
1
answer
36
views
Upgrade 12c client to 19c client
Does Oracle 19c client upgrade 12c client if installed or does 12c client have to be uninstalled prior to 19c installation?
- 1
0
votes
0
answers
24
views
Oracle 19c - Multiple If Conditions
create or replace TRIGGER trigger_name
...
...
BEGIN
IF SYS_CONTEXT (...) IN (...) THEN
IF SYS_CONTEXT (...) NOT IN (...) THEN
IF SYS_CONTEXT (...) NOT IN (...) THEN
INSERT INTO owner.table_name (...) ...
- 29
0
votes
0
answers
38
views
Unified Audit record write to audit trail table failed with Unified audit disabled!
on a production database I keep on observing the following error:
2022-08-15T16:58:36.196692+02:00
Unified Audit record write to audit trail table failed due to ORA-4031. Writing the record to OS ...
- 19
-2
votes
1
answer
34
views
RMAN ORACLE issue
I have the issue when I run the command RMAN as mentioned below :
Please any help
- 1
0
votes
0
answers
48
views
Oracle 19c / Rman-10038: Database Session For Channel channel_name Terminated Unexpectedly
I am getting this error while database restore.
This is the first time such an error has been getting from a process that has had no problems for a long time.
Backup files are on NFS mount.
Total ...
- 29
0
votes
0
answers
20
views
PL/SQL get the session ID (SPID) during a stored procedure on Oracle 19c
I'm trying to get the session ID (SPID) during a stored procedure on Oracle 19c.
In Oracle 12c the following query was enough:
select s.sid, p.spid, substr(s.username,1,20) MY_SCHEMA, s.terminal, p....
- 1
0
votes
0
answers
19
views
Can I run scheduled job as sysdba?
I have user c##admin in my core DB (Oracle 19c), with "sysdba" privileges.
I want it to select from a pdb using a database link without giving a password.
assuming the following:
PDB Name: ...
- 814
0
votes
0
answers
13
views
Best approach to calculate PCTFREE for tables with high chained_row_ratio after ANALYZE TABLE xxx LIST CHAINED ROWS
On two different databases (Oracle 12.1, 19.11) I've seen in V$SESSTAT a high ratio of "table fetch continued row" of at least more than 10% and I'm searching a good approach to calculate a ...
0
votes
0
answers
22
views
Error ORA-00600 on startup
My oracle instance crashed and now when I try to start it I get this error:
startup mount
ORACLE instance started.
Total System Global Area 3690986544 bytes
Fixed Size 9141296 bytes
Variable ...
- 21
1
vote
1
answer
36
views
Oracle 19 - RMAN can't connect to database
I have troubles with starting RMAN and connecting to target database.
I start RMAN ($ORACLE_HOME & $ORACLE_SID env variables are set)
[oracle@MY_MACHINE /]$ /u01/app/oracle/product/19.0.0/dbhome_1/...
- 13
-1
votes
1
answer
44
views
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1600M ORA-01078: failure in processing system parameters
I'm getting the following error:
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1600M
ORA-01078: failure in processing system parameters
How do I fix this error?
0
votes
0
answers
20
views
Are there a logs to harvest on Oracle Active Data Guard instance?
We are trying to see if there is a way for cloud/SaaS-based( like AirByte or Fivetran) CDC/replicate provider to harvest logs on Oracle active data guard instance.
Any ideas?
- 1
1
vote
0
answers
189
views
impdp: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
I'm trying to import a large database, and after a while I get:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
I know this has been asked before, but I can't find a clear ...
- 111
0
votes
1
answer
32
views
In Oracle SQL 19c, how can I INSERT a new record into a supertype and its related subtypes in one statement?
I'm designing a property management database that will hold - OWNER, CUSTOMER, and STAFF information.
As these use many of the same attributes, I intend to create tables for PERSON, ADDRESS and ...
- 1
2
votes
1
answer
75
views
Rotate passwords with zero down time
Big question: How to rotate passwords in an Oracle database in a zero downtime (ZDT) way?
My current thought is to rotate the users. Originally, I had MY_USER that had all the tables and such. Now, ...
- 123
0
votes
0
answers
26
views
Oracle 19c automatic indexing
It seems Oracle automatic indexing feature that was introduced in Oracle 19c works only on Oracle Cloud and not AWS RDS.
Is my understanding correct (that AWS RDS doesn't support it)?
Reference: https:...
- 25
0
votes
0
answers
22
views
Migrate Oracle database to new server
I am looking to migrate an Oracle database with 2 instances to a new server. Here is some quick info
Old server: SLES 12.4 with Oracle 12.1.0.2.0 Standard Edition
New Server: SLES 15.3 with Oracle 19....
- 1
0
votes
0
answers
158
views
Noaudit and Drop Audit Don't Work for Oracle 19c
I've enabled unified auditing at our production database (ver. 19.14). Created policies for auditing all dml's and select queries. After seeing that it's filling up SYSAUX table, i decided to disable ...
- 111
0
votes
0
answers
70
views
Oracle 19c-ee CTX_REPORT.INDEX_SIZE and CTX_REPORT.INDEX_STATS cannot be executed
We created a simple search index, based on a 19c-ee instance of the official Oracle Docker images, with the following script:
CREATE TABLE EVT (
ID VARCHAR2(36) NOT NULL,
EVENT BLOB NOT ...
- 11
0
votes
1
answer
30
views
How to get DML SQL of uncommited statements in Oracle 19c?
I'd like to know if there is a way to get the DML (insert, delete) SQL of uncommitted statements in Oracle 19c in my own session?
For example, if I have typed some statements and I haven't committed ...
- 193
0
votes
1
answer
174
views
ODA X8-2M crossrestore with TDE wallet fails with: DCS-10001:Internal error encountered: failed to open the tde password based wallet
We are trying to crossrestore a TDE encrypted Oracle Database 19.12 on a new ODA X8-2M.
TDE wallet has been successfully backedup (odacli create-backup -in CDBET015 -c TDEWallet) and transferred to ...
- 898
0
votes
0
answers
238
views
When cloning a PDB over dblink in Oracle 19c, is it possible to use file_name_convert without specifying entire paths?
Context
Oracle version: 19.13
On server 1 I have a production database, with a CDB named (for the purpose of this question) CDBPROD and a PDB named PDBPROD.
The datafiles are distributed in multiple ...
- 423
0
votes
1
answer
179
views
What could be the reason for this invalid objects after trying to upgrade?
We recently wanted to upgrade our Database but didnt get to do it as we got a lot of Audit specific invalid objects, after looking online for ressources we couldnt find anything specific reason for ...
- 1
0
votes
0
answers
38
views
How to trickle down order by and limit in oracle views
I have oracle standard edition 2 v19 and we imitating partitions on huge tables using view with union all.
all in all it is working well. until we need to sort.
I would like to know if there is an ...
- 437
1
vote
0
answers
76
views
How to check compatibility of PL/SQL written for Oracle 9i but now to be migrated to Oracle 19c
I have lots of PL/SQL scripts which were developed years ago for an Oracle 9i application which should be migrated to Oracle 19c. I have read that normally there shouldn't be major changes in the PL/...
- 11
0
votes
0
answers
123
views
Oracle RDS Logminer ORA-01291 Missing Log File?
I have an Oracle RDS database I manage, and I need to be able to pull changes from the database for replication to another system. Once connect to the database, below is the commands I run.
EXEC ...
- 101
0
votes
0
answers
366
views
Set 'WE8ISO8859P15' character set for existing Oracle 19c DB while migrating 12c DB
I have a database in 12c (12.2.0.1) which I need to migrate to an existing live oracle 19c DB.(This DB already hosts a couple of live DBs’s)
Character set of DB’s are as below:
12c -> ...
- 1
0
votes
1
answer
57
views
Can I convert a local user to a common user in Oracle?
I have a local user in a pluggable database and it has data, objects etc. under its schema.
Can I easily convert this user to a common user without losing its objects etc.? (Oracle Database 19c)
- 103
0
votes
1
answer
232
views
How to create a custom database with 32k block size via DBCA silent mode?
I am trying to create an Oracle 19c single instance (warehouse) with dbca silent mode. The problem is, when I try the command below, it gets a warning: "Ora-0058: DB_BLOCK_SIZE must be 8192 to ...
- 111
0
votes
0
answers
125
views
Oracle 11g to 19c - Upgrade Error
We upgraded our test database from 11gR2 to 19c (19.12) (OS OL 7.9).
After the upgrade, all the components in the dba_registry seem to be in the UPGRADED state, not VALID. At the same time, we ...
- 29
0
votes
0
answers
715
views
How to solve Error ORA-16810: multiple errors or warnings detected for the member?
Oracle Enterprise Manager 13.5 constantly reports Error ORA-16810: multiple errors or warnings detected for the member.
...
Target type=Database Instance
Target name=xxxx
Categories=Availability
...
- 898
0
votes
2
answers
409
views
How to connect to Oracle RAC from HOST
For a college project, I have to set up a RAC Instance and an application, I already managed to configure my RAC lab by using virtual machines by using this video guide, now I just need to program an ...
- 103
0
votes
0
answers
126
views
See Max Simultaneous Sessions Per User
In an effort to set a reasonable sessions_per_user profile setting for shared database accounts or other non-unique account (ie. an account an application uses to log into the database for all app ...
- 173
0
votes
1
answer
1k
views
How do I SHOW TABLES in Oracle Database?
I'm trying to do a SHOW TABLES command in my Oracle Database 19c as documented here.
But when I run that command all I see in the output of SQL Developer is this:
Show tables handled.
How can I get ...
- 115
0
votes
1
answer
62
views
Does trailing wildcard use index? (where column like 'value%')
I have a query on a Varchar2 field in an Oracle table:
select column from tbl where column like 'value%'
That query uses a trailing wildcard.
Question:
Is Oracle capable of using an index on that ...
- 1,387
1
vote
0
answers
62
views
Extremely slow updates to small Oracle table from rails app (oracle r12 and r19)
I'm not sure if this is a rails issue, or a database issue, but as this seems to be a schema specific issue, I'm looking at it from the database side first. (Other customers with the same setup on ...
- 137
0
votes
0
answers
2k
views
OS Authentication might be disabled for this database
LINUX X64 193000 software installed on "Red Hat Enterprise Linux Server 7.9 (Maipo)"
DBCA:
GUI or silent install always fails with:
[FATAL] [DBT-05509] Failed to connect to the specified ...
- 187
0
votes
0
answers
260
views
Oracle session and process reaching max utilization issue with Oracle 18c+Tomcat9
Currently we migrated to Oracle 18c+ Tomcat 9 after which we are experiencing high utilization issue with both processes and sessions.
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION ...
- 1
0
votes
0
answers
17
views
How to check if a new Oracle PDB has been replicated successfully to the standby side?
How can I check if a newly plugged in 19c PDB on the primary has been replicated successfully (automatically) to the DataGuarded standby side?
- 41
0
votes
0
answers
19
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=&...
- 1
0
votes
2
answers
859
views
Rebuilding an Oracle 19c Standby Database after plugging in a new PDB
I have an Oracle 19c Dataguarded physical standby DB setup running on Linux 7. The container DB had one PDB but I've now just plugged a new PDB into the primary. What do I need to do to get this new ...
- 41
0
votes
1
answer
604
views
ORA-12560: TNS:protocol adapter error logging into Oracle as Sysdba
I have Oracle 19c installed on my windows 10 machine.
When I try to log in as sysdba I am getting an ORA-12560: TNS:protocol adapter error error:
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - ...
- 101
0
votes
1
answer
374
views
Automatically materialize CTE? (instead of temporary table CREATE and INSERT)
I’m a novice trying to learn about query optimization and temporary tables in Oracle.
The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements.
CREATE ...
- 1,387
1
vote
1
answer
318
views
Expdp partitioned table, Impdp Into non-partitioned table same structure
I have a partitioned table X in my production database.
I want to expdp it from production & impdp the same table X with data to my test database.
As I have a standard license in the test database,...
- 135
1
vote
0
answers
29
views
all_views is not showing a view even I have access to the view
I'm having the following issue:
This select does not retreive anything:
select 1 as exist from all_views
where view_name='FND_USER_RESP_GROUPS' and owner='APPS' and rownum=1;
But If I execute this ...
1
vote
1
answer
478
views
Updating a Json column that contains a JsonArray using Json_transform() function in Oracle Database 19c Enterprise Edition
I have a table that keeps USER data.
Here is my table structure:
CREATE TABLE "USERS"
(
"ID" NUMBER(16,0) PRIMARY KEY,
"USER_NAME" VARCHAR2(85) UNIQUE NOT NULL,
...
- 111