Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [oracle-11g]

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

0
votes
1answer
15 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 "...
3
votes
2answers
239 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
0answers
25 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/...
0
votes
0answers
18 views

Oracle query slowdown [closed]

I have a table with 175,483,383 records in it, and I'm doing a query that returns 173,179,674 of them. When I select count(*) (so it gets all the records), it takes 7 seconds. I have select on that ...
0
votes
1answer
17 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 ...
0
votes
0answers
9 views

SDO_NN the second nearest neighbor

Oracle SQL geometry allows to find the nearest neighbor using SDO_NN(neighbors.GEOLOC,p.GEOLOC,'SDO_NUM_RES=1 UNIT = MILE',1) = 'TRUE') How to select the second nearest point?
1
vote
1answer
27 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 ...
0
votes
0answers
49 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 ...
1
vote
0answers
11 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@...
0
votes
1answer
34 views

Connection denied from Java application

I try to connect from my little Java class to Oracle11g database and connection was refused whit SQL error. From Client (SQL Developer) connection works. This is the ERROR when I try to run java ...
0
votes
0answers
13 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 ------------------------------------ ------...
0
votes
1answer
42 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 |...
0
votes
0answers
26 views

How can I drop an undefined object

I'm struggling on creating synonyms for some package (that compiled successfully). Java application display this error: identifier 'blackPackage' must be declared When I try create public synonym ...
0
votes
1answer
27 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 ...
0
votes
2answers
77 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
0
votes
1answer
23 views

Does tables under schema uses multiple tablespaces other than default tablespace

I have schema 'A' in a database and assigned a default tablespace as "myspace". There are 20 tables resides under single schema and all tables are using different tablespaces.Here my doubt is "Why ...
0
votes
0answers
30 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 ...
0
votes
1answer
37 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:...
1
vote
0answers
34 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 | |...
0
votes
0answers
11 views

How do I aggregate data based on CASE results?

I need to create a column that counts the total based off the CASE condition below. For example, the total occurrences the CASE meets the criteria for 'Group 1' AND 'Group 2'. Additionally, I need to ...
0
votes
1answer
63 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 ...
0
votes
0answers
62 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 ...
0
votes
1answer
11 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 ...
1
vote
1answer
57 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 ...
0
votes
0answers
212 views

TNS-12560: TNS:protocol adapter error

I cannot access oracle from remote due to my listener error. I checked all configuration files which are listener.ora, tnsanmes.ora and sqlnet but I didn't find any missing config. Below is my ...
0
votes
2answers
413 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 ...
0
votes
0answers
22 views

Connect to Oracle 11g from remote computer

SQL> connect hr/hr@//192.168.1.113:1521/orcl ERROR: ORA-12541: TNS:no listener I'm getting this error while executing above command. Help me out how to connect to remote oracle database.
0
votes
0answers
21 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) ...
0
votes
1answer
278 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 ...
1
vote
0answers
100 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, ...
1
vote
1answer
39 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....
3
votes
2answers
69 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 ...
0
votes
1answer
50 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&...
-4
votes
1answer
47 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 ...
0
votes
1answer
67 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
1answer
72 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
1answer
358 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 ...
0
votes
1answer
146 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 ...
3
votes
1answer
673 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 ...
1
vote
1answer
3k 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 ...
0
votes
1answer
139 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 ...
0
votes
1answer
175 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 ...
0
votes
2answers
57 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 ...
1
vote
1answer
2k 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 ...
0
votes
1answer
63 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 ...
0
votes
0answers
153 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 ...
0
votes
1answer
35 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 ...
1
vote
1answer
970 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'...
1
vote
0answers
38 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 ...
0
votes
1answer
35 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 ...