Questions tagged [oracle-19c]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
19 views

Can analytic functions add or remove rows from a resultset?

I'm a novice who is starting to learn about Oracle analytic functions (window functions). I know from experience that it's important to fully understand how SQL mechanisms work when using them in ...
0
votes
1answer
34 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 ...
0
votes
0answers
26 views

JSON Search index usable with 'LIKE' but not with '='

We are seeing an issue with Oracle 19c and a JSON search index. Given: A table GEO containing a column JSON_DATA with JSON data and an IS_JSON constraint An index created like this: create search ...
0
votes
0answers
123 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
0answers
13 views

Oracle Text truncate words to 64 CHARS but token limit is 64 BYTES

I'm using Oracle 19c. I have a table with a VARCHAR2(4000) column for which I'm creating a full-text index using Oracle Text. The SQL creating the index is as follow: begin ctx_ddl.create_index_set('...
1
vote
1answer
15 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,...
1
vote
1answer
10 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, ...
0
votes
0answers
52 views

Oracle mview refresh taking long time

the customer I work for is used to launch a script to refresh mviews on its db. Refresh command for all mviews is issued in the following way: DBMS_MVIEW.refresh('"OWNER"."MVIEW"','...
1
vote
0answers
10 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 ...
2
votes
2answers
203 views

Zero/NULL Case Trick

In the book Getting Started with SQL, Thomas Nield talks about a technique he calls the zero/null case trick: There is a simple but powerful tool that can apply different filtering conditions to ...
0
votes
0answers
29 views

ETL with Oracle: how to do an isolated run without overwriting previous results

Objective I'm refactoring ETL. ETL is imlemented as a chain of queries submitted to Oracle one-by-one. While executing, ETL creates/overwrites about 20 output tables. For sake of testing (including ...
0
votes
1answer
39 views

oracle goldengate replication

Please I need help. Actually i am working on oracle 19c to perform a data base replication using the goldengate. I created two data bases (source and destination) the 3 processes (extract, pump and ...
0
votes
0answers
39 views

Oracle 19c Select Query with Database Link

How can I use the following query with database link: set pages 999 col "Size MB" format 999,999,999 col "Objects" format 999,999,999 col OWNER for a25 select obj.owner "Owner&...
0
votes
2answers
28 views

Oracle 19c - find primary DB information from a mounted standby

I have a (probably simple) query about how to find an Oracle 19c primary database's host by querying a mounted standby using sqlplus. I can get the primary name as follows: select ...
0
votes
1answer
28 views

Oracle TDE - opening/closing an encryption wallet

I have a quick question relating to Oracle TDE. Could somebody please explain why both of the following pairs of commands appear to work when opening/closing an ecryption wallet? Is the wallet ...
0
votes
0answers
32 views

Oracle 19c new Installation Hostname and IP Address Issue

I have installed Oracle 19c DB in my windows VM and it gave me below error, I used the oramtsctl.exe command to attach the Hostname and IP address, but hostname addition failed. Now I am only able to ...
2
votes
2answers
7k views

Migrated DB from 12c to 19c with IMPDP failed

I have migrated a DB from 12c to 19c with IMPDP and EXPDP. While importing I have seen some errors. Below is an extract of the errors. Errors During Import Processing object type SCHEMA_EXPORT/TABLE/...
1
vote
1answer
2k views

Oracle19C installation is stuck at executing datapatch in Windows 10 using dbca

I tried using dbca. There are no errors. It just hangs. I tried leaving it running for 4 hours. It is always stuck at 36% even after multiple tries. Logs: [progressPage.flowWorker] [ 2020-05-25 00:...
0
votes
1answer
45 views

How to include the oracle database name to os audit trail?

My DB settings: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 SYS@CDBXXX1> unified_audit_common_systemlog string ...
0
votes
0answers
51 views

Reinstalling Oracle 19c on Windows 10. Fatal Error in Oracle Database Configuration Assistant

I have tried many times to reinstall Oracle 19c. Every time, when I run setup.exe I get an error on the installation step that says [INS-20802] Oracle Database Configuration Assistant Failed. In error,...
-1
votes
1answer
21 views

ORA 600 and 7445 after upgrade from 18 to 19C

I'm getting errors ORA 600 and 7445 in alert log after upgrading Oracle from 18 to 19C, the error mostly occurs on merge statements, below is the error message: Errors in file /db1/oracle/diag/rdbms/...
0
votes
2answers
19 views

How to check the source of analysis task in Oracle Performance Analyzer?

We have provided sql tuning set as input to the analysis task of performance analyzer variable l_task_id VARCHAR2(64); exec :l_task_id:=dbms_sqlpa.create_analysis_task( sqlset_name => '<...
0
votes
1answer
1k views

Why Oracle 19c Database EM Express Webpage is not opening in Microsoft Edge?

I have recently installed Oracle Database 19c on my Laptop which has Windows 10 OS. Now, I want to open the localhost:5500/em page in Microsoft Edge browser, but when I try to do so, the webpage does ...
0
votes
0answers
40 views

Best RMAN Strategy for Oracle VLDB 30TB in NOARCHIVELOG mode

I would like to have your ideas regarding how to optimize backup/restore in a VLDB Oracle Database Environment: RAC 19c 4 Nodes Version 19c Size: 32TB Mode: NOARCHIVELOG Let me explain some ...
0
votes
1answer
438 views

ORA-12705: Cannot access NLS data files or invalid environment specified with jmeter test

I have trying to execute a simple test with jmeter in Oracle 19.7 database. This is my lang configuration in database SQL> select * from nls_database_parameters; ... NLS_NCHAR_CHARACTERSET ...
0
votes
1answer
15 views

remote oracle connection from AIX to Linux Redhat

I want to connect oracle database from AIX to Linux using user id and password only. For that I have changed tnsnames.ora RATARA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT ...
0
votes
1answer
31 views

Get sequence value date?

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger each time a record is created/updated. All the tables & triggers in ...
0
votes
0answers
30 views

Oracle Materialized Views best practice for refresh

I have the following nested MVs order: tab1 tab2 tab3 |----> MV join the 3 tables | |-----> MV aggregate | |------> MV aggregate | |--...
0
votes
2answers
22 views

Matching strings from two columns in one column

I have two columns in an Oracle 19c db C1 a,b,c,d x,y,z C2 1,2,3,4 1,2,3 I want to make a select query which will return one column like this: C12 a1, b2, c3, d4, x1, y2, z3 I`m not able to use any ...
0
votes
1answer
159 views

Oracle 19c cannot shrink blob column

I deleted a bunch of old data from a schema, including BLOB data, and afterwards I tried to shrink and compact everything. I managed to shrink all tables (enable row movement, shrink space compact, ...
0
votes
1answer
77 views

Unable to create pluggable database on 19c using response file

I am trying to create a pluggable database using a response file, but it is giving me the error below, indicating that one of the mandatory parameters is missing. Although I have checked it multiple ...
0
votes
1answer
920 views

Oracle 19c installation stuck at creating and starting oracle instance

I'm trying to install oracle 19c but it's stuck at 52% for over 7 hours. So cancelled it and uninstalled it. I have started installing again but still stuck at 52% that is in creating and starting ...
0
votes
0answers
89 views

Oracle 19c Deinstall : Permission denied

I have an RHEL 7.7 box on which Oracle 12c is running. I have installed 19c on the same machine for the purpose of upgrading from 12c to 19c but need to uninstall it for some reason. I get the below ...
0
votes
1answer
79 views

ORA-12631 using Database Links with Oracle DBMS 19c on Windows Server 2019

We had a set of long-time Oracle 12c Database installations (multiple instances on separate VMs) on Windows Server. Over the past year we transitioned to Oracle 19c on Windows Server 2019. We found ...
0
votes
2answers
127 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
2answers
147 views

"user does not exist " when calling DBMS_LOGMNR.START_LOGMNR

I have a CDB user called "MYADMIN" and I'm trying to make it connect to log miner. -- enable calling admin username on CDB ALTER SESSION set "_ORACLE_SCRIPT"=true / -- create ...
0
votes
0answers
95 views

Oracle 19 ORA-01450: maximum key length (6398) exceeded

I am using Oracle 19 and recently changed the collation to XGERMAN_AI. This required to change the MAX_STRING_SIZE to be EXTENDED as in the following link https://docs.oracle.com/database/121/REFRN/...
3
votes
1answer
693 views

ORA-39014 import in Oracle 19c from DMP file of Oracle12c

I have been testing out expdp/impdp of databases from 12c to 19c( newly created database) but one db have game me this error: nohup impdp system/****@CKLPROD CONTENT=METADATA_ONLY PARALLEL=4 DIRECTORY=...
0
votes
1answer
297 views

Connection to Oracle DB 19c fails when listener is set to the host IP address

I am new to Oracle DB and need help in solving the following issue. I am using sqlplus (SQL*Plus: Version 21.1.0.0.0) to connect to the Oracle DB 19c. Statement: I am able to connect to the Oracle DB ...
0
votes
0answers
28 views

In what sort of way "v$transaction" view will help DBA's to improve the performance of the query?

I've just started learning some DBA tips and techniques in Oracle and I came across v$transaction view. I've read some articles regarding this view but some things are still ambiguous ! I want to know:...
0
votes
1answer
175 views

Linked Server to Oracle error ORA-01804

Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle ...
2
votes
1answer
40 views

Oracle: Decreasing PCTFREE on large table

Question about Oracle Database Enterprise Edition version 19.10.0.0.0 running as a standalone instance. I have a table with billions of rows. The table has always had PCTFREE set to 10. It turns out ...
0
votes
0answers
245 views

Oracle 19c with SAP System - ORA-12154: TNS:could not resolve the connect identifier specified For Physical Standby

Oracle 19c + SAP system. On primary side I have a problem when transporting logs to Data Guard side. Outputs from primary side: SQL> select status, error from v$archive_dest where dest_id=2; ...
0
votes
1answer
619 views

Does Oracle 19c support Ubuntu?

I am trying to install Oracle 19c, and It needs X-display so I typed xhost +, but it doesn't work. Any ideas to solve this problem? Also, I want to know that Ubuntu literally support Oracle 19c.
0
votes
2answers
84 views

Report based on MV: Deliberately make the report fail if MV is out of date?

I have a query that is used in a BIRT report (in IBM's Maximo Asset Management platform). I'm considering storing the query in a materialized view in order to improve performance and reduce the load ...
1
vote
0answers
54 views

How to improve Oracle Standard Edition's performance for testing? [closed]

There's a great post on StackOverflow about improving Postgres performance for testing. https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940 However, ...
1
vote
1answer
77 views

Why am I able to create MV logs using PK — even though there is no PK?

I have a table that does not have a primary key: I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid: create materialized view log on maximo....
0
votes
1answer
77 views

Oracle Database Vault: Changing DV Owner and DV Account-Manager passwords hangs PX Deq: Execute Reply/row cache lock

When changing the password of the users mentioned above the sessions hangs. And it seems to be a deadlock. When changing the password it seems that two sessions are spawned: Blocking session C##...
0
votes
1answer
27 views

Oracle database 19C moving back and forth with time without resetlogs [closed]

my client have a oracle database 19C on windows server 2016 for a business reason he need to be able to move back in time to specific date/time and modify some data and then move forth to the current ...
0
votes
0answers
21 views

Is it possible to select from procedure\function when the PDB is an argument without connect?

I have the following function in oracle 11g. Create or Replace function get_col_value( schema# in varchar2, table# in varchar2, column# in varchar2, rowid# in varchar2) Return varchar2 Is v_cmd ...