Questions tagged [oracle-19c]

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

Filter by
Sorted by
Tagged with
-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 ...
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 ...
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?
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 (...) ...
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 ...
-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 ...
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 ...
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 ...
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:...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 - ...
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