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
1
vote
2answers
124 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 ...
0
votes
1answer
71 views

Oracle 12c query performance varies based on schema/user

I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here's my setup. Oracle 12c DB SAMPLE schema/user WIDGET table (...
0
votes
1answer
16 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 [¿...
0
votes
1answer
21 views

update records that are there in .csv file which will be given as input [closed]

I have .csv file with list of Ids, using those IDs in file- giving them as input, I need to update the record in table, I have around 4000 Ids so 4000 update statements are not possible, need a ...
-1
votes
1answer
25 views

Validation of Column value data to Other table column value [closed]

I have 2 Tables, 1st is the source table which contains City name and 2nd Table is staging table also contains City name and I want to validate the city name for each row in 2nd table is valid or not ...
3
votes
1answer
180 views

How to get Oracle_Home directory?

I'm not asking "where is my home directory", or "how do I find it". I am pretty sure I don't have an Oracle_Home directory and I want one. What is the minimum software package that I should install to ...
1
vote
0answers
19 views

Oracle 12c - Installing SW with an Active Directory Account on RHEL7

My client insists on installing Oracle using an Active Directory Account as the SW owner instead of creating a local oracle service account on RHEL7. I'm opposed, but willing to be flexible to meet ...
1
vote
3answers
60 views

How to select from a table where in a list and then diff that against the same list?

Here is what I need: I have a LIST of keys: A B C D A TABLE keys that contains: A B **... and millions of other entries** ( but `NOT` `C` or `D` ) I want to see what is in the list AND NOT IN the ...
0
votes
0answers
65 views

BI- Directional Golden gate integrated capture (oracle - oracle ) fails [ recovery scn values not set ]

I see " ERROR: Extract EXT1 is not ready to be upgraded because recovery SCN values are not set " with integrated capture, Appreciate your inputs and pointers on the below issue w.r.t integrade ...
1
vote
2answers
396 views

Database Table/Column rules [closed]

We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place, Each table and column name should be meaningful. It should ...
1
vote
2answers
84 views

Can sqlplus fetch more than one row at a time?

We have a task running every day dumping results of a query into a CSV-file -- as the stdout of sqlplus running a query. The number of records is many millions (don't ask), and the job takes over two ...
0
votes
1answer
80 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
39 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
41 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
38 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
49 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
53 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
108 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
63 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
53 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
64 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
34 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
53 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
141 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
48 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
285 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
33 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
25 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
332 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
52 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
115 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
56 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
65 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
738 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
165 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
41 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
101 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
165 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
33 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
80 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
72 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
47 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 ...