Questions tagged [oracle-19c]
Oracle Database 19c. Please also tag oracle for search purposes
100
questions
0
votes
0answers
16 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 max utilization issue both process and session.
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION ...
0
votes
0answers
7 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?
0
votes
0answers
16 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=&...
0
votes
1answer
41 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 ...
0
votes
0answers
29 views
Problems using catcon.pl
We are investigating the use of catcon.pl to launch scripts on multitenant Oracle DBs.
In this case, with APEX upgrade scripts.
For example, the apexins.sql script, is made to give it the arguments at ...
0
votes
1answer
67 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
1answer
43 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
36 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
15 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
17 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
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 ...
1
vote
1answer
16 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
1answer
23 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
0answers
30 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 ...
2
votes
2answers
210 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
1answer
43 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
40 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
0answers
63 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"','...
0
votes
2answers
43 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
0answers
49 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 ...
0
votes
0answers
82 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,...
0
votes
1answer
55 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
...
-1
votes
1answer
27 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
36 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
160 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
42 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
0answers
33 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
1answer
35 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
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
216 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
84 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
0answers
112 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
117 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
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
2answers
160 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
1k 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
119 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
781 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
387 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
30 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
192 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
43 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
277 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
800 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.
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, ...
0
votes
1answer
28 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 ...
0
votes
1answer
73 views
Is it possible to give PDB name to a procedure and select from it?
The following example is not working, I just trying to show what I'm trying to do.
Given table:
-- this table is located at the CDB
create table t1 (tab_name varchar2(35))
/
-- this proc is located at ...
0
votes
1answer
46 views
How to select from pdbs with a cdb user?
I have several store procedures, which analyze oracle logs using log miner. These procedures run on CDB user C##ADMIN which created for this manner.
Now I want to compare the logged values to the live ...