Questions tagged [oracle]

All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.

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

Oracle: why does this DDL yield a different result in production (reordering of columns)?

Problem description This week we had an issue in production, where an SQL script that was executed ended up with a different result than what we had tested before locally, in Docker and on our ...
user avatar
  • 173
0 votes
0 answers
13 views

Different time sysdate and current_date when connect to database since application server

I have a problem with the time that Oracle gives me when working with applications. I currently connect from an application server (apx1) to a server containing Oracle 19c (bdx1) for database ...
user avatar
  • 131
0 votes
0 answers
10 views

SingleStore Execute Immediate a SP/Func inside SP from a query table

I’m having trouble to execute immediate a SP inside a SP from a table. because i have a lot of SP stored in a table (as a data/string). in oracle we can use “select sp_1(:1,:2) from dual” and running ...
user avatar
0 votes
1 answer
19 views

Oracle 12c missing GSMADMIN_INTERNAL

Oracle 12c 12.2.0.1 The GSMADMIN_INTERNAL user was erroneously deleted apparently some weeks ago. Datapump no longer works for export (ORA39006 and 390065) as well as ORA-00942 table or view does not ...
user avatar
-1 votes
2 answers
27 views

Find in what table I can find multiple collumns

I am trying to write a query that will show me the tables that contains all the following columns: ProductGroup, ProductClass and ProductID. But I am not very successful at it. Can somebody help me ...
user avatar
0 votes
1 answer
20 views

oracle - Calculate sum or avg in one query based on the their value

I've this table date customer amount percentage category 20220330 customer1 10 93 01 20220330 customer1 11 91 01 20220330 customer1 13 91 02 20220330 customer1 15 91 02 I' need to calculate sum ...
user avatar
  • 141
0 votes
1 answer
22 views

Manually change datafile size or let oracle do the job during schema duplication

I have to duplicate a (relative) big schema inside the same database (using impdmp). I know Oracle will automaticaly enlarge the data file during the import. What is the right way : let Oracle ...
user avatar
0 votes
0 answers
44 views

SingleStore exception no_data_found

I’m having difficulty finding the exception no_data_found in singlestore. In Oracle, we use no_data_found exception. Is there any alternative solution in singlestore for the no_data_found exception?
user avatar
2 votes
1 answer
225 views

How to terminate user's sessions in Oracle DB exactly 8 hours after user connected?

I would like to terminate user sessions (and optionally also lock the user), exactly 8 hours after user established DB connection. I tried creating a profile, but it just doesn't terminate user's ...
user avatar
  • 123
1 vote
0 answers
46 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 ...
user avatar
  • 111
0 votes
0 answers
24 views

Best approach for purging records (including related records) from an Oracle database with undo capability?

Please let me know which approach you would take. The problem I have a large Oracle 11g DB that needs to have old records purged to free disk space. One of the requirements for the project is that we ...
user avatar
0 votes
1 answer
23 views

Cannot connect to a pluggable database via IDE

I have an Oracle XE database on computer1. When I try to connect to it via sqlplus from computer2, it works fine, for both cdb and pdb connections; e.g.: rlwrap sqlplus timon@//192.168.0.115:9121/...
user avatar
0 votes
1 answer
24 views

Access Oracle Database remotely via DBeaver

I have two machines, a windows 10 notebook with oracle database installed, and a Raspberry Pi 4 with DBeaver installed. On the Raspberry I have PostgreSQL which I can access locally via dbeaver, but ...
user avatar
0 votes
1 answer
25 views

schema ownership/permissions in MS-SQL vs Oracle

Hopefully I'm using the write terms here. I'm pretty good with concepts usually but don't always know the technical terms to describe it correctly. Here goes... I'm used to working in MS SQL Server ...
user avatar
  • 173
2 votes
3 answers
135 views

Update statement runs for longtime and throws ORA-01654 table space error

I have a simple update statement which runs for a long time (>15mins) to update approximately around 120K records in oracle19c,but it also fails atlast with ORA-01654 table space error. Is there ...
user avatar
  • 123
-2 votes
2 answers
62 views

Is the SQL dialect in SQL Server closer to Oracle or to Postgres?

There is an existing application that supports having its data in an Oracle database or a Postgres database, through JDBC. The tables and data are the same, but it can be installed in any of them, to ...
user avatar
  • 112
0 votes
1 answer
22 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 ...
user avatar
0 votes
1 answer
26 views

Error when i use impdp for restore from backup in oracle

expdp backupuser/a123A123a123@//localhost:1521/ORCLPDB1 DIRECTORY=EXP NETWORK_LINK=EXP_TEST DUMPFILE=EXP131066.DMP LOGFILE=EXP131066.LOG TABLES=EMP I use this command to take backup from remote ...
user avatar
1 vote
2 answers
24 views

Applying conditional statement in Oracle DB

In the Oracle DB there are three tables, see below. Table: 'People' Table: 'Dogs' Table: 'Cats' Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4 Depending on the result of a ...
user avatar
  • 177
0 votes
0 answers
16 views

Will SQLNET.EXPIRE_TIME=5 prevent AWS Gateway Load Balancer from timing out after 350 seconds?

We have Amazon Web Services (AWS) Database Migration Service (DMS) tasks erroring out against an on premises Oracle 11.2.0.3 database with ORA-03135 errors. Our network traffic between AWS and the ...
user avatar
0 votes
0 answers
19 views

How to append numbers to a column for duplicates based on date [duplicate]

In a TEST table, I have one column with some non-unique values in ID column along with created date. ID Created Date (Varchar) 12345 2022/06/08 11:39:57 234567A 2022/06/08 11:39:57 234567A 2022/06/...
user avatar
  • 123
1 vote
2 answers
45 views

is my data compressed?

Lets say I want to implement basic compression for my table. I know it can be done in two steps, for example: alter table MYTABLE compress; alter table MYTABLE move; Is there a way to check that ...
user avatar
  • 151
0 votes
1 answer
30 views

SSIS Package is writing corrupt data, but only when run as a Job

Our environment is the following: SQL Server 2019 (15.0.4153.1) + SQL Integration Services (15.0.2000) We have a DTSX Package that uses a OLE connection manager to connect to an SQL Server to read ...
user avatar
  • 126
0 votes
0 answers
25 views

Oracle non-CDB to CDB - Do I need to recreate dataguard?

To convert from non-CDB to cdb it's necessary to install a new instance as CDB and so, to use dbms_pdb.describe in the non-CDB database to generate a xml of the non-CDB to add as a PDB in new CDB ...
user avatar
  • 508
0 votes
0 answers
35 views

FAST refreshable MVIEW moving to COMPLETE refresh only

I have created below FAST refresh MVIEW which does couple of FAST refreshes immediately after creation and all subsequent refreshes are happening as COMPLETE refresh ( DELETE + full INSERT) CREATE ...
user avatar
0 votes
1 answer
48 views

How do I handle an I/O error when restoring an RMAN backup?

I'm using Oracle XE i.e. a subset of 11.2 version. I have below archivelogs folders having one or multiple archivelog files. 2022-05-20, 2022-05-21, 2022-05-22, 2022-05-25, 2022-05-26, 2022-05-27, ...
user avatar
  • 1
0 votes
0 answers
95 views

Oracle [99999][17401] Protocol Violation Error

I am using version Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 I am linking to remote database. But when I query the table I get the error "Protocol ...
user avatar
0 votes
1 answer
29 views

Oracle How to get Execution Plan for SQL executed inside of PLSQL?

Is it possible to examine the execution plan for a SQL statement executed inside a PLSQL block? DECLARE l_count PLS_INTEGER; BEGIN SELECT COUNT(1) INTO l_count FROM foo; END; / For ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
  • 1,387
0 votes
0 answers
47 views

Does this smell of a DB error?

I have Cisco prime Infrastructure on a closed network and I have a problem. I have about 3000 units to manage, and when my server has been online for about 20 mins, I cant delete any unit from my gui. ...
user avatar
0 votes
1 answer
22 views

Oracle Clusterware - What is the difference between Votedisk, quorum disk and quorum failure group?

Some references say quorum disk and vote disk are the same, but "quorum disk" name was used in older versions. But Oracle 19c documentation still use both the names, so I think there is a ...
user avatar
  • 508
1 vote
1 answer
29 views

insertion in a table of objects with nested table in oracle

I have a problem inserting in a nested table in oracle These are the relevant types and tables; create type movies_type as Table of ref movie_type; create type actor_type under person_type ( ...
user avatar
  • 13
1 vote
2 answers
57 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)'...
user avatar
  • 1,387
0 votes
0 answers
28 views

dbms_metadata.get_xxx not able to find view

I am attempting to create a search script that will search all DDL for a user-input string in all readable namespaces. I am modeling my attempt based on https://tedstruik-oracle.nl/ords/f?p=25384:...
user avatar
  • 988
0 votes
1 answer
24 views

Select function-based index data as rows in a query?

I'm trying to learn about indexes in Oracle 18c. As a novice, it's occurred to me that it might be interesting to view the actual data that's in a function-based index: create index ...
user avatar
  • 1,387
2 votes
1 answer
56 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, ...
user avatar
  • 123
0 votes
1 answer
75 views

How to grant select on v$session to a common user see all session of all pdbs?

I've done it': grant select on sys.gv_$session to c##dba_foo container = all; But I can't see sessions from pdbs. Also, the c##dba_foo need permission to kill others session of all pdbs. Do I need ...
user avatar
  • 508
0 votes
0 answers
79 views

Oracle 12c common user - ORA-01017: invalid username/password; logon denied

Why I am not abble to connect to the database with an account I just create? SQL> alter user c##fooo identified by foo123; User altered. SQL> conn c##fooo/foo123 ERROR: ORA-01017: invalid ...
user avatar
  • 508
-2 votes
1 answer
29 views

Running an O/S script from DBMS_SCHEDULER

Have a need to run an O/S bash script from an Oracle 19.6 database. Here is my test case on my test database: I want it to write out to a file, but right now I can't even get the job to run. SQL> ...
user avatar
  • 187
0 votes
1 answer
30 views

Drop the constant recurring JOINs in SQL queries on a SQL-Server, is this possible?

I would like to show the columns of "linked" tables in SQL-Server database. In my particular case, there are two tables "Loads" and "Locations", linked by Loads....
user avatar
  • 365
0 votes
1 answer
42 views

Postgres equivalent of Oracle's RMAN.VALIDATE_DATABASE

I am migrating a system using Oracle DB to Postgres (12.7). Various checks are run weekly against the existing DB, including a RMAN.VALIDATE_DATABASE (actually RDSADMIN.RDSADMIN_RMAN_UTIL....
user avatar
0 votes
2 answers
64 views

ORACLE using decode

I need to list all undo tablespace names in an Oracle (19c) database (regardless of whether they're used or not) from the ts$ table. My SQL statement below gives that info but it also lists all the ...
user avatar
  • 41
-1 votes
1 answer
27 views

Deleting duplicate value in View from the Base table

BASIC INFO : I have a table : YARDI_PROPERTY -> PK -> HMY, and a VIEW which uses HMY from it. Issue : Getting duplicate HMY in the view. Requirement : TO delete those duplicates. IMPLEMENTATION :...
user avatar
0 votes
1 answer
25 views

Creation Synonym among different db schemas

might seem a stupid question, but I'm pretty new to the db world. Is that a good practice to have two oracle schemas which have synonyms pointing to each other ? like SCHEMA_A owns a SYNONYM_1 ...
user avatar
  • 1
0 votes
1 answer
30 views

How to store files for thick client app

How to correctly store files for thick client app (Ora DB <-> Win App)? A) If we store files in DB, DB is hard to manage and, as I understand, violates best practices. B) If we store files on ...
user avatar
0 votes
0 answers
13 views

Enterprise manager - ASH graph

I'd like to know the numbers in the left the graph also have some relationship between the amount of active session waiting an event, For example: Usually seem be only the load of the machine, but ...
user avatar
  • 508
-1 votes
2 answers
38 views

Struggling with improving efficency of this code

QUERY FOR COLUMN INSIDE A VIEW(need for this to work faster) CREATE OR REPLACE VIEW "ODS"."VW_YARDI_DLR_PROPERTY_INFORMATION" ("SPCODE", "HMY", "SPNAME&...
user avatar
0 votes
0 answers
60 views

IMPDP causing ORA-39002 with no subsequent message

In the past, I've seen ORA-39002 followed by subsequent errors providing more detail on the issue. In this case, I get no other information in the console or in the logfile that is created in my ...
user avatar
0 votes
0 answers
43 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 ...
user avatar
0 votes
0 answers
15 views

Some easy way to list OS disks used by ASM without use asmcmd or sqlplus?

For example: I can list the disks with kfod: [root@rac1 bin]# ./kfod op=disks status=true disks=all dscvgroup=true -------------------------------------------------------------------------------- ...
user avatar
  • 508

1
2 3 4 5
151