Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

1
vote
1answer
355 views

Use cases for NoSQL databases: Time series

I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance). There're also some ...
3
votes
1answer
649 views

How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job. I have a remote Oracle 11g R2 database on unix and a local one on Windows. I read that you can install the oracle ...
3
votes
1answer
70 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 ...
2
votes
1answer
326 views

How can I connect to a network MySQL server using ODBC?

Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC. ...
1
vote
1answer
30 views

Sync data in SQL Server 2014 with Oracle database12c

I have installed Oracle database 12c enterprise on my laptop. How can I implement data synchronization between Oracle 12c and SQL Server 2014 that whenever an INSERT,UPDATE,DELETE or ... happend on ...
0
votes
1answer
279 views

Toad 9.7.2 for Oracle hangs on Windows 7

I am using Toad 9.7.2 on Windows 7 64 bit. It is generally working fine but there is one problem. When I click on certain tables in "Schema Browser" and then click on "Data" tab to see records in ...
0
votes
1answer
15 views

concurrent insertion in mutually exclusive tables in oracle

There are three tables: create table C(id_row integer primary key) create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row) references C(id_row)) ...
3
votes
2answers
23k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
0
votes
1answer
25 views

Run a script as a sheduled job using sql developer scheduler GUI

I have a script Alter Trigger TEST.LIVE_DELETE Disable; Alter Trigger TEST.LIVE_UPDATE Disable; ----------Running the comparison script---------------- var scanid number; SET SERVEROUTPUT ON ...
2
votes
3answers
69 views

Generate a list from columns that do not contain a zero for each row

I have a table TAB1 in an Oracle database: +----+----+----+----+----+----+ | ID | C1 | C2 | C3 | C4 | C5 | +----+----+----+----+----+----+ | 1 | 1 | 0 | 3 | 0 | 5 | | 2 | 11 | 22 | 0 | 44 | ...
0
votes
3answers
28 views

I want a select to give me id's that are not in a consecutively order

I have an Oracle db with a table like this: +----+-------+ | ID | INDEX | +----+-------+ | 1 | 0 | | 1 | 1 | | 2 | 4 | | 2 | 5 | | 2 | 6 | | 3 | 0 | | 3 | 1 | | 3 ...
1
vote
1answer
43 views

Merging adjacent date intervals into a single row

Here is the scenario: If the employee transaction is continuous display as-is. if the employee got any gap display as a new row. Below is the sample table Emid Fromdate Todate ----- -------- ...
1
vote
3answers
332 views

Oracle Compatible Parameter to set

Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?
1
vote
1answer
666 views

Expdp error UDE-22303

I am experiencing the following error, when I am trying to make an export to the database using expdmp utility: UDE-22303: operation generated ORACLE error 22303 OCI-22303: type ...
2
votes
1answer
52 views

SQL query to identify missing hinted indexes

We have a fairly large number of queries using index hints in our 11.2.0.4 database. I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were ...
0
votes
1answer
30 views

need to get data from Global Temp Table from db1 to db2 and show the data in Oracle form

I need to get data from Global Temp Table from oracle db1 to db2 and show the data in an Oracle form. In db1, a stored procedure is populating data in the GTT and showing it on Oracle forms as this ...
2
votes
2answers
562 views

Oracle Data Pump Import imports no records

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported. Processing object type ...
0
votes
2answers
277 views

Different user can not access table

I have sysdba user and with that login I created a number of tables in db. After that I created one more user by login with sysdba. But this second user can not have access of tables that has been ...
0
votes
1answer
30 views

Oracle: How to create secondary index based on every word in a CLOB

I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX ...
0
votes
1answer
42 views

Oracle DB: Can one db file be shared between table space

Can one db file be shared between table space? I am deleting temp table space as below, Get all the files in table space: SELECT FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME = ...
0
votes
1answer
19 views

Loging queries and their error Text

I need to find out the statements that are unsuccessfully executed in sql, along with their error trace. As of now I created a trigger which is triggered on server error, which stores the sql text and ...
0
votes
1answer
6k views

Oracle installation requires old rpm versions, what to do?

I am installing Oracle 11g R2 on OEL6. I run the following command, for to check only prerequisites ./runInstaller -silent -responseFile /0/grid/response/crs_install_mk.rsp -executePrereqs It ...
2
votes
4answers
12k views

Oracle 12c create user

I have some problem with creating user in Enterprise Oracle Database System. A could'n create user using command like create user name identified by password; because of exception: ORA-65096: ...
1
vote
2answers
117 views

SQL*Plus save current COLUMN settings

In SQL*Plus, is there a way to save current COLUMN settings similar to STORE which saves SET commands in a script? COLUMN can be used to list the settings, but they are not in script format.
1
vote
3answers
73 views

Finding “islands” of holiday periods for employees

I'm looking for some tips to find a nice solution for my problem: Let's say I have a table, where days of vacation of employees are stored. e.g. CREATE TABLE test_dates ( id NUMBER ...
2
votes
3answers
653 views

How do I tell the actual IOPS used by Oracle?

This is hopefully a simple question. We have a large DB deployment that we need to scale up over the next year. For scaling we have been relying on our vendor, but recent developments have caused us ...
13
votes
5answers
66k views

How to see list of databases in Oracle?

Is there an equivalent to MySQL SHOW DATABASES statement? Is it possible to find databases in a cluster? i.e. databases present on the network on some other system? Could I analyze the files present ...
0
votes
2answers
39 views

Understanding RMAN Backupset

I feel a bit confused when I read about RMAN! what are the differences between: RMAN> backup as backupset RMAN> backup database; RMAN> backup database plus archivelog; RMAN> backup ...
0
votes
0answers
37 views

SPLIT PARTITION issue?

we had written program to ADD partition on every month. But, one of our customer forgotten to execute the program from Jan to July and now, he is facing performance issues (because corresponding table ...
0
votes
1answer
433 views

Spool in Shell script not working for me

#!/bin/sh echo "Started at" `date +%Y/%m/%d" "%H:%M:%S` sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF 2>&1 > /dev/null SET SERVEROUTPUT ON SET FEED OFF spool ech.txt create BEGIN ...
2
votes
1answer
33 views

Oracle Application Express Listener PDF Report

We need our reports to be available in PDF format on our Oracle 11g, APEX:4.2.6 Followed this tutorial and selected "Oracle APEX Listener". But the generated PDF report is empty. We are using EPG. ...
0
votes
1answer
14 views

Oracle Temp Table Space - Alias clean up ORA-01119

How to drop the temp table space including alias. SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP; SQL> ALTER TABLESPACE TEMPRM_TEMP ADD TEMPFILE ...
1
vote
1answer
534 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
35 views

Can inserting new rows make the RDB not use an index?

Say I have a table with 10000 records. I then delete them and create them again. This is done with "normal" sql, row by row (not some sort of bulk insert (BCP in sqlserver lingua)). Does the ...
0
votes
2answers
38 views

Database link from Local Oracle 11g to Oracle Database Cloud Service 11g

We are using Oracle database from Oracle Database Cloud Service, now we are not able to connect Cloud db from Local oracle db. Somehow we created link using below commnd, CREATE DATABASE LINK link_1 ...
3
votes
1answer
150 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
3
votes
3answers
3k views

Transfer data from DB2 to Oracle

I want to transfer data from an old DB2 system to a new Oracle database. How should I go about doing this?
0
votes
4answers
182 views

What is a DB Link good for?

Here is defined what a DBLink is but I really understand for what kind of situations you need it? What I could imagine is a situation where I have a limited (in size like sql-server express) ...
0
votes
1answer
31 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
8
votes
1answer
280 views

Why would function based indices I’ve created lower the cost but not show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was OK to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
0
votes
2answers
2k 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 ...
1
vote
3answers
4k views

Advice for ETL from Oracle to SQL Server?

I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL ...
0
votes
0answers
41 views

Upsert - Should I verify the row exists first, attempt an update, or try to insert first?

In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but ...
0
votes
2answers
58 views

Best Approach to Migrating Oracle to MySQL - Run on EC2 in AWS

I'm trying to migrate an Oracle schema as well as all of the data within the DB to a MySQL database. I want to then make an EC2 app on AWS to kick off whatever I've written for this. Are there any ...
0
votes
1answer
31 views

Oracle 11g - drop maxvalue partition, keep data

I have some tables with data like this: CREATE TABLE "PARTTEST4" ("year" number(4,0) NOT NULL ENABLE) PARTITION BY RANGE ("year") (PARTITION "P_OLD" VALUES LESS THAN (2010), PARTITION ...
0
votes
1answer
943 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 ...
0
votes
0answers
32 views

Fetching the data from the Oracle database is slow in Linux as compared to Windows

I am facing a performance issue in Linux while fetching data from the Oracle database. My scenario is as follows: 1) I have the backend server which is on UNIX with Oracle database running . The ...
4
votes
1answer
59 views

How to install olaptrain sample schema on oracle 12c?

I need to install the sample schema available from the Oracle website: http://download.oracle.com/otndocs/products/warehouse/olap/olaptrain_install.zip I am trying to run the commands as given ...
1
vote
1answer
162 views

What's the fastest way to migrate an Oracle database from Unix to Linux?

We have an Oracle database installed on AIX 7.1, and now we want to migrate our database instance to RedHat 6.2. The RedHat server has Oracle 11.2 installed already. What's the fastest way to ...
1
vote
1answer
521 views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...