Questions tagged [oracle-11g]

Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.

118 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8 votes
1 answer
989 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 ...
user avatar
  • 245
4 votes
1 answer
6k 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 ...
user avatar
3 votes
0 answers
5k views

How to apply a condition on "count over partition"?

I'm using Oracle 11g. Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit. With this table: datetime_id | user_id | place_id and I want to ...
user avatar
2 votes
1 answer
19 views

Moving logs (not archive logs or redo logs)

My employer has six Exadatas: three in a RAC in one data center, and the other three in a RAC in another data center. One is primary, and the other is a DataGuard standby. On the standby RAC, we also ...
user avatar
  • 169
2 votes
0 answers
1k views

Changing a JSON object parsed with APEX_JSON

I would like to be able to change a JSON object parsed with the APEX_JSON.parse() function, but I found nothing in the official documentation, no tips or examples about that. In the code below I ...
user avatar
2 votes
1 answer
542 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 ...
user avatar
2 votes
2 answers
21k views

How can I find port running Oracle Enterprise Manager

I need to connect to the Oracle Enterprise manager of a remote machine. But I dont know the port on which OEM is running on the remote machine. I have access to the machine from terminal though. Is ...
user avatar
  • 141
2 votes
1 answer
21 views

Combine intersecting ranges to produce new ranges based on the points of intersection

I have a table with data like this: ID valid_from valid_to item_a Item_b Item_c 1 20.5.2016 15:28:02 21.5.2016 7:12:49 1 2 - 1 21.5.2016 7:12:50 ...
user avatar
  • 21
2 votes
0 answers
911 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. ...
user avatar
  • 139
2 votes
0 answers
2k views

Oracle 11g displaying ORA-01034, ORA-27101, and Linux "error 2" messages.

When I try to connect to Oracle 11g using: sqlplus noah/noah@orcl I get these errors: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or ...
user avatar
  • 21
1 vote
0 answers
25 views

TDE error when attempting to encrypt a datafile

I'm encountering error ORA-28374: typed master key not found in wallet when attempting to run alter database datafile '+DATA/testdb/datafile/users.2514.1233355332' encrypt; against an 11g database ...
user avatar
  • 41
1 vote
0 answers
28 views

Propagate where condition into CTE of a view, using index?

I have written a multi-step view using CTEs. The first step is a union on the source table (used to turn two columns begin_date and end_date into separate rows); then there is a group by that ...
user avatar
  • 111
1 vote
0 answers
362 views

SSIS won't connect to Oracle issue after ODAC driver un-install & re-install

I installed the ODTwithODAC1120320_32bit drivers and client and could successfully connect SSIS to an Oracle database. I noticed that the installation had defaulted to the E: drive which is where the ...
user avatar
1 vote
0 answers
158 views

sqlplus connection to Oracle 11g takes much more time to establish than from another machine

I have an existing Ubuntu 16.04LTS 64bit system that connects to Oracle 11g (11.2.0.4.0 64bit/Windows). From that Linux machine I can connect to Oracle via sqlplus instantly, using sqlplus uid/pwd@...
user avatar
  • 141
1 vote
0 answers
39 views

Using conditions to return specific rows

From the following table i am trying to retrieve specific rows based on a criteria +----+--------------+------+ | ID | UPDATED_DATE | TYPE | +----+--------------+------+ | 1 | 01/13 | P | |...
user avatar
  • 11
1 vote
1 answer
1k views

Is it possible to show DML that is locking some table?

I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the ...
user avatar
  • 111
1 vote
0 answers
1k views

Trigger for add discount on total price calculated from other table - oracle 11g

I have the following schema: CREATE TABLE ORDER( OrderId NUMBER PRIMARY KEY, Value NUMBER); CREATE TABLE ORDERITEM( OrderItemId NUMBER PRIMARY KEY, OrderId NUMBER, ...
user avatar
  • 47
1 vote
0 answers
43 views

Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)

I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
user avatar
  • 111
1 vote
0 answers
2k views

Oracle Number (10, 0) decimal in (C#) code

The following query is executed to add a new column to an existing table: ALTER TABLE TABLE1 ADD COLUMN1 NUMBER(10, 0) DEFAULT -1 NOT NULL. Using DBForge to check the database, result in NUMBER (10, ...
user avatar
  • 113
1 vote
0 answers
133 views

How to detect if insert transactions in oracle db are slow?

At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation: Some production devices send data results from tests to Server A, ...
user avatar
  • 11
1 vote
0 answers
67 views

How to configure partial replication of Oracle DB

Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE. The goal is to keep in PRODUCTION the minimal amount of data required for online operations, and use ARCHIVE to store all the ...
user avatar
  • 131
1 vote
1 answer
41 views

Oracle query performance when filtering results

I am currently working on a query for a front end application, which is experiencing weird performance issues when filters are applied on large sets of data. Here is a link to an SQLFiddle of the ...
user avatar
  • 11
1 vote
0 answers
42 views

how to show row values in column?

i have a database sample that looks like this:- as you can see my student master id is repeated. i want student master id to appear only once (in one row) can corresponding roll numbers to appear in ...
user avatar
1 vote
1 answer
71 views

Comparing data types of a primary and foreign key

I'm writing a script to check SQL against a standards document before it's implemented. I'm currently trying to write a script that compares the Primary and Foreign key's data type and precision. I ...
user avatar
1 vote
0 answers
309 views

How to turn on the expanded mode in Oracle SQL*Plus?

As in the switch \x of PostgreSQL. For more information on the PostgreSQL expanded mode, fetch https://serverfault.com/questions/618642/psql-expanded-mode-equivalency-for-mysql. Both PostgreSQL and ...
user avatar
  • 46
1 vote
0 answers
43 views

Get elapsed from log table

From a log table with a row for every column changed in another table I need to get the elapsed time between two complex event. In particular the log table has the format create table audit_log as ...
user avatar
  • 631
1 vote
0 answers
488 views

How to connect to Oracle 11g using Oracle-SQL-Developer from a Mac

I am currently running Oracle 11g on my Windows 10 machine, but I use my mac a lot and I want to connect to the installed Oracle 11g on my windows from my mac using SQLDeveloper. How can you achieve ...
user avatar
1 vote
0 answers
2k views

Oracle after run procedure returned ORA-02046

I made small changes in a procedure to calculate value for statistic monitoring. In procedure calculate_1 on remote DB, I added check null or no null - PARAMETER_ID OPEN CALCULATE_PARAMETER; ...
user avatar
1 vote
0 answers
40 views

Data synch issue in oracle

We use a oracle 11g database, and we have data coming from our app as well as from external resources. Now we have some tables that are populated from both UI and the external process/sources, and we ...
user avatar
1 vote
0 answers
2k views

How not use temp tablespace

How to not use the temp tablespace in oracle? I have something like this: set timing on; create table export_table tablespace tbspace1 noparallel nomonitoring as select /*+ full(cs) full(soi_aux) ...
user avatar
1 vote
0 answers
3k views

Unique constraint violated even though the table is empty

The item_type table is empty, yet on this: INSERT INTO item_type (id, name) VALUES (1, 'PHYSICAL') I get error: Error: ORA-00001: unique constraint (MYDB.SYS_C0013475) violated SQLState: ...
user avatar
1 vote
0 answers
495 views

EXTPROC issue during 10g to 11g database

I am currently 10g to 11g database migration and trying to configure EXTPROC. EXTPROC32 was configured as part of 10g to execute C++ library and working fine. It appears that 11g does not provided ...
user avatar
  • 111
1 vote
0 answers
229 views

How to recover LogMiner after its tablespace had been deleted?

Here is my story: I created separate table space for Log Miner, create tablespace logminer_ts datafile '/path/to/logminer.dbf' size 50M AUTOEXTEND ON And moved Log Miner to the created table space: ...
user avatar
  • 794
1 vote
1 answer
6k views

ORA-01157: cannot identify/lock data file 201

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files; * ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: ...
user avatar
  • 174
1 vote
0 answers
7k views

How to drop invalid Java classes from Oracle?

I tried to load some jars into my database, but I think I messed up something, because I tried as DBA and then with some oracle user... This was my first command: call dbms_java.loadjava('-genmissing ...
user avatar
  • 153
1 vote
0 answers
341 views

Oracle Application Server Integration With APEX

This might not be directly related to DBA, if so kindly advise. I need to integrated some reports created via Oracle Reports Builder with APEX. Reports are in RDF format. Which version of Oracle ...
user avatar
1 vote
0 answers
1k views

Script to find unused indexes over a period of time doesn't work in Oracle 11g

The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the ...
user avatar
  • 133
1 vote
0 answers
72 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
user avatar
  • 111
1 vote
0 answers
232 views

Sync/Replication of 2 Oracle Databases with NetApp storage

I am working on a high availability setup of Oracle Enterprise Manager. I need the back-end repository replicated (preferably in real time, or something close) to another repository, in which I could ...
user avatar
  • 11
1 vote
0 answers
973 views

No network ACL, yet my user can successfully connect to external server

This one's had both me and our dba scratching our heads. We have an Oracle 11.1.0.7.0 instance, DEV, with a user who can successfully connect to our ldap server, e.g.: DECLARE l_session DBMS_LDAP....
user avatar
1 vote
0 answers
306 views

Port no in Oracle Forms 11G

Which port number to be used in forms 11G Preference-->Runtime--> Application ServerURL Section? I have: 1521-Given at the time of database creation 7003 and 7004-Given at the time of WLS Domain ...
user avatar
1 vote
1 answer
850 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
user avatar
  • 111
0 votes
1 answer
29 views

Migrate from Oracle 9.2 to 11g

I have a executable application that using Oracle ODBC Driver V9.2. My Old server Have Oracle 10g on windows 2003 32bit. I install and configure new server using Oracle 11g on Windows Server 2016 x64. ...
user avatar
0 votes
0 answers
27 views

Best approach for purging records (including related records) from an Oracle database with undo capability?

Please let me know which approach you would take. The problem I have a large Oracle 11g DB that needs to have old records purged to free disk space. One of the requirements for the project is that we ...
user avatar
0 votes
1 answer
43 views

Is there any method like max() function? oracle version 11.1.0

I want to select the maximum value, but if the user has more than one value in one column, then leave all I have a table : create table package (U_id, service, offer,product) as select 1, 12345, ...
user avatar
0 votes
0 answers
37 views

replace duplicate column values only on one column, from second occurrence onwards

I have a simple query in Oracle 11g select ID,Section,Capacity,Output from yeilds order by section; that returns data in this form ID Section Capacity Output 2 SectionA 45 30 3 SectionA 45 25 4 ...
user avatar
  • 465
0 votes
1 answer
47 views

Sum of Total per Client

I want to know the total of each product that each customer has bought and I don't know how to add the total per customer. The tables are as follows: CREATE TABLE CLIENTES -- Customers ( CODIGO ...
user avatar
0 votes
0 answers
34 views

How to store data in loop and after assign data set to out ref cursor in Oracle

I tried to store date in loop and after that dataset set to OUT SYS_REFCURSOR. In this time I declared array variable and I tried to store data in loop by each iteration. and after, I want to assign ...
user avatar
0 votes
0 answers
29 views

How to resolve the "file 1 needs media recovery" error in Oracle 11g?

I am trying to migrate oracle 11g database from Windows to a Linux server. I have followed these steps for migration: I have opened database in mount mode. Then I have backed up file on the Windows ...
user avatar
  • 1
0 votes
1 answer
48 views

ORA-12514 error after rebooting

We have an oracle 11g cloud database that was working fine until recently when it started throwing the ORA-12514 after every reboot. The instance is programmed to be started up every morning and ...
user avatar