Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
715
questions
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
-3
votes
1
answer
55
views
MYSQL workbench like gui tool for oracle 10 g or more
I am a java full stack developer who want to write oracle sql queries to simplify writing is there any mysql workbench like similar gui tool for oracle by oracle or any other please sugest
1
vote
1
answer
78
views
Updating an oracle updatable view through an oracle_fdw foreign table in PostgreSQL fails with ORA-02014
I created an updatable view V1 in an Oracle 11g DB (it has INSTEAD OF triggers for UPDATE, DELETE and INSERT); it originally had a GROUP BY query in it, which I moved to another view V2. This other ...
- 406
0
votes
1
answer
599
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
65
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
2
answers
354
views
expdp fails with error ORA-31626 without any details on a full export
I am exporting a complete database (full=y) on oracle 11gR2 on Linux, and always get this error. There are no other messages, so the problem is not about timeout or permissions as shown elsewhere. ...
- 4,912
1
vote
1
answer
415
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
0
votes
1
answer
822
views
Improving OPENQUERY performance from Oracle to SQL Server
I'm currently working on a database migration, from Oracle 11g to SQL Server 2019. We're talking here of about 10-12 GB, in something like 300 tables (so creating them manually is out of reach, as is ...
- 3
1
vote
1
answer
1k
views
Receiving error "can not access rows from a non-nested table item" while working with associative arrays
Below , I'm trying to use the data of the array I've declared,
in the where clause of the query but I receive the following error :
declare
v_input varchar2(400) := '0,1,2,3,4';
type ...
- 1,412
-1
votes
1
answer
107
views
How to restore a Oracle Database if i only have the datafiles?
My Oracle database Test server crashed, I have no rman backups but I still have the datafiles because they are on a SAN Storage, so if I have datafiles, how can I restore my Oracle database?
- 29
0
votes
1
answer
50
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
57
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
113
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
1
vote
1
answer
66
views
insertion in a table of objects with nested table in oracle
I have a problem inserting in a nested table in oracle
These are the relevant types and tables;
create type movies_type as Table of ref movie_type;
create type actor_type under person_type
(
...
- 13
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, ...
0
votes
1
answer
2k
views
Oracle 11gR2 - Expdp Full Database With Exclude Some Table Data
We will migrate our database using datapump.
It will be full database export with exclude some table's datas.
But we want to do a different operation for 2 different tables. We want to export the data ...
- 29
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
-1
votes
1
answer
484
views
Why can't Many to Many relationships exist in relational database management systems?
While reading Oracle 11g Sql by Joan Casteel, the author mentions that many to many relationships couldn't exist in relational databases. I was wondering what the reasoning behind this fact was.
- 11
0
votes
1
answer
62
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 ...
-1
votes
1
answer
86
views
Example tablespcae in Oracle 11g
I need to know why example table space is created by Oracle 11g ? Does it effects the performance if it is full ? if yes how can we stop it being used ?
Thanks
0
votes
0
answers
327
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
40
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
131
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 ...
0
votes
1
answer
183
views
Oracle 11gR2 (11.2.0.4.0) - Drop and Remove Datafiles
We have a database of 2000 users.
We will drop all remaining schemas, leaving only 2 end users, excluding Internal (Oracle maintained) users.
After this operation, there will be a very high reduction ...
- 29
0
votes
3
answers
810
views
Oracle database won't start, ORA-03113
Running Oracle 11.2.0.4.0 on CentOS and discovered the hard drive filled up, giving error in alert log:
ORA-19815: WARNING: db_recovery_file_dest_size of 64424509440 bytes is 99.98% used, and has ...
- 149
0
votes
1
answer
68
views
When to perform which type of incomplete recovery?
This is purely learning purpose, that which incomplete recovery type is use in which condition? And DBA is bound to choose one or can choose anyone of following Incomplete recovery type?
Scn-based ...
- 93
1
vote
1
answer
158
views
Why is Oracle's TZ_OFFSET wrong for all cities on only one server?
Why does Oracle think that the timezone offset of Denver is UTC-7:00?
The server time and systimestamp look right.
Server 1: Oracle 11.2.0.4.0
SELECT SESSIONTIMEZONE FROM DUAL;
America/Denver
cat ...
- 251
0
votes
1
answer
82
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(...
- 13
0
votes
1
answer
270
views
Fetch all rows and leave column value empty if no equality match
I have a query where I want to fetch all rows where the value in column x is between 400-600.
Including in this I want a matching column value from antoher table.
But I want all 200 rows between 400 ...
- 103
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
1
answer
270
views
I am counfused about when Oracle database won't do parsing
I am confused about when Oracle database won't do parsing?
In the AWR report, there is a metrics called "execute to parse", which means more SQL just execute without parsing when it ...
- 11
0
votes
1
answer
1k
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 ...
- 29
1
vote
2
answers
1k
views
Show All online SID Database Oracle 11 in Linux SuSe and Find Database file [closed]
I'm new with Oracle DBA in my new office, and they gave me a development server that already have a lot SID database online in SuSe (I checked with lsnrctl status)
My questions are :
How to check ...
0
votes
1
answer
227
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 ...
- 101
0
votes
1
answer
228
views
IMPDP Job Failing with ORA-54002
I am trying to import a dump file from an 11g database into a 19c database instance. I am running the job as SYSTEM as recommended by Oracle. The job starts but quickly devolves into failing SQL ...
0
votes
2
answers
2k
views
Limiting join to top 1 row for each row
I'm trying to join two tables and filter join results but I couldn't manage it and I'd appreciate any help.
I have these row sets:
Table A
id | userid | targetid | start_date
--------------------------...
- 101
2
votes
2
answers
845
views
identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared [closed]
I am using oracle 11g express edition and I am getting the below error when trying to create a change set.
Error report:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_CDC_PUBLISH....
- 131
1
vote
2
answers
8k
views
Use of Parallel hints in a SQL query
What's difference between parallel (4) and parallel 4 with and without brackets?
For eg :
select /*+parallel(4) */ * from table_name;
select /*+parallel 4 */ * from table_name;
- 11
-1
votes
3
answers
1k
views
Oracle 11g, show running queries [duplicate]
I've a web application which submits some queries to Oracle 11g. I want to know what are these queries, exactly.
I there a way I do this in Oracle 11g? Couldn't do this in the web application itself.
0
votes
1
answer
2k
views
What happens if log files storage is full?
I'm currently studying oracle 11g,i had this question in mind but never found the answer even in the docs.
What happens if log files storage is full?
does the DB stop or block any DML or DDL execution?...
0
votes
2
answers
150
views
Creating two databases in Oracle 11g
I am a student. I have assignments and projects simultaneously.
I want my project database and the other simple databases separate.
I have no option to use MySQL or others as my lecturer suggested to ...
- 11
3
votes
1
answer
93
views
How to figure out the latest version of a table in the 'recyclebin' section of PL-SQL developer
There are three tables with the same name Pantea in my recyclebin section of pl-sql developer and I don't know how to find the one that had been dropped the last (I mean the last version of the table)...
- 1,412
0
votes
1
answer
16k
views
How to join two table without getting duplicate from both left & right table in Oracle
Tables Details:
CREATE TABLE Test2 (
ID INT,
Value INT,
other INT);
CREATE TABLE Test1 (
ID INT,
TYPE INT,
other INT);
INSERT INTO Test2 VALUES (123456, 5, 12);
INSERT INTO Test2 VALUES (123456, 10, ...
- 5
0
votes
1
answer
74
views
How to add duplicate records by basing on the count from another table (SQL)
I wanted to add a duplicate record by basing on the count from another table
For example:
In table #1, I have the following records:
+------------------------+------------+
| first_name | last_name | ...
- 13
0
votes
1
answer
5k
views
How to join two table without getting duplicate from right table in Oracle
Tables Details:
CREATE TABLE Test2 (
ID INT,
Value INT,
other INT);
CREATE TABLE Test1 (
ID INT,
TYPE INT,
other INT);
INSERT INTO Test2 VALUES (123456, 5, 12);
INSERT INTO Test1 ...
- 5
0
votes
1
answer
2k
views
How to add a new listener on a new port and restrict it to one instance only
I am running oracle 11.2.3 and we have a server with multiple instances running. All of them are registered to port 1521 with the default listener. Now we are required to make one instance available ...
1
vote
1
answer
320
views
Query to get duplicate records then update it by putting a sequence number
I was trying to get all records with duplicate data and update it by putting a sequence number.
Let's say i have this data:
+-------------------------------------------+
| first_name | last_name | ...
- 13
1
vote
0
answers
151
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
votes
1
answer
2k
views
How To Delete Record From dba_audit_trail in Oracle 11gR2
I want to delete records older than one month from AUD$ table, but I have to do this on dba_audit_trail, because I have to do this job according dba_audit_trail's timestamp. I can't find any clear ...
- 55