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
2answers
39 views

Causing ORA errors in a program using JDBC. How can I find what's causing them?

So I'm building a java app to interface with the company's database. Whenever it causes an ORA error, it just prints something like the following to system output: ORA-00911: invalid character Is ...
1
vote
0answers
491 views

TNS-12500: TNS:listener failed to start a dedicated server process Oracle9i Database on Windows 2003 64x server

My client wants to move his database from Windows 2000 server to Windows 2003 Enterprise Edition 64x server. I have created the Windows servers in VMware and installed the Oracle 9i databases on it. ...
1
vote
1answer
1k views

Special characters in Oracle utl_http request

When calling a web service using utl_http, the calls fail when the SOAP envelope contains characters like ¿, ± or º. IS http_req utl_http.req; http_resp utl_http.resp; request_env ...
1
vote
1answer
43 views

Union of data from all tables with name containing a substring

I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the ...
5
votes
4answers
11k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
1
vote
2answers
122 views

How to populate another column with a sequence value generated for an identity column?

I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it ...
0
votes
1answer
43 views

Error with my trigger. What should I do?

This trigger fails when it's called: create or replace TRIGGER UNIQUE_PHYSICIAN_TRG BEFORE INSERT ON UNIQUE_PHYSICIAN FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> ...
1
vote
0answers
50 views

oracle DB connection - hostdef extension doesn't exist

I am trying to connect to an Oracle DB through ERwin, yet I keep getting this error message: I know that the DB exists, as my coworkers are accessing it. The Username and Password are also correct, ...
1
vote
1answer
167 views

all_tables and dba_segments giving different space occupied by the same tables

When I ran the below queries they gave different space occupied by the same table: select bytes/1024/1024 as space, owner from dba_segments where segment_name='PARAMETER_STATUS'; SPACE OWNER ...
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 ...
2
votes
2answers
5k views

Oracle.exe (SHAD) process keeps increasing

I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener ...
2
votes
1answer
157 views

Filtering by minimum value per group problem

I have a select statement, which when simplified produces the following: +------------+----------+-----+ | Group | Name | ID | | vegetables | potatoes | 1 | | vegetables | carrots | 6 ...
0
votes
1answer
854 views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
0
votes
1answer
52 views

Connecting 4 tables - finding some differences between them

I have 4 tables in a Oracle database: SOL_PUBLIC.SOL_CHEST@RACDB (PUB): ID ... 100 ... 200 ... 300 ... SOL_PUBLIC.COORD@RACDB (PUB): ID FK_ID_CHEST x y 1 100 11 11 2 200 ...
0
votes
0answers
372 views

Installing oracle 9i on windows 7

Is it possible to install oracle 9i on windows 7 32 bit? And where can i download the standalone oracle http server?
0
votes
1answer
2k views

SQL Developer - trying to import an excel file into a table

I'm trying to import an excel file into a table. The excel file has dates in the following format: 20/08/2008 12:00:00 AM and I am telling SQLDEV the following: DD/MM/YYYY H24:MI:SS AM To import the ...
2
votes
1answer
311 views

Modify LISTAGG query for 10g

below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated. SELECT ...
5
votes
1answer
608 views

Find columns referenced by virtual column expression

Does anyone know of any Oracle dictionary views that hold references of 'real' columns to virtual columns? For example lets say I have a table like this: create table t ( c1 varchar2(5) ,c2 as (c1 ...
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 ...
1
vote
1answer
19k views

Oracle: Output not shown even if SERVEROUTPUT variable set to ON [closed]

I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON. I have the following annonymous block code: DECLARE v_rec_count number:=0; BEGIN for rec in ...
0
votes
3answers
934 views

“sqlplus as sysdba” startup error

My setup: Oracle 11g 32G RAM 8 CUP threads Three oracle instances have been running. But running this: sqlplus "/as sysdba" gives me a prompt: Connected to an idle instance. When I issue ...
1
vote
1answer
8k views

Using wm_concat to concatenate rows, but in an order determined by another column

Let's say I have 3 columns: p_id, description, order_by. I am trying to do the following: I would like to concatenate the description for all like p_id values. So we are talking a group by p_id ...
1
vote
2answers
441 views

Oracle connection suddenly refused on windows 8

I am having all kinds of connection issues with sql developer all of a sudden. It was working till yesterday and stopped working on windows 8 machine. i am still able to connect via command prompt. ...
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
1answer
61 views

Logging into Oracle

I am new to Oracle (coming from a SQL Server world). This is going to probably be some simple newbie question. I just did a fresh install of Oracle 12c Express. The install was successful, but now I ...
0
votes
1answer
94 views

How can we drop a constraint without losing the automatically created index?

How can we drop a constraint without losing the automatically created index? If it's possible using a single query.
0
votes
1answer
162 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
2answers
564 views

Faulty oracle11g regexp_replace on removing duplicates

I have a comma separated list of values (as one single string), which contains duplicates, and I want to remove them. I tried with REGEXP_REPLACE but for certain values it gives me a wrong result: /* ...
-10
votes
1answer
340 views

What are the edges of typical (or specific) paid databases like oracle of mysql compared to the free oness? [closed]

Note: I am building a startup. I am currently using mysql. I am pretty much stuck. Others recommend other free databases like mongodb. I am seriously considering paid one IF and only IF it gets better ...
0
votes
0answers
301 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 ...
1
vote
1answer
67 views

How to update table1 field by using other table and function

I have two tables and one function: Table1 contains shop_code,batch_id shop_code| batch_id | regstriy_id 123 | 100 | 12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name ...
1
vote
0answers
405 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 ...
1
vote
1answer
421 views

SQLPlus Command Line In-line Set Commands

I have the need to run linux command line direct query, including SET options. The query by itself runs fine. However, when I attempt to add set commands, it fails. (my alias 'db' connects to ...
0
votes
0answers
433 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
1answer
360 views

Reason of ORA-00604 - ORA-00904: on online Redefining

I made Redefining Tables Online on a table . It worked fine but later when I tried to truncate the table I got this error . ORA-00604: error occurred at recursive SQL level 1 ORA-00904: ...
1
vote
0answers
261 views

Pass in dynamic value to Oracle job scheduler [closed]

How does one pass a dynamic value into the oracle job scheduler when the underlying command is a stored procedure? example procedure: create procedure foo (bar varchar2) ... Job creation in ...
1
vote
1answer
531 views

Oracle Alternative to EXISTS EXCEPT

I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here: Comparing Columns that can contain NULLS - is there a more elegant way? I can't get this ...
0
votes
0answers
68 views

Database too slow after CHARSET Conversion [closed]

I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset. The conversion is made without any ...
1
vote
0answers
78 views

Oracle update query sequence of actions on DB side [closed]

I searched online but could not find particularly consolidated source of what happens when you execute an update query. I know that there are a few actions happening like create redo data and logs ...
0
votes
1answer
32 views

number field in varchar2 field

I have a table with a number field (non-null). I have another table with a varchar2 field. Examples: Users UID,Name 123,Mickey 41128,Joe 43,Alice Groups UIDS,Name (123,43),Children (43),Admins ...
0
votes
1answer
285 views

How to resolve error ORA-19802 when starting up an Oracle database instance?

I tried starting up my instance and received the error ORA-19802. Cannot use db_recovery_file_dest without db_recovery_file_dest_size. How can I resolve the problem?
2
votes
0answers
298 views

Oracle faster overlap check [closed]

We've a table with two columns beginrange and endrange, which contain hex values. To this table no overlapping ranges should be inputted. We had implemented the check by having a composite functional ...
1
vote
1answer
111 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
291 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. ...
3
votes
1answer
481 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
142 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. ...
1
vote
1answer
67 views

Oracle 10 xe; backing up oradata folder enough to recreate a duplicate later?

Let us say, I backup of the the oradata folder. Is that enough to recreate the exact same database? E.g., I reinstall oracle xe 10 on the same pc, using the same installer, into the same folder, and ...
1
vote
1answer
110 views

Oracle: Recover source code/table structures from “utterly wiped out” development database

Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain ...
4
votes
2answers
12k views

Estimate a Database Size

I have to do some project, and need to estimate the DB size. I'm gonna to use Oracle in the project, because will have a lot of transactions and size. But in the "presentation" I need to specify, in 5 ...
0
votes
1answer
51 views

A question about oracle upgrade from 12.0.0.1 to 12.0.0.2 using dbua

I found this guide for upgrade. After the upgrade, can I delete safely the old DB product dir /var/oracle/app/oracle/product/12.1.0 ?