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

5
votes
2answers
650 views

Oracle View Ignoring External WHERE Clause

Good day, This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but ...
5
votes
1answer
1k views

Oracle aggregate functions slows query down massively

I have a table with ~12M rows of data in it. Here is the table structure: SYSTEM_ID BATCH_ID MEASUREMENT_INDEX, PARAMETER_ONE, PARAMETER_TWO The primary key is made up of the first three columns. I ...
5
votes
1answer
3k views

How can I get the Oracle TNSListener to start after changing my host name?

When running lsnrctl status I can see that it is using the old host name, but I don't know where it is pulling that value from. My Oracle installation is 11.2.0 on Windows7 x64, if that makes any ...
5
votes
2answers
21k 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 ...
5
votes
1answer
70 views

What is Utility_DSN?

I'm not exactly sure when it happened, but I've started noticing that some of the hosts in my Enterprise Manager grid have a target named UTILITY_DSN. Does anyone know what this is? The one I noticed ...
5
votes
1answer
1k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
5
votes
2answers
4k views

Current isolation level in oracle

How to get the current (default) transaction isolation level in oracle?
5
votes
2answers
1k views

EXP-00085 warnings while doing full backup

During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings [...] . exporting post-schema procedural objects and actions EXP-00008: ORACLE error 6502 encountered ...
5
votes
3answers
964 views

Do roles have an owner in oracle?

Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles ...
5
votes
1answer
155 views

how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server

How can I compare two Oracle database servers which are on two different machines? We need to ensure that the new server is setup correctly. For example, one machine is running Oracle 10g on ...
5
votes
3answers
644 views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
5
votes
1answer
2k views

Oracle's Merge v. Select->Update/Insert

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value? I'm trying to optimize a database, but I haven't found any speed tests comparing the two. I found ...
5
votes
2answers
2k views

I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer

When trying to use the SQL Tuning Advisor from SQL Developer I get this error. Tuning advisor was working some days before.
5
votes
1answer
2k views

DBMS_Resource_Manager.Calibrate_IO Usage

DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter. The documentation says this value should be set to the... Approximate number of physical disks in ...
5
votes
3answers
884 views

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

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 ...
5
votes
1answer
385 views

Oracle Support Tips & Tricks

How can My Oracle Support (Metalink) be used effectively? What are your secret hints to this system? What saved you the most time?
5
votes
1answer
209 views

Sudden increase in log_file_sync waits

I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion. Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
5
votes
1answer
400 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
5
votes
2answers
210 views

Implementing uniqueness with Oracle

For example, say I have something like CREATE TABLE COMPANY(id int not null primary key, ...); CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null, CONSTRAINT ...
5
votes
1answer
810 views

Oracle Grid Control Notification for Data Guard failures

Grid Control 10.2.0.5. Does anyone know a way to configure a Grid Control notification rule for a failure of the SQL APPLY process on a logical standby database? I'd really like an email notification ...
5
votes
1answer
1k views

DROP PARTITION strategy using LIST-REF composite partitioning

We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
5
votes
1answer
825 views

Using sys.diana and sys.pidl to parse PL/SQL

It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
5
votes
1answer
51 views

Why does dropping a MVIEW trigger a full refresh?

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again. The MVIEW was created with a statement like this: create materialized view ...
5
votes
1answer
13k views

How to resolve ORA-01031: insufficient priviledges error on Windows installation?

I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database ...
5
votes
1answer
782 views

Global locking for multi-master Oracle GoldenGate replication

This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se. Problem I'm working on an intercontinental data replication ...
5
votes
3answers
1k views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
5
votes
1answer
833 views

Trying to copy a schema using impdp

I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool. I've setup a database link back to itself and the link works fine. Then I'm running this: impdp ...
5
votes
2answers
1k views

How to install Oracle 10g Express and 11g Enterprise on same machine

I have to continue maintaining an Oracle 8i database that receives information (about 20 Inserts and Updates a day) from a 9i database. I am upgrading the 9i database to 11g which does not support ...
5
votes
1answer
297 views

SQL Server Linked server query problem (group by)

I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well. I am trying to migrate to SQL Server 2012 via SQL Server ...
5
votes
4answers
13k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
5
votes
1answer
662 views

How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)?

I'm running Oracle ASM (11gR2) with normal redundancy on SATA disks (WD Raptors). This is on a physical standby database, if it matters. Occasionally, disk sectors develop read errors (ECC failures). ...
5
votes
1answer
413 views

How can I make the audit system create new audit files if they already exist?

I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the ...
5
votes
1answer
343 views

What must be in place to validate a XMLTYPE against a schema?

I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
5
votes
3answers
698 views

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
5
votes
1answer
678 views

What is the meaning of “select * from relational(owner.table)”?

We recently got some errors in our alert log of the form 301598: Tue Jul 16 03:58:29 2013 301599: ORA-01555 caused by SQL statement below 301600: SELECT * FROM ...
4
votes
3answers
598 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
4
votes
2answers
1k views

What storage engines work with Oracle?

http://en.wikipedia.org/wiki/Database_engine mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?
4
votes
4answers
634 views

How are foreign key indexes helpful?

How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
4
votes
4answers
3k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
4
votes
2answers
4k 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 ...
4
votes
4answers
3k views

Over use of Oracle With Clause?

I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each ...
4
votes
1answer
1k views

how to use dual foreign key in oracle 10g

I have created two tables create table ref1(id varchar2(3) primary key); create table ref2(id varchar2(3) primary key); Now I want to create a third table with a single column as foreign key for ...
4
votes
1answer
12k views

Why Oracle sessions are still alive after two weeks being Inactive

DB: Oracle 10g O/S: Windows Server 2003 64 bits I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe) select * from V$session where UPPER(program) LIKE '%W3%' ...
4
votes
4answers
946 views

Best method to give two Oracle databases on the same server access to common data

With a production and a test database on the same server, how should access be provided to some common quarterly update tables for both databases? It is possible to put these tables into a third ...
4
votes
3answers
34k views

Oracle SQL - CASE in a WHERE clause

Is it possible to somehow do this? WITH T1 AS ( SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL SELECT 3 AS SEQ, ...
4
votes
1answer
4k views

How can I query names using regular expressions?

How could I search for a specific pattern in a column? For example, I like to get all user names that start with either letter A or B. (btw: this is tagged oracle, but it might be interesting in ...
4
votes
2answers
2k views

Does Oracle support table-level & column-level permissions?

If there is a customer table, and some extra-private/secure information like SSN is there, should I create another table to store such information and lock that table so only certain people can view ...
4
votes
3answers
3k views

Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other ...
4
votes
2answers
149 views

Virtual Oracle server for education?

I am currently a College Student and am taking an introduction to Oracle 10G. I cannot seem to make it to class all of the time and am curious to ask if there is any Virtual Oracle Server or something ...
4
votes
2answers
8k views

What is Key Preserved Table concept?

I read in the Oracle Documentation about key-preserved table in Updating Join Views Section. However, I didn't find any simple to way understand it. I hope to receive some simple conceptual details ...