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
1answer
25 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
11 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
26 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
46 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 ...
0
votes
0answers
23 views

In ORACLE 11g , triggers, how i can get EXCEPTIONS, BEFORE they can be trigged

I had a package, very extensive who cannot be modified nor debugged. So i wrote an trigger then to get the table info before the process crash, BUT, in testing of the trigger as i insert in the table, ...
0
votes
1answer
12 views

SQL EXECUTE TASK IN SSIS WITH ORACLE CONNECTION

following my previous question: Oracle source with SSIS 2008 on how to connect to an Oracle instance, which ended up working for me. However, I am currently stuck trying to run a merge statement on ...
1
vote
1answer
22 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
11 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
75 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
32 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
17 views

Export the table results into a text file automatically using sql developer

I have a table with contents as below +-----------+-------+ | Name | Value | +-----------+-------+ | IHA_NAME | Rexon | | IHA_Value | 65 | +-----------+-------+ I want to export this data ...
0
votes
0answers
50 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
30 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
115 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 ...
0
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
93 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 ...
0
votes
0answers
37 views

How to convert join result of column to row in sql Oracle

I have two tables related, with maximum 7 columns in the second table: Table 1: |ID_TABLE_1(PK) | STATE |ID_CAT |DATE | DESCRIPTION| |270184 |1 |3 |09/03/2020 | 7 test ...
0
votes
0answers
13 views

odi column lineage query

I have no odi experience and i have inherited an oracle data warehouse fed by odi implementations. I need to validate the source to target flows for several fields.My first attempt is to try to create ...
2
votes
0answers
111 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
44 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
61 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 ...
0
votes
0answers
51 views

Database migration assistant for Unicode by Oracle is not responding after using schedule column option

I have posted this issue on Oracle Community but I would like to post it on here so that I will have more opinions on this issue. Issue: I am facing an issue with DMU 19.1 which is the DMU ...
2
votes
1answer
141 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
65 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
42 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 ...
0
votes
0answers
41 views

Oracle ACL error on 10g

I need to execute a procedure that writes on an oracle 10g server, the procedure runs from an oracle 11g, I have the following error: ORA-24247: network access denied by access control list (ACL) ORA-...
1
vote
1answer
33 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.
0
votes
0answers
28 views

SQL Access Advisor with big Select query

I need to get recommendations from SQL Access Advisor for big Select query (> 10000 simbols). I use DBMS_Advisor, but one procedure i need support only 4000 symbols. How can I use this package with ...
1
vote
1answer
29 views

Big string in SQLPlus

I need to write a Select query in bind-variable and this query has more than 2500 symbols. Variable sql_txt clob; Exec :sql_txt := 'Select .....'; But SQL Plus support only <2499 symbols. How ...
0
votes
1answer
95 views

Error during upgrade : Oracle 11g to 12c

While upgrading catupgrade.sql, we are getting below error. Due to errors found during the upgrade process, the post upgrade actions in catuppst.sql have not been automatically run. * ...
0
votes
0answers
599 views

Getting error SP2-0158: unknown SET option

I am getting error while running my script SP2-0158: unknown SET option "errorlogging" SP2-0158: unknown SET option "echo" SP2-0158: unknown COLUMN option "ENTMAP" SP2-0158: unknown COLUMN option "...
0
votes
1answer
117 views

Oracle: Connects with sqlplus but not from perl

I need to connect to an Oracle database from a perl script, it works in my tests and when using sqlplus, but not in production. This is a summary of the situation: Oracle 11g XE (11.2.0.2.0) : (Test ...
-3
votes
2answers
443 views

SQL QUERY RELATED TO JOINS

I am practicing SQL and i got stuck at this query: "Display the salesmen id, salesmen name, total sale amount and total discount by the salesman who has made sales. Display the result in descending ...
0
votes
1answer
87 views

How do I recreate the XE database in Oracle 11g Express?

My Oracle XE database was useless mess of unused tables etc. Using SqlPlus and several google results, I finally managed to drop the XE database, but now when I connect with SqlPLus IO cannot create ...
0
votes
1answer
66 views

How To Create A New bash_profile And Connect New Database To It In Oracle [closed]

OS -> Linux DB -> Oracle 11gR2 Normally my .bash_profile file looks like this: TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=abcde.localdomain; export ORACLE_HOSTNAME ...
0
votes
5answers
144 views

Alternative to Triggers

I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do ...
0
votes
0answers
68 views

Oracle 11.2.0.1.0 showing “no matching authentication protocol”

I have installed the above oracle version (personal edition) for my developmental purpose. But when I try to login through sqlplus, always it throws me the error no matching authentication protocol. ...
0
votes
1answer
175 views

Does canceling update operation perform automatic rollback

I ran into a bit of confusion. I'm under the belief that database operations are automatic - that is either the query succeeds or the transaction is automatically rolledback. For example - if you have ...
1
vote
1answer
49 views

Patching after upgrade (windows 11gr2 -> 12cR2)

I have a following scenario where I had a patched oracle 11gR2 DB (applied Windows Bundle and OJVM patches) and then it was upgrade to Oracle 12cR2. 12cR2 was a clean installation (no patches applied, ...
0
votes
0answers
15 views

Environment variables for Oracle OPAM 11g

Is there a way to set environment variables when logging through Oracle OPAM 11g to #OPAMSH console? Prior to OPAM integration, environment variables are set through a logging script where a question ...
0
votes
1answer
29 views

Oracle 11g connectivity over windows based client-server net [closed]

I have two PCs as follows: I want to create network for both PCs and share Oracle database from PC1 PC1 (windows 10 Pro 64-bit operation system) - Oracle 11g Installed PC2 (windows 10 Pro 64-bit ...
1
vote
1answer
279 views

How to select last record of each month

I have a table: Table1(DDate date,Deposit_Key Number , Amount Number) some example data are: DDate Deposit_key Amount 1/1/2019 1 10 1/2/2019 1 ...
0
votes
1answer
29 views

“Server not open” / “Drop tablespace” catch-22 situation

I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress". Fair enough. The fix for this is as ...
0
votes
0answers
35 views

Sql Oracle 11g. Want to find the what are all the values are modified in a table?

I have ten attributes in a table. I want to find what are all the datas are being modified and which are all the fields??? Could you please help me out
0
votes
1answer
48 views

Receiving error : ORA-01747 While trying to calculate COUNT(*) of a specific table

Hope you're doing well. I have two tables and as I far as I know for this scenario there is no need for their structures. When I run this query : SELECT COUNT(T.*) AS X FROM table1 T INNER JOIN ...
3
votes
1answer
39 views

Query Performance Help

A brief history. I have an oracle sql query which I'm looking to pull data when a certain preference is enabled. In this query I had a date range to filter the data to a specific range. This was ...
0
votes
2answers
44 views

Better ways to update columns of a table than using Merge Statement

I have a table with this structure Table1(Cust_No Varchar2(50), First_Name Varchar2(50), Last_Name Varchar2(50), Error_Code Number(1) , Error_Desc Varchar2(50)) ...
0
votes
2answers
2k views

Error During IMPDP in oracle database 12c

I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7 EXPDP Command: expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log ...
0
votes
2answers
95 views

How to use TO_NUMBER function to update columns of a table Using oracle pl/sql

Hope you're doing well. I have a table with this structure : Table1(Cust_No Varchar2(50), First_Name Varchar2(50), Last_Name Varchar2(50), Error_Code number(1), ...

1
2 3 4 5
15