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
149 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
29 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 ...
3
votes
0answers
48 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
3
votes
0answers
70 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
207 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
538 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
50 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 ...
2
votes
0answers
49 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 ...
2
votes
0answers
72 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
85 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
121 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
109 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
78 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
160 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
1k 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
566 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
81 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
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 ...
2
votes
0answers
328 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
869 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
575 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
621 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
184 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
5k 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
827 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
385 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
2k 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
238 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
361 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
24 views

Specifying Oracle Service Name in UNIX Profile

Is there any way we can specify Oracle Service Name in UNIX Profile? I know that there is an environment variable ORACLE_SID for specifying SID in the Profile, But how about service name? If there ...
1
vote
0answers
38 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
176 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
32 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
43 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 ...
1
vote
0answers
39 views

plsql procedure gets invalidated

procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem in my case procedure p1()--resides in db1 is begin .... select col1 ...
1
vote
0answers
46 views

Refreshing Materialized view creates many child cursors

Doing some tests, I noticed that having REFRESH FAST ON COMMIT materialized view and flashbacks enabled cause Oracle to create many child cursors in some cases. For example, CREATE TABLE table1 ...
1
vote
0answers
65 views

Redefining a table with a user-defined type using DBMS_REDEFINITION

The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, The reason for doing this is to ...
1
vote
0answers
57 views

RETURNING syntax with multiple columns

Again, another question related to the answer of the one there: Insert with OUTPUT correlated to sub query table So I ended up with this query, written for SQL Server: CREATE TABLE tempIDs ( ...
1
vote
0answers
1k views

Importing a different character set export dump into another DB instance

I am trying to import a given dump file using oracle imp utility. However; I am facing the following issues. C:\Users\AD>imp DB_USER/*****@dev1 file=C:\test\db42312.dmp log=C:\test\import2.log ...
1
vote
0answers
153 views

Importing bulk csv file to oracle database

I am going to import a bulk csv file to oracle database. This file contains around 200m records. I was wondering what is the best way of importing this file? I check the performance of SQL developer ...
1
vote
0answers
45 views

Multiple handler entries in lsnrctl services output

This what I get for an lsnrctl services. LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 04-APR-2014 10:01:43 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to ...
1
vote
0answers
36 views

is there a DB client which supports oracle editions?

We are using a clever feature or oracle 11gr2 called edition-based redefinitions. This means each stored proc and view looks different depending on the edition you set in the session. There are ...
1
vote
0answers
235 views

automating oracle database installation on windows

I'm trying to install oracle 12c database on windows using the silent install with a response file as: setup.exe -silent -responseFile file.res where I generate the response file using the gui and ...
1
vote
0answers
326 views

Export Oracle Database User data from client

I am working on client system. There is a database on server contains many users. I have the access to database with a userid and password. my userid is "ncon" and Database name is 'HYDPPD'. We are ...
1
vote
0answers
133 views

How to display '0' where no data found in PL/SQL Oracle Reports

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 ...
1
vote
0answers
161 views

Oracle restore backup on other server

I have following situation: One server with Oracle has to be backed up and recovered on other Oracle server. Second Oracle DB has the same user/schemas, but with a different older data. I intend to ...
1
vote
0answers
117 views

whats the fastest way to copy data from table to new one?

Whats the fastest way to copy table "table_1" to new table called "table_2" ? I'd like the constraints copied as well as the data.