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

7
votes
1answer
157 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
3k 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 ...
7
votes
2answers
377 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
2answers
58 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
263 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
1answer
14k views

How can I Estimate Table Sizes within Schema (Oracle)

I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far: SELECT table_name, owner, last_analyzed FROM all_tables I'm fairly new to SQL so I have no idea how I ...
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
3k 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
4k 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
1answer
27k 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 ...
7
votes
3answers
107 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
537 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
1answer
436 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
952 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, ...
7
votes
1answer
353 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
6
votes
5answers
13k 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 = ...
6
votes
3answers
9k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
6
votes
5answers
6k 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 ...
6
votes
4answers
687 views

Does an IOT guarantee order in a select?

We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1). The priority does not matter ...
6
votes
2answers
4k views

Is Oracle DB licensing needed for a test environment?

I'm planning to setup an Oracle Standard Edition instance on a server in our test environment. Do I need to purchase a license for this?
6
votes
3answers
10k views

Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data: for i in (select * from STUDENTS) ...
6
votes
2answers
2k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
6
votes
3answers
1k views

How to document Oracle database?

I'm new to Oracle databases. I have used to SQL Server and the description fields (MS_Description) for tables and columns for documentation purposes. Is there an equivalent for Oracle? What is the ...
6
votes
2answers
7k views

SQL Server Express vs. Oracle Express Edition vs DB2 Express-C

I have been trying to compare the features of the SQL Server Express, Oracle XE and DB2 Express-C, but find it difficult to compile all the differences into one place. Especially the current ...
6
votes
3answers
15k 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?
6
votes
3answers
472 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
6
votes
1answer
5k views

When a record is locked in Oracle, can we know which record is locked?

When a record is locked ,can we know which one is locked? How can I get the record rowid or something else info? I can get some info by this sql SELECT ...
6
votes
2answers
3k views

How do I query to pull all data in row based MIN and MAX service date?

I'm very new to Oracle SQL especially in the Pentaho report builder. It lets you do some things and block others. PL/SQL is not available so I must do this in a query. I need to be able to pull ...
6
votes
4answers
578 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
6
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
6
votes
1answer
824 views

Materialized views in oracle

How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
6
votes
3answers
1k views

Clustered index in SQL Server vs index organized tables in Oracle

I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how ...
6
votes
2answers
759 views

Does it make sense to consolidate Oracle databases?

I am more experienced with SQL Server and Sybase than Oracle, and understand those products well. I've been asked to look for ways to reduce the server estate running Oracle. I understand that an ...
6
votes
2answers
2k views

Oracle 10g - How to prevent one user from hogging all resources?

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
6
votes
1answer
273 views

What is “SAP on Oracle”

OK, total newb question. What is "SAP on Oracle", exactly. This link is talking about how SAP can run on Oracle (I think). I'm not understanding how this is working. My understanding: SAP is a ...
6
votes
2answers
337 views

In Oracle, can we restore a database onto the same instance with a different name?

In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
6
votes
1answer
2k views

Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces?

Quoting from Oracle documentation about logical storage structures: A database must have the SYSTEM and SYSAUX tablespaces: Why is it compulsory to have these tablespaces?
6
votes
1answer
1k views

What's the difference between a v$ view and its dba_ equivalent?

What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.
6
votes
2answers
465 views

Can Oracle be installed on Windows without being an administrator?

Can the oracle software be installed on windows without having administrator privileges?
6
votes
2answers
25k views

How to troubleshoot enq: TX - row lock contention?

I have the following situation. I have RAC. On both nodes there are the locks. On the First Node SID EVENT USERNAME BLOCKING_SESSION ROW_WAIT_OBJ# OBJECT_NAME ...
6
votes
2answers
32k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
6
votes
2answers
829 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 ...
6
votes
1answer
89 views

Why are some aggregates treated differently in the RHS of a rule in the model clause?

With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent: select ...
6
votes
2answers
14k views

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
6
votes
2answers
324 views

Is it worth partitioning a Table by single value in Oracle?

Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in ...
6
votes
2answers
9k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
6
votes
3answers
2k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
6
votes
1answer
14k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
6
votes
1answer
5k views

Introduce partitions into an existing non-partitioned table

What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this? Especially in cases where there are many ...
6
votes
1answer
579 views

sqlplus consuming 99% CPU

Oracle's installed on one server, I'm running sqlplus on a different (application) server. I've recently encountered a problem getting sqlplus to do anything; it simply spins, consuming 99% CPU even ...