Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
715
questions
0
votes
2
answers
243
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 ...
- 149
0
votes
0
answers
167
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 ...
- 9
0
votes
1
answer
187
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 ...
- 11
1
vote
1
answer
1k
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 ...
MarkPrescher
-1
votes
1
answer
67
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 ...
- 11
0
votes
1
answer
4k
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 ...
- 3
2
votes
1
answer
209
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 ...
- 21
1
vote
2
answers
180
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 ...
- 13
0
votes
2
answers
3k
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
1
answer
853
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 ...
- 233
0
votes
1
answer
169
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
0
answers
151
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
1
vote
1
answer
161
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 (...
- 113
1
vote
1
answer
30
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.
- 31
1
vote
1
answer
196
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 ...
- 31
0
votes
1
answer
494
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.
* ...
- 1
0
votes
0
answers
4k
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
1
answer
698
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 ...
- 103
0
votes
1
answer
532
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 ...
- 404
0
votes
1
answer
1k
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
...
- 23
0
votes
5
answers
1k
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 ...
- 33
0
votes
0
answers
245
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.
...
- 113
0
votes
1
answer
2k
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 ...
- 103
1
vote
1
answer
62
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, ...
- 11
0
votes
1
answer
33
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
1
answer
2k
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 ...
- 1,412
0
votes
1
answer
170
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 ...
- 177
0
votes
1
answer
293
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 ...
- 1,412
3
votes
1
answer
43
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 ...
- 33
0
votes
2
answers
788
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))
...
- 1,412
0
votes
2
answers
6k
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 ...
- 123
0
votes
2
answers
405
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,412
-2
votes
3
answers
916
views
Is Oracle DBLink Vulnerable? [closed]
We have a web page on internet that calls a web service located on our intranet.
The web service talks to Database A to get some data.
Database A has a DBLink to Database B. This link is used to ...
- 149
0
votes
1
answer
495
views
Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits
I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This ...
- 531
0
votes
1
answer
102
views
Oracle database issues
I had my database working properly but I had an issue and when I tried to repair it I executed the following command
alter tablespace system add datafile '/var/oracle/oradata/data.dbf' size 10000m ...
- 1
3
votes
3
answers
2k
views
De-Duplicate from LISTAGG
I am using 11.2 version of oracle.
I want to de-duplicate from LISTAGG.
below is existing data in oracle db:
Speaker | Qualification | Product
P A P1
P B ...
- 33
2
votes
2
answers
2k
views
How to use "regular expression" to separate specific strings in Oracle
I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output:
field1 field2 level
1 30 1
2 4 2
52 0 3
8 1 ...
- 1,412
0
votes
1
answer
3k
views
DB_RECOVERY_FILE_DEST_SIZE automatically decrease size after shutdown
I am using oracle database 11gR2.
I increased db_recovery_file_dest size with alter command
alter system set db_recovery_file_dest_size=60G scope=both;
When I shutdown database and again open ...
- 123
0
votes
2
answers
182
views
Syntax error in setting unused column
I have a table named e_emp3 in Oracle with these fields among others:
FNAME VARCHAR2(20 BYTE)
SAL NUMBER(8,2)
DEPTNO NUMBER(4,0)
I need to alter e_emp3 and set unused column(...
2
votes
1
answer
3k
views
RDS: permission denied for relation spatial_ref_sys when copying
I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get,
ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
- 213
2
votes
1
answer
248
views
What prerequisites package is needed for Oracle 11g + 12c on the same OL7 server?
I need to install 3 Oracle homes on the same Oracle Linux 7 server, but it's not clear to me what prerequisites I need to install before proceeding.
What I'm planning to set up, is a two-standalone-...
- 195
-1
votes
1
answer
3k
views
Facing error 'No data found ' while trying to write a query using 'Cursor' PL/SQL in oracle
Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query
just with the aim of practicing 'Cursor's .
I have three tables with below structures :
Student:(Student_id , ...
- 1,412
0
votes
1
answer
1k
views
How to view databases and tables after 'imp' import using Oracle SQL developer
Maybe that's a wrong form of answer...
The problem is:
I've got an Oracle database dump file (very old, created with imp utility)
At last, I've imported it. The command was imp file=E:\Downloads\...
- 101
-1
votes
1
answer
8k
views
How to generate ddl for a synonym
what is the query used to generate ddl of synonym in one environment so that I can run it in the other environment
0
votes
0
answers
357
views
ORA-27369: job of type EXECUTABLE failed with exit code: There are no child processes to wait for
I have existing .bat file where i call FDSLoader64.exe on daily basis to update following tables.
FF_ADVANCED_AF
FF_ADVANCED_SAF
FF_BASIC_SAF
FF_BASIC_AF
FDSLoader64.exe is supposed to download ...
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
votes
1
answer
826
views
query with LISTAGG() string function does not return the desired output
I'm new to ORACLE and I'm trying to use a string aggregation function in my query to concatenate the value of rows with a comma ,
this is the structure of my tables:
Student(STUDENT_ID,student_name,...
- 1,412
2
votes
3
answers
14k
views
Do I Alter user quota in USERS tablespace?
I'm new to Oracle so please bear with me.
After installing Oracle XE 11.2, I created user SALES and granted create session and create table to the user.
Once I logged in with SALES I tried to create ...
- 135
0
votes
1
answer
193
views
Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL
Hi everyone hope you're doing well.
AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger
as you can see below :
CREATE OR REPLACE TRIGGER T_Insert
AFTER INSERT ON tag
...
- 1,412
0
votes
1
answer
878
views
Resizing and reclaiming free space on Tablespaces
I would like to reclaim some free space from our tablespaces. I can see that UNDOTBS1 and USERS for example have 30GB and 10GB free.
these are the tablespaces:
| tablespace | used mb | free mb ...
- 7,374