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

6
votes
0answers
161 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 ...
5
votes
0answers
279 views

Should I group by and then join, or join and then group by?

(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables) Assume three tables t1, t2 and t3 with a pairwise 1:n ...
3
votes
0answers
49 views

Oracle Data Pump Import with running advanced replication

In our system we have a running advanced replication between two databases(11g release 2) and want to use pump import to import a backup on one site. However the imported data is not replicated to the ...
3
votes
0answers
358 views

Use LDAP for name look up with Oracle Instant Client

Partly as a result of having problems with conflicting versions or bitness with the full installation of Oracle client, my development team (of which I'm a member) has been moving towards using Oracle ...
3
votes
0answers
158 views

How to avoid hardcoding a database link name in a package body

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
3
votes
0answers
114 views

Why would function based indices I’ve created lower the cost but now show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was ok to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
3
votes
0answers
2k views

Access 2010 linked table - solve the “decimal field's precision” error

I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables ...
3
votes
0answers
588 views

Mismatched Oracle drivers - need upgrade (10.2.0.1 to 10.2.0.5)

I was forwarded here by recommendation from Stack Overflow ...... We are running Oracle Enterprise software on various client and DB servers and we are experiencing a problem (outlined here) where ...
2
votes
0answers
112 views

Failed to drop column - value too large

I'm trying to remove column from DB and got a strange error: Error starting at line 147 in command: alter table config drop column dte Error report: SQL Error: ORA-12899: value ...
2
votes
0answers
94 views

Find columns referenced by virtual column expression

Does anyone know of any Oracle dictionary views that hold references of 'real' columns to virtual columns? For example lets say I have a table like this: create table t ( c1 varchar2(5) ,c2 as (c1 ...
2
votes
0answers
21 views

How do I get information about how an index set was created in Oracle?

A long time ago I created a CTXCAT index with an INDEX SET using ctx_ddl.create_index_set and ctx_ddl.add_index but forgot to save the DDL anywhere and now I need to recreate this index on another ...
2
votes
0answers
232 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
2
votes
0answers
318 views

Why OEM RMAN backup jobs fail after changing the preferred credentials?

In an Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, Running on Oracle Linux 6.5, we configured full RMAN backup schedule using OEM (Oracle Enterprise Manager 11g ...
2
votes
0answers
235 views

How can I connect to a network MySQL server using ODBC?

Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC. ...
2
votes
0answers
272 views

Historical SQL Monitoring

Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to historical activity? I am specifically interested in getting wait events aggregated at the SQL operation level, not just ...
2
votes
0answers
113 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
180 views

Oracle Text - Changing SYNC to MANUAL not working always

Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below ...
2
votes
0answers
2k views

Oracle Select Query Over Database Link Suddenly Hangs Endlessly

I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like ...
2
votes
0answers
899 views

Insert into table from a query to a linked database hangs

I have a schema with some database links to other schemas that we use to pull data from. We pull the data into a staging table, massage it, and then do some merges into our schema/database. The data ...
2
votes
0answers
85 views

Oracle 10g weird behaviour

We have a thick client application using an Oracle 10g database. We recently upgraded the application and in the process migrated the database tables from a latin to unicode character set. After the ...
2
votes
0answers
396 views

oracle streams apply: how to get a reason why LCR message was not applied

I've set up bidirectional oracle streams replication (11gR1) using identical scripts on both machines (DB1 and DB2). Although changes from DB1 are being applied to DB2, changes from DB2 to DB1 aren't. ...
2
votes
0answers
1k views

Oracle high Paging Faults

What are the reasons that lead to terrible high Paging faults. The following is the topas information and background about the Environment. OS: AIX 6.1 Softwares: Oracle 11gR2 Paging Space:30720 ...
2
votes
0answers
976 views

Standby controlfile error

I am doing dataguard. I have manually done a backup for the standby database. It was working fine. Problem arose when I used the standby controlfile. On the primary, I have created a standby ...
2
votes
0answers
673 views

Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?

Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
2
votes
0answers
714 views

Why does Oracle hang on “ALTER TABLE ADD … NOT NULL”?

Using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, the operations below have completely stumped me. The first locks up (table has DDL lock, finally killed query after 20+ minute wait). ...
2
votes
0answers
241 views

Oracle sqldeveloper Database Diff - not seeing indexes/sequences

I am using Oracle SQL Developer's database diff tool to compare schemas. (SQL developer version : 3.2.09) It works great on some environments, but in one, I can only see differences in tables - the ...
2
votes
0answers
6k views

Oracle 11g .DMP viewer

I have received a dump of a Oracle 11.2.0.2.0 db, which I'd like to export to some readable format like plain text, CSV, excel... Is that possible in some sort of a simple way? I've found a few ...
2
votes
0answers
940 views

oracle crash - not starting

We have a Windows Server 2008 R2 Enterprise x64 Oracle 10.2.0.3 Adempiere - ERP We had a power breakdown at 03.00 AM. At 07.30 AM we started the server At 08.00 our colleagues observed that the ...
2
votes
0answers
475 views

SQL Developer Data Modeller and Data Dictionary question

I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please? I can connect SQL Developer to Oracle XE's data dictionary without any problem, ...
2
votes
0answers
3k views

SQL*Plus, @, and relative paths

Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot. For example, under ...
2
votes
0answers
270 views

What do I need to do to avoid ORA-32696 errors?

I have a SELECT query using several WITH blocks. Sometimes it runs successfully. Very often it fails with the error "Lookup Error - Oracle Database Error: ORA-32696: HTI: No free slot". I Googled the ...
2
votes
0answers
1k views

ORA-01017: invalid username/password; logon denied

I've come across the abnormal situation, and can't understand what happens. I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to ...
2
votes
0answers
2k views

Oracle GoldenGate replication is stuck

I deployed Oracle GoldenGate to integrate many-to-one databases. After initial loading everything works fine. Until recently, some of the replicat seem to stuck and do not proceed to change ...
2
votes
0answers
409 views

Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a ...
1
vote
0answers
56 views

ORA-01406 while building a Sphinx index

We are getting ORA-01406 error while trying to build an index over Oracle DB using Sphinx: root@bdt:~# ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/ NLS_LANG=AMERICAN_AMERICA.UTF8 indexer bsrpIndex ...
1
vote
0answers
27 views

Should an SCN obtained from one RAC node be immediately usable on other RAC nodes?

I'm seeing ORA-08181: specified number is not a valid system change number and don't understand why. The error happens around once a month from a process that runs every few minutes - so very rarely. ...
1
vote
0answers
38 views

Prevent row from being inserted with a trigger

I'm trying to prevent a row being inserted if a certain condition is met using a trigger. I'm using a user defined exception to do this, however the row is still being inserted, despite the exception ...
1
vote
0answers
25 views

How to replace empty, multi-column result with single 'none'

If it is possible to make query in Oracle which will return single column/row with value "none" instead of empty, multi-column result? Otherwise return proper amount of columns with result. This is ...
1
vote
0answers
32 views

Replacement for Pragma Restrict references

Up to Oracle 11.1 you could mark a package as only reading from the database by adding PRAGMA RESTRICT_REFERENCES( DEFAULT, WNDS); This has now been deprecated. I would like to enforce read only on ...
1
vote
0answers
44 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
1
vote
0answers
78 views

'ORA-00059: maximum number of files exceeded' on Oracle 12c

I have a test database. Every night a script drops and recreates a number of pluggable databases that are used for our automated test runs. Recently, I have started getting 'ORA-00059: maximum ...
1
vote
0answers
26 views

Oracle - physical writes to an index

We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value. ...
1
vote
0answers
2k views

How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?

I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping. Here's the error I get: ...
1
vote
0answers
70 views

Database Migration with Liquibase using different DBMS

Our customer is using Oracle and we want to use MySQL for development. Therefore our MySQL Scheme has to be in sync with Oracle. I tried to use Liquibase, but I get problems at the time of applying a ...
1
vote
0answers
40 views

Importing data into Oracle 12

I got a set of files from a client which are, as far as I can tell, copies of a database in Oracle 11. Each folder has the following structure: - init<DB>.ora - orapw<DB> - arch - dbf ...
1
vote
0answers
50 views

Testing equivalence in strings with different character sets

I have to synchronize a number of tables with string columns between two databases: the source Oracle 10.2.0.3 NLS_CHARACTERSET WE8MSWIN1252, NLS_LENGTH_SEMANTICS BYTE the destination Oracle ...
1
vote
0answers
1k views

sqlldr error - Invalid number

I am trying to load the following csv file into a table: TIMESTAMP ; SESSIONID;PROXY_SESSIONID; ACTION;USERNAME ;USERHOST ;OS_USERNAME ;CLIENT_ID ...
1
vote
0answers
76 views

Oracle: separate column histograms for partitions

I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY). When I collect stats on the table, I end up with a HEIGHT BALANCED histogram ...
1
vote
0answers
107 views

Running materialized view refresh in parallel

I have a large materialized view that is refreshed periodically (complete refresh) and I would like to do it in parallel. I can already run the query part in parallel but the problem is (much slower) ...
1
vote
0answers
200 views

Oracle parallel query: finding out which Producer a Consumer is waiting for

One of our materialized view refresh's (part of a daily ETL routine) was hanging today; it usually completes within 5 minutes, but this time, I had to kill it after 23 hours. V$SESSION contained this ...