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

1
vote
1answer
37 views

Getting ORA-01017: invalid username/password in non-CDB database

I have installed Oracle 12c (software only in Linux) and created a non-CDB database (single tenant) database. Following is my listener.ora. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = ...
1
vote
2answers
64 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
0
votes
1answer
38 views

Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB?

I'm trying to config user to run logMiner from scheduled job periodly. my user called admin I've grant the admin user the following: grant select on v_$database to admin grant execute on ...
0
votes
2answers
87 views

Query against history table not using index (Oracle 12c)

I have a history table which has an 'ID' and 'TIMESTAMP' column as such CREATE TABLE hist ( HIST_ID INTEGER, HIST_TIMESTAMP TIMESTAMP, ID INTEGER, -- this is the id of the table that is being ...
1
vote
0answers
33 views

Oracle Monitoring RMAN: report need backup

Hello Oracle Database Admins! In the last few months I came across that it would be a good idea to monitor rman backups and check if any file needs backup. RMAN> report need backup; --> Report ...
0
votes
1answer
30 views

Return output of Oracle query

How to change below query to see results of my_sql (now I see only query, not it results) in SQL Developer? DECLARE my_sql VARCHAR(1000); BEGIN FOR t IN (SELECT t.table_name, t.owner FROM ...
2
votes
3answers
69 views

using tnsnames.ora and sqlplus client without installing oracle 11g server on linux client [closed]

I want to create a connection to remote Oracle server using SQLplus client and tnsnames.ora without installing Oracle 11g server on the Linux client. I used debian and bash script to generate reports ...
0
votes
1answer
23 views

Procedures In Oracle [closed]

--Procedures Exercise: create or replace procedure Display is cursor ABC is select empno, ename, sal from emp where deptno=10; emp_rec ABC%rowtype; Begin Open ABC; Loop fetch ABC into emp_rec; exit ...
1
vote
2answers
52 views

How could I get the value as Y or N in one table if value is exists on another that meets certain condition?

I have to generate simple report from three tables named customer, product, and order. My report should have a column that shows whether the customer ordered that product or not. How could I get the Y ...
0
votes
1answer
39 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
3
votes
1answer
25 views

Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer. Greetings, I want understand what I need to correct in order for these tables to work ...
1
vote
2answers
55 views

ORA-01017 connecting to example schemas on Oracle VM appliance from Windows host

I'm a software developer and am working on getting an Oracle VM Appliance running for testing purposes (the Database App Development VM with Oracle 12c - ...
1
vote
1answer
55 views

Explain plan gives different results depending on schema

I have a query that joins a multi million row table to one containing hundreds of thousands of rows. Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both ...
0
votes
1answer
33 views

Shared memory error while connecting to Oracle

I get this error message each time I try to connect to my Oracle XE local database under Ubuntu 15.10 : user@user-X220:/u01/app/oracle/product/11.2.0/xe/network/admin$ sqlplus SQL*Plus: Release ...
0
votes
0answers
30 views

Changing database parameters after removing a node from RAC oracle

Oracle 11.2.0.3 on SunOS 5.1 i have a client who is running a 2 node RAC environment but has shutdown the instance on one of the nodes. I was wondering whether after doing this they should change the ...
0
votes
1answer
44 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
34 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
36 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
25 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 ...
1
vote
2answers
58 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
175 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
74 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
35 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
25 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
53 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
46 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
40 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
36 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
42 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
87 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
69 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
74 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
39 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 ...