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

3
votes
1answer
126 views

I want to change oracle “homedir” how to do?

A good link as answer is ok. I want to change "homedir" or "basedir" of oracle db from /oracle to /var/oracle. How to do? I have set the oracle unix variables($ORACLE_HOME,$ORACLE_BASE) with the new ...
0
votes
1answer
48 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
1answer
37 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 ...
1
vote
1answer
29 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
2answers
26 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 ...
0
votes
2answers
3k views

Oracle schema import is not importing all the tables present in the schema dump file

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not ...
0
votes
1answer
142 views

Converting sql into mongodb nosql

I am trying to convert oracle sql into its mongodb equivalent version. create table my_hll as select mod(ora_hash(n), 1024) bucket, max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val from previous_data ...
8
votes
2answers
233 views
+250

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
40 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?
4
votes
2answers
3k views

Oracle ROWID, can we assume it is sequential in a insert-only scenario?

How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why? I had a situation where I had to verify whether new records were added to table which has no ...
0
votes
0answers
30 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 ...
2
votes
1answer
957 views

ORA-16000 when trying to perform select on read only Oracle database

My application's SQL encounters an ORA-16000 error when trying to access a read only Oracle database. ORA-16000: database open for read-only access ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: ...
0
votes
0answers
17 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 ...
2
votes
1answer
1k views

Finding why an Oracle listener got automatically restarted

I got the following message in the <listnername>.log file. I want to find out the reason why it got automatically shutdown and restarted again with different PID. 03-MAR-2013 03:00:57 * ...
2
votes
1answer
250 views

Estimating tablespace size

I'm trying to estimate the size of a tablespace using these steps: Space Estimations for Schema Objects and I was little confused about some of the steps. In step 3 for calculating the rowsize I ...
0
votes
0answers
17 views

RMAN Duplicate internal operation [on hold]

How RMAN Duplicate works internally?Please explain in detail how RMAN picks the latest backup and duplicate works
1
vote
1answer
3k views

Not receiving archivelogs from production to remote side

I have a scenario in which we have Designed data guard. Where we have 2 standby sides. One standby is located locally and 2nd standby is located on remote side. The distance between local and remote ...
1
vote
1answer
30 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
29 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 ...
13
votes
3answers
33k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
1
vote
1answer
207 views

Can we restore backup for standby from external hard drive with differnet drive labels

I am using windows server 2003 and Oracle database 10g R2 and i have taken the backup using RMAN of my primary database in Drive label E. The size of backup is 1.5TB. In my standby database i don't ...
0
votes
2answers
291 views

sqlldr running slow after indexes created

let me explain this.... I had running 1 session of sqlldr while I run indexes into table for 6 fields... now the indexes are completed but the sqlldr running very slow it load one file after each 30 ...
0
votes
1answer
24 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 | ...
-1
votes
0answers
45 views

Oracle session killed, but the table still not released [on hold]

Oracle session killed(using alter system kill session command), but the record is still not released(err : could not reserve a record), even after restarting DB server (node 1 and 2) and all ...
0
votes
0answers
27 views

Want To Learn About MongoDB [on hold]

I am doing a little bit of research on MongoDB related to the following key points: (i) Experiment And Results (In which applications, MongoDB is used and what are the results) (ii) Threat To ...
0
votes
1answer
13 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 ...
1
vote
2answers
173 views

Need help in incrementing the date in oracle

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this. select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' ...
0
votes
1answer
40 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
0answers
31 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
1answer
406 views

Oracle 10gR2 - fix for SSL version following Poodle attack

We use Oracle utl_http to send https requests from within our database (10gR2). As SSLv3.0 is now considered insecure (POODLE) we need to upgrade to TLS. How can we ensure TLS usage when sending ...
0
votes
1answer
29 views

Oracle RAC First Node not accessible [on hold]

I have a question about Oracle RAC. I have an Oracle RAC with 2 nodes (11.2.0.3). When second node is not in access, no problem and users can work with database. But when the first node, that is ...
0
votes
1answer
40 views
+50

Sybase 12 to behave like Oracle and SQL Server 2014 - non blocking transaction read

We utilize an ODBC connection so our code can access various databases - namely Oracle, Sybase 12 and SQL Server 2014. NOTE : We have a table called which we use for locks. This table only ever has ...
1
vote
1answer
700 views

Internal query on CDEF$ and CON$ very CPU bound

I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the ...
0
votes
1answer
2k views

Export, based on query, into INSERT text file

I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building ...
-1
votes
2answers
64 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
0answers
29 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 ...
0
votes
1answer
30 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
18 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, ...
1
vote
1answer
241 views

Tracing failed login attempts in Oracle

I have some users who are logging in with a wrong password and their accounts get locked. Now I need to find the source of the failed login attempt. The machine, username etc. Is it possible to find ...
0
votes
1answer
246 views

Check Oracle Service is Started from Batch Command

Uses: Oracle9i Standard Ed; I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem. After the System had rebooted, I have ...
0
votes
1answer
31 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
33 views

If I have an Oracle DB dump file, can I compress it in a backup using 3rd party backup app [closed]

While backing up an Oracle Database Dump file, can it be further compressed during a backup using Commvault? If so, at what rate %? Many thanks
0
votes
2answers
104 views

Pentaho Kettle - Unable to connect to virtual Oracle Database

I'm attempting to connect to a virtualized Oracle database but I am receiving the following error: Error connecting to database: (using class oracle.jdbc.driver.OracleDriver) Listener refused the ...
0
votes
1answer
54 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 ...
-1
votes
0answers
27 views

Currently running TOP Sqls [closed]

I am trying to make Top 10 Sql that currently running. I have 2 queries which makes this but i could not understand the logic. I hope you guys can help me to make it. First SQL is ; select ...
1
vote
1answer
349 views

how to prevent an oracle block corruption from duplicating a database with a few nologging tables

I got a few data corruptions in a test db when I performed a duplicate from a production db where a few tables have the nologging option. Oracle support suggested to turn on the force logging option ...
1
vote
1answer
766 views

ORA-01122: database file 1 failed verification check

I moved data file and database started fine but I did the following steps: SQL> shutdown SQL> startup nomount SQL> alter system set control_files='/var/oracle/oradata/control.dbf' ...
0
votes
1answer
74 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 ...
0
votes
0answers
27 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
24 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 ...