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.
7,811
questions
7
votes
3
answers
14k
views
Is Oracle DB licensing needed for a test environment?
I'm planning to setup an Oracle Standard Edition instance on a server in our test environment.
Do I need to purchase a license for this?
0
votes
1
answer
588
views
Failing to enable TCPS on Oracle 11
I have added the following settings to my Oracle 11 server (Bolded text is the change)-
sqlnet.ora-
SQLNET.AUTHENTICATION_SERVICES = (**TCPS,** NTS)
listener.ora-
SID_LIST_LISTENER =
(SID_LIST =
...
CommunityBot
- 1
0
votes
0
answers
15
views
Not possible to connect to remote Oracle XE with public IP
I have a problem with my database, I cannot access to it via public IP (remote).
I have developed a java stocks program, which uses JDBC to connect to DB.
My database is Oracle Database Express ...
3
votes
1
answer
1k
views
How to list backups when not connected to target DB in Oracle RMAN
I'm trying to list backups in RMAN completed within a certain date range. This works fine when I can connect to the target database and recovery catalog.
However, I'm assuming a situation where I am ...
CommunityBot
- 1
0
votes
0
answers
11
views
Composite FK with 1 column common in both parent and child
I have a parent and child relationship as follows, where Table 1 is the parent and Table 2 is the child. There is only 1 column common in both parent and child tables. For this FK relationship, can I ...
1
vote
1
answer
320
views
How to change defaults in oracle docker image
I have been trying to change a few defaults in the database and set a language in default oracle docker image, but I have been failing. I have been trying to
ALTER SYSTEM SET NLS_LANGUAGE = 'GERMAN' ...
CommunityBot
- 1
0
votes
2
answers
2k
views
sqldeveloper file open dialog freeze
When I try to open a file using the file open dialog, sqldeveloper freezes for almost a minute. After that the dialog opens at the user temp folder.
I think it is trying to access some unavailable ...
CommunityBot
- 1
2
votes
1
answer
1k
views
ORA-00205 error during the startup of physical standby database
Steps I have followed:-
Created a physical standby database by using DUPLICATE TARGET DATABASE.
Then checked the log gap between these two databases which was fine and in sync.
After that standby ...
CommunityBot
- 1
2
votes
2
answers
5k
views
Linked Server throws metadata error
I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, ...
6
votes
2
answers
3k
views
Case insensitive search of CLOBs in Oracle 12
Question: What is the most performant way to do a case-insensitive search in a CLOB column?
I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. ...
CommunityBot
- 1
0
votes
1
answer
595
views
Oracle Forms 12c key next item not running on execute query
I am running oracle forms 12c
I have a client_id, client name, and contact number fields where if I type client_id it gets the clients name and contact number and those fields get populated
but when I ...
CommunityBot
- 1
0
votes
1
answer
55
views
Why does user default tablespace not change from USER to new tablespace when import transport tablespace in oracle using IMPDP command?
I imported a transport tablespace from source database to my target database using IMPDP command. After importing, my schema's default tablespace shows as USERS tablespace. But when I created that ...
- 88.3k
0
votes
1
answer
4k
views
Configure wallet location in sqlnet.ora and listener.ora
I have a host containing multiple DBs and the connection to the DBs need to be SSL enabled. Since each DB has a unique wallet, I specify the wallet location in sqlnet.ora as:
WALLET_LOCATION=(SOURCE=(...
CommunityBot
- 1
0
votes
1
answer
33
views
Has error code: ORA-28056 when using RMAN to restore table
I'm using Oracle 19c.
I have error when using RMAN to restore 1 table.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==...
- 1,765
0
votes
1
answer
312
views
Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL
I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
CommunityBot
- 1
0
votes
1
answer
459
views
Access tables from multiple schemas
I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"
All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
CommunityBot
- 1
1
vote
2
answers
1k
views
Suppress some output from PL/SQL script in Toad for Oracle
Question:
In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own ...
CommunityBot
- 1
1
vote
2
answers
924
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 ...
CommunityBot
- 1
3
votes
2
answers
3k
views
Efficiently finding distinct values
I have a number of tables with the primary key (month, year, number) and cardinalities differ somewhat. For the tuple (month, year) the history doesn't go back very far, this will probably not grow ...
- 395
5
votes
3
answers
27k
views
ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS
I am using Toad to connect my database and its working fine for me .
But when i try to connect with the same credential , i am getting the error message as shown below:
ORA-12154 TNS: could not ...
0
votes
0
answers
16
views
What do I need to install to connect to a remote Oracle database?
I need to install an Oracle client on an Ubuntu server (20.04.6) so that Perl scripts can connect to a remote Oracle database.
The database version is "Oracle Database 12c Enterprise Edition ...
- 101
0
votes
1
answer
2k
views
Archive log backup error and backup completed with errors
My prod DB backup completed with errors stating the below error.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =======...
CommunityBot
- 1
0
votes
1
answer
998
views
What could be the reason for this invalid objects after trying to upgrade?
We recently wanted to upgrade our Database but didnt get to do it as we got a lot of Audit specific invalid objects, after looking online for ressources we couldnt find anything specific reason for ...
CommunityBot
- 1
0
votes
2
answers
25
views
Revoking DBA Role From a User while it has also another having dba role in Oracle Database?
I have a very old user named "ADMINX" that has control in almost all tablespaces in an Oracle 19c database. It has been given "DBA" role and also a custom role named "...
- 127
1
vote
1
answer
666
views
Oracle Transportable Database export ORA-39340: unsupported object, INDEX
I want to get data dump from PDB(Transportable Database). DB version 12.2.0.1.0. Enterprise Edition.
Before I get export I applied oracle 27162931 and 27162975 opatch. Then I altered tablespaces ...
CommunityBot
- 1
0
votes
1
answer
697
views
Upgrade Oracle RAC to version 19c - runcluvfy not passed because of missing patches - but they are applied!
Good morning all,
I hope anyone can help me with this. I am trying to upgrade an Oracle RAC of two nodes from version 12.2 to 19c. As part of the prerequirements, I have applied the patch 28553832 in ...
CommunityBot
- 1
1
vote
2
answers
4k
views
Oracle 19c Installer hangs with endless Reading from the pipe output
I am attemping to install Oracle 19c on Windows 10 64 bit.
No Oracle products have ever been installed on this machine.
When I run setup.exe the cmd window launches, then the splash screen and then ...
CommunityBot
- 1
0
votes
1
answer
788
views
Is there a cross-platform way to compare data in one columnd on each sitde of replicated data, like a checksum or hash?
I have an Oracle 12 database with lots of tables, and I am replicating several of the tables (a subset of rows) into a SQL Server 2016 database. The subset of rows can be established with a WHERE ...
CommunityBot
- 1
0
votes
1
answer
369
views
Error handling in PL/SQL
I'm coding something in PL/SQL for the first time and have come up with a situation.
We are building a package to handle insertion tasks but it's supposed to handle the check violations instead of ...
CommunityBot
- 1
0
votes
2
answers
69
views
Is it constraint on database client-server deployment (Firebird, Postgresql, Oracle) that it should not be made over WiFi (due to unreliability)?
Database client-server connectivity can sometimes happen. I mean: there is client side dll that sits on the client computer (device, it can be mobile phone as well). For the Windows OS based clients:
...
1
vote
1
answer
1k
views
PRKP-1001 : Error starting instance,CRS-0215 : could not start resource
We have 3-node Oracle 10g R2 RAC database. Following is the status of various RAC components:-
ora....b2.inst application ONLINE OFFLINE
ora....db2.srv application ONLINE ONLINE ...
CommunityBot
- 1
0
votes
1
answer
24
views
Disabling Login of a User Who Have Scheduled Jobs to Run in Oracle
I have a custom oracle user(let's say monitor_user) which has lots of procedures and jobs that sends e-mail of query results. But for security reasons i want to disable its login to database from an ...
- 127
0
votes
0
answers
20
views
Which file specify Oracle's DB full version (major and minor)? Oracle 19 and up
In some monitoring service I have, I need to detect Oracle DB upgrades/patches, and for that, I want to sample the Database version.
I am aware that creating a connection and running the query select *...
- 101
0
votes
1
answer
5k
views
Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries)
Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this:
"ORA-04031: unable to allocate 56 bytes of shared memory ("shared
pool", "unknown object", &...
CommunityBot
- 1
0
votes
1
answer
307
views
How to print empty string as a result from xml node creation using oracle in a procedure
i want to print one of the node value as Author ="" empty string in xml node element as below:
This syntax is in oracle:
CREATE OR REPLACE FUNCTION GetRtData
(
pf_wkstn_oid_sh number,
...
CommunityBot
- 1
2
votes
2
answers
438
views
Deadlocks With Trigger Based Replication
A table ORIGINAL exists with the following structure:
ID VARCHAR Length 10 (key)
VALUE VARCHAR Length 10 (non-key)
A table REPLICATION exists with the following structure:
ID ...
CommunityBot
- 1
1
vote
1
answer
2k
views
How do I import a dump for a deleted tablespace?
I'm trying to import a database dump, however I am encountering this error:
ORA-39083: Object type TABLE:"MYSCHEMA"."MINF" failed to create with error:
ORA-00959: tablespace '_$deleted$5$0' does not ...
CommunityBot
- 1
0
votes
1
answer
381
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
CommunityBot
- 1
0
votes
1
answer
545
views
In Oracle 18c I have a tablespace with many datafiles (I mean a lot ..) . Is it possible to alter or move the tablespace to big file sizes?
In Oracle 18c there are some tablespaces with many (a lot, really) datafiles. Not sure why the previous DBA implemented it this way.
But anyway I would like to ask if it is possible to alter the ...
CommunityBot
- 1
3
votes
1
answer
17k
views
Why create a Materialized View Log with Primary Key or RowID, etc?
I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED ...
CommunityBot
- 1
0
votes
1
answer
838
views
All file in redo log group corrupt
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u02/oraredo/O12C/redo02b.rdo'
0RA-00312: online log 2 thread 1: '/u02/oraredo/O12C/redo02A.rdo'
i got ...
CommunityBot
- 1
0
votes
0
answers
29
views
How to hide DBMS_METADATA.GET_DDL create scripts from users?
I want to give certain authorizations to users who connect to the database. The scenario is as follows; users will be able to assign select queries to the tables and access the tables, but only users ...
Murat Simsek
3
votes
1
answer
121
views
what is Oracle view exu8con used for
I once found an Oracle system view SYS.EXU8CON, and its data seems to be relevant to information of all database objects, like object id, owner, etc. It also has some columns I can't tell what they ...
- 626
-1
votes
0
answers
20
views
Official oracle EE docker image out of memory during startup
I installed this image in docker (27.1.2) on redhat 9 (5.14.0-362.18.1.el9_3.x86_64, 16 gigs of memory, two CPUs, virtual machine):
docker login container-registry.oracle.com
docker pull container-...
-3
votes
1
answer
45
views
Are there mnemonics in the database design space that you find helpful when communicating database design concerns?
A popular and useful mnemonic in the software development space is SOLID.
Single responsibility
Open–closed
Liskov substitution
Interface segregation
Dependency inversion
Are there mnemonics in the ...
- 607
1
vote
1
answer
328
views
Oracle Database Snapshot?
Is there a proper snapshot function or anything analogue to it on Oracle Database (19c, 21c) on a schema or PDB level?
For example, I wanted to run some tests that will alter tables, procedures and ...
CommunityBot
- 1
2
votes
3
answers
8k
views
Add ENum Attribute to existing table using Alter
I am using oracle XE and trying to add a attribute that restricts to only three available value 'Small','Medium','large' to an existing table using Alter Table and Enum.
Tried doing,
ALTER TABLE ...
- 2,551
0
votes
1
answer
465
views
What is the default column name given to a TABLE() type with a single column
CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);
SELECT [¿what goes here?] as missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))
What is the default column name that should be in [¿...
CommunityBot
- 1
0
votes
1
answer
610
views
RMAN Duplicate PITR without connection to Target database
I would like to understand which network requirements are needed following the least privilege needed policy in order to perform a DUPLICATE DATABASE UNTIL SCN in this scenario
My environment is Azure,...
CommunityBot
- 1