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
1answer
36 views

Grouping a sequence into ranges

I have 50000 students records in different exam centers having different subjects, in oracle table like this: Center_Code Roll_No PaperCode 10050 1 711 10050 2 ...
3
votes
0answers
255 views

Fast restore of dropped partitions

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps: restore the database (or at least the necessary tablespaces) to the ...
2
votes
1answer
56 views

How to find the number of rows changed by an UPDATE statement in Oracle?

Imagine the following table: id | name | family ---+------+-------- 0 | John | Smith 1 | Mary | Winters 2 | John | Doe and now the following statement: UPDATE t SET name = 'John' WHERE id = 0; ...
1
vote
1answer
47 views

Oracle TNS Permission denied when creating database

I hope you can help me. I need to restore an Oracle 9i (9.2.0.6.0) database on a Windows Server 2003 R2 x64 from a cold backup. I managed to get the 9i installer and the version patch (since I was ...
2
votes
1answer
867 views

Finding why an Oracle listener got automatically restarted

I got the following message in the <listnername>.log file. I want to find out the reason why it got automatically shutdown and restarted again with different PID. 03-MAR-2013 03:00:57 * ...
0
votes
1answer
66 views

oracle conversion variable value to sequence name

please can someone help me? I want to call a sequence from a variable (seq_x) as in the code but I cannot. if :op.qt is not null then select BATCH_X_SEQ into seq_x from kbatchseq where ...
1
vote
1answer
447 views

How to select data out of an Oracle collection/array?

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an ...
0
votes
0answers
51 views

How to optimize this query on oracle?

I have built this query in oracle to get some data i need from the two tables below. At SELECT REGEXP_REPLACE i get the IN_01 as input parameters from the user and it can be much bigger list than ...
10
votes
5answers
22k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
2
votes
1answer
480 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
0
votes
2answers
5k views

sqlplus startup - failure in processing system parameters

I am trying to do the startup command this is what I have done set oracle_sid=ORCL sqlplus /nolog SQL> conn /as sysdba startup It shows this error message, failure in processing system ...
2
votes
1answer
159 views

Estimating tablespace size

I'm trying to estimate the size of a tablespace using these steps: Space Estimations for Schema Objects and I was little confused about some of the steps. In step 3 for calculating the rowsize I ...
0
votes
0answers
29 views

Procedure stuck at a bulk collect

Here's a procedure I'm working on. It's called from another procedure, which uses it in a nested BULK_COLLECT loop on every entry in another table. It gets stuck on "FETCH uniquephysicians BULK ...
0
votes
1answer
23 views

How is the password file accessed when it's on an ASM diskgroup and required to connect to ASM?

When a RAC is turned on, it will try to accessing the OCR registry which is located on ASM. ASM also holds the asm password file. However to open ASM, it needs to access the password file. If the ...
0
votes
1answer
1k views

How to install ROracle on Linux

I am trying to install the ROracle library. This fails: install.packages("/storage/Downloads/ROracle_1.1-11.tar.gz", repos = NULL, type = "source") ** package ‘ROracle’ successfully unpacked and MD5 ...
0
votes
1answer
20 views

Where is oraenv located in solaris?

I had a problem recently with oraenv and oratab and finding out where oraenv is located was a bit challenging. Since it's a question I ask myself often, I wanted to have it here. It may help others.
2
votes
1answer
438 views

How to switch Quiescing to Normal Mode in Oracle?

I have two instance database Oracle, they are ORCL11 and ORCL10,.... I've configured them, it looks like correct. Then, I connect to replication user admin ORCL10 conn repadmin/repadmin@orcl10 And ...
1
vote
3answers
108 views

Restart of rownumber based on count in other table in oracle sql

I have two tables which do not have a common key. I want to do a join on these two tables without a cartesian join. Table 1 has around 40,000 rows (the count varies from day to day in production) ...
0
votes
0answers
23 views

How to find opmnctl status from user other than Oracle user

I just want to use any other user than oracle to check opmnctl status or stop/start the services. I created 1 "test" user with group oinstall and dba group, because oracle belongs to these groups, ...
0
votes
1answer
43 views

Recursive relationship in oracle

I have a table which stores the customer , related customer and count of related customers. The related customer can have again further related customers it's like a recursive relationships. I ...
0
votes
0answers
29 views

How to concatenate using cast(collect()) in Oracle DB?

My current query: select distinct mb.BP_TYPE,mb.ABS,mb.BLD_SEQ_CODE, mb.BPCODE||'~'||(select LISTAGG(mbp.PROCEDURE,' ') within group(order by mbp.LINE,mbp.SLN1,mbp.SLN2) from MIS_BS_BB_PROC mbp ...
0
votes
0answers
26 views

Turn on multi-threading in Oracle for ETL purposes

As you can see from this question, I was able to configure multi-threading in SQL Server and build a workflow in Informatica to multi-thread a job to move data faster into SQL Server. Multi-thread ...
1
vote
1answer
267 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 ...
2
votes
1answer
76 views

Significance of “.WORLD” on end of Oracle connection

What is the significance of having ".WORLD" on the end of an Oracle connection name? I've found things to still connect without that suffix. I've also noticed that both the client tnsnames.ora file ...
3
votes
0answers
26 views

UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?

I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible ...
1
vote
1answer
49 views

PL/SQL block fails in job but succeeds in SQL*Plus

I created a PL/SQL block which gathers certain data from tables in my Oracle 12c database and writes them into a .csv file. I cannot share the whole PL/SQL block but can outline the code --create ...
0
votes
2answers
31 views

IP Change in Oracle database Query

I have a two node RAC database running in OEL 7 server. Now if there is a need to change the IP addresses of the already running server to a new one, will it hamper my functioning of database? What ...
3
votes
3answers
4k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
0
votes
1answer
150 views

migration of oracle xe 10 to xe 11 fails with wrong paths used in the import

I'm following the documentation to upgrade an OracleXE 10.2 database to OracleXE 11.2. The export succeeded without any problem, the import (on another machine with XE 11) fails though. The log file ...
3
votes
3answers
3k views

Permissions needed to call DBMS_DATAPUMP from within package

I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a ...
0
votes
2answers
249 views

Oracle Error: the sga requires more space than was allocated for it

(Oracle SQL 12c running on Windows 10) My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this ...
0
votes
1answer
26 views

Oracle home is missing

I have an Oracle 12c database running in OEL 6.. yesterday due to some OS related issues, my Oracle home software mount-point got erased completely. The only thing that is left is the pfile and data ...
1
vote
1answer
44 views

Backing up database with missing datafile

Is there a way to backup a Oracle 12c database which has a missing datafile? I dont have backups. Database is running in archive log mode. One of the data files is physically missing from the disk. ...
2
votes
1answer
621 views

Internal query on CDEF$ and CON$ very CPU bound

I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the ...
1
vote
1answer
7k views

JDBC pooling Oracle driver Socket read timed out

I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver. Connection pool config: ...
1
vote
3answers
288 views

Import Database from Oracle .DBF files

Recently my Oracle Installation has been crashed and I reinstalled the Oracle again. I have my old Oracle Data files (.DBF files) which have been recovered from old installation. Now, I need to ...
2
votes
1answer
165 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
0
votes
1answer
39 views

Execute immediate call causes 'variable not in select list' error

Execute immediate call causes 'variable not in select list' error hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code: set ...
-2
votes
1answer
32 views

Merging two rows when there is a duplicate value in one of the columns [closed]

Consider a table T1, having columns C1, C2, C3... C1 C2 C3 CAD PF1 100 CAD PF2 200 USD PF3 300 Result should be C1 C2 ...
4
votes
2answers
7k views

How can I view what NLS settings are active for other session?

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
1
vote
1answer
135 views

Type with nested table in oracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a ...
0
votes
0answers
27 views

How to split one column data into multiple columns [duplicate]

I have a table called Items. First column(Items) have n number of Split Items. select * from items ITEMS SPLIT ITEMS 1111 2525 1111 4587 1111 9874 1111 8787 ... Instead of giving ...
3
votes
1answer
185 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
0
votes
3answers
219 views

Replacing the results of a row

I'm working with Microsoft Reporting Services 2008. In our database, we have a small group of confidential students that need to be taken into account for several queries. If they are a confidential ...
3
votes
2answers
75 views

Pass (comma separated) -yet single parameter to cursor

I have to get selection for a given string (with comma separated values) passed to the cursor. There can be multiple values that could be passed via single variable. My code is somewhat like: CURSOR ...
2
votes
2answers
868 views

Oracle Data Pump Import imports no records

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported. Processing object type ...
0
votes
1answer
151 views

ORA02063 error while connecting to remote databse

I have connected to remote database using DB link. From prod databases it works fine but from test env reporting error invalid username/password. Please help.
2
votes
3answers
77 views

Oracle : Redo / Undo for nologging table + Append + archivelog

I'm trying to replicate a table from an external Oracle DB to my system using a DB Link. First i tried doing it using an mview but it caused alot of redo/undo(I did the same steps as below but it ...
0
votes
2answers
136 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
2
votes
2answers
2k views

Repopulating Oracle tables using data from Excel/CSV file

I'm developing a Java EE application that utilizes Hibernate/JPA. Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle ...