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,584
questions
0
votes
0
answers
33
views
with oracle impdp can you drop the objects that does not exist in the dump file but are existing in the database?
For exemple if i have a bunch of objects in the database that don't exist in the dump file, with the impdp can i drop these objects and in the end i only have the objects that are in the dump file ...
3
votes
2
answers
244
views
How can I compute the TOTAL of multiple COLUMNS from a PIVOT?
I have the following ORACLE SQL table/data:
A
DATE_FIELD
C
D
1
01-JAN-13
hello
0
2
01-FEB-13
wonderful
10
3
20-MAR-13
world
20
4
21-APR-13
tree
10
5
21-JUN-13
tree
30
6
01-JUL-13
world
10
7
30-...
- 33
1
vote
1
answer
157
views
How to find the error column for "ORA-01438: value larger than specified precision allowed for this column"
I get this error ORA-01438: value larger than specified precision allowed for this column, but I don't know what column. How can I find the column with the error?
INSERT INTO plants(created_at,...
- 115
0
votes
0
answers
20
views
Does AWS RDS Oracle support SSL between Standby and Read Replicas?
Does AWS RDS Oracle support TLS 1.2 between cluster members e.g. Standby (Multi-AZ deployment) and Read Replicas? I cant seem to find any documentation to confirm this.
- 101
0
votes
0
answers
22
views
use incremental backups for Oracle standby forward roll on a regular basis?
My boss asked me to create a copy of a production database that lags for one day. Well, it's not a complex task, just have a standby with a 24 hour delay. But it comes with a cost of storing a lot of ...
- 151
0
votes
1
answer
373
views
Oracle Database XE : [FATAL] [DBT-50000] Unable to check available system memory
I installed oracle database xe on centos 'linux'
and when I run this command: /etc/init.d/oracle-xe-21c configure
I get the following exception:
[FATAL] [DBT-50000] Unable to check available system ...
1
vote
1
answer
101
views
ORA-04091 table is mutating trigger/function may not see it when cascading, works otherwise
I have the following two tables:
CREATE TABLE entries
(
id NUMBER(20, 0) NOT NULL PRIMARY KEY,
first_name VARCHAR2(512) DEFAULT NULL ...
- 151
0
votes
1
answer
53
views
Where is Oracle TNS redirection configured?
The short version,
I need to connect to an Oracle server and I am getting redirected to an IP address that is blocked by the firewall. What do I need to ask to find out what IP addresses I will be ...
- 115
0
votes
1
answer
59
views
Oracle Database - Can I create a table and reserve some space for it?
Business Case:
We have an application that creates backups of its data. Each time that a user creates a backup, a registry is created in another table (like a historical table) ...
Right now, we ...
- 147
0
votes
2
answers
63
views
Set up PL/SQL Developer
i have got PL/SQL developer installed. However, it asks me to enter a password, username, and database. I dont know what to enter in this case. Am I going to need "oracle SQL client" to set ...
- 1
0
votes
0
answers
62
views
how to efficiently move and compress LOBs in ORACLE?
community!
I have a table with three SecureFile LOBs in it (lets name them A, B, C) and I want to compress them.
At first I wanted to do it in three parts, every time moving and compressing one ...
- 151
0
votes
1
answer
51
views
sqlplus: command not found Using SSH on Windows to VMware linux
I have oracle Database 12c in a virtual machine with the database working normally in a linux environment (Centos).
Through windows, using ssh I was able to connect to the linux virtual machine. But ...
- 3
0
votes
1
answer
50
views
Why is to_char used when doing a divide by 0 in Oracle based SQLi?
For conditional-error-based SQLi, instead of writing 1/0 directly like MYSQL, we need to write to_char(1/0) for Oracle, what is the reason behind this? Example,
Oracle:
SELECT CASE WHEN (YOUR-...
- 1
-2
votes
1
answer
35
views
UseWho gives me an example of the total salary format comma
Who gives me an example of the total salary this
Select sum(to_char(salary,'9,999,999')) from employees;
Output
Ora-01722
- 1
0
votes
2
answers
68
views
How to implement "SELECT INTO" in Oracle with PostgreSQL commands?
I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves ...
0
votes
2
answers
91
views
Add unique conditional constraint to exsiting table
I want to add a unique conditional constraint to an existing table in oracle but I get an error.
ALTER TABLE T_EVENTATTENDEE ADD CONSTRAINT EVENT_ATTENDEE_UNIQUE UNIQUE (CASE WHEN C_DELETED = 0 THEN ( ...
0
votes
1
answer
56
views
How to use Transparent Data Encryption in Oracle?
I'm struggling when trying to implement DTE in my local Oracle 19c environment (with pluggable database ("orclpdb")).
What I'm trying to do:
Create sys user (done)
Create "admin" ...
- 101
0
votes
1
answer
192
views
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
we are getting this error when we try to create this scheduler_job but we try to create it give us this error:
BEGIN
DBMS_SCHEDULER.CREATE_JOB
( job_name=>'PROC_TKN_WA_FIX_ARREARS',
job_type=...
0
votes
0
answers
51
views
How does JDBC over LDAP connection work?
The JDBC connection I should use is defined in Oracle SQL Developer with the following syntax:
jdbc:oracle:thin:@ldap://intranet.oid-01.dama.ch:3063/EDWHPD,cn=OracleContext,dc=emea,dc=dama,dc=ch
But ...
- 133
0
votes
1
answer
48
views
Receiving error "can not access rows from a non-nested table item" while working with associative arrays
Below , I'm trying to use the data of the array I've declared,
in the where clause of the query but I receive the following error :
declare
v_input varchar2(400) := '0,1,2,3,4';
type ...
- 1,390
0
votes
1
answer
50
views
Receiving error "Local collection types not allowed" while working with associative arrays
I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
- 1,390
0
votes
2
answers
39
views
Command line command to read Oracle Database export Success Message In log File
We have several oracle 19c servers with daily exports. I would like to find a way to run a command to read the export log file to find the success message for the day. e.g. I would like to putty into ...
- 1
1
vote
1
answer
45
views
Huge Increase in Storage Needed to Store Data in Oracle
I have a table stored in a tablespace where the size growth is usually not too big. However, on the last data loading, it suddenly needed so much more additional space, while inserting the same exact ...
- 11
0
votes
1
answer
33
views
create table based on another table
I have a table called " Product " it has: ID_P, Name, expiration date
Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
- 41
1
vote
1
answer
82
views
How to automatically add TLS certificates to Oracle wallets?
When accessing external webservices via HTTPS via the Oracle database you need to add the corresponding certificate to a database wallet to prevent a certificate validation error. When one creates an ...
- 113
0
votes
1
answer
44
views
how to create a calculated column
I want to create a table, this table's name is "Prod", in his table, I have the:
ID, name, Buying_price and selling_price
when the user inserts the data, I want him to enter "name, and ...
- 41
0
votes
2
answers
130
views
Oracle sql - all records deleted and cannot retrieve data
All records from all of my tables were removed from database. I didn't use any DELETE method to clear the database. Have you got any idea what could happen? I have never had similar situation before.
...
3
votes
0
answers
73
views
Optimizing queries using indexes
I'm doing an exercise, where we have to improve several queries:
(30%) Give me FH and FC per month, filtered by the aircraft model (e.g., "777").
(30%) Give me ADOSS, ADOSU per year, ...
- 131
0
votes
1
answer
31
views
Oracle 19c create table result_cache clause syntax
I could not find the exact syntax for the "Result_Cache" clause in the Oracle 19c documentation for the CREATE TABLE statement. It mentions the values of DEFAULT or FORCE, but not the ...
- 1,103
1
vote
1
answer
32
views
Is there any advantage using non-equi join instead of where clause in Oracle SQL?
I've been studying Oracle SQL non-equi join and while there are so many different use cases, I noticed that joining tables using a range of values with non-equi join is basically the same as using ...
- 113
0
votes
0
answers
14
views
Oracle VM display crash
As shown above, I can't view the text in the oracle desktop. Not sure how to solve it. Please help me.
0
votes
0
answers
65
views
default passwordless wallet for utl_http in Oracle
I have Oracle 18c and by default the following fails with ORA-29024: Certificate validation failure:
DECLARE
req utl_http.req;
res utl_http.resp;
BEGIN
req := utl_http.begin_request('https://...
- 233
-2
votes
1
answer
56
views
TRIGGER THAT UPDATES A COLUMN FROM INSERTED ROW (ORACLE)
Basically I need a trigger that updates a column of the inserted row, where it takes a value from another table by comparing the foreign key.
Something like this:
Thanks in advance.
2
votes
2
answers
185
views
What is Oracle Tablespace equivalent in SQL Server
I've been tasked with converting an Oracle database to SQL Server based on a DDL. An example Oracle create table statement is as follows:
CREATE TABLE "RENTAL"."ADDRESS"
( ...
- 123
0
votes
0
answers
82
views
Using a different time for recovery after restoring an Oracle Database 19c
After restoring a oracle database with the following command, recovery fails :
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 8;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(xxxxx)';
CONFIGURE ...
- 938
0
votes
1
answer
70
views
Oracle TDE - what mean rotate (rekey) encryption key? why and when?
Maybe I missed something. Reading about Oracle transparent data encryption (TDE), some references say about rekey (or rotate) the master key. But why should we do that?
I mean, why to execute the ...
- 590
0
votes
0
answers
24
views
need to set limit the data comes from child table
Genres is my parent table and contents in one of the child table, what is want is need to set the limit for the data comes from child table, how can i achieve this concept?
0
votes
1
answer
52
views
Interpretation of impdp/expdp character set warnings
impdp and expdp report encoding warnings in the form of:
export done in A character set and B character set
import done in C set and C character set
Why do both warnings name two character sets? What ...
- 103
0
votes
0
answers
29
views
Creating dimensions and fact tables in ORACLE 11g
I'm modelling a data warehouse based on star schema, and now I'm implementing the dimensions and fact tables in Sql developer.
For creating dimensions I found statements in Oracle documentation as ...
0
votes
1
answer
62
views
SQL : How to automatically run different SELECT query on a daily basis base on the current system date
I have 31 tables one for each day of the month where the table names are like subscription_x where x is the day of the month
On a daily it should run a select * from subscription_x to get data from ...
0
votes
0
answers
17
views
Does Oracle forces its own "estimate percent" even when using "estimate_percent" parameter in dbms_stats?
For example, why even using estimate_percent=>2 (2%) parameter, it still show sample size = num_rows (100%)?
SQL> exec dbms_stats.gather_table_stats(ownname=>'TESTE',TABNAME=>'T1',...
- 590
0
votes
0
answers
88
views
Memlock and HugePages in Oracle database, which value to set?
Oracle says:
The value of the memlock should be at least 90 percent of the current
RAM when HugePages memory is enabled and at least 3145728 KB (3 GB)
when HugePages memory is disabled.
But the MOS ...
- 590
0
votes
1
answer
46
views
Can I copy a newer Java JRE over the existing one in Oracle Client 19.16?
A server I manage has Oracle Client 19.16 with Java JRE 1.8_331 installed. Can I just copy a newer version of the Java JRE, like 1.8_351 over the old one without having any negative effect on Oracle ...
- 103
0
votes
0
answers
56
views
What’s wrong with provided apex instance administrator password
1
I have installed Oracle Database 21c Enterprise Edition on Oracle windows 10 I am now trying to install Apex 22.2 .
Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance ...
2
votes
0
answers
104
views
Prevent appending schema name to function-based index calls
When a function-based index created using
CREATE INDEX I_FN_TEST
ON T_TEST (TESTPKG.TESTFUNC (TEST_COLUMN));
Oracle appends the current schema name to the TESTPKG call, making it CURRSCHEMA.TESTPKG....
- 21
0
votes
0
answers
63
views
Is there a utPLSQL way to mock procedures, functions, or tables?
In tSQLt, you can isolate dependencies for unit testing using procedures like FakeTable to create a dummy version of whatever your test subject depends on, that contains only data you specify. This is ...
- 153
0
votes
0
answers
40
views
Version managed data and long transactions
I'm looking for a solution for version managed data in PostgreSQL (and Oracle).
This means database objects having different properties in different versions of the data set. Objects can even be ...
0
votes
3
answers
84
views
Can't find my username in the dba_sys_privs table
I logged into oracle SQL Developer with user test. I need to know if I have SELECT ANY TABLE privilege. So I executed the following query:
select *
from dba_sys_privs
where grantee='test'
But it ...
- 115
3
votes
0
answers
239
views
Difference between To_Number (Null) and Cast (Null as Number) in Oracle
There is a difference in behavior between these two variants to create a view with a null number column and then a table from that view: One fails and one succeeds.
At the same time there is no ...
- 76
2
votes
0
answers
28
views
How to derive a LOCK ID from a user lock handle?
I have a lock handle from dbms_lock.allocate_unique() but I would like to get the Lock ID from the handle. By ID I mean the value of SYS.DBMS_ALLOCATED_LOCK.LOCKID and V$LOCK.ID1. From what I can see, ...
- 656