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) ...
5
votes
1answer
914 views
Oracle 10g express database link to Oracle 8i
I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
5
votes
1answer
235 views
DBA's Scope in datawarehousing Projects
As a DBA trainee, what needs to be known in order to be part of datawarehousing projects?
I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
5
votes
1answer
576 views
Case Behavior Changed Between 11.2.0.1 and 11.2.0.2
I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...
5
votes
1answer
280 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
2answers
8k 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
2answers
634 views
Making the Oracle optimizer use a virtual column to find out about a partition
I have a large table that has three columns like this:
"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7)
GENERATED ALWAYS AS
(
(extract(YEAR FROM START_DATE) * ...
5
votes
1answer
2k views
Oracle RAC failover not working correctly
I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both ...
5
votes
1answer
68 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
731 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
1answer
160 views
Map Oracle roles to Active Directory groups?
Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
5
votes
1answer
135 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
255 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
626 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
960 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
3answers
647 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
372 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
2answers
194 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
821 views
MySQL or Oracle Associate Certification?
I have 15+ years in software development and 7+ in web development, and I want to get a certification in another area, like DBA. I have 7+ years of experience like MySQL developer. Which is better for ...
5
votes
1answer
4k views
What makes an Oracle Applications DBA different from an Oracle DBA?
All Oracle databases support some type of application that the Oracle DBA would need to have some knowledge of. What makes an Oracle Applications DBA different than this? Does the term mean that ...
5
votes
1answer
611 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
2k views
Add Oracle Label Security to an existing Oracle installation
We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g ...
5
votes
1answer
821 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
1k 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
1answer
53 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
297 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
361 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
271 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
468 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
565 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
171 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
1answer
465 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
332 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
117 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
0answers
98 views
9i streams capture doesn't evaluate rules
I have a streams replication set up between two oracle 9.2.0.8 instances as sources and an 11g instance as a destination, processing a small subset of data from each database (let it be one table ...
4
votes
3answers
507 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
432 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
2answers
2k 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?
4
votes
4answers
419 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
3answers
271 views
SQL Server pivot query on Oracle source
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:
╔══════════╦══════════╗
║ ...
4
votes
4answers
2k 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
3answers
9k views
How to get rid of ORA-28002 message the password will expire within 6 days?
I have a user getting an ORA-28002 indicating that the password will expire within six days. I ran the following:
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
But when I try to log ...
4
votes
4answers
775 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
1answer
425 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
3answers
822 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
121 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
3answers
352 views
What are the top 5 design decisions which need to be made when implementing a database?
I am implementing an Oracle System to store event alerts from an application so I guess all the schema, database design will be handled by the application through a sript.
I am new to Database ...
4
votes
3answers
19k 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
3answers
756 views
Detect Index Coalesce
Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.
Note: I am not referring to the COALESCE ...
4
votes
2answers
3k views
Updating Multiple Rows with Data from Column A into Column B?
I have about 60,000 rows that I'm needing to update the information from column_a to column_b. For example, 60,000 customers need their arrival_time to match their departure_time. I understand that ...
4
votes
4answers
199 views
What's the best way to list groups of values that are contained in other groups
For example, starting with a table like this:
create table t as
select 'A' as x, level as y from dual connect by level<=5
union all
select 'B' as x, level+2 as y from dual connect by level<=5
...
