Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
715
questions
0
votes
0
answers
487
views
cx_Oracle high memory use
I am using cx_Oracle with python to select a dataset of about 200,000 rows.
I am having memory issues with my application, so I am memory profiling. The memory use for these lines is over 1G, however,...
- 103
5
votes
2
answers
5k
views
PL/SQL function to receive a number and return its binary format
I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for ...
- 1,412
0
votes
2
answers
499
views
Facing error while executing function inside the package in PL/SQL
Hi everyone hope you're doing well.
I'm new to oracle and I'm reading about the concept of 'Package' and 'Package body'.
I have a function as you can see below
CREATE OR REPLACE Function ...
- 1,412
1
vote
1
answer
185
views
Troubleshooting oracle database errors from cloud application
I am trying to troubleshoot an issue that presents as different database errors such as, ORA-01000: maximum open cursors exceeded or Unable to create DB Connection. I have reviewed the PLSQL to ...
- 111
2
votes
1
answer
347
views
Oracle isn't restoring correctly a backup
First of all I'll say I'm using:
Oracle Database 11g Express Edition
Oracle SQL developer
What I'm trying to achieve
Create a backup and restore it, so for example I can delete a table after a ...
- 127
0
votes
1
answer
907
views
Disable Oracle substitution parsing
I need to update a string (VARCHAR2) value in an Oracle database which contains Javascript code.
The update script contains the following string:
c===l&&p(a,b)
which when executed from "...
- 659
3
votes
2
answers
388
views
Lock out of Oracle based on Windows username
I have this logon trigger to only allow certain users to log in to an Oracle database (even if they have the correct password to enter the database):
CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER
...
0
votes
0
answers
48
views
How to use tablespace
I am trying to implement Transperent Data Encryption. I am done with setting up the wallet and creating the tablespace.
My doubt is how to specify tablespace so that every object(tables/functions/...
- 101
0
votes
1
answer
431
views
Dataguard the datafiles are created in another diskgroup
I have a recently made physical dataguard configuration and everything works correctly so far, but I've noticed that the datafiles that are created in the primary are created in the standby in a ...
- 135
2
votes
1
answer
611
views
SDO_NN the second nearest neighbor
Oracle SQL geometry allows me to find the nearest neighbor using
SDO_NN(neighbors.GEOLOC,p.GEOLOC,'SDO_NUM_RES=1 UNIT = MILE',1) = 'TRUE')
How can I get the select the second nearest point?
- 213
1
vote
1
answer
3k
views
Oracle 11g - Spool file - enforcing not quoted columns
I am trying to spool large datasets (200k+ row) to csv format and need the columns not to be quoted. This is possible via the export settings of the SQL Developer GUI, but I am trying to do this via a ...
- 11
0
votes
0
answers
680
views
Physical memory usage of Oracle 11g
We are setting up a new server with Oracle database (Oracle 11g r2) for our ERP application. The new server is on VMWare, 256 GB RAM, 48 vCPUs, and SSD SAN storage (RAID 10) connected directly to the ...
- 101
1
vote
0
answers
167
views
sqlplus connection to Oracle 11g takes much more time to establish than from another machine
I have an existing Ubuntu 16.04LTS 64bit system that connects to Oracle 11g (11.2.0.4.0 64bit/Windows).
From that Linux machine I can connect to Oracle via sqlplus instantly, using sqlplus uid/pwd@...
- 141
1
vote
2
answers
504
views
Connection denied from Java application
I try to connect from my little Java class to Oracle11g database and connection was refused with a SQL error.
From client (SQL Developer) connection works.
This is the ERROR when I try to run java ...
0
votes
1
answer
372
views
Where is my audit as Oracle sys audit is enable
On an Oracle 11gr1 database, SYS audit is enabled:
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ------...
- 813
0
votes
1
answer
2k
views
Oracle Query to Compare dates from Two Different tables
I have these two below tables. One is storing Data Received for an Asset and other one is when the Customer Representative contacted.
Asset_Data_Historical
+---------+------------------+
| AssetID |...
- 1
1
vote
1
answer
1k
views
Filter predicates executing before Access predicates
This is oracle 11g. When I try to do a select query on a table, in the explain plan option I see that the filter predicates of the query(predicates on column without indexes) are executed before the ...
- 123
0
votes
2
answers
623
views
Welcome message for oracle schema
I want to display welcome message when ever a oracle schema try to login database using TOAD or PLSQL developer only
- 69
1
vote
1
answer
563
views
Does tables under schema uses multiple tablespaces other than default tablespace
I have schema A in a database and have assigned the default tablespace as myspace.
There are 20 tables that reside under this single schema and all the tables are using different tablespaces.
My ...
0
votes
0
answers
154
views
Restrict Oracle database schema to login using Toad/PLSQL [duplicate]
I want to restrict my Oracle database schema for example john to login to database using tools like toad or PLSQL developer. this schema is used for application login using JDBC thin client. I cannot ...
- 69
0
votes
1
answer
348
views
error writing input to commandERROR: Invalid username and/or password
This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted.
~~~End Step Output/Error Log~~~
Error Log:...
- 355
1
vote
0
answers
39
views
Using conditions to return specific rows
From the following table i am trying to retrieve specific rows based on a criteria
+----+--------------+------+
| ID | UPDATED_DATE | TYPE |
+----+--------------+------+
| 1 | 01/13 | P |
|...
- 11
0
votes
1
answer
3k
views
Indexing binary data in PostgreSQL?
I am converting an Oracle 11G DB to PostgreSQL v11 DB.
I am super new to the whole database thing and I have encountered a query using index type of ctxsys.context to create an index.
I have no idea ...
- 42
0
votes
0
answers
675
views
Silent install of Oracle XE 11g fails with error -3
I'm trying to install silently Oracle 11g XE in the default location (C:\oraclexe).
PS D:\Profiles\xxx\Downloads\x.x\oraclexe\oraclexe-11.2.0> .\setup.exe /S /v/qn
However, the command generates ...
- 101
0
votes
1
answer
85
views
Oracle 11g, Will droping partition reuse free space on bigfile tablespace
I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace.
My partitioning range is one day and I have stored ...
- 653
1
vote
1
answer
3k
views
Is it possible to show DML that is locking some table?
I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the ...
- 111
0
votes
2
answers
12k
views
Splitting Comma Separated string into columns by using REGEXP_SUBSTR
Here is my example string:
select 'po1,qty1,po2,qty2,..,POn,QTYn' str from dual.
The string length is dynamic. It may have more than one PO and QTY.
Now, I want the output displayed as all the POs ...
- 1
0
votes
0
answers
25
views
Oracle Best-Buyer-Customer is Wholesale or Retail buyer
I have data warehouse with Fact table Orders, and CustomerDimension, and TimeDimention, and BookDimension.
CREATE TABLE DIM_CUSTOMER(
DIMCUSTID NUMBER(6) PRIMARY KEY,
CustId NUMBER(4) ...
- 47
0
votes
1
answer
2k
views
How to create stored procedure that returns query result?
I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There ...
- 165
1
vote
0
answers
2k
views
Trigger for add discount on total price calculated from other table - oracle 11g
I have the following schema:
CREATE TABLE ORDER(
OrderId NUMBER PRIMARY KEY,
Value NUMBER);
CREATE TABLE ORDERITEM(
OrderItemId NUMBER PRIMARY KEY,
OrderId NUMBER,
...
- 47
1
vote
1
answer
437
views
Order after rollup - oracle 11g
I am using rollup() function in group by expression to show the result query,
but my output like this:
Year Month AmountOfMoney
==== ===== =============
1998 March 130....
- 47
3
votes
2
answers
139
views
How can I justify making changes to my redo logs?
I don't spend a lot of time with my Oracle environment. I'm more of a SQL Server guy. I need to look into the alert logs to check for correlation of an application error. What I found instead was ...
- 355
0
votes
1
answer
475
views
In a database trigger on event create - How do I reference the created object?
Given the following trigger:
CREATE OR REPLACE TRIGGER
TRG_CREATE_TABLE_INITRANS
AFTER CREATE ON SCHEMA
BEGIN
IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
exec 'alter table '||<table_owner&...
- 1,873
-4
votes
1
answer
52
views
Optimise my query [closed]
select
REMITTORACCOUNTNUMBER,
TRANTYPE,
RRN,
AMOUNT,
responsecode ,
(CASE
when auxrc is NULL then 'N'
else auxrc END) as cbsstatus,
responsecode as switchstatus
...
- 1
0
votes
1
answer
290
views
Optimize Oracle 11g SQL*Plus connection for large remote SELECTs [closed]
Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a ...
0
votes
1
answer
165
views
Updating Database table from another machine
I am trying to update my database using a batch file. Batch file is located on machine say ABC which should pick a text file from the local directory on ABC and upload that file to DATABASE located on ...
0
votes
1
answer
3k
views
Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it?
I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause.
set up:
create table test_t ( x ...
- 129
0
votes
1
answer
2k
views
Consecutive dates count
I have table data like below:
CLIAM_KEY MBR_ID SERV_FROM
clm1 mem1 01-JAN-18
clm2 mem1 02-JAN-18
clm3 mem1 03-JAN-18
clm3 mem1 04-JAN-18
clm4 mem1 ...
4
votes
1
answer
7k
views
Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)
Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675).
This security alert addresses the ...
- 41
1
vote
1
answer
12k
views
Oracle 11g XE to 18c XE - What is the difference? Is 18c can be used like 11g XE?
I have worked on Oracle 11g XE about two months ago. After that I de installed them about a week ago. Now, I am curious to learn a little bit more about databases and I tried to installed Oracle 11g ...
- 107
0
votes
1
answer
511
views
Oracle 11g: increasing buffer cache with AMM
On a 2-node RAC we have enabled Automatic Memory Management. The advisor views indicate that increasing the buffer cache by 4 GB will half the physical reads, and we have 4 GB to give.
How must we ...
- 43
0
votes
1
answer
4k
views
Unable to connect to oracle database. Getting ORA12505 error
I am newbie to ORCALE
I am trying to connect to oracle server and i am getting this error. This is was working fine before but now i am facing this issue. Need help
An error was encountered ...
1
vote
2
answers
246
views
How to make a backup of an Oracle database that does not mess up the regular backup cycle?
Goal
I want to move a database from server A to server B. Both server have the same technology so one way is to shutdown the source database, copy data files,control file, redo logs, parameter file ...
- 7,664
4
votes
2
answers
20k
views
How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0
I am running following command to dump my database
./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY
and ...
- 141
1
vote
1
answer
504
views
ORA-24155 - Why am I getting this error when creating a chain rule
When trying to create a new chain rule, I receive ORA-24155 stating that the the rule I'm trying to create is not in the ruleset which doesn't make sense.
I stop and drop all jobs/chains and then ...
- 23
0
votes
0
answers
964
views
Oracle to SQL Server Conversion help: sys_connect_by_path, listagg, regexp
I've been tasked with converting a process from Oracle to Sql Server 2012.
I've converted most of the query over to SQL Server but am having a hard time translating over these queries to SQL Server ...
- 309
0
votes
1
answer
48
views
How to get a selection from a join, where there is no result in one of the tables
I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify ...
- 103
1
vote
1
answer
7k
views
Creating a table space sql developer
I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here.
I'...
- 13
1
vote
0
answers
53
views
Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)
I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
- 111
0
votes
1
answer
115
views
Oracle PK index extremely low performance
There's a table MYHIST containing 42M rows.
The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which ...
- 131