We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [oracle]

All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.

1,033 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
1answer
794 views

Oracle Ref Partitioning: Deadlock due to child table row migration

My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc. ORDERS table is LIST partitioned on STATUS column and all ...
6
votes
2answers
89 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
5
votes
1answer
3k views

ORA-04091: table ExpenseTable is mutating, trigger/function may not see it

I have two tables and a trigger and am inserting values in ProjectsTable like this: CREATE TABLE ProjectsTable ( ProjectID NUMBER(6) NOT NULL, ProjectName VARCHAR2(200) NOT NULL, Cost ...
5
votes
2answers
2k views

FileWatcher Troubleshooting Options

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't ...
5
votes
2answers
2k 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, ...
5
votes
1answer
915 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, ...
4
votes
1answer
2k views

Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)

Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675). This security alert addresses the ...
4
votes
0answers
265 views

RMAN-06169, but only when backing up via OEM 13c

When running a Level 1 or Level 0 backup via OEM 13c, the following RMAN error causes the run to fail. (The RMAN script is being ran with proper sysdba credentials from OEM 13c.) RMAN-06169: could ...
4
votes
0answers
5k views

DBeaver launch script from file

I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances. I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems ...
4
votes
0answers
813 views

Oracle: built in SQL pretty-print?

Oracle has some packages that will dump objects in SQL format, such as dbms_metadata.set_transform_param. One option that can be set is to "pretty print" the SQL. Is there anything in Oracle that ...
4
votes
1answer
2k views

How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job. I have a remote Oracle 11g R2 database on unix and a local one on Windows. I read that you can install the oracle ...
4
votes
0answers
2k 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). ...
4
votes
1answer
141 views

Determine weekend based on country

I've a table called BOND which have columns BOND NAME, COUNTRY of ISSUE and ISSUE DATE BOND Country ISSUE_DATE =========================== B1 INDIA 20-AUG-2017 B2 ISRAEL 18-AUG-2017 How ...
4
votes
1answer
2k views

Deferrable Foreign Key Constraints

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE? This answer on StackOverflow mentions the performance impact that comes with no ...
3
votes
1answer
292 views

Oracle Database 19.3 on Windows: Howto impdp or expdp from a UNC path in Windows?

With oracle database release 18.3 it is possible to use a unc path as a directory in windows and pump a database pump from this directory with impdp. Example: C:\>sqlplus system/passwd@exampledb ...
3
votes
1answer
96 views

Alert on Table size

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system ...
3
votes
3answers
659 views

How to find archivelog gaps in the backups

I manage Oracle databases that are backed up using RMAN. How can I find gaps in the backed-up archivelogs? I.e., I want to detect when something like this happens: Take full backup with archivelogs ...
3
votes
1answer
1k views

Why would a query return oracle.sql.NUMBER@[hex value] in place of a numeric value?

I'm seeing some odd behavior in an Oracle 11gR2 database. When I select from a particular table, using SQL Developer, certain rows of the result set include non-numeric results in a NUMBER column (...
3
votes
1answer
2k views

Oracle 12c Rman Duplicate Archive Log Not Verified

I am trying to duplicate a DB from live to dev but it is failing. Can anybody advise what is going wrong? Both databases are running Oracle 12c. All database files seem to be copied over but it fails ...
3
votes
0answers
1k views

Getting email notification when jobs are created under user_schema rather than sys?

I log in as sys and set the smtp for scheduler: BEGIN DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15'); DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'user@compny.com')...
3
votes
0answers
241 views

UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?

I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible ...
3
votes
0answers
110 views

Oracle replicat is abending when oracle is having special char while loading to teradata

I am doing replication from oracle to teradata where oracle is having below charter in one column. ->->-> ACSII value for this char is 26 . I need to replace it with null . In the charmap we have ...
3
votes
0answers
560 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 ...
3
votes
1answer
3k views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
3
votes
0answers
929 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 ...
3
votes
1answer
3k views

12c Grid Agent installation issues - Shell path is incorrect or not defined

I am installing 12c Grid Control agent on my server via Enterprise manager. The targer server is a UNIX server with AIX OS on it. I add target host manually, enter authentication information, host ...
2
votes
0answers
39 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
2
votes
0answers
46 views

In SQLPlus, why the first run of the command doesn't work?

I use SQLPlus connecting to an Oracle database. See below snapshot. Why the first run of the command show con_name doesn't work and the second run works? Edit I have to admit that I typed the ...
2
votes
0answers
93 views

Incremental Schema Stats (With partitioned/Subpartition table) gathering is taking too much time in oracle version 12.2.0.1

When I am gathering schema stats with below options, it is taking too much time: EXEC dbms_stats.gather_schema_stats ( ownname => 'SCHEMA', granularity => 'AUTO', CASCADE=> true, ...
2
votes
0answers
49 views

How to monitor progress of Oracle Materialized View

I have created a materialized view in Oracle 12.1 database. The master table has materialized view log created, so that I can do a fast refresh. I have executed the refresh using DBMS_MVIEW.REFRESH, ...
2
votes
0answers
57 views

Does Azure SQL natively support being the subscriber in transactional replication where Oracle is the publisher?…and if PostgreSQL is the publisher?

Is there a native way to setup transactional replication where an Azure SQL database is the subscriber and the publisher database is in Oracle? What if the publisher database is in PostgreSQL? If ...
2
votes
0answers
502 views

How do I do an ODBC connection to ORACLE from SSIS/ Visual studio?

Despite that OLE Db and ADO.NET are newer than ODBC potentially, I've heard that ODBC connections to Oracle are potentially faster within SSIS. So --- I'm trying to set up a simple ODBC connection to ...
2
votes
0answers
72 views

Non-standard number data types in Oracle

I'm not sure that this question belongs to this StackExchange. I've asked this question on StackOverflow: Besides the "usual" number data type where precision is greater then scale there are ...
2
votes
1answer
61 views

Identify Non matching records in Join- Oracle

I have a Lookup table like below Table MASTER CD SEQ DEF 1 1 A 1 1 B 1 1 C 2 1 A 2 1 B 2 1 C 2 1 D Table INFO SID CD SEQ DEF 1000 2 1 A 1000 2 1 B 1000 2 1 C 2000 2 1 B 2000 2 1 C 2000 2 1 D ...
2
votes
2answers
210 views

Deadlocks With Trigger Based Replication

A table ORIGINAL exists with the following structure: ID VARCHAR Length 10 (key) VALUE VARCHAR Length 10 (non-key) A table REPLICATION exists with the following structure: ID ...
2
votes
0answers
2k views

ORA-01031: insufficient privileges solution oracle

Create or replace procedure myprocedure is stmt varchar2(1000); stmt2 varchar2(1000); begin stmt := 'create global temporary table temp(id number(10))'; execute immediate stmt; ...
2
votes
0answers
1k views

Configuration of TCP/IP with SSL and TLS for Oracle

I am trying to configure the connections to my Oracle database with tcp / ip using ssl I have been guided by the procedure of the site https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-...
2
votes
0answers
664 views

Unable to start Oracle Listener - “The user name or password is incorrect”

I am trying to install Oracle Database 12c on a Windows Server 2012 machine. The installation goes well for the most part, except that I get the error "Oracle Net Services configuration failed" (that ...
2
votes
1answer
3k views

Why create a Materialized View Log with Primary Key or RowID, etc?

I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED ...
2
votes
0answers
34 views

Replication to SQL Server and to Oracle not working for one database

I have a replication for many databases on one server to one SQL Server database and to Oracle. Replication for one database is not working while the other four databases are working. For SQL Server ...
2
votes
1answer
306 views

Oracle encryption wallet 12c

I exported a database to a transportable tablespace and send the data to a customer. They received the error: ORA-28365 Wallet is not open. Do I need to close the wallet before creating the export? ...
2
votes
0answers
361 views

Why is Oracle SQL slower than cursors?

I am converting an SQL Server system to run on Oracle (11g). The system has all been set up, data loaded, etc. The Oracle system was loaded using all standard default values. The amount of data is ...
2
votes
0answers
3k views

“Acknowledge over PGA limit” wait event

Is it possible for this wait event to activate/showup even when the PGA has not crossed over PGA_AGGREGATE_LIMIT? I am continuously seeing this wait event in the ASH, but the PGA allocated stays ...
2
votes
1answer
1k views

Unable to get output variable of Oracle procedure using Sql linked server

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure. OLE DB provider ...
2
votes
2answers
3k views

Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file

I am stuck at this for a while and had been Googling for answer but can't seem to find any.... I have an Oracle 9i table with BLOB field that I need to extract the BLOB field into a raw file on the ...
2
votes
1answer
382 views

Oracle using sub-optimal index on bind variables

Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields). And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third ...
2
votes
0answers
588 views

Oracle Instant Client 64-bit “Unable to connect, SQLState=” Windows Server 2012

I have installed the Oracle 12.2 Instant Client ODBC 32 & 64 bit drivers and required software (VS C++ redist 2013 32 & 64 bit) on Windows Server 2012. I can create a connection and connect ...
2
votes
0answers
1k views

Case insensitive search of CLOBs in Oracle 12

Question: What is the most performant way to do a case-insensitive search in a CLOB column? I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. ...
2
votes
1answer
475 views

Oracle sys SYSDBA audit information to a report

I am trying to produce a monthly report on Audit. I am able to query the DBA_AUDIT_TRAIL, for all other users and no issue in that. I wanted to include the SYS/SYSDBA activities in this report and ...
2
votes
0answers
246 views

Oracle to SQL server replication recent data filter options

The current setup: A live application DB is on an oracle server, which has a nightly replication over to an SQL server that users (read me) can use to bash out reporting from. The issue is due to ...