Questions tagged [oracle-11g]

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

Filter by
Sorted by
Tagged with
-2
votes
0answers
15 views

Oracle 11gR2 Single Instance with Grid Inf. - Change Server IP

Server: Oracle Linux Server 6.10 DB Version: 11.2.0.4.0 with Grid Inf. Database Files: ASM srvctl stop database -d db_unique_name (as oracle) srvctl stop listener (as oracle) .$GRID_HOME/bin/crsctl ...
0
votes
1answer
44 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
1answer
36 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
0answers
21 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 ...
0
votes
0answers
25 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 ...
0
votes
1answer
23 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
1answer
47 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 ...
0
votes
0answers
35 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 ...
0
votes
3answers
156 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 ...
0
votes
1answer
32 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 ...
1
vote
1answer
48 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 ...
0
votes
0answers
31 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
25 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
31 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 ...
1
vote
0answers
21 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
1answer
95 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 ...
0
votes
1answer
146 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 ...
1
vote
2answers
165 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
1answer
33 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
1answer
92 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
2answers
60 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 --------------------------...
2
votes
2answers
312 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....
1
vote
2answers
335 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;
0
votes
0answers
76 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 ...
-1
votes
3answers
462 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
1answer
305 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
2answers
51 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 ...
3
votes
1answer
49 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)...
0
votes
1answer
5k 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, ...
0
votes
1answer
20 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 | ...
0
votes
1answer
964 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 ...
0
votes
1answer
600 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
1answer
26 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 | ...
1
vote
0answers
16 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
votes
1answer
867 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 ...
0
votes
2answers
96 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 ...
0
votes
0answers
72 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 ...
0
votes
1answer
69 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 ...
0
votes
1answer
713 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 ...
-1
votes
1answer
47 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 ...
0
votes
1answer
1k 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 ...
2
votes
1answer
135 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 ...
1
vote
2answers
66 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 ...
0
votes
2answers
454 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 ...
2
votes
1answer
437 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 ...
0
votes
1answer
117 views

SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')

I upgrade Oracle from 11.1.0.6.0 (32bit version) to 11.2.0.1.0 (64bit version) using RMAN on the same Windows computer (64bit). After RMAN restored and recovered, I then try to run SQL> startup ...
0
votes
0answers
106 views

getitng difference of two consecutive rows

Hi i have a result like this from my query. and i want to get the difference of a specific epmloyee break time so i need to minus the time (OUT-IN). I have search the net but i see nothing related ...
1
vote
1answer
76 views

How does MOVE COMPRESS affect future data?

I'm working with Oracle 11g and a requirement came down the pipeline to "[C]ompress our data [sic]." And I'm wondering how compression will affect future data. The actual PL/SQL isn't an issue (...
1
vote
1answer
18 views

The full contents of the bind-variable are not displayed in SQL Plus

I wrote a big query in bind-variable. I want to print it in SQL Plus, but the text doesn't show, only small part of it.

1
2 3 4 5
15