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

7
votes
2answers
9k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
0
votes
1answer
493 views

Import big CSV on Oracle

I have big CSV files and placed it on Linux (Amazon AMI). I have installed instant client on Amazon Linux AMI and I can connect to my Oracle database using SQL Plus. How can I import all the CSVs to ...
0
votes
1answer
108 views

ORA-01113: file 1 needs media recovery — system01.dbf

The only way to recover is from media backup? I know I cannot "rebuild" system datafile, but if I don't have a backup - I know shame on me! - Is there anyway to recover database?
0
votes
1answer
53 views

What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment?

I see some files in there like an init.ora and opctrn11.h, but what is the purpose of this directory, if we use %ORACLE_HOME%/database to store the p/spfile instead?
1
vote
2answers
8k views

Create Oracle DB LINK to a database with no DB_DOMAIN

I'm trying to create a database link between this two DBs. My local DB is 11.2.0.3. SQL> select * from global_name; GLOBAL_NAME ...
1
vote
2answers
60 views

ORA-01180 when restoring the database

I get the following error message when trying to restore the database from backup using RMAN. rman target / RMAN> set dbid=130474031 RMAN> configure device type 'sbt_tape' clear; RMAN> ...
0
votes
1answer
27 views

ORA 01722: Invalid Number caused by different comparison

The following causes the above error: SELECT * FROM ( SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ...
0
votes
0answers
31 views

Deleting a Row in Table- Best way to also delete all rows it affects? [on hold]

I have created a very simple SQL script (see below) that deletes rows in my table if a column name is a certain value. DELETE FROM table_name WHERE some_column=some_value; However, this table ...
0
votes
1answer
114 views

Restoring oracle database

I come from SQL Server and I need to restore a oracle .bkp file in my database. Im using RMAN and I have the .bkp file in: F:\Oracle Back Ups\Back.bkp´ The command that I use in RMAN is: RMAN> ...
0
votes
0answers
19 views

Next date to be displayed [on hold]

train depature and arival arv dep day 13:40 pm day 1 20:45 pm 23:20 pm day 1 02:20 am day 2 I need the output as (give input as ...
1
vote
1answer
43 views

Get ORA-01157 when startup database

I created a datafile /tmp/encrypted_ts.dbf. When the machine started again, the datafile was gone. When I started it I got ORA-01157, the details are: ORA-01157: cannot identify/lock data file 54 - ...
0
votes
0answers
32 views

What is the best ways to filter data on the fly

We are implementing a new plan for ETL database, but we do not want to store the intermediate table on our local sql server (only want those records that we need). any body know the best ways to do ...
1
vote
4answers
5k 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
1answer
3k views

Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server

i am having a bit of a linked server problem . I created a sql server linked server and it's been working fine for last 2 years and suddenly not working . SQL Server Microsoft SQL Server 2005 - ...
2
votes
3answers
1k views

Why do shadow processes exist?

Oracle architecture has two types of process: server process or foreground process and background process, but: Some people use it to refer to foreground processes, others use it for background ...
2
votes
1answer
45 views

How to compare dates on minute level in plsql?

If I'm correct, then date comparison on seconds level is as simple as SELECT 1 FROM sample_table WHERE sysdate <= sample_table_date_field; but haven't found any simple or stable solution for ...
0
votes
1answer
6k views

Can't connect to a Oracle db instance via SQLDeveloper

When I set the connecting information on the GUI in SQLDeveloper I get the following error (everytime!) Status : Failure -Test failed: IO Error: The Network Adapter could not establish the ...
-1
votes
3answers
157 views

Incorrect query filter according to the requirement

My requirement wholly depends on the QUERY. I am filtering my gridview on the basis of three parameters. Project_name (Always required) Date (Not always) Vehicle_No (Not always) So, what my ...
-3
votes
1answer
69 views

Problem with procedure in plsql [on hold]

Create a stored procedure called UPD_JOBSAL to update the minimum and maximum salaries for a specific job ID in the JOBS table. The procedure should provide three parameters: the job ID, a new ...
1
vote
2answers
60 views

Join SQL Server table in OPENQUERY on multiple keys

I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys: SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A INNER JOIN ...
1
vote
2answers
2k views

Restoring an Oracle Backup Properly

I've been trying to do a proper restore of a database for weeks now and I'm convinced that I'm doing something simple wrong. I took a cold backup of the database before an update to an application ...
0
votes
1answer
51 views

Oracle over-indexed tables

I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more ...
0
votes
1answer
113 views

Strategies to import big data into oracle

Which strategies are available to import very larg amount of data from an external system which is delivering about 500 million small transactions every day? Oracle becomes very slow with high ...
0
votes
0answers
19 views

ORA-01745: invalid host/bind variable name using ORACLE [duplicate]

With my below debugged query, I got error as ORA-01745: invalid host/bind variable name Here is the query, SELECT DISTINCT sv.mkey, vehicle_no, TO_CHAR (date_in, 'dd-MM-yyyy') || ' & ...
5
votes
1answer
112 views

Recursive Join to get Spans

I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their ...
3
votes
1answer
749 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 ...
6
votes
1answer
80 views

Parallel option performing worse on cluster

I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not ...
0
votes
1answer
389 views

Oracle trigger error: table %s.%s is mutating, trigger/function may not see it

I am having some issues while trying to use triggers over tables where I need to get the reference of custom data type from a table. I can't seem to get it to work, here are the scripts I am using ...
0
votes
0answers
80 views

How to improve a sql query

If the query is taking 50 seconds needed to execute but taking 100% CPU and load increased on database. So will need to tune? If possible how to monitor to suggest tune team. I am monitoring an ...
0
votes
2answers
63 views

Can Oracle 11g Express Edition Work on Parallels

I am using a Mac and need to use Oracle 11g. I have Parallels installed with Windows 8.1 on it. Do I need to download anything else or just download Oracle 11g Express Edition and run it?
0
votes
2answers
183 views

Oracle database for learning purposes

I would like to know if I can download Oracle Database on my Mac OS X and use it for learning purposes? I know that Oracle Database Express Edition 11g is free, but is it free for OS X?
1
vote
1answer
208 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
0
votes
2answers
3k views

SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle

I am trying to Export Data from SQL Server to Oracle using the "Microsoft OLEDB Provider for Oracle". I am using "Export Data..." option from Task menu. I get to connect to the source database (SQL ...
1
vote
1answer
40 views

Does the function inside the Select clause evaluate the passed in columns before the where clause?

I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question. select ...
0
votes
1answer
3k views

SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

Inside of our SQL Server, we have created a linked server to an Oracle server. We ave mapped several users to the Oracle server and specified the credentials. On the Linked Server 'Security' ...
0
votes
1answer
32 views

Drop partition and rebuild global indexes “online”

I am using Oracle 11g. I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt. ...
2
votes
3answers
376 views

Archive Logs filling up Flash Recovery Area (FRA) space

I got hired to fill a position of DBA that I wasn't quite ready to fill, and while the training is getting me there, I am running close to a potential issue. My oracle 11g database's Flash Recovery ...
16
votes
13answers
5k views

Comfortable sqlplus interface?

I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available. What is a ...
1
vote
1answer
48 views

Conflict resolution in versioned tables

I'm trying to merge two workspaces that contain version-enabled tables. I can't figure out how to resolve conflicts, because I have a lot of rows with the same ID value. They differ only in time ...
1
vote
2answers
54 views

Updating duplicate records with different data

I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this ...
0
votes
1answer
575 views

Running Oracle Export Utility within a Shell script

I have wasted a lot of time on this. Any ideas of what I am doing wrong here? Script in bash #!/usr/bin/bash . . . /export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \ ...
0
votes
1answer
49 views

Connection error [closed]

I am facing a problem with connection of SQL Developer when trying to connect with Oracle 11g R2 The error is: Status : Failure -Test failed: IO Error: The Network Adapter could not establish ...
1
vote
1answer
547 views

SQLDeveloper.exe itself is getting access denied

Installed Oracle 11g and the 11g client. Everytime I start SQLDeveloper it prompts for the java.exe and again which files to associate. It must be trying to save this in registry (or maybe disk). ...
1
vote
1answer
204 views

Grant execute on specific type in Oracle

I have created a package and defined a type for collections as follows: create or replace package <schema>.<package> as ------------------------------------- type <type> is ...
0
votes
1answer
39 views

Backup and Restore with RMAN Oracle 12c

I'm trying using RMAN to create and restore backups. I'm getting an error stack trace when trying to restore to one of the backups that I have made. The error is RMAN-00571: ...
1
vote
2answers
51 views

Serialize :new in trigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new. What I am looking for ...
7
votes
1answer
2k views

Use LDAP for name look up with Oracle Instant Client

Partly as a result of having problems with conflicting versions or bitness with the full installation of Oracle client, my development team (of which I'm a member) has been moving towards using Oracle ...
0
votes
1answer
42 views

I don't want to have the error when I drop a table although the table doesn't exist [on hold]

I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist. I did this set echo on set serveroutput on alter session set ...
1
vote
2answers
11k views

Trigger to automatically add values to a table in Oracle

I am new to Oracle. I am looking for a trigger which helps to automatically insert values into a table when data is entered into another table in the same database. I have tried a lot and didn't ...
-1
votes
0answers
61 views

How to run an exe from an oracle trigger

I am new to Oracle came from SQL Background. I want to run an exe using trigger in oracle. As there is no direct way to do this, I have created a java procedure to run a trigger. my code is as ...