Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
101
questions with no upvoted or accepted answers
9
votes
1
answer
1k
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 ...
- 265
4
votes
1
answer
7k
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
6k
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
3
votes
3
answers
7k
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: ...
- 194
2
votes
1
answer
32
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 ...
- 196
2
votes
0
answers
2k
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
2
answers
24k
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
26
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
1
answer
8k
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 ...
- 163
2
votes
0
answers
933
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
1
answer
421
views
What are the ways to recover Oracle database that has corrupted data file, very old RMAN full backup, and incomplete/corrupted archived logs?
For an Oracle 10g/11g database that refuses to start due to datafile (SYSTEM01.DBF) corruption, does RMAN restore command immediately overwrite all current datafiles from the last found full RMAN ...
- 11
1
vote
0
answers
77
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 ...
- 51
1
vote
0
answers
153
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
396
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
167
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
3k
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
2k
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
53
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, ...
- 143
1
vote
0
answers
175
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
97
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
61
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
46
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
75
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
452
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 ...
- 66
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
...
- 641
1
vote
0
answers
489
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: ...
- 503
1
vote
0
answers
513
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
278
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:
...
- 874
1
vote
0
answers
362
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
73
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
237
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
1k
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
312
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
924
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
54
views
Calculate (n) workdays from a given date, using a calendar table
As shown on below image, I have two tables;
Customer : with about 4 million records; and 3 columns
[Customer] : Customer ID
[Date] : Creation date
[Num_Days] : Number of working days to calculate ...
- 1
0
votes
1
answer
617
views
ORA-32771: Cannot Add File To Bigfile Tablespace
I have 1TB tablespace that have one bigfile , The disk size now full and added extra disk to my server but there is no way to expand Drive that bigfile in it , so need to add another bigfile ,if ...
0
votes
0
answers
66
views
Top reasons for temp tablespace to go offline
It was marked offline and it is fixed already.
We want to know the list of causes that marks the temp tablespace offline.
0
votes
0
answers
14
views
How to clear unneeded Oracle 11g backup files that can't be applied?
I have inherited an Oracle 11g DB running on Windows. The first order of business is taking care of an HD space issue which is caused by over 2000 archive file (.001 files). As I understand it, these ...
- 327
0
votes
1
answer
51
views
Query optimization [Oracle DBMS]
I have a query that always must return a single row and its execution time is about 5s, so I need to optimize it:
SELECT * FROM RESPONSE res
INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res....
- 1
0
votes
1
answer
58
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
116
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 ...
- 193
0
votes
1
answer
55
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, ...
