Questions tagged [oracle-11g]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
24 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 ...
user avatar
1 vote
1 answer
29 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 ( ...
user avatar
  • 13
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, ...
user avatar
0 votes
1 answer
68 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 ...
user avatar
0 votes
0 answers
34 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 ...
user avatar
  • 465
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 ...
user avatar
  • 169
-1 votes
1 answer
87 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.
user avatar
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 ...
user avatar
-1 votes
1 answer
44 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
user avatar
0 votes
0 answers
33 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 ...
user avatar
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 ...
user avatar
  • 1
0 votes
1 answer
43 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 ...
user avatar
0 votes
1 answer
62 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 ...
user avatar
0 votes
0 answers
48 views

TNS:destination host unreachable

I have a Linux server running oracle database 11g with an interface setup (eth0). due to some other requirements, i added a new interface (eth1) to connect to a separate vlan and a proxy was also ...
user avatar
  • 1
0 votes
3 answers
295 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 ...
user avatar
  • 149
0 votes
1 answer
40 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 ...
user avatar
1 vote
1 answer
61 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 ...
user avatar
0 votes
1 answer
26 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(...
user avatar
  • 13
0 votes
1 answer
40 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 ...
user avatar
  • 103
1 vote
0 answers
23 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 ...
user avatar
  • 41
1 vote
1 answer
110 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 ...
user avatar
0 votes
1 answer
304 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 ...
user avatar
1 vote
2 answers
303 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 ...
user avatar
0 votes
1 answer
41 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 ...
user avatar
  • 101
0 votes
1 answer
133 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 ...
user avatar
0 votes
2 answers
157 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 --------------------------...
user avatar
  • 101
2 votes
2 answers
403 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....
user avatar
  • 131
1 vote
2 answers
525 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;
user avatar
0 votes
0 answers
88 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 ...
user avatar
  • 15
-1 votes
3 answers
631 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.
user avatar
0 votes
1 answer
441 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?...
user avatar
0 votes
2 answers
64 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 ...
user avatar
  • 11
3 votes
1 answer
54 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)...
user avatar
  • 1,302
0 votes
1 answer
7k 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, ...
user avatar
0 votes
1 answer
22 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 | ...
user avatar
0 votes
1 answer
2k 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 ...
user avatar
0 votes
1 answer
766 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 ...
user avatar
1 vote
1 answer
27 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 | ...
user avatar
1 vote
0 answers
23 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 ...
user avatar
  • 111
-1 votes
1 answer
1k 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 ...
user avatar
  • 25
0 votes
2 answers
116 views

Oracle limit the number of files of audit

I would like to know how to limit the number of files of audit generated in the audit directory, I have an Oracle 12c database. The AUDIT_TRAIL parameter is equal to NONE, however it continues to ...
user avatar
0 votes
0 answers
85 views

Is there a way to optimize the query if we are using ROUND function 100 times in a single query

The below query is to compare the last day of the month and select either zero or a value. I am not pasting the entire query as its huge and the i am just pasting the condition which if possible can ...
user avatar
  • 9
0 votes
1 answer
89 views

ignored in stored procedure with clause

Hi i'm currently working with oracle that will give me my requered output. I able to make the query but when i'm putting it in stored procedure it gives me error of Error(11,1): PL/SQL: SQL Statement ...
user avatar
  • 11
1 vote
1 answer
805 views

Error ORA-12505 when connecting to Database Oracle 11g SQL Developer

recently I installed Oracle XE 11g with SQL Developer. When I first ran it and created a database and a new user, everything worked perfectly - I was able to connect to the database and was able to ...
user avatar
-1 votes
1 answer
49 views

get data each day and show 0 if no data

Hi i'm trying to create query that will give me the employee double break count each day. I need to display each day but what i able to display now is the only days that has value. This is the same ...
user avatar
  • 11
0 votes
1 answer
2k views

Oracle query get schema name, size and creation date

I'm having trouble making a query with the following output, Schema Name, Size and Creation Date. I'm new to Oracle and after more than 4 hours of searching I decided to create a question here. I ...
user avatar
  • 3
2 votes
1 answer
149 views

Why is an Oracle Package Variable Intermittently Incorrect after Multiple Updates

I am supporting an application that runs on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production. We have a table that stores hourly data that uses triggers call a package to ...
user avatar
1 vote
2 answers
79 views

Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11)

The ORACLE SQL manual (Page 2-58 of the main 11.1 SQL Manual) states: The total length of a datetime format model cannot exceed 22 characters. However, I've been able to use a format string for ...
user avatar
  • 13
0 votes
2 answers
875 views

ORACLE TABLESPACE USERS GETS 100% USED

I am facing a problem with oracles users tablespace. It automatically grows really fast. What kind of data is stored there? And what happens when it hits 100% of space used? Is there a solution to ...
user avatar
2 votes
1 answer
532 views

Nulling a BLOB column in Oracle 11 impdp

I need to import a 50 GB dump file containing three schemas. Over 90% of the file is taken up by a single BLOB column. How could I import the schemas, including the table containing the blobs, but ...
user avatar
  • 233

1
2 3 4 5
15