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

2
votes
2answers
866 views

How to disable flashback query logging for a specific table (Oracle)?

We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it ...
0
votes
1answer
10 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 ...
2
votes
0answers
277 views

Oracle faster overlap check [closed]

We've a table with two columns beginrange and endrange, which contain hex values. To this table no overlapping ranges should be inputted. We had implemented the check by having a composite functional ...
1
vote
1answer
103 views

Is there any way to use “like” operator during oracle table partitioning

I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me. In ...
0
votes
0answers
20 views

Third Party tools to monitor and administer Oracle database [on hold]

What will be the cost and features of any Third Party tools to monitor Oracle Database other than Oracle Grid Control/Cloud. Any open source also available?
1
vote
1answer
26 views

Grant privilege to see column names in null constraint violations when inserting in views

My database has two schemas, one with the actual tables and one that is used by an application. The application schema doesn't have any privileges on the actual tables. Instead there is a view for ...
3
votes
1answer
1k views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
-1
votes
0answers
16 views

Oracle: convert upper on the fly

select translate('hal','abcdefghijklmnopqrstuvwxyz','bcdefghijklmnopqrstuvwxyza') from dual; This return me correctly 'ibm' But i want IBM,how to translate on uppercase on the fly without retyping ...
1
vote
1answer
4k views

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

I am not a professional, sorry. But perhaps you can help me anyway ... I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus ...
0
votes
0answers
19 views

Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python. ...
1
vote
2answers
112 views

Specify NLS-Settings during Oracle installation under Windows

Is there an easy way to specify NLS parameters for the database during the installation? We want to install a new database (environment: Windows). For testing purpose we need NLS settings for ...
3
votes
1answer
27 views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
0
votes
0answers
22 views

SQL Loader Pass Dynamic Value

I'm newer to Oracle so I hope what I am trying to explain makes sense. I am required to used SQL Loader to batch load CSV files. There are about 20 organizations uploading around 15 CSV files each. ...
1
vote
1answer
24 views

Oracle 10 xe; backing up oradata folder enough to recreate a duplicate later?

Let us say, I backup of the the oradata folder. Is that enough to recreate the exact same database? E.g., I reinstall oracle xe 10 on the same pc, using the same installer, into the same folder, and ...
1
vote
1answer
45 views

Oracle: Recover source code/table structures from “utterly wiped out” development database

Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain ...
4
votes
2answers
8k views

Estimate a Database Size

I have to do some project, and need to estimate the DB size. I'm gonna to use Oracle in the project, because will have a lot of transactions and size. But in the "presentation" I need to specify, in 5 ...
0
votes
3answers
170 views

Upgrading Oracle from 11.1.0.6 to 11.2.0.2 on Linux Red Hat

I am trying to upgrade the Oracle db from 11.1.0.6 to 11.2.0.4 in Red Hat Linux 5.3. The current database is installed on location. I checked the path using command cat /etc/oratab ...
0
votes
1answer
25 views

A question about oracle upgrade from 12.0.0.1 to 12.0.0.2 using dbua

I found this guide for upgrade. After the upgrade, can I delete safely the old DB product dir /var/oracle/app/oracle/product/12.1.0 ?
0
votes
1answer
126 views

Connect by prior - multiple fields and query efficiency

I have 2 tables one of which defines the hierarchy for the other equipment id desc 1 item1 2 item2 3 item3 4 item4 5 item5 6 item6 7 item7 8 item8 ...
0
votes
1answer
1k views

Cannot remotely connect to oracle express

I have installed Oracle Express in a oracle-6.5-x64 virtual machine (let's called it host). The installation went fine, I am able to connect and run commands using SQLPlus from the host. Now I'm ...
0
votes
1answer
58 views

How do I find gaps between ranges of numbers?

Consider the following table: T_ID | T_START | T_END -----+---------+------ 1 | 0.25 | 0.5 2 | 0.8 | 1 3 | 0.4 | 0.6 4 | 0.2 | 0.3 5 | 0.7 | 0.8 T_ID is unique. ...
0
votes
0answers
9 views

PL/SQL error: PLS-00103: Encountered the symbol “EXCEPTION” [migrated]

I'm making a PL/SQL error handling template. I'm getting this error: 'PLS-00103: Encountered the symbol "EXCEPTION"' and I can't figure out what's wrong. We're using an Oracle database. DECLARE ...
0
votes
0answers
31 views

What is the appropriate GCC version I should use for building my External Procedure?

When working with Oracle External procedures, what is the GCC version I should use to build my procedure? Does Oracle get shipped with a GCC version and libraries that it keeps in one of its own ...
2
votes
1answer
38 views

Deferrable Foreign Key Constraints

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE? This answer on StackOverflow mentions the performance impact that comes with no ...
16
votes
5answers
72k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
0
votes
1answer
41 views

PL/SQL recursive lookup with cursor

I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin ...
7
votes
0answers
194 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
243 views

Set Oracle Home (install Oracle Home) for Toad 12.6

I had Toad 11.5 and I'm trying to install Toad 12.6, to connect to Oracle 11g on a 64-bit Windows 7 machine. No matter what I try, when I start Toad 12.6, I get the error: "No valid Oracle clients ...
0
votes
1answer
37 views
+50

Installing Oracle Forms and Reports on a 64bit machine

I'm trying to install Oracle Forms and Reports on a new server 64 bit (Ubuntu Trusty Tahr). (I'm only actually interested in the report server component). I can't find a version that I can install ...
0
votes
1answer
199 views

Generic or better yet custom message of the day in Oracle sql plus

I'm trying to create a message of the day type notification when users login to sqlplus. Currently I use the login.sql to add a dbms_application_info and set a module and action for the message. ...
0
votes
2answers
41 views

Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication. MacBook:~ mangeles$ ssh oracle@10.10.1.64 oracle@10.10.1.64's password: Last ...
18
votes
8answers
14k views

How do you version your Oracle database changes?

I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files ...
-2
votes
0answers
30 views

Creating Mview in Oracle is throwing error

I keep on getting error when I try to create a fast refresh materialized view. Not sure what is the cause of getting the error. ---PREBUILT TABLE--- Create table k_dim_mvw( id NUMBER(10), time ...
0
votes
2answers
332 views

Oracle server 11g stops working with 12541 error until IP address changes [on hold]

Running Oracle 11g 11.2.0.1.0 Server mysteriously stopped accepting connections to its databases. In other words, you run sqlplus and try connecting as any account and get an error that the listener ...
-3
votes
0answers
21 views

best software to run Oracle code [on hold]

what kind of software is best to download on PC with Windows OS to practice in writing and running code in Oracle (let's say at school or for preparing for certification)?
2
votes
1answer
154 views

Oracle user locks (dbms_lock): guaranteed order of requests granted?

I was wondering if Oracle guarantees that the sequence of dbms_lock.request granted is the same as the sequence in which they were requested. In my tests it always was the case, but I couldn't find ...
1
vote
2answers
30 views

Repeating rows of date intervals based on another table in Oracle

I have a table of maintenance plans with date intervals between each required maintenance period. Fiddle: http://sqlfiddle.com/#!4/af742 create table maint_sched (maint_id number, start_date date, ...
0
votes
0answers
13 views

How to create a procedure by using the existing procedure in oracle 11g? [closed]

Can anyone please tell me? How to create a procedure by using the existing procedure? ( SQL DEVELOPER ) Thanks! Arunkumar.
3
votes
2answers
459 views

Security for Oracle linked server from SQL Server

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data ...
2
votes
1answer
287 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
0
votes
1answer
2k 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
113 views

Mutiple updates with FORALL

I have 2 tables, one is a fact table (100000000 records), and the other is a catalog (2000), I need to update the FK at the fact table, can I use for all in this case?. I wrote this but it doesn't ...
2
votes
1answer
656 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: ...
1
vote
1answer
346 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
2
votes
1answer
46 views

Oracle 12c upgrade - backwards compatible with 11g?

Are there any application code changes required for SQL or PL/SQL to continue to work in 12c, or is it backwards compatible with 11g? Are there any queries that would work in 11g that break in 12g? ...
1
vote
2answers
14k views

How to move csv file into oracle database using sql developer?

How to move csv file into oracle database without creating table firstly using sql developer rather than sql loader?
0
votes
1answer
49 views

Arithmetic operations on a table

can anyone give me some advice on the following situation: I have two basic tables: Then a view created by the two: select product.IDPRODUCT, sum(quantity) as totalOut, PRODUCTSTORAGE.DATAINPUT, ...
1
vote
1answer
258 views

One Materialized View in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
2
votes
1answer
836 views

Which packages are required to install Oracle 11.2.0.3 on Solaris 11?

Where can I find information on which packages are required to install Oracle 11.2.0.3 on Solaris 11? The documentation only mentions Solaris 10. I tracked down the packages required for Solaris 10, ...
0
votes
0answers
208 views

Oracle - Slow queries without prepending schema name or providing hint

I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table. select max(col) from table; - takes 300 seconds (index full scan) select max(col) from schema.table; - ...