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

UTL_TCP how to monitor number of connections?

I have a leak somewhere in the number of UTL_SMTP connections being made and closed. There is an underlying limit of 16 from its reliance on UTL_TCP. I'd like to be able to monitor the connection ...
0
votes
2answers
54 views

Checkpoint during Backup

Can database process make automatic checkpoint when I'm in process of copying files during ALTER DATABASE BEGIN BACKUP; and ALTER DATABASE END BACKUP; You can assume that process of copying ...
1
vote
1answer
62 views

How to correct this oracle error?

This query SELECT praogrupos0_.ID_GRUPO_SEDE "ID1_209_", praogrupos0_.ID_EJERCICIO "ID6_209_", praogrupos0_.ID_INSTITUCION_LIQUIDADORA "ID7_209_", praogrupos0_.ID_PERIODO "ID8_209_", ...
0
votes
0answers
79 views

Performance tuning of a SQL query

Problem statement: 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 ...
0
votes
2answers
91 views

Trigger to compare inserted value with value from another table

I have two tables: Stockitem (serialno, qty) serialno is the primary key and Order(serialno,orderno,qty) orderno and serialno are the primary keys. I want to create a trigger in order to ensure ...
0
votes
0answers
28 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
1
vote
1answer
36 views

DB2 Naming Standards - Similar to OFA

I'm an Oracle DBA getting started on DB2. On Oracle we have a document called Optimal Flexible Architecture that sets a group of naming standards, like what should be the disk name for the database. ...
0
votes
2answers
54 views

Using a query in an update

I have a query which generates a result set (key, a, b). I want to use an update statement where, for each entry in a table with Tkey = key, set ColA = a, ColB = b. How would I do this? Right now ...
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; ...
0
votes
2answers
44 views

How to transpose rows to column in datatabse [duplicate]

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below Col_ID Col_Group Col_Price 1 AA 0 1 BB 2000 1 ...
1
vote
1answer
68 views

Error at line 1, column 25: ORA-00923: FROM keyword not found where expected

I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database. Could anyone please help me convert it so it is correct for normal ...
0
votes
2answers
53 views

Can Oracle databases start automatically when Windows machine is booted/rebooted?

In one of my client machines every time Windows starts we have to start the Oracle database manually. Is there a solution/ command to start the database automatically when the machine is ...
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 ...
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 ...
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 ...
4
votes
1answer
85 views

Result on execution of any SQL is number 2

After successful execution of any sql in oracle sql command line it shows 2 like i want to see the table or description of any table it show 2 instead of showing the table. SQL> select * from dual ...
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 ...
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
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
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.
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
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 ...
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 ...
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 ...
6
votes
2answers
149 views

Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below: create table test_table1 (col1 nvarchar2(100)); table TEST_TABLE1 ...
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 ...
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. ...
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
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 ...
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
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
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 ...
1
vote
1answer
23 views

Oracle 12 - Reporting on Roles

Is there a way to report on all the custom roles and/or responsibilities created within Oracle, Outside of SQL? I am looking for a dashboard that shows how many have x role, y role that could be ...
1
vote
1answer
25 views

sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows

While i am trying to connect with with database by the command sqlplus /nolog than the error message is coming "sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows" ...
0
votes
1answer
55 views

How to disable all password requirements in Oracle?

When I try to create a new database using DBCA it stops me because my password isn't complex enough. I am working with Oracle in a VirtualBox VM on my own laptop, so I don't need the password ...
4
votes
2answers
97 views

Oracle sort order changed

We recently deployed a new environment with a newer version of Oracle (12c) instead of 11r2. Only recently I noticed my data returned is sorted differently. Instead of having small letters, capital ...
3
votes
1answer
38 views

How to convert following mysql query to oracle?

I have a mysql query like following, Column time contains date in long format. SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time, SUM(CreatedInstanceCount) AS ...
1
vote
1answer
43 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
1
vote
2answers
56 views

Oracle DB export, possible charset conversion if NLS_LANG not set

I need to export an Oracle database from one server and import it on another server. But I am getting the following information message at the beginning: Connected to: Oracle Database 10g Release ...
1
vote
1answer
44 views

Oracle how to manage the flashback log space? [closed]

I set db_flashback_retention_target=1440min,Why the flashback log kept 7 days? Thanks! closed as unclear what you're asking by Phil, Michael Green, Tom V, Shanky, Andriy M Jan 5 at 12:53 Why the ...
1
vote
1answer
33 views

Oracle RAC: Can two transactions share an SCN?

Under what conditions (if any) can two transactions share an SCN? To put another way, is there a guarantee that if several DML statements share an SCN they are guaranteed to be part of the same ...
1
vote
0answers
42 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...
1
vote
1answer
33 views

Oracle standby databases monitoring

I have many databases for which I have configured data guard setup. When it comes to monitoring the standby's, I use OEM to check the lag and I have enabled mail alerts as well as and when gap is more ...
1
vote
2answers
36 views

Oracle binaries query

im not sure if this would be considered as a question or not. I would delete it if not counted for. But just wanted to know, where can I get Oracle 12.1.0.1 software binaries. ...
0
votes
1answer
36 views

How to recover the data from Redo Log Group which has not been archived yet

I have a normal situation where we have 3 Redo Groups (A,B & C) with 1 member (100 MB) each and database is also in Archive log Mode. In normal scenarios whenever there is redo log switch, there ...
0
votes
1answer
25 views

How to update scheduler job credentials in oracle enterprise manager?

I have a job scheduled in oracle 11g which runs on daily basis. Job is running with User "user_dba" (unix server user). The job is failing from the day I changed the password for the user "user_dba". ...