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
37 views

SQL Server Replication: Central Subscriber : Loading data in the same table

I have configured a Transactional Replication from Oracle (11g) to SQL Server 2014. I have many clients with the same database and we want to have a Multi-Publisher and only one Subscriber. The ...
0
votes
0answers
237 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
311 views

scipt for rman configuration & database backup in dataguard environment

environment Oracle Solaris 10 -> 5.10 Generic_147147-26 sun4v sparc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit production Oracle Solaris Cluster 3.3u2 for Solaris 10 sparc ...
0
votes
0answers
249 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. ...
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
94 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
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 ...
0
votes
0answers
155 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
215 views

php oracle 11g client side character encoding

My oracle 11g database has utf8 character encoding. And data like ä ö ü etc. Oracle is not on the same host as my php application. The database host can be a Windows 7 machine (in Windows-1252) and a ...
0
votes
0answers
375 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
93 views

Can we Audit Synonym Usage in Oracle?

When Auditing is enabled in Oracle, is there a way to differentiate Private Synonyms access from actual object access? For example If I have Table: S1.My_TABLE Private Synonym: S2.My_TABLE ...
0
votes
0answers
163 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
597 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
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 ...
0
votes
0answers
24 views

write data on multiple storageNodes in oracle NoSQL

I'm using oracle NoSQL on ubuntu (newbie to both) and created 3 storageNodes (with 3 RN on each) in one computer on different ports (5000,6000 and 7000) in a store named mystore. Now I need to write 1 ...
0
votes
0answers
374 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
123 views

How to downgrade Oracle Database from 11gr2 to 10g?

In certain situation i have to downgrade from 11gr2 to 10g but coudn't find right way to downgrade. Can you please give a right direction and possible causes to Database.!
0
votes
0answers
45 views

Oracle MODEL clause: Carry yesterday's leftovers to today

I'm new to the MODEL clause. I'm trying to track volume of worked/unworked to-do items over time. What I HAVE is: work_date born (# of new to-dos per date), robo (# of computer-closed to-dos ...
0
votes
0answers
34 views

Rman append archivelog to backup

is it possible to configure RMAN to append archivelogs to backup automatically? I know that it is possible to issue BAKCUP DATABASE PLUS ARCHIVELOG but, is it possible to configure rman to do this ...
0
votes
0answers
909 views

Slow running Oracle query caused by unnecessary full table scan

I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan. ...
0
votes
0answers
178 views

Script to automatically Identify tablespaces above threshold and bring it back to below threshold value

I am facing all the tablespaces crossing threshold problem in all most all the databases we have in our environment. Because of this much manual effort is been needed. I am able to identify the ...
0
votes
0answers
97 views

In USER_INDEXES but not in USER_IND_COLUMNS

I can't believe I don't know this but what type of index (Oracle) would have an entry in the USER_INDEXES view and not in the USER_IND_COLUMNS view? I see two system entries in USER_INDEXES. Here is ...
0
votes
0answers
162 views

Toad Error using Proxy Connection

I created a login for a user two days ago to an oracle database and altered another user so this new user could do a proxy connection as that user. Everything worked fine that day and the user was ...
0
votes
0answers
40 views

Oracle query optimization time

Is there any way to get how long it takes the optimizer to optimize a query in Oracle?
0
votes
0answers
23 views

RMAN multilple channels

When to use multiple channels in RMAN? Is it effective only when backup is performed to multiple physical disks or tapes? I've found only that every channel is different session opened by RMAN in ...
0
votes
0answers
24 views

hierarchical query

I have a Oracle table that has child and parent rows similar to the example below P_JID has the parent's JID, it can go as far as 4 levels JID P_JID NAME STATUS ...
0
votes
0answers
50 views

Oracle Linked Server on SQL partial insert

I am using OpenQuery to insert into OracleLinkedServer, inside a while loop from while ( @id <= @cnt) Begin INSERT INTO OPENQUERY ( MY_LINKED_SERVER,'SELECT COL1, ...
0
votes
0answers
216 views

Sql*PLUS: Output each field as a new line

Right now the configuration I have is the following: set pagesize 0; SET NULL *NULL*; SPOOL output/output.txt I am outputing the results to a text file in order to run a function on the file to ...
0
votes
0answers
52 views

Monitoring active query step using only pure sql

I would like to monitor actively running query on Oracle. What I mean is to gather info about: Actually running step. (What is currently db engine doing in this query) Actual query plan used in ...
0
votes
0answers
35 views

Difference between @multitenant(SINGLE_TABLE) and @multitenant(VPD) in EclipseLink

Despite the fact I searched a lot in the internet and read many articles, I still be unable to get the difference between the Single_Table strategy and the VPD strategy of EclipseLink. At first I ...
0
votes
0answers
43 views

Configuring management interface on Oracle RAC Listener

i have a SCAN configuration ready & working, but require along with it that each node be separately reachable on a management interface for administrative connections. Listener would have to be ...
0
votes
0answers
55 views

Is this MERGE statement correct?

Question: Is this MERGE statement correct ? emphasis on the ON and AND .. are they rightly placed? MERGE INTO VR_ORDER_LINE USING (SELECT * FROM E14) ON ( VR_LINE.RETAIL_NUMBER = ...
0
votes
0answers
46 views

IO contention on raid5 happens?

I found all of the Oracle data files including redo, control, system, index and everything are in just one directory. And that directory is on Raid 5 disks. In this case do I need to do something ...
0
votes
0answers
358 views

How to find out the maximum number of datafiles per tablespace in Oracle?

According to this Oracle document, the maximum number of database files per tablespace is "Operating system dependent; usually 1022". I know there are queries for some of the other limits mentioned ...
0
votes
0answers
173 views

Portable Method of Deploying Oracle Express on Windows (Ideally without Installer)

I'm looking for a portable way of deploying Oracle Express alongside a C# application on a continuous integration machine running CruiseControl. Ideally I would not have to use the installer - this ...
0
votes
0answers
70 views

Oracle SQL Updating Trigger Statement

Hey all so let me see if I can clearly explain what I would like this trigger to exactly do. First of all the trigger will deal with 2 tables: Delivery Table: DeliveryNum |ItemName | Quantity | ...
0
votes
0answers
88 views

Oracle Linked Server only works with Sql Login

I'm having this weird error for some time. When I login to sql server and use the linked server SELECT * FROM OPENQUERY(MEGA, 'SELECT 1 FROM DUAL') I receive this error OLE DB provider ...
0
votes
0answers
21 views

How to use DMBS_LDAP to select 3 attributes for selected entries

I'm attempting to use DBMS_LDAP to create a procedure in Oracle 11.2.0.2.0 that ultimately updates an employee table from active directory. The attributes I want are sAMAccountName, GivenName, and ...
0
votes
0answers
175 views

Character set for 11.2 version database in oracle for sql* loader

NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ...
0
votes
0answers
91 views

Oracle In-Memory POPULATE_STATUS does not change to completed

I'm experimenting with the Oracle In-memory options for BI purposes, when I'm trying to populate one table to in-memory in the v$im_segments the POPULATE_STATUS does not change to completed, for other ...
0
votes
0answers
153 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
0
votes
0answers
147 views

Is it possible to configure SQLNet / TNS_ADMIN options *without* sqlnet.ora file?

Background: We have an application using OC(C)I via Oracle 11 Instant Client, and we need to set DIAG_SIGHANDLER_ENABLED=FALSE to disable OCI overwriting our own crash handling. However, this is a ...
0
votes
0answers
200 views

Connecting to Oracle database installed in one machine using Toad from another machine

I've an Oracle 12c installed in one machine in the LAN, and am able to access this database from the other machines in the LAN by the java applications using the jdbc thin driver connection ...
0
votes
0answers
27 views

Oracle Mview - Get DML Type

I'm trying to create a replication of a table on a remote server. Table on remote Server : tbl_remote. i created a MATERIALIZED VIEW LOG on tbl_remote. also in my local server i created a ...
0
votes
0answers
153 views

Why only ~half of RAM is used by Oracle on Linux server?

Why Oracle 11.2 would be using only around half of available RAM on RHEL5 box? I have 64GB but 32GB is free which really annoys me. Any memory not used is wasted memory in my books - especially on ...
0
votes
0answers
91 views

how to update 'update trigger fired record's column?

I'm new on this task. I tried to update when I/F table insert after trigger. but it wasn't work.. There is my trigger source like.. create trigger T_ATABLE_I after insert on ATABLE for each row ...
0
votes
0answers
105 views

How to cast as type of a table column?

I have multiple views with union like this: select column1, column2 from mytable union select cast(extcol as nvarchar2(15)), cast(extcol2 as nvarchar2(10)) from exttable But later I change the size ...
0
votes
0answers
54 views

create_index_cost used_bytes vs alloc_bytes

I'm reading a lot about create_index_cost, but I can't seem to find out why there is such a discrepancy between the size of used_bytes and alloc_bytes. I know what each parameter means, but why is ...
0
votes
0answers
55 views

Help with Joins - Cartesian Product

I have 5 regions that each contain 1 store and 1 warehouse. I have a table that contains the regions and corresponding stores and warehouses. I also have a table that lists the inventories of both ...