Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
116
questions with no upvoted or accepted answers
8
votes
1answer
935 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 ...
4
votes
1answer
5k 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 ...
3
votes
0answers
4k 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 ...
2
votes
0answers
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 ...
2
votes
1answer
476 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
1answer
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 ...
2
votes
0answers
901 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 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 ...
1
vote
0answers
12 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 ...
1
vote
0answers
11 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 ...
1
vote
0answers
304 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
0answers
140 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@...
1
vote
0answers
37 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 |
|...
1
vote
1answer
289 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 ...
1
vote
0answers
974 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,
...
1
vote
0answers
41 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 ...
1
vote
0answers
1k 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, ...
1
vote
0answers
107 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, ...
1
vote
0answers
56 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 ...
1
vote
1answer
40 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 ...
1
vote
0answers
41 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 ...
1
vote
2answers
156 views
Error while running script to backup redo logs
I am very new to DB administration and i was hired to manage an enterprise database.
We have a standby Oracle DB server that runs on Linux.
There is an FRA on that standby that gets filled up ...
1
vote
1answer
1k views
Generate & Email report from Oracle Forms 11g
I generate a report in PDF through passing of parameters from Oracle Forms 11g. I have a requirement to email the same report automatically as well. Following is the part of forms where I have passed ...
1
vote
1answer
642 views
drop table causes ORA-08103 with another one
In my process I have 2 tables.
target table, load table.
The target table holds the current data, and a lot of queries are fired against it.
When new data coming we use a FULL LOAD and the goal ...
1
vote
1answer
69 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 ...
1
vote
0answers
243 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 ...
1
vote
0answers
39 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
...
1
vote
0answers
484 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
0answers
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
0answers
38 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
0answers
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) ...
1
vote
0answers
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: ...
1
vote
0answers
422 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 ...
1
vote
0answers
204 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:
...
1
vote
1answer
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: ...
1
vote
0answers
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 ...
1
vote
0answers
340 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 ...
1
vote
0answers
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 ...
1
vote
0answers
68 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 ...
1
vote
0answers
230 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 ...
1
vote
0answers
912 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....
1
vote
0answers
305 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 ...
1
vote
1answer
741 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 ...
0
votes
1answer
24 views
Counting failed attempt
Here is my code:
create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(...
0
votes
1answer
46 views
Oracle 11g - Decreasing Big Table Size
I want to reduce the size of a table.
I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using ...
0
votes
1answer
32 views
Why is the schema name necessary here and how to compile without it
I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form.
Here's a snippet to illustrate the problem (I changed the ...
0
votes
0answers
11 views
Search table data based on attribute and storing the output in nested table collection which is traversed through ref cursor
CREATE OR REPLACE PACKAGE BODY search_pkg IS
/* PROCEDURE TO SEARCH PURCHASE DATA INPUT IS PURCHASE ID
INPUT : PURCHASE_ID
OUTPUT : DATA IS STORED IN THE COLLECTION DECLARED IN ...
0
votes
0answers
20 views
Oracle Database systemd service: ORA-01031: insufficient privileges
I'm having troubles starting an oracle database with systemd.
The start and stop script was created in /home/oracle/scripts/
oracledbstart.sh:
#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/11.2.0....
0
votes
0answers
8 views
How to use multilinguals via UTF8 in oracle 11g
My oracle(11g) DB uses UTF8 encoding, when I insert/update a row in a different language (Greek/Latin/Japanese) the row is getting updated, but when I check for the stored record in DB, it shows just &...
0
votes
0answers
57 views
Compare previous column and current column in ORACLE SQL and do SUM
This is my query to get TIME_DIFFERENT-> TO_CHAR (DATE '1900-01-01' + ABS ( LAG(MODIFIED_DATE,1)OVER(ORDER BY MODIFIED_DATE DESC) - MODIFIED_DATE), 'HH24:MI:SS') AS TIME_DIFFERENT
Now, I want to ...
