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
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 ...
- 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 ...
- 41
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 ...
- 159
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 ...
- 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 ...
- 133
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 ...
- 121
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 ...
- 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 ...
- 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.
...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
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@...
- 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 |
|...
- 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 ...
- 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,
...
- 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 ...
- 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, ...
- 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, ...
- 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 ...
- 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 ...
- 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 ...
- 111
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 ...
- 104
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 ...
- 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
...
- 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 ...
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;
...
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 ...
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) ...
- 111
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: ...
- 421
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 ...
- 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:
...
- 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: ...
- 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 ...
- 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 ...
- 175
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 ...
- 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 ...
- 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 ...
- 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....
- 519
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 ...
- 121
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 ...
- 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.
...
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 ...
- 183
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, ...
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
...
- 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 ...
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 ...
- 1
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 ...
- 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 ...