All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

0
votes
1answer
42 views

How to make a query on multiple columns without writting all possible permutations?

I'm at my first question on stackexchange, as i have a few days since I struggle on this matter: I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like ...
1
vote
1answer
33 views

Importing .dmp file into SQL developer: Do I need to import seperately for each table?

I have a large .dmp file which I would like to import into SQL developer. This contains all the data I want to import across several Schemas. Do I need to split this up into each separate table in ...
0
votes
0answers
30 views

'OraOLEDB.Oracle' provider is not registered on the local machine - Oracle Database Client 12C

I have some problem with connect to the Oracle Database by Oracle Client. I installed on laptop oracle client - version 32 bit. I can connect to the database from laptop when i use 64 bit ...
0
votes
1answer
29 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, ...
1
vote
0answers
24 views

How can I “buffered” mine Oracle logs using LogMiner with 'committed data only' option properly?

Using Oracle LogMiner with committed data only flag, According to the Oracle documentation, all the committed records with the same commit scn, will be joined together and will appear in a sequence. ...
0
votes
1answer
44 views

How to declare reserved words as variables in Oracle PL/SQL?

What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration? I realize bind and substitution may be acceptable alternatives for an actual query, but what about ...
1
vote
1answer
20 views

multi-tenant data related to multiple customer? [closed]

tenant type design on the database schema tables. We have about 20 customers data and single web application is serving it. Question, how do we improve query performance or what type of database ...
0
votes
0answers
12 views

Oracle E-Business Suite Installation

What should i do to download latest Oracle EBS ? I mean, There are lots options on eDelivery and i confused a lot. Oracle E-Business Suite 12.2.5.0.0 for Linux x86-64, 113 files Select Alternate ...
1
vote
1answer
29 views

how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time

how do I change the query below so it only reads and displays 132 characters each line with w(id, line#, line, rest) as ( select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), ...
0
votes
0answers
17 views

Is it a sign that oracle `TNSListener` has been started?

I am trying to do ETL from oracle to Hadoop using Sqoop. I have checked the IP and the ports. All ports are good. But I still get ERROR that shows "The Network Adapter could not establish the ...
0
votes
2answers
37 views

Is there a way to reference a column dynamically within a trigger?

I asked a similar question a few months back and I'm improving upon my solution! I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of ...
0
votes
1answer
53 views

Sanity Test on Oracle Upgrade Database

My oracle sever has been upgraded from Oracle 10g to 12c. DBA has created a box for that now my question is how developers will perform testing on that.. Can anyone provide me the sanity or unit ...
4
votes
1answer
173 views

Select multiple columns from subquery - Connect by

I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy: SELECT folder_name name, ...
0
votes
0answers
34 views

Oracle database in maintenance mode without patch

I could not enable maintenance mode in oracle database without patch Also I don't have patch(Since I don't have Support Identifier). @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE SP2-0310: unable to ...
0
votes
2answers
68 views

Unable to open database (Datafile corrupted)

I am unable to open the database, but i am able to mount it. When i try to open the database, getting following error ORA-03113 - end-of-file on communication channel I am using Oracle 12C. On ...
0
votes
2answers
33 views

Function Variable Number of Arguments plsql

I am looking to do a function with non fixed number of arguments like decode function. The syntax for the DECODE function in Oracle/PLSQL is: DECODE( expression , search , result [, search , ...
0
votes
2answers
23 views

Is there a way in PostgreSQL to set an default owner for all future create table statements in a session

We have a program that transfers cobol data (index files) to a database. Until now we transferred the data to an oracle database. In the beginning of the program I logged in with user sys and ...
0
votes
0answers
19 views

network Adapter could not establish the connection in netbeans with oracle11g in linux

I am using fedora 23 64 bit. My system is up to date. I have installed oracle 11g xe. I have downloaded the jdbc driver 'ojdbc6" , now i am trying to connect netbeans with the oracle database but it ...
0
votes
2answers
46 views

Why is a Full Database Backup (as opposed to a level 0 backup) required in Oracle after switching to ARCHIVELOG mode?

The Oracle 12c 2 Day DBA manual states: While whole database backups can be an important element in your overall backup strategy, they are also a required step in some situations, such as when ...
1
vote
1answer
52 views

no more data read from socket error with triggers

I have created one trigger that calls one procedure, using oracle sql developper. Here is the code for trigger : CREATE OR REPLACE TRIGGER noteTrigger BEFORE INSERT OR UPDATE OF valoare OR ...
0
votes
0answers
45 views

Query tables in a tablespace to find the physical location of extents?

Is there any way to query DBA_EXTENTS to find the actual, physical location on disk of each extent in a table?
0
votes
1answer
49 views

Very fast individual queries but when joined super slow

I've got query 1: select department_id from USER_ACCESS where USER_ACCESS.USER_ID = 13170 AND USER_ACCESS.MANAGEMENT = 1 Takes about 0.475 seconds, which is not a lot for the data set I m ...
0
votes
0answers
42 views

Drop Datafile and Reuse the Tablespace - Oracle

Oracle 12C is my database. I was moving the datafile from one directory to another directory by making the tablespace Offline. ALTER TABLESPACE TABLESPACE_NAME OFFLINE NORMAL; But accidently, while ...
0
votes
0answers
32 views

What does this do: sys.dbms_logrep_imp.instantiate_schema

When running impdp .... sqlfile=some_file.sql I get a sql file containing this command sys.dbms_logrep_imp.instantiate_schema(...) what does this do? Google seems unable to find reference to ...
1
vote
1answer
38 views

Importing Oracle12c database

i am using Oracle 12c in debian Jessie i want to import an old database exported with expdp on oracle 12c when i try to launch the commande 'impdp' i got those errors : 'ORA-39002: invalid operation' ...
0
votes
0answers
31 views

Convert table values into column header without PIVOT [number of column values is dynamic] - Oracle 10g [duplicate]

I have a table in the following format ID NUM P_VAL Q_VAL ---------------------------------------- 445120000 00 101 25 445120000 10 103 25 445120002 00 ...
0
votes
1answer
27 views

Expand a range columns into a single column

I'm thinking this is an easy query but I don't know how to start. I have a database table called Booklet with columns id, controlNoFrom and controlNoTo. Sample data looked like this: id | ...
0
votes
0answers
34 views

Oracle Netca is configuring IP instead of hostname in listener

I have a standardized Windows 2012 image that I'm running a build package on. On multiple servers it uses hostname in the listener file, but on one test machine it defaults to IP. Is there a reason ...
0
votes
0answers
41 views

Error ORA-12547: TNS:lost contact

I installed Oracle12c Database in my server i run SQLPLUS with the command 'sqlplus /nolog' and when i type 'conn sys /as sysdba' i got this error ORA-12547: TNS:lost contact Can you help me ...
-2
votes
2answers
86 views

Oracle SQL Query

Query the two cities in STATION (Table) with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or ...
0
votes
1answer
67 views

ORA-01122: database file 5 failed verification check

Oracle version: 11g r2 OS: win server 2008 SQL> alter database open; alter database open * ERROR at line 1: ORA-01122: database file 5 failed verification check ORA-01110: ...
0
votes
1answer
20 views

Is it possible to perform skip N rows and fetch M rows without full scan

Let's say I've got this query: SELECT EMP_ID,LAST_NAME FROM EMPLOYEES WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME; Now if I want to show only the first ten rows between 50 and 60, ...
0
votes
2answers
69 views

Error during Oracle database 12c Release 1 installation

I am trying to install Oracle database 12c on Oracle Linux 7 version. But it is keep on failing with the below error. [INS-30131] Initial setup required for the execution of installer validations ...
0
votes
0answers
34 views

How do I create a oracle database patch list overview?

I need to create an list for Oracle database patch versions for a recurring audit on patch states. It will check whether the current patch state of Oracle database meets the required patch state of a ...
0
votes
1answer
38 views

Purging audit records - Oracle

At the end of each financial year, audit team asks for audit records for samples dates in a year as per their methodology and ideally we are supposed to provide them. (Say, give them list of schemas ...
0
votes
1answer
28 views

Record of schema related changes - Oracle

Is it possible to get the all changes that is made to schema as in what privileges was first assigned to the schema on creation, creation date, if later modified with different privilege what was that ...
0
votes
1answer
20 views

Redo/Noredo in Oracle

I have an Oracle 11.2.0.3 about 700GB in size running in noarchivelog mode in a pretty slow storage setup. It barely gives IOPS and there is resource crunch in terms of hardware resources. The ...
0
votes
1answer
67 views

Why did a query change its execution plan and how to anticipate the change?

We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple ...
8
votes
3answers
258 views

Unexpected CASE evaluation logic

I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL ...
0
votes
0answers
38 views

SSDT - Unresolved Reference to temporary table in linked server

Here's the scenario. I am extracting data from a table in an Oracle database via a linked server as part of an ETL process. I am developing the solution in Visual Studio 2013 with SQL Server Data ...
0
votes
1answer
22 views

Oracle not able to add Default value in PL/SQL

I trying to create table in PL/SQL, the problem is the at DEFAULT ' '. CREATE OR REPLACE PROCEDURE CREATE_TABLE AS strTABLE_NAME varchar2(32); BEGIN strTABLE_NAME := 'Inventory'; execute ...
0
votes
3answers
46 views

Oracle: Not able to get value from All_Synonyms in PL/SQL

Dabatase: Oracle User: Not dba In my oracle database, im able to do the following: SELECT * FROM ALL_SYNONYMS But when i do it in pl/sql, it is able to compile and execute, but the dbms.putline ...
0
votes
1answer
50 views

Why does Oracle change my date fields?

I have an Oracle 11g XE set up in Moscow, Russia, and therefore on MSK time zone. When a client in the same timezone stores a DATE variable from Java with, say time set to 12:00, the time is changed ...
0
votes
1answer
50 views

Cannot TNSPING from VirtualBox Client to Oracle Database On Host

I am new to Oracle, attempting to learn about connecting remote clients to an Oracle 12c Database server instance. SUMMARY: I cannot get TNSPING to work from a client machine to a known good database ...
2
votes
1answer
39 views

Oracle Patch Level Differences (Oracle Enterprise Manager and DBA_REGISTRY_HISTORY)

There is a difference in the patch level that I am seeing on my Oracle database. On the Oracle Enterprise Manager, it shows that it is patched up to .7 but on DBA_REGISTRY_HISTORY it does not show any ...
0
votes
1answer
53 views

Customer just bought an Oracle license - am I allowed to install Oracle 11.2.0.4 without support contract?

I understand that when I buy an Oracle license, I can choose which version I install, these days either 11gR2 or 12c. But which patch level am I eligible to install? Is it 11.2.0.1 which was released ...
0
votes
1answer
50 views

PL/SQL Can't grant privileges while logged as system

I can't grant the execute privilege on DBMS_LOB (I need it to operate on files), even when I'm logged as system. What can I do? Granting DBA ROLE doesn't help at all. Error starting at line 7 in ...
0
votes
1answer
21 views

ORA-24033 : No recipient for message has been detected

I am using Oracle E-business Suite R12.1, 11G database version. Error I encountered when trying to create or modify a user or responsibility: ORA-24033 : no recipient for message has been ...
0
votes
1answer
68 views

Error launching sqlplus

I try to run sqlplus on my server with the command sqlplus /as sysdba and I got this error sqlplus: error while loading shared libraries: ...
0
votes
1answer
84 views

localtimestamp or curent_timestamp for default value

In Oracle-12c I have some columns that I am converting from timestamp with time zone to timestamp with local time zone. Some of those same columns also currently have a default value of ...