Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
23 views

Searching Oracle tables for particular values

I want to search all tables, all rows in a particular schema for a particular value. This is so I try to understand where a value is getting persisted. How do I do this in Oracle?
0
votes
1answer
16 views

SAP BO Freehand SQL date formatting (Oracle db)

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query ...
0
votes
1answer
31 views

Impdp 12c takes table original size

I want to import one of my schema "vvs1" to r&d database with content=metdata_only. expdp works perfect but when i am going to import on r&d server it takes tables original size. for example:-...
0
votes
0answers
15 views

How to disable Oracle OCI client transparent reconnect

I have an application I wrote that uses OCI to connect to an Oracle database. The database has recently been upgraded to Oracle 18, and since then database searches in the application randomly stop ...
-1
votes
1answer
29 views

Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)?

PostgreSQL supports "Event Triggers" which is defined as, To supplement the trigger mechanism discussed in Chapter 39, PostgreSQL also provides event triggers. Unlike regular triggers, which are ...
1
vote
2answers
28 views

Fiscal Year dynamic function (Oracle SQL)

My Fiscal Year starts on 1st October (2017) and ends 30th September (2018) I want to write a function that will dynamically calculate first day of October. Currently I have: select Add_Months(Trunc(...
0
votes
1answer
54 views

Fiscal Year to Date filter

This is my data: I need a Fiscal Year to Date logic that will gather all previous months starting October. However, for October 2018 I want to start form 15th, for remaining years it must start from ...
1
vote
1answer
18 views

Oracle Subselect Union Multiple Column

I want to select and count all cats and dogs at one location; location | cats | dogs | new york | 150 | 66 | paris | 34 | 12 | What I've got so far: SELECT cats,dogs FROM (select ...
3
votes
1answer
92 views

How do I restore an Oracle file system backup onto a new developer machine?

A server (not mine) has died due motherboard temperature problems and disk controller problems. Given that the company's IT support should have known better, and so on, the company does have a file ...
0
votes
1answer
34 views

sqlldr not recognizing new line/row

Our organization switched to a new tool and now our text files are generated with only a trailing CR instead of a CR & LF. Now sqlldr thinks all of the data in the file is 1 row. How can I fix ...
1
vote
3answers
32 views

How can I find the backup that requires the least amount of archivelogs to restore?

Oracle 11.2.0.3 with daily full backup and hourly archivelog backup. RMAN with a catalog. If I take a backup at 11pm at night and it finishes 5am the next day and I want to restore a backup to a ...
0
votes
1answer
25 views

Missing archivelogs when restoring duplicate

We restore backups to our test systems from production via a point in time RMAN Duplicate with an online full backup and hourly archivelog. Every blue moon something goes wrong (working to resolve ...
0
votes
1answer
31 views

Custom return type of a function in Oracle PL SQl

I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different ...
0
votes
0answers
20 views

Copying Oracle DBF files to AWS S3 bucket [on hold]

I took backup of my oracle database using RMAN, now I want to move those DBF files to AWS S3 bucket. Can anyone suggest me the best and fast tool to move to AWS S3 bucket? Thanks in advance.
0
votes
0answers
31 views

startup problem with oracle database [on hold]

Trying to start the Oracle 10g database SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/oracle/product/10.2.0/db_1/dbs/...
0
votes
1answer
21 views

Oracle dbms_xplan.Display() equivalent to SQL Server 2017

I am converting some sql from Oracle to SQL Server 2017. One of SQL code is having dbms_xplan.Display() inside the script. I want to know what is the equivalent of dbms_xplan.Display() in SQL Server ...
2
votes
1answer
33 views

Network line switch-over in Oracle Data Guard

We have a Primary database with a remote Standby which established using Oracle Data Guard. They can be syncing over a private Wireless Network and a Wired LAN (There are two options as network medium)...
2
votes
1answer
60 views

Filtering previous month data with case statement

These are my available attributes: DT_CURRENT_DT Month Year 11-OCT-18 10 2018 12-OCT-18 10 2018 13-OCT-18 10 2018 14-OCT-18 10 2018 15-OCT-18 10 2018 16-OCT-18 ...
1
vote
1answer
33 views

How to automate CSV-exports of queries in PL/SQL Developer (Oracle)?

I'm no pro in Oracle or PL/SQL Developer (I mostly use and admin mySQL and MS SQL Server). I'm not an admin of any sort, but have read access to many tables in Oracle via PL/SQL Developer. I need ...
0
votes
1answer
44 views

How to make a function from a concrete SELECT statement in Oracle SQL?

How to replace a concrete value with a variable in Oracle SQL? I have select 5, min(id) from my_table where id > 5 --AND ... complex condition UNION ALL select 6, min(id) from my_table ...
-1
votes
0answers
22 views

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection [closed]

I am using a MacBook pro and I'm writing a script using the SQL developer provided by oracle for mac os. when i try to run my script i need to set up a connection I've been looking online with no ...
0
votes
0answers
32 views

vCPU's on VMWare for Oracle

We have an Oracle database running on Windows Server 2016 on top of a VMware host. The VM layer is there so we can move the entire server to another hosting center in case of a disaster. The physical ...
0
votes
2answers
47 views

Oracle db impdp command syntax error

I'm trying to run impdp system/password dumpfile=FILE.dmp fromuser=USER1 touser=USER1 directory=$DIRECTORY on oracle server but keep getting following error: LRM-00118: syntax error at '=' at the ...
-1
votes
1answer
42 views

How to copy data and schema from oracle to ms sql

Is there any easy way to copy data and schema from oracle to ms-sql. i am using sql server 2017 and oracle sql developer Version 4.1.4.21 I need one time migration. I am using SSMA but getting ...
1
vote
0answers
39 views

Count neighbors and sum of the neighbors values

I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct ...
0
votes
1answer
41 views

What is the best way to check if a value in a table has changed when writing to a database?

Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value For example, let's say I have a record with the value "link" under thetag_type field and "...
0
votes
1answer
32 views

bind variable and shell scripting with sqlplus

I wish to execute DMLs using bind variable with shell scripts. For example, something like: #!/bin/bash SH_NUM=10 sqlplus -S test_user/test_pass <<EOD var a number; a:=${SH_NUM} insert into ...
0
votes
1answer
34 views

Optimize Oracle 11g SQL*Plus connection for large remote SELECTs [closed]

Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a ...
0
votes
1answer
45 views

Running loadjava with Windows authentication

I want to run following command under Windows with Oracle Database 12c installed: loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar The problem is that I use ...
2
votes
2answers
96 views

How can to make a new user see table in existing schema [closed]

I'm new into Oracle so this question might be dumb. I created new user and now I would like him to see table (or even better all tables by one command) in already existing schema (hr schema in this ...
0
votes
1answer
29 views

Moving Oracle / Oracle datafiles to different file system on Linux

We have Oracle installed on /root, which is now running out of space due to increasing data in Oracle database. On the same machine we have another file system with free space. I want to know the ...
2
votes
1answer
28 views

execution plan TABLE ACCESS BY INDEX ROWID cardinality

This is the explain plan for a certain query. The query contains a PK order by clauses and matches on two other columns on the where clause. Since PK is used for index full scan, I assume cardinality ...
0
votes
2answers
80 views

ORA-01506: missing or illegal database name

I connected as sysdba and tried below: SQL> startup; ORA-01506: missing or illegal database name SQL> I tried multiple scenarios changing the Oracle_home and path variables but no luck. ...
1
vote
2answers
31 views

How to configure Oracle11g to send out Password Expiry Notifications vide SMS or e-mail?

Please guide me in configuring Oracle11g to send out Password Expiry Notifications vide SMS or e-mail before password expiry of one week or upon user's profile entering into grace period or any other ...
0
votes
0answers
32 views

How to view the job sql or pl sql source in oracle?

Is it possible to get the job source code by view or table in oracle? I can't find it the view for it.
0
votes
0answers
17 views

SP2-0667: Message file sp1<lang>.msb not found [duplicate]

I am getting the below errors while accessing the SQLPLUS. SP2-0667: Message file sp1.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory I tried multiple ...
0
votes
1answer
29 views

Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it?

I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause. set up: create table test_t ( x ...
1
vote
1answer
25 views

Oracle 11g - DROP database command on ACFS

In Oracle 11g and higher (on RHEL 6) issuing the 'drop database' command in mount restrict mode will delete all the DB's ASM datafiles. Can somebody tell me if this command also drops all ACFS ...
0
votes
0answers
14 views

Oracle Advanced Connection Security SQL

I am looking for a way to check the encryption and integrity protection level in effect for the current session. Is there a system view where I can get those details? It would also be good if the ...
0
votes
1answer
50 views

System-level way to enforce that reserved words may not be used for Oracle column names?

Is there a way in Oracle 12c to enforce that reserved words / keywords may not be used as column names? I am aware that it is not good practice to use keywords in this way. However from what we have ...
1
vote
0answers
57 views

Oracle 11g Select query returns NO ROW SELECTED but the table actually has data

In my production, There is a table named ABC under X schema. I have granted SELECT privilege on ABC table to another user named Z. When I execute the query SELECT COUNT(*) FROM X.ABC; as user Z it ...
0
votes
1answer
61 views

Switching alias in subquery equality leads to different result

Trying to learn sql queries recently and as the title says I stumbled upon some "behavior" i didn't understand. This query returns the managers from the database. SELECT * FROM employees e WHERE ...
1
vote
0answers
37 views

Case sensitive logon on some users

Looking for a way to let selected users have case-sensitive logon check on Oracle 12.1. Setting SEC_CASE_SENSITIVE_LOGON = TRUE enables all users to have case sensitive logon, but trying to limit that ...
0
votes
1answer
19 views

why OEM allow to connect system as normal and sys dont? [closed]

In OEM I can connect with system as normal but with sys cant connect as normal. Why is that?
1
vote
1answer
24 views

Problems with connecting to a remote Oracle 12c database

tl;dr RMAN failing to connect to standby database with correct credentials. RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied Password file is correct, ...
2
votes
1answer
58 views

Build a histogram of road speed based on road length by grouping road segments

A data set look like this: The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID A roadID (e.i. Lake Street) belongs to a ...
1
vote
3answers
33 views

PL/SQL date or to_char question

begin if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN DBMS_OUTPUT.PUT_LINE('FRIDAY'); else DBMS_OUTPUT.PUT_LINE('Not Friday'); end if; end; / I am trying to ...
0
votes
1answer
40 views

How to execute sqlplus by root using sudo in linux?

Scenario: *oracle xe11g r2 is installed( and runs properly when logged in as oracle). *logged in as user, root *added root to groups oinstall and dba as follows: #useradd -G oinstall root #useradd -...
0
votes
1answer
41 views

Consecutive dates count

I have table data like below: CLIAM_KEY MBR_ID SERV_FROM clm1 mem1 01-JAN-18 clm2 mem1 02-JAN-18 clm3 mem1 03-JAN-18 clm3 mem1 04-JAN-18 clm4 mem1 ...
2
votes
1answer
66 views

Is there a function which the opposite of NULLIF?

I am aware of the NULLIF function which compares two values and returns NULL if they match. Is there a function which will compare two values and return NULL if they don’t match? That is filter out ...