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

10
votes
5answers
13k views

Cannot drop nonexistent constraint and cannot create it either

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands: ...
10
votes
3answers
6k views

Where can I find example databases for MySQL, SQL Server and Oracle?

I want to experiment with the 3 large database management systems. For this purpose it would be cool to have the same database (the same tables, etc.) for each of those. Where can I find sql scripts ...
10
votes
2answers
547 views

large data movement

I would like to move billions of rows from schema1.table1 to new schema2.table2 where table2 is a refactored one from table1. Hence their table structure is different. both table1 and table2 are ...
10
votes
2answers
2k views

Security for Application Developers doing PL/SQL work in Oracle

How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
10
votes
1answer
537 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
9
votes
3answers
2k views

Why do I have to select from the dual table?

This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL). select 'abc' abc, 1 ...
9
votes
2answers
2k views

100 TeraBytes Capacity Database - Resources and Time Estimates

I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment: Storage Capacity ~ 100TB Tables ~ ...
9
votes
2answers
2k views

Does Oracle PL/SQL have a standard ASSERT routine?

I'd like to use an ASSERT routine functionally similar to the one found in other languages, i.e. a construct (be it a procedure, syntax...) ASSERT( <condition>, <msg>) such that when ...
9
votes
1answer
958 views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
9
votes
3answers
13k views

Is there any difference between Rem and — comments for SQL*Plus?

On SQL*Plus prompt, both Rem and -- qualify as comment indicators: Rem this is a comment -- this is also a comment create table emp ( id number primary key, name cvarchar2(40)); Is there any ...
9
votes
2answers
2k views

Oracle Shutdown Method

Shutting down a database before doing an upgrade or a patch can be done several ways. shutdown immediate; or shutdown abort; startup restrict; shutdown immediate; or shutdown abort; startup ...
9
votes
1answer
2k views

Why am I NOT getting a mutating table error in trigger?

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation: A mutating table is a table that is being modified ...
9
votes
3answers
266 views

Can I rely on functions being executed first in SQL

Please consider the following script: create or replace function f(p_limit in integer) return integer as begin set_global_context ('limit', p_limit); return p_limit; end; / create view v as ...
9
votes
1answer
38k views

Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus: c:\>sqlplus ace SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...
9
votes
1answer
8k views

Limit the degree of parallelism (DOP) available to any query

On Oracle Exadata (11gR2), we have a relatively beefy database. cpu_count is 24 parallel_server_instances is 2 parallel_threads_per_cpu is 2 We noted, through observation in Oracle Enterprise ...
9
votes
2answers
4k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
9
votes
2answers
3k views

Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?

I want to access data in an Oracle 11g database from SQL-Server 2008 I set up a linked server and when I execute select * from [Link_server_name]..Oracle_schema.Oracle_table and Oracle_table ...
9
votes
2answers
612 views

Why is a “non-single-group group function” allowed in a subselect but not on it's own?

Why does the first query not fail with the same error as the second: with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual) select sum(units) from (select product_id, ...
9
votes
1answer
373 views

SQL substitution feature in Oracle 10g

Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle ...
8
votes
3answers
1k views

Should date formats be specified in SQL statements?

I see code from developers using implicit date conversion. I would like a definitive answer to why they should not do this. SELECT * from dba_objects WHERE Created >= '06-MAR-2012';
8
votes
2answers
7k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
8
votes
5answers
4k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
8
votes
3answers
3k views

I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?

I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the ...
8
votes
4answers
11k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
8
votes
4answers
11k views

Feature comparison: Oracle Database and Microsoft SQL Server [closed]

Many companies have a heavy influence on market and technologies around them, and you often compare the products before you choose, e. g. I liked Oracle APEX, its flexibility and features, and we're ...
8
votes
2answers
13k views

How to catch and handle only specific Oracle exceptions?

From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955. How can I rewrite the following to catch only the error ORA-00955? begin EXECUTE IMMEDIATE ...
8
votes
2answers
2k views

What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?

The following two queries seem to be equivalent when executed in sql*plus: 1. select * from user_tables; 2. select * from user_tables / Is there any difference between both versions?
8
votes
2answers
115 views

Read ancient Oracle database into current Oracle version

An old computer with Oracle 8i is dead. We have the daily backup of the database (C:\oradata). We are unable to install this old Oracle version anywhere because we don't have the install CDs for it. ...
8
votes
3answers
2k views

Database backups in Oracle - Export the database or use other tools?

One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like exp / expdp as backup. Would this be a good practice ? What ...
8
votes
1answer
701 views

Column level security

I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but ...
8
votes
3answers
2k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
8
votes
4answers
296 views

What would I need to do to make sure my DB policies have to pass a security audit?

I have an audit coming up, and I was wondering what physical, electronic, and logical access controls an auditor would look for when auditing a database for an ERP system. I'm really new to this ...
8
votes
3answers
3k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
8
votes
2answers
4k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
8
votes
1answer
6k views

How to connect to another db using stored procedures?

I need to get some data from an external db and make some calculations with it in another db, is it possible to connect to an external db from a stored procedure? Thanks guys. PS. Im using Oracle ...
8
votes
1answer
8k views

Duplicate an Oracle database user

We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the ...
8
votes
4answers
81k views

How do I generate a tnsnames.ora file to connect to an Oracle Database?

I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find. Do I need to generate the tnsnames.ora file myself? If so, where do ...
8
votes
1answer
2k views

Is there an execution difference between a JOIN condition and a WHERE condition?

Is there a performance difference between these two example queries? Query 1: select count(*) from table1 a join table2 b on b.key_col=a.key_col where b.tag = 'Y' Query 2; select ...
8
votes
3answers
1k views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
8
votes
2answers
853 views

LNNVL Justification

LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of ...
8
votes
1answer
6k views

What specifically does OracleBulkCopy do, and how can I optimize its performance?

To summarize the specifics: We need to stage approximately 5 million rows into a vendor (Oracle) database. Everything goes great for batches of 500k rows using OracleBulkCopy (ODP.NET), but when we ...
8
votes
2answers
2k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into ...
8
votes
1answer
523 views

How can a datapump export have its resource usage limited on linux?

Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
8
votes
2answers
358 views

How to modify an update in Oracle so it performs faster?

I have this query: UPDATE ( SELECT h.valid_through_dt, h.LAST_UPDATE_TMSTMP FROM ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h WHERE h.FUND_ID = d.FUND_ID AND ...
7
votes
5answers
16k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
7
votes
5answers
7k views

Auto-Commit in SQL Server and Oracle

When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards. I ...
7
votes
2answers
14k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
7
votes
2answers
5k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
7
votes
3answers
19k views

How do I find my current SCN?

Given any version of Oracle: How do I find my current SCN? What is the maximum possible SCN?
7
votes
2answers
1k views

If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?

I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database: create or replace function tail_f return varchar2_tab pipelined as n number; ...