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

9
votes
2answers
2k 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
315 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
247 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
2answers
1k 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 ~ ...
8
votes
3answers
813 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
3answers
4k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
8
votes
5answers
2k 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
2k 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
2answers
3k 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
4answers
8k views

Feature comparison: Oracle Database and Microsoft SQL Server

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
105 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
1k 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
385 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
863 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
2answers
920 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 ...
8
votes
1answer
676 views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
8
votes
4answers
280 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
2k 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
3answers
8k 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 ...
8
votes
1answer
4k 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
2k 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
2answers
44k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
8
votes
1answer
555 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
2answers
535 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
3answers
13k views

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? ...
8
votes
2answers
1k 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
381 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
1answer
4k 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

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 ...
7
votes
2answers
1k 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 ...
7
votes
3answers
5k 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 ...
7
votes
2answers
762 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; ...
7
votes
2answers
1k 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?
7
votes
2answers
22k views

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from ...
7
votes
3answers
7k 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: ...
7
votes
3answers
3k views

Multiple Oracle Instances — is this a good practice?

One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're ...
7
votes
2answers
3k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
7
votes
1answer
147 views

Cardinality is so far off from reality

I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2. select count(*) from table1 where col1 = ...
7
votes
2answers
336 views

Is this a new common pattern in Oracle Where exists ( Select NULL FROM…)?

Years ago, it was common to write where exists (Select * from some_table where some_condition) Last year I noticed that many t-sql scripts switched to using the number 1 instead of the star where ...
7
votes
3answers
800 views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
7
votes
2answers
57 views

Oracle 10g: Is it possible to designate a undo point?

I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does ...
7
votes
2answers
247 views

Why is Oracle using the index here?

Name Null Type -------- -------- --------- ID NOT NULL NUMBER(4) GROUP_ID NUMBER(4) TEXT CLOB There is a btree index on group_id. Here's how many ...
7
votes
2answers
2k views

Limit redo for materialized view complete refresh or manual equivalent

A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore ...
7
votes
1answer
2k views

Oracle materialized view refresh schedule

Since I see that it is possible to specify an expression like sysdate + 1, here are my questions: Is it possible to specify the exact time of the refresh for materialized views in oracle? Is it ...
7
votes
1answer
3k views

Can I use OpenLDAP to authenticate users in Oracle?

I like to use OpenLDAP to authenticate my Oracle users. It should be possible to fetch at least the password from the directory. There appearently is something for the Oracle LDAP servers, but would ...
7
votes
3answers
91 views

Tablespace on ephemeral storage

For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral". This question is ...
7
votes
1answer
382 views

How can I rewrite for SQL Server a trigger that writing for Oracle?

How can I rewrite for SQL Server a trigger that was originally written for Oracle ? Here is my Oracle trigger code: CREATE OR REPLACE TRIGGER P000KUL_TEST BEFORE INSERT ON P000KUL REFERENCING NEW AS ...
7
votes
1answer
337 views

Map Oracle roles to Active Directory groups?

Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
7
votes
2answers
337 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
1answer
429 views

Generate XML using Oracle XML schema

I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA. I have created tables using the XMLTypes generated. I can create XML using XMLQuery and if I understand correctly, ...