Questions tagged [oracle-11g]

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

Filter by
Sorted by
Tagged with
3
votes
2answers
114 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 ...
0
votes
1answer
38 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 ...
0
votes
2answers
14 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(...
1
vote
1answer
27 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
0
votes
0answers
7 views

Loading data from CSV file to oracle table using sql loader

i want to load the data from CSV file to oracle table with this below condition. if the table is empty then only the data should be loaded into table otherwise data should not be loaded. is this ...
0
votes
0answers
15 views

Global nonpartitioned index vs local partitioned on for a partitioned table

We have a select query which is really slow. We are using views in the from clause. Global nonpartitioned index is used for accessing the rows of a partitioned table. My doubt is that if Global index ...
2
votes
1answer
21 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-...
0
votes
1answer
41 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 , ...
0
votes
1answer
39 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\...
0
votes
1answer
25 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
0answers
20 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
0answers
11 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
vote
1answer
16 views

query with LISTAGG() string function does not give 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
vote
3answers
50 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 ...
1
vote
1answer
45 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 ...
0
votes
0answers
15 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 ...
0
votes
0answers
23 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,...
0
votes
0answers
34 views

Everyone can't use the linked server

I'm having an issue with a linked server on MSSQL Sever 2016. I created a link to an Oracle server, TKCS. It uses the Oracle OLEDB drivers installed by the Oracle client (11g). On the security tab ...
5
votes
2answers
547 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
vote
2answers
51 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
vote
1answer
61 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 ...
2
votes
1answer
40 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 ...
0
votes
1answer
18 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
275 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
29 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
1answer
28 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 ...
1
vote
1answer
20 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?
1
vote
1answer
53 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
59 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
14 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
48 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
15 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
52 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
31 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
31 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
87 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
31 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
37 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
57 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
107 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
78 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
16 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
70 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
348 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
1k 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
23 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
22 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
436 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 ...