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

0
votes
0answers
67 views

Startup Oracle Database with an NFS mount on the control file

I currently have an Oracle Database which works just fine on 1 server. I want to share the folder which contains its' control file to another server via an NFS mount. However when mounted I get ...
0
votes
0answers
40 views

Non-structured Query performs better than on clustered, hash-clustered and indexed?

I am using the following query as a base line on some unstructured data (no indexes, no clusters etc) and the query is performing better on the unstrutured data than when I add indexes to the join ...
0
votes
0answers
2k views

ORA-28040: No matching authentication protocol

I'm trying to connect to my RAC database using odbc drivers. While trying I get ORA-28040: No matching authentication protocol I have added this in both client and the server: SQLNET....
0
votes
0answers
62 views

Oracle jobs not running

I have created a new oracle instance and imported a schema consisting of schedular jobs in it. The import was successfull but the jobs are not running automatically. I can run the job manually by exec ...
0
votes
0answers
98 views

Wrong result with Oracle left join restrictions

all! I'd like clarifications about how Oracle handles left joins that contains more than one join restriction. See the query below: select category.category_id , category.parent_category_id , ...
0
votes
0answers
37 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, SUM(NVL(...
0
votes
0answers
275 views

Troubles on export data with PL/SQL developer

I'm trying to export data from a specific table using PL/SQL developer and Oracle 11g. Here's how I did : select * from mytable right click on mytable then "Export data" chose my table on a grid ...
0
votes
0answers
47 views

Oracle: How to get the beginning and ending index of CONTAINS() search result on a CLOB

I'm using a CONTAINS query to search a CLOB for some user-defined string (with wildcards), i.e. "cat % the dog", which could return something like "When the cat was cornered, the cat scratched the dog....
0
votes
0answers
35 views

Not able to login with / as sysdba on same server, but able to login with other user

I am logged into the server with Admin user. When i try to connect to a database with a user which has dba privilege it succeed. But strange thing is that when i try to connect with / as sysdba, it ...
0
votes
0answers
26 views

How can I debug this RAC IP issue?

I have a two node RAC database with 5 scan IPs. Issue is i'm able to ping all the scan ips from my database server whereas when I try to ping the same from my local pc, one of the scan ips is not ...
0
votes
0answers
18 views

How to join irregular punches

Hello I have a table with punchces ... it might look like: emp_id | punch_type | punch_time ------------------------------------------------ 1 | I | 20/8/2015 08:00:...
0
votes
0answers
3k views

Oracle:Test connection failed because of an error in initializing provider. ORA-12170: TNS:Connect timeout occurred

We have Migrated SQL server 2008R2 to SQL server 2012 from Windows 2008R2 onto Windows 2012R2. Everything went well , except for the Oracle database connection: We have a SSIS package created on SQL ...
0
votes
0answers
199 views

Issues with SCAN IPs in Oracle RAC

I have 5 SCAN IPs running in my two node RAC database - 12c. Thing is that one scan listener is running in my node 1 and other listeners are running in node 2. When I try to ping the one scan listener ...
0
votes
0answers
25 views

How can I edit the prediction query to pass the parameters from a GUI?

I have created a data mining in Oracle SQL Developer and I got the the following query using the "Apply" node. I have to use this to predict the values for the class variable when the values for the ...
0
votes
0answers
114 views

Oracle database doesn't go to upgrade mode

I am trying to upgrade/migrate from 10g to 11g. When I start the database in upgrade mode with: startup upgrade select status from v$instance STATUS -------- MOUNTED It doesn't go to upgrade ...
0
votes
0answers
228 views

Restore .dmp file into csv

We have asked our clients for certain data in a csv file format and we just got a .dmp file. I have been trying to open it using MySQL as if it were .dump but it doesn't work. I have seen other ...
0
votes
0answers
168 views

Interpreting AWR report(top queries)

Could someone help me to understand a part of awr report? Below I'm pasting the top sql statements. I see there was a (very) huge number of executions of the first query. I see it was called from web ...
0
votes
0answers
44 views

PL/SQL follow a Item through a database

Hey I need some help creating a SQL statement for an Oracle DB. The situation: I want to follow a special item through two stations (UWA and KWA) with several steps where it may (!) get cut in up to ...
0
votes
0answers
252 views

Oracle 12c 2-node RAC, after first reboot CRS is hanging/stuck on octssd on the second node

My issue is with the Cluster Time Sync Service on the second node of an Oracle 12c RAC 2-node cluster on Oracle Linux. After the first reboot the second node is stuck on the octssd.bin process. I ...
0
votes
0answers
37 views

Calculating table size

I'm trying to follow these steps: Space estimation to estimate the size of my table that I'm creating. I specified an INITIAL as 1M and using the steps above I have the following estimations: ...
0
votes
0answers
41 views

column values combination

I have chains of nodes like A -> B -> C -> F D -> E and store them in an Oracle table pair with 2 columns left_node and right_node left_node right_node A B B C ...
0
votes
0answers
56 views

Identifying high load sql

We had an oracle outage some days ago. We have inbetween awr tools. I genarated awrrpt report but dont see anything suspicious. Which report should I generate to see sql, which caused very high cpu ...
0
votes
0answers
42 views

Query on Sqlplus utility

I have a OS user 'test' who doesn't belong to dba group or oinstall group. I need to access the database using sqlplus but I'm unable to access the sqlplus even after setting oracle home ans path and ...
0
votes
0answers
161 views

How to know imdp size?

I have a dump file using expdp with compression (oracle version >= 11g). Before running impdp I want to check if there is enough space on the machine but it seems as there is no parameter in the impdp ...
0
votes
0answers
22 views

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle?

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle? I need to implement several big selects to DB2 and also I need to retrieve consistent data which was commited on time when ...
0
votes
0answers
42 views

SPLIT PARTITION issue?

we had written program to ADD partition on every month. But, one of our customer forgotten to execute the program from Jan to July and now, he is facing performance issues (because corresponding table ...
0
votes
0answers
107 views

Fetching the data from the Oracle database is slow in Linux as compared to Windows

I am facing a performance issue in Linux while fetching data from the Oracle database. My scenario is as follows: 1) I have the backend server which is on UNIX with Oracle database running . The ...
0
votes
0answers
111 views

resource manager consumer group OTHER_GROUPS is not part of top-plan Test_plan

I want to use resource manager to limit execution time, with this parameter MAX_EST_EXEC_TIME. but running the below is giving me following error. how to fix it ? ORA-29382: validation of pending ...
0
votes
0answers
463 views

Materialized View does not reflect changes after fast refresh

I have a query with many aggregate functions working on almost a million records every time, it's taking up to (ten of) minutes to complete the query so I created a materialized view for it, and since ...
0
votes
0answers
126 views

How to call Oracle Function from POSTGRESQL

I am sorry if this topic already exists. I have: PostgreSQL 9.3 , Oracle 11gR2 on Linux . I used oracle_fdw to query Oracle table from Postgres but oracle_fdw can not call function. Questions: How ...
0
votes
0answers
74 views

.dmp file generating problem on oracle

I have a server windows2003 with oracle 10g and developer 6i. My experience is when I install developer 6i first and then install database 10g then .dmp file generate perfectly using imp or exp. but ...
0
votes
0answers
118 views

Is there a command like Linux `tail`, but for Oracle?

Is there a command like Linux tail, but for Oracle? For example just to leave open in a terminal, to view all SQL queries as they happen without having to refresh.
0
votes
0answers
34 views

Oracle DB - DR corruption

One of my databases in my DR is fully corrupted ( I have checked running validate database using RMAN). Now to recover all the data files which are corrupted I need to copy the data files from my ...
0
votes
0answers
102 views

Export backup from a different location

I have two servers. One database server and another is an app server. I have client installed in my app server and I have the respective the tns in my app server to connect to the db server. Now can I ...
0
votes
0answers
2k views

ORA-00911: invalid character error in insert statement

I created a table called TROUBLE in Oracle Database 10g Express Edition by this command create table TROUBLE ( City VARCHAR2(13) NOT NULL, SampleDate DATE NOT NULL, Noon NUMBER(3,1), Midnight NUMBER(...
0
votes
0answers
66 views

Oracle Performance on 2 servers

I have the same database on two different servers(production and test) with major differences in query time as follows: Query1: SELECT distinct dms_get_FAST_serverpath(d.id) full_name FROM ...
0
votes
0answers
144 views

Cann't connect from another server to Oracle

I can connect locally like normal : sqlplus sys as sysdba I can also connect sqlplus sys/pass@hostname:1521/servicename as sysdba the service I get from when I do lsrnctl status but when I try ...
0
votes
0answers
152 views

Getting milliseconds elapsed during a stored PL/SQL procedure

I've been using sysdate to time procedures, but a lot of my procedures are very small procedures that get called thousands of times. Is there a way to get milliseconds elapsed instead of seconds? I ...
0
votes
0answers
1k views

Oracle trigger after insert error ORA-01403: no data found

I'm trying to make a trigger that select a value inserted in other table and concat with other value in an insert on other table that's my code CREATE TABLE "SEX" ( "ID" NUMBER, "CS" ...
0
votes
0answers
328 views

Partitioning existing table in Oracle

(Oracle) I'm trying to do an exchange partition on an empty partitioned table to fill it with data from a non-partitioned table. I'm getting the following error and can't find the cause. I'm not 100% ...
0
votes
0answers
16 views

JDBC - accessing a query, then querying that query

Sorry if the way I phrased that is confusing. I'm writing a program that acts kind of like a filter - it retrieves an answer to a query, counts the rows, then I want to do a slightly more narrow ...
0
votes
0answers
333 views

Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python. However,...
0
votes
0answers
137 views

What is the appropriate GCC version I should use for building my External Procedure?

When working with Oracle External procedures, what is the GCC version I should use to build my procedure? Does Oracle get shipped with a GCC version and libraries that it keeps in one of its own ...
0
votes
0answers
376 views

Out of memory error from Java stored procedure

I have a Java stored procedure inside Oracle 11g database. The procedure uses lots of memory, so I'm setting the max memory size in same session: create or replace function setMaxMemorySize(num ...
0
votes
0answers
54 views

Is there a way to check which views have data? Oracle 11g

I'm undergoing a cleanup process for a data mart design. However, I want to get rid of any views (in the design) which result in having 1 or less rows of data. Instead of manually going through 2,...
0
votes
0answers
221 views

TAF vs SCAN in Oracle 11gR2

We are planning to use Oracle 11gR2 RAC. I am wondering if TAF is required to setup even when using SCAN in Oracle 11gR2 RAC. Please let me know if there are any advantages of using TAF along with ...
0
votes
0answers
546 views

Removing locks in Oracle without being admin

3 wild sessions each got a row exclusive lock in the same table: SELECT * FROM DBA_DML_LOCKS; How can I remove the lock without being admin? If I was admin I would list (SID, SERIAL#) of the ...
0
votes
0answers
270 views

Maxing out the shared pool's memory by executing many UPDATE statements (ORA-04031)

I was executing a bunch of UPDATE statement. Around ~32.8K got executed and committed, then got the error: cx_Oracle.DatabaseError: ORA-04031: unable to allocate 3896 bytes of shared memory ("...
0
votes
0answers
942 views

Oracle RAC takes forever dropping tables

We are seeing table drops consistently take tens of seconds on our Oracle 12c RAC. It appears to be spending on its time on reliable message waits. Which I think means that it asks the other node to ...
0
votes
0answers
520 views

WHENEVER OSERROR suppresses error messages

$ sqlplus <connstring> 2>&1 <<! WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; SET ECHO ON; @nonex.sql; ! Produces SQL*Plus: Release 11.2.0.1.0 ...