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,808
questions
0
votes
2
answers
20
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
0
votes
0
answers
14
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
-4
votes
0
answers
21
views
OCP Certification (1Z0-082 and 1Z0-083) [closed]
I need best resource to complete OCP certification .
- 1
0
votes
1
answer
23
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
25
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 ...
3
votes
1
answer
119
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 ...
- 33
-1
votes
0
answers
19
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
44
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 ...
- 135
0
votes
1
answer
30
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 ...
0
votes
0
answers
17
views
Loading a file using sql loader having header record which needs to be column 1 in the table to distinguish client name
I have 3 clients sending their own data files which have the same detail record layout which needs to insert in the same staging table and they are distinguished by header record.
File 1
Clientname1 ...
0
votes
2
answers
67
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:
...
- 101
1
vote
1
answer
29
views
How does Oracle 12c handle the execution order of identical DML statements across distinct transactions?
I'm administering an Oracle 12c database and need to understand how the database handles the execution order of DML (Data Manipulation Language) statements when executed across different transactions ...
- 11
0
votes
1
answer
41
views
ORA-01555 snapshot too old: optimize query?
My users are launching an ugly UPDATE with linked sub-selects, an online query coexisting in a very agressive online environment.
In the trace, I saw an estimation of the UNDO retention needed, which ...
- 123
0
votes
0
answers
44
views
How does table partitioning size affect query performance?
In my system, I have a set of Oracle 19c database tables which stored temparary data, which are getting expired. I have a solution where I partitioned these tables with the create timestamp of these ...
- 1
0
votes
1
answer
31
views
Strategy for managing partioned table
I have a table that grows at a rate of about 250 million rows per year. The primary key is a timestamp and two numeric identifiers. The timestamp is not uniformly distributed over year but follows ...
- 560
0
votes
2
answers
35
views
How to block changes to objects in a schema in Oracle?
Does anyone know if it's possible to block changes to objects in a schema in Oracle? Any kind of insert, update, or delete, etc.
Taking the tablespace offline is possibly not an option.
We'll probably ...
- 801
0
votes
0
answers
22
views
To remove the oracle home or not
We have all of our DBs on Exadata's in Oracle 19. For some reason one of the other DBAs installed an Oracle 18 home and it never gets used or patched. Our security office keeps flagging it since it is ...
- 13
0
votes
1
answer
32
views
The instance to which the redo log belongs in Oracle RAC cluster
I have a cluster of Oracle RAC with 4 primary nodes
master 1, master2, master3, master 4
I want to know which master node generates an online redo log or archived redo log.
For example, if there is ...
wang michael
0
votes
1
answer
26
views
What are the consequences of a 100% full SYSAUX tablespace?
I have Oracle 19c database with more than 20k objects and more than 100 active sessions at the same time. I found that SYSAUX tablespace is ~99.7% full. It is clear enough what to do with it.
I would ...
0
votes
1
answer
22
views
What is the function of the folders rdbms and tnslsnr on Oracle database
Question
What is the function of the folders rdbms and tnslsnr on Oracle database ( Windows ) ?
The issue with the folders rdbms and tnslsnr is that they are getting larger and larger 40gb+, and ...
1
vote
0
answers
34
views
Oracle 11g, unable to drop user
I was unable to drop a user due to a strange error and I could not fix it, this is my log:
SQL> SELECT s.sid, s.serial#, s.status, p.spid FROM v$session s, v$process p WHERE s.username = 'GGATE' ...
- 11
1
vote
1
answer
29
views
TDE: Format of Data in Datafiles (.dbf files) with NUMBER and VARCHAR2 datatype
Nice to meet you all.
This is the first time I have posted here, so please feel free to let me know if I am wrong.
I am doing some research on the Transparent Data Encryption feature. I want to ...
- 13
0
votes
0
answers
11
views
I need to make the existing non partitioning table as partitioning table and non partitioning table has many linked child tables
The main table is linked with many child tables using foreign key. And need to add the list partition in the main table but i want all child table will also be linked with this partitioned table. ...
Nitika Jain
0
votes
1
answer
22
views
Oracle - manual replication and force logging
When we configure Data Guard, we enable force logging. If we set up manual replication (via rsync and cataloging the archives at the destination + database recovery), do we need to enable force ...
- 801
0
votes
0
answers
47
views
Is there a way to know until what time a point-in-time recovery of an Oracle database was performed?
Is there a way to know until what time a point-in-time recovery of an Oracle database was performed?
I only found references to the columns RESETLOGS_CHANGE# and RESETLOGS_TIME in V$DATABASE, but ...
- 801
0
votes
0
answers
28
views
oracle 19c user getting EXPIRED & LOCKED within several minutes when all password related settings in its profile are set to unlimited
I cannot understand why the account in Oracle 19c NON-CDB database getting locked few minutes later after creation. Audit trail is not configured.
I'm running the database in container, it's Oracle ...
0
votes
1
answer
31
views
Select hierarchical rows in proper order
How do I fix this query in Oracle?
SELECT t.id, t.code, t.text
FROM our_table t
WHERE EXISTS (
SELECT 1
FROM our_table root
WHERE root.id = t.root_id
AND root.code IN ('1', '2')
)
...
- 109
0
votes
1
answer
27
views
Oracle - Completly audit of normal users and DBA's
Which is the option to enable user audit and DBA actions?
I mean, let's say we audit a table called salary. No normal user can update the table without being audited.
But a DBA (which has full access ...
- 801
0
votes
0
answers
36
views
Select, Filter and Sort ist not working with Oracle SQL Developer
I am using SAP ADBC to UPDATE some Oracle database entries.
It's working and the data appearance in the SQL Developer between "normal" and UPDATED entries look same.
But when I use a,
SELECT ...
- 1
0
votes
0
answers
60
views
Rotate encryption key for asm tde wallet oracle 19c
I have 1 database that has the tde encryption wallet located in ASM rather than o/s
I now need to rotate the encryption key. I normally do this by changing the key ob one node then copying to the ...
0
votes
1
answer
42
views
Oracle 19 recover table with remap and constraints issue
I have a Oracle 19 DB that I am testing table restores from RMAN backups. I am using this code.
recover table "HR".REGIONS OF PLUGGABLE DATABASE 'pdb'
until ...
- 13
0
votes
0
answers
94
views
ORA-65139: Mismatch Between XML Metadata File And Data File
I have two systems - SYS1, SYS2(stand alone). On both is running an oracle 19c database. I want to create a plugable database from SYS1 and want to plug in the SYS2 database.
I created the pluggable ...
- 1
0
votes
2
answers
229
views
How to Restore RMAN Backup at Specific Date
I have daily backups of my test system which is taken daily full backups and also hourly arch backups. I need to restore one of these backups to another linux server. I've completed all steps until ...
- 127
1
vote
2
answers
179
views
RMAN - keep until and control_file_record_keep_time
Should I adjust control_file_record_keep_time to match the KEEP UNTIL value in my RMAN script?
Here's my monthly backup script:
RUN
{
BACKUP AS COMPRESSED BACKUPSET FULL
FORMAT 'FULL_MONTHLY_%...
- 801
0
votes
0
answers
36
views
What are the implications of PLSQL_OPTIMIZE_LEVEL=2 and PLSQL_DEBUG=TRUE?
I have an Oracle database (19c EE) where several users develop PL/SQL Code. When I query the view USER_PLSQL_OBJECT_SETTINGS it shows me 700 entries with PLSQL_OPTIMIZE_LEVEL=2 and PLSQL_DEBUG=TRUE. I ...
- 123
0
votes
1
answer
51
views
Oracle database backup recovery
Just startet to study Oracle, i need to restore database backup. Log file said it was made in Oracle Database 10g Enterprise. I have Oracle XE 18, is it even possible to make it for XE version usind ...
2
votes
1
answer
40
views
Oracle optimizer does not use column group information from multiple tables and misses correlation
I have a problem where the execution of a query is too slow in oracle 12c standard edition.
I figured out, the optimizer estimates wrong cardinalities because it misses the correlation between two ...
- 21
3
votes
0
answers
47
views
How to check if Oracle Database is using EBR ( EDITION-BASED REDEFINITION) or not
As per Oracle's documentation, since version 11g, Edition Based Redefinition (EBR) is initialized with a default edition named ORA$BASE. Any future EBRs are children of this so-called "edition&...
- 31
0
votes
1
answer
61
views
How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?
Context
Here is the DDL that I am intending to use to define the table for a logistics/delivery company.
CREATE TABLE scraping_details (
id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and ...
- 73
0
votes
1
answer
40
views
Is it any performance downgrade when query separate db (by db link) on same physical server
I have simple question but i could find any information about it. I also run some tests but didn't get any results that i measurable. Let say i have same structure with same data on my db and second ...
- 1
0
votes
0
answers
90
views
Calling stored procedures multiple times in the same transaction
I have a PLSQL Package with several stored procedures inside.
This package is used to create specific reports.
I call the procedures like that :
package.init(parameter1 => 'Blablabla');
package....
- 1
1
vote
0
answers
67
views
How to Recreate Redo Log Files During Database Restore?
is it possible to recreate the redo log files during the database restore?
I used the following command:
run{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
set newname for database to '+DATA';
RESTORE ...
- 801
0
votes
1
answer
30
views
Listing all declared advanced query rewrites in Oracle
The DBMS_ADVANCED_REWRITE documentation page for Oracle explains how to declare query rewrites and drop them, but doesn't explain how you can list all declared rewrites in the database.
Is it possible ...
- 103
0
votes
0
answers
138
views
Oracle 19C: Unable to process database creation using DBCA (Database Configuration Assistant)
After downloading Oracle 19c, I ran terminal as administrator and followed the instructions based on the official documentation (https://docs.oracle.com/en/database/oracle/oracle-database/tutorial-...
LittleGobbyJr
0
votes
0
answers
116
views
ORA-03113 error running Oracle container in Docker
I am on an Mac M1 machine. I am trying to spin up a docker container with the command
docker run -it -e ORACLE_PASSWORD=<pwd> gvenzl/oracle-free:23.3-slim-faststart
The image is pulled ...
0
votes
1
answer
22
views
sqlplus column header misaligned
In wide sqlplus reports, column headers get misaligned from the data
The code that led to this output was:
set linesize 32767
set trimspool on
col z format a1
alter session set nls_date_format='...
- 101
0
votes
1
answer
47
views
Oracle temporary tablespace performance
I'm looking for ways to improve my database performance for heavy analytical queries and found that my temp tablespace has 1M extents (default for uniform tablespace). With my usual temp tablespace ...
- 110
0
votes
0
answers
45
views
How to Determine the Low High Water Mark (LHWM) in Oracle DB?
How could one determine the position of the Low High Water Mark (LHWM) for a non-partitioned heap table segment in Oracle?
I am concerned, that if we only do direct path inserts and never update, ...
- 123
1
vote
1
answer
67
views
ORACLE: RDS On AWS
This may be a lack of understanding as I am a MSSQL DBA more than Oracle.
We are running Oracle on RDS in Amazon.
Engine Version 19.0.0.0.ru-2020-10.rur-2020-10.r1
I dropped an old _backup table but ...
- 431
1
vote
0
answers
36
views
Object visualization problem with owner DBA
I'm having a problem viewing other owners' objects, even though I'm logged in as a user with DBA permissions
PL/SQL does not display the view/edit spec & body option
For testing purposes, I ...
