Oracle Database 11g Release 2. Please also tag oracle for search purposes.

learn more… | top users | synonyms

2
votes
3answers
75 views

Calculating overlap between groups

I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple ...
0
votes
1answer
29 views

Instant File Initialisation on Windows Oracle Server

I require to add additional datafiles to a SmallFile tablespace. The process took several minutes on my system for 1 datafile. Is it possible to allow Oracle the same capability as SQL Server using ...
1
vote
1answer
536 views

Oracle cannot connect to SQL developer as sys account

I am trying to connect to SQL developer as sys account. It is always giving the error invalid username/password. When I try to connect it through SQLPLUS, it gets connect with any wrong password ...
0
votes
1answer
399 views

How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status

I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH. I've MV with this PL/SQL : CREATE MATERIALIZED VIEW ...
0
votes
0answers
238 views

Pipelined function returning anydataset

I am using the dynamic pivot see here to pivot a data set where it returns an object of type anydataset. It works great in SQL but I am having a hard time using it in a package. In the package the ...
0
votes
0answers
81 views

Oracle NLS_CHARACTERSET mismatch while import

I have source Dump with NLS_CHARACTERSET WE8MSWIN1252 NLS_NCHAR_CHARACTERSET AL16UTF16 Requires the target database system is with NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET ...
1
vote
0answers
368 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
0
votes
1answer
130 views

String to timestamp in datastage 9.1.2

Apologies for asking a question that I suppose is very basic. I'm just starting out with Streams. I'm trying to read a sequential files containing a timestamp field and a value field, like this: ...
0
votes
1answer
345 views

How to change password of user in physical standby database Oracle 11g?

Hi I am new to Oracle 11g, I have a physical standby database and a primary database of Oracle 11g on two different servers. I have changed the passwords of users(schemas)like sys, sysman, system, ...
1
vote
1answer
277 views

how to prevent an oracle block corruption from duplicating a database with a few nologging tables

I got a few data corruptions in a test db when I performed a duplicate from a production db where a few tables have the nologging option. Oracle support suggested to turn on the force logging option ...
0
votes
0answers
62 views

Query to return first row after order, for various records

I have a scenario where i need to write a select query which retrieve value from 2 tables by looking at value from 3rd table after applying order by asc. eg: MyTable1 ID keyId flag 1 123 Y ...
1
vote
1answer
81 views

Grant privilege to see column names in null constraint violations when inserting in views

My database has two schemas, one with the actual tables and one that is used by an application. The application schema doesn't have any privileges on the actual tables. Instead there is a view for ...
0
votes
0answers
32 views

Reverse Engineer not updating new Objects Oracle Data Modeler

I Have a problem. I using Oracle Data Modeler. I did reverse engineer for generate physical model. I'm using Oracle Data Modeler. I did reverse engineering of a database(?) to generate the physical ...
3
votes
1answer
350 views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
0
votes
0answers
122 views

SQL Loader Pass Dynamic Value

I'm newer to Oracle so I hope what I am trying to explain makes sense. I am required to used SQL Loader to batch load CSV files. There are about 20 organizations uploading around 15 CSV files each. ...
0
votes
0answers
95 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
1answer
32 views

Configuring Listener on Oracle Server

I am very much new to Oracle environment. I was trying to connect to my Oracle server from another machine on the same network. I found out that I need to configure Oracle Listener on the server that ...
0
votes
1answer
380 views

How do I find gaps between ranges of numbers?

Consider the following table: T_ID | T_START | T_END -----+---------+------ 1 | 0.25 | 0.5 2 | 0.8 | 1 3 | 0.4 | 0.6 4 | 0.2 | 0.3 5 | 0.7 | 0.8 T_ID is unique. ...
0
votes
2answers
747 views

Install JVM for Oracle 11g XE

I am trying to configure JavaMail in my Oracle 11g XE(11.2.0.2.0) database, which requires loadjava utility. But after some research I came to know that XE version of Oracle doesn't provide Java/JVM ...
0
votes
2answers
163 views

How to block external connections for an Oracle database?

I have an oracle database (11g-r2) on my (windows) computer for development, and I am trying to forbid any outside connections. Is there a way to do this?
0
votes
1answer
83 views

Oracle Equivalent of the following SQL Server syntax

I'm looking for the Oracle equivalent of the following SQL Server syntax: select Cast(0x41424344 as varchar(max)) In SQL Server, it selects 'ABCD'. 0x41424344 is the hex of 'ABCD'
0
votes
2answers
566 views

RMAN restore from tag

oracle 11.2.0.4 on redhat 6 SERV1 is prod server running database MAZ, SERV2 is developemt server with no databases on it. i have noted the dbid and last SCN, and then taken a backup on SERV1 using ...
0
votes
0answers
257 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
53 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 ...
3
votes
3answers
123 views

Why does the CBO eliminate `distinct` and not `group by` in this query?

The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by. My questions are: 1) Why does it choose to do so ...
2
votes
0answers
147 views

SELECT stuck giving results from the query result cache, though table has changed

A process that runs every two minutes or so started to misbehave. We noticed it was because the cursor fetched repeatedly 0 rows, even if indeed there were new rows satisfying the conditions in the ...
0
votes
1answer
601 views

Oracle RAC, unable to connect to node vip [closed]

I have a new oracle RAC installation running 11.2.0.4 on redhat 6. I can connect to sqlplus from a client using the node name but it is timing out when i use the node vip eg. first one works, second ...
0
votes
1answer
40 views

Unable to configure oracle 11g net server

I installed Oracle 11g on my Windows Server 2008 workstation. After I try to configure oracle net server with port number 1521. But ONS error say to me as "Use another port number. This port is ...
0
votes
2answers
736 views

ORA-00600 argument description

I am experiencing several ORA-00600 error events, presumably during exceptionally high I/O. Does anyone know where I can lookup the meaning of the following arguments for ktspfmdb:objdchk_kcbnew_3? ...
0
votes
1answer
1k views

Oracle 11gR2 RAC unable to connect to SCAN address [closed]

I'm having a problem with my RAC environment. I am using RedHat 6, Oracle 11.2.0.4 SE. Have a 2 node RAC setup using ASM. When connecting using SQL Developer, i can connect to my service ...
1
vote
1answer
1k views

2 Different Oracle DB Version on same server

Is it possible to install and run 2 different oracle version(11g and 12c)software and db on same server? is it possible then how we can? and if not then why? Thanks Rahul Joshi
0
votes
1answer
56 views

How to create oracle user for more than one version

I have server under RHEL 6.3 installed on that server Oracle 11gr2 and 12c, I want to create user lets say Ahmad but I want this user to be sysdba on both servers, for single server I edit ...
0
votes
3answers
130 views

Is using ASM a pre-requisite for RAC?

I am about to build our first RAC development environment, and as I'm new to RAC and have not used ASM I wanted to keep things as simple as possible. I was under the impression that ASM is a ...
-2
votes
1answer
463 views

LOAD DATA statement not working in Oracle SQL Developer

When I run the statement: LOAD DATA LOCAL INFILE 'C:/path/date.txt' INTO TABLE dimdate FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (date_id, yyyymmdd, fulldate, weekofyear, month, quarter, ...
0
votes
2answers
5k views

Set Oracle Home (install Oracle Home) for Toad 12.6

I had Toad 11.5 and I'm trying to install Toad 12.6, to connect to Oracle 11g on a 64-bit Windows 7 machine. No matter what I try, when I start Toad 12.6, I get the error: "No valid Oracle clients ...
0
votes
0answers
104 views

Dmp file import create triggers error

I've taken a full database backup from one server to be moved to another using the following command (both using Oracle 11g): exp SYSTEM/password@db1 full=y grants=y rows=y file=backup.dmp And now ...
3
votes
1answer
102 views

Re-import packages in Oracle

I'm aware of options to re-import data to tables using table_exists_action and can give options like skip,append or replace for the data. But is there an option to re-import packages (re-create) into ...
1
vote
1answer
349 views

cluster_database TRUE/FALSE what does it mean in Oracle RAC?

I have 3 databases on an Oracle RAC (two nodes) one database which is running with CLUSTER_DATABASE=TRUE on both nodes, the other two runs on each node with CLUSTER_DATABASE=FALSE what does it mean ...
1
vote
1answer
56 views

a table with one column only? [closed]

What is the use of a table with only one column? TableName ---------- Table_PK Say I have a table named Academic and the staff has a positionID but this positionID is: tablePosition ...
0
votes
1answer
518 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 ...
0
votes
0answers
376 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 ...
0
votes
0answers
78 views

Query returns rows for the first time only (Oracle to SQL-Server using ODBC 64bit)

SELECT MJ_GR, GRP_NO, GRP_NAME, EGRP_NAME, sys_guid() rowguid FROM STK_GRP WHERE GRP_NO NOT IN (SELECT "BrandNo" FROM HH_Brand@salesbuzz) AND MJ_GR IN (SELECT ...
-1
votes
2answers
129 views

Query to get the listener.ora path in Oracle 11G

Can anyone help me out with an PL/SQL query to get the path of the listener.ora file in Oracle 11G? I require it to be fetched from the Oracle DB only via an SQL query & not via the Operating ...
0
votes
1answer
161 views

Is the Advanced Security Pack required for windows authenticaion?

Can anyone tell me if the Oracle Advanced Security Pack is required for single sign in under Windows? I've been told we are not licensed for the "Oracle Advanced Security Pack" and need to remove it, ...
0
votes
2answers
2k views

Query to get ORACLE_HOME path in Oracle 11G

Can anyone help me out with an PL/SQL query to get the ORACLE_HOME path in Oracle 11G? I require it to be fetched from the Oracle DB only & not via the Operating system's environment variables. ...
1
vote
0answers
124 views

How can you re-synchronize oracle streams automatically?

I have noticed that when a process of oracle streams (e.g. capture/apply/propagate) is stopped or disabled, and at the same time, changes are made to the source and/or destination ...
0
votes
0answers
86 views

Oracle 10.0 package shows INVALID status in reference on oracle 11g on import with IMPDP

I have imported dump of oracle 10 version with tables, procedures and packages to oracle 11g version. While am not facing any issues with tables and procedures, certain body of packages compiles with ...
0
votes
1answer
41 views

IS-A relation when defining tables

I already asked this question on SO, prior to realizing that there may have been a more suitable place. I am trying to implement an IS-A relationship between tables. Say I have a singer and a band ...
0
votes
1answer
237 views

Oracle DB Point In Time Recovery faulted

I'm testing a procedure via RMAN backup to retore/recover at recover-point, but it fails. Exactly I've executed the first time and it works: Backup: RMAN> backup database INCLUDE CURRENT ...
0
votes
1answer
45 views

Backup files not found in ASM

I have an Oracle database 11gr2 and I have installed Oracle Grid on Oracle Linux 5.9. I have two disk groups. /etc/init.d/oracleasm listdisks : flash_recovery_area DATA The first disk group is ...