All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

1
vote
1answer
7k views

JDBC pooling Oracle driver Socket read timed out

I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver. Connection pool config: ...
2
votes
1answer
630 views

Internal query on CDEF$ and CON$ very CPU bound

I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the ...
0
votes
2answers
47 views

SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1)

I have an Oracle 11.2.0.1 database using dynamic memory allocation with parameter MEMORY_TARGET. When I decrease it with SCOPE=BOTH, the size of the SGA doesn't seem to decrease along with it. I have ...
0
votes
0answers
16 views

Get data type by ID

I was looking at this answer for checking the data type of a value. It's quite handy and spits out something that looks kind of like this: Typ=1 Len=12: 48,48,48,48,48,48,49,49,49,49,49,49 The part ...
1
vote
2answers
734 views

How to get a last DML operation in Oracle 10g-11g

I can get a number of last INSERT's/UPDATE's/DELETE's from SQL%ROWCOUNT var. Also there exists a way to get a last DML time of each row. But is there a way to get a last DML operation performed on a ...
2
votes
2answers
2k views

Oracle 11g installation stuck at installation

I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in ...
2
votes
1answer
169 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
0
votes
1answer
106 views

impdp schema name not found

I have Oracle Database 11.2.0.4 . I want to import a dump file to it with impdp . The Dump file is for a "Popualtion" schema . the schemas name is case sensitive. How can i import this file to ...
1
vote
1answer
34 views

Nested, inline pivot table (oracle)

I'm trying to go from a table like this: ID | NAME | METADATA ------------------------------- 1 | test | '{"a":"x","b":"y"}' where the metadata column is a JSON string. What I'd like to end up ...
0
votes
1answer
44 views

Microsoft SQL Server Vs Oracle [on hold]

I'm working in SQL Server as database administrator/Developer, and currently I was thinking about oracle. Many people said that it is a future and we will have more oracle databases then sql server. ...
2
votes
2answers
2k views

Repopulating Oracle tables using data from Excel/CSV file

I'm developing a Java EE application that utilizes Hibernate/JPA. Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle ...
0
votes
1answer
3k views

How to reference a table from another schema inside a package?

I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema. The package has a procedure that uses a cursor. When I create the package I get ...
2
votes
3answers
48 views

Count is slow Oracle

this is my query : SELECT Count(last_upd) FROM S_CONTACT WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd + 1/24 > SYSDATE - (1 / (24 * 60)); It takes 84 seconds to return ...
2
votes
0answers
43 views

What is a base64 RAW? How do I use it?

Oracle has a function for encoding a binary value, specifically a RAW type, into base64: BASE64_ENCODE. This makes sense. I want to turn binary into something I can more easily port across systems. ...
2
votes
2answers
38 views

what is difference between oracle instant client and oci?

I've been reading upon the definitions of oracle instant client and oci but being new to oracle database having a bit of difficult time to understand what they are/for. I would appreciate someone can ...
0
votes
2answers
262 views

Oracle Error: the sga requires more space than was allocated for it

(Oracle SQL 12c running on Windows 10) My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this ...
0
votes
1answer
41 views

Cannot create user using administrator credentials in Oracle

I am trying to get some hands-on experience with ORACLE and I installed Oracle Database 11g Express Edition on my PC at home. I logged into the 'Application Express' by entering TEST in the Workspace ...
0
votes
1answer
157 views

ORA02063 error while connecting to remote databse

I have connected to remote database using DB link. From prod databases it works fine but from test env reporting error invalid username/password. Please help.
0
votes
1answer
766 views

Oracle 12C, cannot drop NOT NULL constraint on a DEFAULT ON NULL column

In windows7 I Install oracle 12 C Then I connect to database as sys user and create new user so: CREATE USER C##USER IDENTIFIED BY user_pass; GRANT ALL PRIVILEGES TO C##USER; Then I connect to ...
1
vote
1answer
325 views

One Materialized View in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
1
vote
1answer
42 views

How to select all rows from column including two specific numbers within a set of digits, but not in beginning or end?

I'm attempting to retrieve all rows that include two specific characters within a set of characters corresponding to the attributable column of data. However, the specified characters can't be ...
1
vote
0answers
481 views

Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

I'm using IMPDP with the "network_link" for perform export/import in a single-pass. It runs fine until I get this error. ORA-39171: Job is experiencing a resumable wait. ORA-30036: unable to ...
2
votes
2answers
23k views

How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?

I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping. Here's the error I get: ...
1
vote
2answers
42 views

How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

This query: select distinct profile from dba_profiles; Returns 3 rows: default default1 default2. I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query ...
1
vote
1answer
55 views

understanding ORA-01792 maximum number of columns in a table or view is 1000

We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error: ORA-01792: maximum number of columns ...
0
votes
1answer
42 views

Regarding Analyze table then performance improve

I used function which contain 3 table like a,b,c and table a contain index like ind1 on two column. Now I call store procedure which contain code and also get data from above function. And I also ...
1
vote
1answer
140 views

ORA-01122: database file 1 failed verification check

I moved data file and database started fine but I did the following steps: SQL> shutdown SQL> startup nomount SQL> alter system set control_files='/var/oracle/oradata/control.dbf' ...
0
votes
1answer
122 views

Converting sql into mongodb nosql

I am trying to convert oracle sql into its mongodb equivalent version. create table my_hll as select mod(ora_hash(n), 1024) bucket, max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val from previous_data ...
3
votes
1answer
554 views

How to avoid hardcoding a database link name in a package body

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
0
votes
0answers
44 views

create table in a different schema

I am trying to create a table on a different schema without using "create any table" privilege. I have an application user/schema. TABLESPACE_NAME USEDMB MAXMB ...
0
votes
1answer
56 views

Oracle SQL: Outer Join Not Working As Expected

I am joining two tables, T_ADMSTU_PROG_ENROLL (renamed prog_enroll) and T_ADMGRAD_STUDENT (renamed grad). I would expect the result to have the following columns: TERM_CODE, STU_INST_UID, MAJOR_DESC, ...
0
votes
1answer
342 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
2
votes
1answer
54 views

How to compare dates on minute level in plsql?

If I'm correct, then date comparison on seconds level is as simple as SELECT 1 FROM sample_table WHERE sysdate <= sample_table_date_field; but haven't found any simple or stable solution for ...
3
votes
2answers
38 views

Modify linked server definition (Oracle) in SQL Server 2012

I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works ...
2
votes
4answers
1k views

How do I tell the actual IOPS used by Oracle?

This is hopefully a simple question. We have a large DB deployment that we need to scale up over the next year. For scaling we have been relying on our vendor, but recent developments have caused us ...
2
votes
1answer
665 views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
0
votes
1answer
1k views

Oracle Enterprise Manager Installation

First I install oracle 11g database with this configuration Step 2 of 9 I selected Create and configure a database Step 3 of 9 I selected Desktop Class Step 4 of 8 Global database name: Blitz ...
0
votes
2answers
140 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
0
votes
0answers
11 views

Trouble With Basic Query Searching [migrated]

Not sure if the this is the right place to ask but i have a question with beginner sql. I have the table dept and emp which include: SQL> desc dept; Name ...
1
vote
1answer
302 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
2
votes
2answers
892 views

Oracle Data Pump Import imports no records

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported. Processing object type ...
3
votes
2answers
924 views

FileWatcher Troubleshooting Options

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't ...
1
vote
1answer
140 views

Type with nested table in oracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a ...
0
votes
1answer
2k views

Oracle Duplicate Database with no archived logs

I am trying to perform a duplicate operation from one server to another (testing disaster recovery) on oracle 11g. I am very much still learning about Oracle. The system is not in production, it is ...
0
votes
0answers
40 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
1
vote
2answers
429 views

RMAN 11g/12c Client compatibility

I'm having a Recovery catalog database 12c version. While I'm trying to register the target database (11g) with the catalog I get the following error: Recovery Manager: Release 12.1.0.2.0 - ...
0
votes
1answer
24 views

ora-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair During AWR Creattion

When I'm create AWR Report then following error occurred plz help. Cause: An attempt was made to perform operation with an invalid Start/End Snapshot Pair.
0
votes
0answers
33 views

Find Bottleneck of Store procedure in Oracle 11g

I written a store procedure which retrieve data from 6 to 7 table using join and used sys-refcursor. Now problem is data fatch approximate 700 to 750 rows at 18 seconds but in some case take more ...
8
votes
2answers
9k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
0
votes
1answer
514 views

Import big CSV on Oracle

I have big CSV files and placed it on Linux (Amazon AMI). I have installed instant client on Amazon Linux AMI and I can connect to my Oracle database using SQL Plus. How can I import all the CSVs to ...