Questions tagged [oracle-11g]

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

117 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
1answer
942 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
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 ...
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
486 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
904 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
17 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
13 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
321 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
144 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
372 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
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, ...
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
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, ...
1
vote
0answers
110 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
167 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
670 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
249 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
441 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
205 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
921 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
766 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
0answers
12 views

Oracle SQL grant privilege to fetch xml from a website

I need to fetch data from a website in XML format, I added the website's certificate to the wallet. the XML for example can be found here: https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?...
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
51 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
12 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
9 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 &...