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
1answer
60 views

Is Oracle 12.2 backward compatible with Oracle 12.1

Current Oracle DB I am using is 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit' and we have a use case for using 'Oracle 12.2.X.X.0'. We want to use the elongated object name ...
0
votes
1answer
31 views

Oracle Password File Version

When trying to perform certain user security actions, I get the following error: ORA-46953: The password file is not in the 12.2 format. How do I migrate a 12.1 password file to 12.2? Or, is this ...
0
votes
2answers
43 views

Grant schema permissions effective for everything existing and everything that will exist

I'm looking to do what this answer explains but in an Oracle environment: https://dba.stackexchange.com/a/125400 Essentially what I'm looking to do is "GRANT schema permissions that are effective ...
2
votes
0answers
40 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
3
votes
1answer
36 views

Query Performance Help

A brief history. I have an oracle sql query which I'm looking to pull data when a certain preference is enabled. In this query I had a date range to filter the data to a specific range. This was ...
0
votes
1answer
38 views

deadlock detected during oracle expdp

I have an Oracle 11g and I wanted to export all my shcema objects, with this command: expdp as '/ as sysdba' directory=DIR dumpfile=DWH%U.dmp logfile=DWH.log filesize=40000m parallel=32 schemas=DWH ...
0
votes
0answers
47 views

Is possible to send email alert / notification for long running query in OEM

Due to long running queries our batch failed sometimes so we requested to identify if any query is running more than 5 min then it should be killed and alert / notification to the team. Is this ...
1
vote
1answer
103 views

Help needed to tidy up deprecated (and mal-functioning) SQL code [closed]

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (...
0
votes
0answers
24 views

merge statement taking 30mins for 1777820 rows? how can i optimize it

merge into apl_scp_direct_dispatch t Using (select siteid, item, bucketid,band_qty from i2_scp_inventory_bands@DB_DMSAPIL_LINK where band_type = 'DEPBAND' and band_level = 2 ) s ON ( s....
0
votes
0answers
36 views

Connecting to postgres via ODBC from AWS Oracle RDS

I would like to connect a 12.1 Oracle RDS AWS instance to a Postgres database (also running at AWS but hosted on an EC2 instance). There are a number of examples of how you could do this (eg https://...
0
votes
0answers
33 views

In a trigger, does assigning a column to the “old” value incur a cost? (Oracle) [duplicate]

For the sake of readability, in one of my triggers I have :new.COLUMNNAME := old.COLUMNNAME; alongside the sets for the rest of the columns. Does this statement get removed before runtime? (That ...
0
votes
1answer
52 views

How to select distinct? not select duplicated row

I have 2 table select DISTINCT (B.MAIN_ID), A.NAME, A.NAME_ID, A.CREATED_DATE from FAMILY A, ADDRESS B where B.F_ID = A.N_ID result: MAIN_ID | Name | NAME_ID | CREATED_DATE 100 | ...
0
votes
1answer
56 views

How to run INSERT INTO AS SELECT in parallel on Oracle?

I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP. INSERT INTO TEST_USER.AUD_BACKUP SELECT * FROM SYS.AUD$; COMMIT; How do I use ...
-1
votes
1answer
33 views

Adding columns for group by result values in oracle

Lets say my table like this and the table name is Employee. Employee ID Name Age 1 Dilshan 22 2 Udara 25 3 ...
1
vote
1answer
43 views

Test-Automation: Diff Data in Oracle Database - what are best practices? [closed]

We're re-writing a COBOL application in Java. We want to make sure, the applications behave the same, by validating our unit tests produce the same DB changes. How can we easily diff the data in the ...
0
votes
0answers
52 views

Why is my continuous query notification script only returning events from production database?

What I am using: Python 3.7 Oracle 12c (development) Oracle 10g (production) RedHat 6 cx_Oracle 7 oracle_client 11.2.0 What I am doing: Building a python listener that uses continuous query ...
1
vote
2answers
74 views

how to quickly insert data as select

In an Oracle DB have about 200M rows (about 20GB data) that I would like to insert into another table by insert into foo select * from bar where some_unimportant=condition; How do I do that the ...
1
vote
1answer
93 views

Managing AUD$ without DBMS_AUDIT_MGMT

I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on Oracle DB version 12c (12.2.0.1.0). With time (in 3 years) my AUD$ table grew to 500GB in ...
1
vote
0answers
35 views

How to truncate a table without locking it?

I have a SYSTEM's table AUD$ in ORACLE's database that is constantly being populated (whenever a DB user logs in to DB - a records is written to AUD$). The table AUD$ has like 100 million records (...
-5
votes
2answers
165 views

What is a “LOB”

I see the term LOB used often when describing data types in columns of tables. What is it an acronym of, and why is this data object important?
1
vote
1answer
25 views

Oracle expdp command executed in a command file not honoring %U instead using _U without a sequence

I have generated a bunch of expdp commands and stored them in a .cmd file. When I execute one of the commands by copy and paste into a command prompt, it works fine. If I try to execute the whole ....
0
votes
0answers
23 views

Oracle - Dispatchers parameter overwrite shared_servers and max_dispatchers?

I'm not understanding how to use these parameters. For example, when I set dispatchers parameter with (PROTOCOL=TCP) (dispatchers = 4) don't matter if shared_servers parameters is 0, the oracle ...
0
votes
2answers
236 views

Errors using expdp with `flashback_time=systimestamp`

Oracle 11.2.0.1.0 on Windows 2012 Std: expdp '/ as sysdba' full=y directory=exp dumpfile=... logfile=... flashback_time=systimestamp Fails with: ORA-39001: invalid argument value ORA-39150: bad ...
0
votes
1answer
51 views

Copy DBF files from production to development environment

I'm not an expert. I'm a Software Developer but got trapped in doing this DBA work. We have a development environment and we would like to copy the DBF files from the production environment to the ...
0
votes
0answers
28 views

Oracle's Unusual Behaviour: Either database not opened or connected to an idle instance on startup

I have been getting this issue multiple times whenever I am trying to connect to oracle db. Connected to an idle instance error This was because the database din start correctly Database not ...
0
votes
0answers
110 views

Install Oracle 12c on Oracle Linux 6.7 port in use

I have attempted to install Oracle 12.1.0.2.0 on Oracle Linux 6.7 multiple times without success. I have installed the database software only with the configuration assistant and am attempting to ...
1
vote
1answer
49 views

Does Visual Studio 2017 Community (.NET Framework 4.6.2) support Oracle 8i [closed]

Does Visual Studio 2017 Community (.NET Framework 4.6.2) support Oracle 8i? I am trying to connect with .NET Framework 4.6.2 to a production Oracle 8i database. I googled it, but I can not find ...
-3
votes
2answers
61 views

Need a shell script that can run a oracle query and produce output in .csv format

Can you please help- need a shell script that will have a oracle query inside it and produce a .csv format output in specific location, that script should be a scheduled in a cron job which runs, the ...
0
votes
2answers
514 views

Error During IMPDP in oracle database 12c

I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7 EXPDP Command: expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log ...
0
votes
1answer
116 views

Can't connect through sqldeveloper because locale not recognized even though my language/regional settings are English/US

On a certain machine I consistently cannot connect to an oracle db due to the locale not being recognized: Other people have noted the locale or language settings may not be compatible with the ...
0
votes
0answers
25 views

Search string into dbms_xplan.display_awr output

I want to search into the result of this query. Select cursor(select * from table(dbms_xplan.display_awr(sql_id, plan_hash_value,null,'all +outline +peeked_binds'))) "PLAN" from gv$sql where ...
0
votes
1answer
36 views

Issue migrating oracle package to postgres using ora2pg

I was migrating an oracle(12c) package to postgres(Enterprise db 10.5) using ora2pg. The migration run successfully for most of the functions. However, one function referenced multiple times in the ...
1
vote
1answer
66 views

APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created

I am trying to install APEX 19.1 on ORACLE XE 18c but the problem is that APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created. While I execute apex_rest_config.sql script via sqlplus, an ...
0
votes
3answers
110 views

Oracle SQL - find the character '&' in a string

I am intending to do an update (Oracle SQL) for any customer records that includes an ampersand in their name (eg: Joe Bloggs & Co.) to replace this ampersand with the actual word 'and' (eg: Joe ...
0
votes
0answers
32 views

ORACLE timed Datapump on Linux? (No crontab)

what is the best way to create a timed Datapump Dump of a PDB on a Linux System? No Crontab. And how do I implement it?
0
votes
1answer
71 views

Oracle 12c create manual script for automatic startup on RHEL7

I need to create a script to start and shut down a 12c database in rhel7 automatically when the operating system starts and shuts down, it turns out that I have tried the common options with dbstart ...
1
vote
1answer
45 views

Oracle 11g alternative options for oracle 12c with function

I am using (with function) in Oracle 12c. in 11g this is not available. Is there any other way we can implement with function in 11g? with function x(p_NUM in number) return number is n ...
0
votes
2answers
53 views

SQL*Plus - interactive mode on windows

How can I issue commands through SQL*Plus on Windows? In Linux is like this: sqlplus -s /nolog<<EOF conn username/password@database EXECUTE some_proc; EXIT; EOF And Windows?
0
votes
1answer
45 views

Use quoted schema in an insert statement executed thorough PreparedStatement in Java

I created an unquoted table my_table under a quoted schema "Quoted_User". When executing the statement INSERT INTO "Quoted_User".my_table (custom_id) VALUES (DEFAULT) using the Intellij Oracle ...
0
votes
1answer
33 views

Copy package in Production DB to Test DB with dependencies?

This is my first question here, so please let me know if this isn't the right place to ask. I'm a new programmer, fresh out of school. Primarily I develop in Python/C#, with a dash of SQL when needed....
0
votes
1answer
73 views

Oracle database issues

I had my database working properly but I had an issue and when I tried to repair it I executed the following command alter tablespace system add datafile '/var/oracle/oradata/data.dbf' size 10000m ...
-1
votes
1answer
45 views

How many concurrent sessions does Oracle database support?

I want to have an application linked to an Oracle database. The users will request data through the app, and the app will access the database and retrieve the info. My question is: how many ...
0
votes
1answer
29 views

How to put result of SQL into bind variable

I'm trying to put the result of the query into a bind variable like this, but doesn't work: var teste number; exec :teste := (select trunc(avg(e.salary),2) from hr.employees e); How I make it to ...
0
votes
0answers
49 views

Update Certificate in Oracle Wallet

We have been downloading data from our vendor for over ten years over the Internet through an encrypted connection enabled by an Oracle Wallet. The self-signed certificate expired, so the vendor sent ...
1
vote
1answer
39 views

How to increase the value of a column in a table by a constant by consulting another table?

I have prepared the database of the college given as create table depts( deptcode char(3) primary key, deptname char(70) not null); create table students( rollno number(2) primary key, name char(50),...
0
votes
1answer
45 views

Oracle - clean LOB files - recovering disk space

I have a friend who has a website and asked me for help. I often use MySQL databases but never Oracle databases. And unfortunately he has an Oracle database, so I can't find a solution. The available ...
0
votes
2answers
308 views

SSIS Slow Data Transfer between Oracle and SQL Server 2016

I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS. It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the ...
-1
votes
1answer
37 views

Month on Month count to determine variance of table growth

Good Afternoon Team, Hoping you can help me . Every month i get a count of records associated to a table. What i would like to do is month on month calculate the % variance (between jan vs feb, feb ...
0
votes
0answers
27 views

Data Loss issue while migrating data from Source(Oracle) to Data Warehouse Staging Database(SQL Server)

Project Overview Loading data from Oracle Database to Datawarehouse Staging Database(SQL Server). Using ETL process we are loading data from Oracle to SQL Server. Staging Database in SQL server is an ...
0
votes
1answer
35 views

Oracle function based index with empty function name

I'm trying to understand this syntax in the creation of an index in a database I'm using: CREATE INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3')); After searching for a ...