Questions tagged [oracle-18c]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
80 views

How to drop a datafile of an already dropped tablespace

In an 18c Oracle database I have a tablespace that has been dropped. I need to add this tablespace anew, but when I try to do so I get this error: ORA-01537: cannot add file 'MYTABLESPACE.DBF' - file ...
jahu's user avatar
  • 113
0 votes
2 answers
142 views

SYS password has expired and cannot be changed due to ORA-00257: Archiver error

In the past : When I had once the error ORA-00257: Archiver error, I had resolved it using the RMAN command: rman target sys/1234 delete archivelog all; This time , when I try to connect to rman ...
eathan's user avatar
  • 23
0 votes
0 answers
54 views

Oracle 18 - How can I add additional components to an existing client installation

When you first install Oracle Client, there are numerous checkboxes for various components. Once you have installed Oracle Client, how can you go back an select additional components you didn't check ...
NealWalters's user avatar
0 votes
0 answers
14 views

Error when creating new database using Oracle DCA

I am getting NullPointerException while trying to add new database using Oracle 18c trace log is here: https://jpst.it/3cTNF Thanks for the help in advance :)
Ahmad Abdel-Salam's user avatar
0 votes
0 answers
485 views

LAST_DDL_TIME and TIMESTAMP in Oracle DBA_OBJECTS when Object Type = JOB

In the Oracle View DBA_OBJECTS, there are the columns LAST_DDL_TIME and TIMESTAMP. These columns are very well explained at Oracle Database Object - Last DDL vs TIMESTAMP When the object_type is JOB, ...
eathan's user avatar
  • 23
0 votes
1 answer
3k views

What causes temp tablespace to be full and generates ORA-01652 error

I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space. Is it too much sorting occur on TEMP? How do I identify such issue from AWR? And ...
Jason Oon's user avatar
0 votes
1 answer
422 views

Intended use case for virtual columns with function-based index?

What is the intended use case for virtual columns with a function-based index? The reason I ask (novice): I've been exploring options for pre-computing columns in a table. For example, in a roads ...
User1974's user avatar
  • 1,457
1 vote
2 answers
113 views

Remove third ordinate from string of 3 ordinates

I have geometries represented as strings (Oracle 18c): with cte as ( select 'LINESTRING ( 1.0 2.0, 3 4)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0)'...
User1974's user avatar
  • 1,457
0 votes
0 answers
169 views

Oracle - How to Set Table and Index Statistics on empty table in order to test out explain plans?

I have an empty table with some indexes. I would like to test out different queries on this table to verify the explain plan is behaving as expected. Normally I would insert a large amount of fake ...
Matthew Moisen's user avatar
1 vote
2 answers
132 views

db<>fiddle (Oracle): Query works in 18c, but not 21c

I have a Oracle query that I use to analyze the vertices of SDO_GEOMETRY polylines: WITH lines AS (SELECT SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 ...
User1974's user avatar
  • 1,457
1 vote
1 answer
73 views

In a string, replace third number in each set with new number

I have a string that has coordinates. Individual coordinates are separated by spaces. Vertices (X Y Z coordinates) are separated by commas. Vertex groups are wrapped in brackets and separated by ...
User1974's user avatar
  • 1,457
1 vote
1 answer
86 views

Get explain plan for query with inline function

I have a query that has an inline function: with function with_f(p_text in varchar2) return varchar2 is begin return p_text; end; select with_f(dummy) from dual Is there a way to get the ...
User1974's user avatar
  • 1,457
2 votes
1 answer
701 views

Insufficient Privileges after creating Pluggable Database

I have to confess I have no idea about this pluggable database thing. I have experience in PostgresSQL, MySQL, SQL Server, but Oracle has me defeated. I have Oracle 18c running in a Docker image. I ...
Manngo's user avatar
  • 2,809
0 votes
1 answer
43 views

How to request an enhancement to SDO_GEOMETRY / Oracle Spatial

I want to suggest a few ideas to Oracle about enhancements to SDO_GEOMETRY / Oracle Spatial. Example: Requesting a function to select vertices, including the multi-part numbers of SDO_GEOMETRY ...
User1974's user avatar
  • 1,457
1 vote
1 answer
276 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 ...
Bernie L's user avatar
0 votes
2 answers
149 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) ...
User1974's user avatar
  • 1,457
0 votes
1 answer
132 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) ...
User1974's user avatar
  • 1,457
0 votes
1 answer
1k 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 ...
Tiedt Tech's user avatar
0 votes
0 answers
155 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 ...
AxD's user avatar
  • 133
-1 votes
1 answer
188 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/...
Sam's user avatar
  • 409
0 votes
1 answer
1k 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 ...
Matthieu's user avatar
  • 213
2 votes
0 answers
290 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 ...
Sanjay's user avatar
  • 81
-1 votes
1 answer
316 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: ...
Hassan's user avatar
  • 1
-1 votes
1 answer
2k 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 ...
Mark Raymond's user avatar
0 votes
1 answer
97 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:...
AxD's user avatar
  • 133
-3 votes
1 answer
1k 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 ...
AxD's user avatar
  • 133
0 votes
1 answer
335 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 ...
Sankama's user avatar
1 vote
1 answer
560 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-...
Petr Kostroun's user avatar
1 vote
0 answers
122 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> ...
Petr Kostroun's user avatar
0 votes
1 answer
192 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? ...
lear1's user avatar
  • 69
0 votes
1 answer
266 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 ...
Riptide's user avatar
  • 101
1 vote
1 answer
19 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"). ...
James Flattery's user avatar
0 votes
2 answers
137 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 ...
User1974's user avatar
  • 1,457
0 votes
1 answer
345 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
Stanislav's user avatar
1 vote
1 answer
4k 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://...
KrishKhatri's user avatar
-1 votes
3 answers
362 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 ...
User1974's user avatar
  • 1,457
1 vote
2 answers
837 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 ...
User1974's user avatar
  • 1,457
1 vote
2 answers
500 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 ...
User1974's user avatar
  • 1,457
0 votes
1 answer
337 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 ...
Mikhail Aksenov's user avatar
0 votes
1 answer
1k 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 ...
User1974's user avatar
  • 1,457
0 votes
1 answer
24 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 ...
User1974's user avatar
  • 1,457
0 votes
2 answers
1k 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. ...
User1974's user avatar
  • 1,457
3 votes
1 answer
2k 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). ...
Manngo's user avatar
  • 2,809
0 votes
2 answers
1k 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(...
User1974's user avatar
  • 1,457
0 votes
1 answer
168 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....
Mathew George's user avatar
0 votes
1 answer
164 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)?
Bauerhof's user avatar
0 votes
2 answers
46 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 ...
SweetPotato's user avatar
0 votes
1 answer
857 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....
lear1's user avatar
  • 69
0 votes
0 answers
185 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 ...
Lenniey's user avatar
  • 133
0 votes
2 answers
487 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; ...
User1974's user avatar
  • 1,457