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

Recreating an artificial Oracle Flashback Archive

I have an databases where changes to data were tracked by adding auditing timestamp columns to the end of each row. I want to migrate to Oracle's Total recall or Flashback archive so I need to trick ...
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
145 views

Buffer too small for CLOB to CHAR or BLOB to RAW

I am trying to select a clob from database to put it in a jasper report. The query is: <queryString language="SQL"> <![CDATA[ select to_char(PLAN_DESCRIPTION) as PLAN_DESCRIPTION ...
0
votes
0answers
22 views

Oracle: Is it safe to temporarily lock all users not belonging to SYS_GROUP?

I know you can startup an instance in restricted mode, but I need at least one other user account besides SYS and SYSTEM to have remote access to the instance, because I need to connect with that user ...
0
votes
0answers
98 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
66 views

Clob (Swift message) data in Oracle table

I have swift data in clob column and want to insert this data in multiple columns in different table. create table test_swift (id number , block4 clob ); insert into test_swift values (123, ...
0
votes
0answers
64 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
0
votes
0answers
124 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
907 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
238 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
256 views

(Oracle PL/SQL) How to capture multiple error lines in exception section?

begin some SQL...; exception send_SMS('SQL error: '||SQLERRM); end; This sometimes reports: ORA-02063: preceding line from DBLINK_NAME How to capture the preceding error lines in a PL/SQL ...
0
votes
0answers
103 views

Oracle 11g installation on fedora 20 - stuck at step6 - Oracle-base.com installation procedure

I am trying to install Oracle Database 11g Release 2 (11.2.0.1) On Fedora 20 (F20)Oracle 11g in my laptop and I am following the steps provided in this Link But it is getting stuck after step 6. Can ...
0
votes
0answers
28 views

what is the problem of this script on Oracle about restore and recover database

this script to restore and recover the target database when we : Removed the target database to simulate and setup a disaster recovery situation, the instance is shutdown, and all the configuration ...
0
votes
0answers
15 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
126 views

In Oracle, when you update an SDO_GEOMETRY column with the same value is the MDSYS SPATIAL_INDEX updated?

Because generating and updating a spatial field is relatively expensive we want to avoid it when possible. Here is the relevant bit of our schema: +---------+---------------+ |LATITUDE ...
0
votes
0answers
40 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
299 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
428 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
289 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
140 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
106 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
310 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
177 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
232 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
452 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
103 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
216 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
764 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
444 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
141 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
1k 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
211 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
108 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
42 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
27 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
56 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
49 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
471 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
230 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
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
206 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
175 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
175 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
223 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
177 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
96 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
124 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
57 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 ...