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
171 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 ...
3
votes
0answers
32 views

Can an active session in oracle be dead?

I have a time consuming query which populates a materialized view. It has been running for 18 hours - which was not expected. SELECT STATUS FROM V$SESSION WHERE SID=194; The above query shows the ...
3
votes
0answers
669 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
268 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
160 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
623 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
22 views

what is causing the reads in this configuration?

i have a setup here where we use a Solaris 10 x86, zpool for oracle12c-redo-log using mirrored disks and mirrored ZIL. logbias="latency", recordsize=128: pool: xxxxxx01_redo00 state: ONLINE scan: ...
2
votes
0answers
90 views

Update all rows

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example: update mytable set mycolumn=null; or: update mytable set ...
2
votes
0answers
156 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
28 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
304 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
453 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
247 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
376 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
140 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
201 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
3k 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
87 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
441 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
2k 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
1k 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
715 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
751 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
276 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
7k 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
977 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
518 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
298 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
448 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
40 views

How to identify concurrency issues in Oracle 11g?

For a short period in time we had a concurrency issue that lead to subsequent faults in our application (Java, Spring, Hibernate). Since we are using dictionary managed tablespaces, I first thought ...
1
vote
0answers
18 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
1
vote
0answers
31 views

Export entire Oracle schema to ASCII and import into DB2

I'm looking for a solution to export a number of schemas from Oracle to ASCII or other flat file format. The data then has to be read by DB2 import. Does anyone know the what oracle export commands ...
1
vote
0answers
50 views

Fast restore of dropped partitions

I want to test the restore of errorneously dropped partitions of a subpartitioned tables. This restore would consist of three steps: restore the database (or at least the necessary tablepsaces) to ...
1
vote
0answers
77 views

Oracle instead of trigger update performance issue

I have a large table containing over 15million records with 4 columns that form a compound key. Because we use a development environment that doesn't support compound keys I was required to create a ...
1
vote
0answers
39 views

What changes to Oracle installation are needed to work with Microsoft MSDTC

We have windows 2008 and 2003 servers that use COM PLUS-MSDTC and distributed transactions with 11g(11.2.0.3) running on SunOS and SPARC hardware. The oracle server was installed years ago by a DBA ...
1
vote
0answers
51 views

ORA-01861 - Input seems ok and FX/FM modifiers not used

Using SQL Developer 4, I am getting an ORA-01861 exception when I do the following (and BTW, I ticked 'Ignore NLS Definitions'): alter session set nls_date_format = 'DD-MON-YY'; alter session set ...
1
vote
0answers
82 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
44 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
46 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
47 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
45 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
66 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
316 views

“ORA-00936: missing expression” setting up Oracle Publisher in SQL Server

I'm trying to set up an Oracle publisher in SQL Server so I can perform replication. I've gotten connected to Oracle, the drivers work great (I have other linked servers), I can select an article I ...
1
vote
0answers
127 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
29 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
6k 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: ...