Questions tagged [oracle-18c]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
146 views

Very old date displaying incorrectly when unformatted

At my organization, people have been entering unknown dates as "11/11/1111". When I run a query with the date field, the value shows up as "11/04/1111". However, if I format it as ...
0
votes
2answers
25 views

Select SDO_GEOMETRY line vertices as rows -- using recursive WITH clause

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) ...
0
votes
1answer
116 views

Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)

Oracle 18c: In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line: Select SDO_GEOMETRY line vertices as rows ...
0
votes
1answer
45 views

Select SDO_GEOMETRY line vertices as rows

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) ...
0
votes
1answer
48 views

Oracle XE 18c database size limitation

According to official Oracle documentation available at https://www.oracle.com/database/technologies/appdev/xe/faq.html it has limitations: What are the resource limits for Oracle Database XE Oracle ...
0
votes
0answers
41 views

SQL*Plus 18/19 on Windows: Cannot access SQL scripts

I have this very strange observation on my Windows 10x64 machine: Given the following script file (file name test.sql): DEFINE SYS_NAME = &1 DEFINE SYS_PWD = &2 DEFINE ORACLE_HOME = &3 ...
-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
1answer
74 views

Oracle: "table is mutating, trigger/function may not see it"

(Yes, this is one of those question again...) I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server. I have a table I1 (called t_link in my ...
1
vote
0answers
24 views

Audit SELECT on a particular column

How do I do audit SELECT on one column in the table? The column in question is a BLOB column so most likely these blob columns are accessed using some dbms_lob. Table Emp: column Type Emp_Id Number ...
-1
votes
1answer
52 views

Reset password Oracle database 18c [duplicate]

I have installed Oracle databases 18c. I forgot the account administrator of the databases, how to recover the password? SP2-0640: Not connected SQL> conn XEPDB1 Enter password: ERROR: ORA-01017: ...
-1
votes
1answer
143 views

"ORA-01109: database not open" when trying to open a database

Everything I can find about ORA-01109 says "run alter pluggable database XXX open;", but I'm getting ORA-01109 when running that command! select name, open_mode from v$containers; NAME ...
0
votes
1answer
35 views

How can I make this DECLARE PROCEDURE working?

I'm new to Oracle DB, coming from MS SQL Server. I created this script: DEFINE USER_NAME = &1 DEFINE PASSWORD = &2 DEFINE TABLESPACE = &3 CONNECT &DB_ADMIN/&DB_PWD@//localhost:...
-3
votes
1answer
112 views

Cannot connect new users to pluggable database

I'm new to Oracle DB (coming from MS SQL Server). For a start, I downloaded a Docker container for Oracle Database 18c XE. When I run that container, after creating a pluggable database, I don't seem ...
0
votes
1answer
41 views

Set Parameter in SPFILE still outputs the same error

for the installation of a SAP IDM system, I need to know how to use the command alter system set "_ORACLE_SCRIPT"=true SCOPE=SPFILE; command correctly. The installation creates a user and ...
1
vote
0answers
116 views

TNS-00534: Failed to grant connection ownership to child

when I tried to connect to my oracle instance with sql developer, I see this error in logs TNS-12518: TNS:listener could not hand off client connection TNS-12560: TNS:protocol adapter error TNS-...
1
vote
0answers
71 views

Error in starting oracle 18c express editiom

I have little problem with starting oracle 18c express edition, when I tried to startup, it wrote me the error ORA-00205: error in identifying control file, check alert log for more info SQL> ...
0
votes
1answer
57 views

Oracle RAC 18c Patching - datapatch

After patching GI Home and DB Home simultaneously using command: .oraenv +ASM1 opatchauto apply /patchlocation Should i run ./datapatch from DB home or GI home? Or it doesn't matter from which home? ...
0
votes
1answer
32 views

Why is the schema name necessary here and how to compile without it

I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form. Here's a snippet to illustrate the problem (I changed the ...
1
vote
1answer
17 views

Restricting input from two other tables

I'm trying to create the table CanTeach but I am getting an error ERROR 1005 (HY000): Can't create table 'mydb'.'CanTeach' (errno: 150 "Foreign key constraint is incorrectly formed"). ...
0
votes
2answers
34 views

Why does selecting an attribute of an Oracle object require an alias?

My GIS software has an Oracle 18c object type called ST_GEOMETRY. The object type has attributes: The attributes of the spatial type represent the following information: Entity: The type of ...
0
votes
1answer
120 views

Error when using DBCA to create database Oracle XE 18c

I am trying to create a new database using dbca. Windows 10. Please help me to find solution. Here is the log
1
vote
1answer
2k views

Error when using DBCA to create database

I am using Oracle Database XE (18c). The install completed without any error however, when I try to create a database using dbca it shows error: java.lang.NullPointerException. The log is: https://...
-1
votes
3answers
223 views

Function: if either x or y are null, return null

I'm looking for an Oracle 18c function that evaluates to null if either of two variables is null. The Constraints: (details here) The function/expression can't contain spaces (so CASE won't work). I ...
0
votes
2answers
263 views

Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?

I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column. Options for the GEOMETRY column datatype include: ESRI's proprietary ...
1
vote
2answers
109 views

Materialized View: What is the Update Trigger property?

I've created a materialized view in an Oracle 18c database. When I look at the Info tab of the materialized view in Toad, I see that there is a property called Update Trigger. What does the Update ...
1
vote
1answer
51 views

ORA00918 - Column Ambiguously defined [closed]

I can't find what's causing the error. All columns are qualified by their table names. SELECT trunc(PAT_ENC.CONTACT_DATE,'MONTH') as MonthYear, appt.name, PATIENT.PAT_NAME, patient.pat_mrn_id, ...
0
votes
1answer
88 views

Oracle 19c Standard Edition RPM installation

I'm wondering if it's possible to install Oracle Standard Edition from RPM, not Enterprise Edition. It's possible to choose edition when you do 'runInstaller', is it possible to set some parameter ...
0
votes
1answer
288 views

Materialized view not purging (over dblink via view)

I've created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c): create materialized view primary_system.workorder_mv build immediate refresh force on ...
0
votes
1answer
17 views

Select parent rows and include cost of children

I have a WORKORDER table that has parent and child WOs in it: with workorder as ( select 'WO37342' as wonum, null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as ...
0
votes
2answers
128 views

Check for spaces or zeros in all columns in all tables

I have multiple tables in an Oracle schema that have columns with incorrect values: Number columns where the value is zero, instead of null. Text columns where the value is a space, instead of null. ...
3
votes
1answer
627 views

How do I create a new Oracle schema?

I’m pretty familiar with Relational DBMS in general, but not with Oracle. I have Oracle 18c XE running inside Docker (on MacOS). I connect to it using a third party client (SQLPro Studio on MacOS). ...
0
votes
2answers
207 views

Select arbitrary single value for GROUP BY: What's the fastest option?

I have a query that I use to indicate locations in a map where there are overlapping points: select min(objectid) as min_objectid, longitudex, latitudey, count(1) as count, min(...
0
votes
1answer
54 views

Need for backup with Active Dataguard

When exploring the features of Oracle Active Dataguard, I found that one of them is block change tracking to allow the standby db to take the load of backups instead of the primary database doing them....
0
votes
1answer
55 views

enforcing function/constant names length in Oracle 18 to be 30 characters

in Oracle 18 is there a way to enforce the max length of function names (and constant-names) to be 30 chars only (as it is in Oracle 11 and priors)?
0
votes
0answers
33 views

Application connection pooling load balancing the transactions on 18c RAC

Application connecting a 2 node 18c RAC. JVMs are using connection pool and transactions are routed to the nodes via the connections in the pool. So the initial load across the nodes are distributed ...
0
votes
2answers
43 views

What are the benefits of moving redologs, data- and controlfiles to another location?

I have installed Oracle 18c XE and was wondering what benefits/disadvantages would come from seperating the data that is stored inside the databases managed by the dbms and the actual Oracle ...
0
votes
1answer
374 views

Oracle 18c opatch util cleanup not working

System: [oracle@hostname:/opt/oraclegrid/18]$ uname -a SunOS hostname 5.11 11.4.24.75.2 sun4v sparc sun4v Database: Oracle 18c 18.10 (April 2020 patch), RacOneNode setup Opatch OPatch Version: 12.2....
0
votes
0answers
80 views

ORA-01403 _sometimes_ while reading a file on Oracle 18c

Edit: We just checked: it also happens in on-premise Oracle 18c, latest patchset and everything. In Oracle PL/SQL we are opening and reading a file. The file exists, we are getting data, but then ...
0
votes
2answers
44 views

Trigger: Only raise error if related record was NOT created between now and last Sunday

I have a trigger that prevents inserts into a DELETES table under certain conditions: CREATE OR REPLACE TRIGGER D202574_custom BEFORE INSERT ON D202574 FOR EACH ROW DECLARE v_upd_row NUMBER; ...
0
votes
0answers
15 views

How to shutdown and startup a database to delete datafiles in between

OS: Windows 10 Professional / DBMS: Oracle 18c XE How would one approach the issue of losing a connection while shutting down the database? I would like to have one script which deletes datafilepaths ...
0
votes
1answer
23 views

Is it possible to grant a user the equivalence of ownership over a another users schema?

Im trying to create a trainer/trainee environment in Oracle 18c XE. So far I have 4 trainee users which all have the hr-sample installed. Now, I plan on having one trainer user that is able see ...
0
votes
3answers
344 views

How can we clean up/backup of .DBF files in Oradata folder in ORACLE on windows

We have very less space on our C Drive where the DBF files are stored in windows server. Can we clean up some space any logs can we removed on observation could see that DBF files are taking huge ...
0
votes
2answers
3k views

Can't find missing packages Oracle Linux 8

super newbie here. Trying to learn DBA stuff. I've been trying to install oracle database 18c on Oracle Linux 8 but there are two missing packages that are just not being found. I need these to ...
1
vote
2answers
2k views

Impact of "_oracle_script"=true in Oracle database 18c

I just installed Oracle XE 18c (I migrated from 11g XE) ... After running the installer, I saw new features; for example, Oracle now supports a multitenat model and a concept called pluggable ...
0
votes
1answer
39 views

Migrate Oracle 12c to 18c in AWS RDS

Currently we have an Amazon RDS Oracle 12c instance and we need to upgrade it to Oracle 18c in a new RDS instance. What is the correct approach to do this? Can we set up an 18c instance staright ...
0
votes
2answers
542 views

Unable to import DMP file that is made in 11g2 to Oracle 18c

My OS=CENTOS 7 Oracle=18c XE I am trying to import a DMP file that is exported from XE 11g2 and now trying to import it in 18c XE. Here are the errors: [root@linux gsw]# impdp user/pass ...
0
votes
1answer
71 views

Not able to access Oracle manager online. Port 5500 is closed [closed]

I am using Oracle 18c XE on CentOS 7. The database installed successfully but not able to bring it online to my hostname. what I did is: Added port 5500 in firewall: firewall-cmd --permanent --add-...
0
votes
1answer
2k views

[[FATAL]] [[DBT-06103]] The port (5,500) is already in use [duplicate]

I am trying to install oracle 18c XE on CentOS 7. I have opened the ports: firewall-cmd --permanent --add-port=1521/tcp firewall-cmd --permanent --add-port=1630/tcp firewall-cmd --permanent --add-...
0
votes
1answer
74 views

Is Oracle 18c XE good enough to start learning to be a DBA? [closed]

I don't know if the esteemed community here will find this question silly, but at the same time I thought that I would get a good answer to my question here. As of now, I am into writing SQL queries/...
1
vote
1answer
470 views

Keep getting "Connected to an idle instance" in 18c when trying to connect to database

I installed Oracle Database 18c yesterday and then installed Oracle Application Express v20.1. Everything was perfect and I was able to work on Apex too. Then I shutdown the computer and today I ...