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

-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
80 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
28 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
99 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
45 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
64 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
34 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
26 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". ...
1
vote
1answer
55 views

CREATE TABLE AS SELECT creates non-nullable columns

I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link. Oracle creates all these tables with non-nullable columns and that causes me ...
5
votes
1answer
167 views

Oracle crashing on CentOS

Here is alert_XE.log: kkjcre1p: unable to spawn jobq slave process Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_cjq0_23146.trc: Process J000 died, see its trace file kkjcre1p: unable to ...
2
votes
1answer
77 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
3answers
143 views

Difference between commit and checkpoint

I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article But they are advanced for my understanding. I mean both appear to ...
3
votes
1answer
148 views

How to merge 2 tables in Oracle

I have 1 big table with 25 milions of rows and i have to merge data of another table with 500,000 rows into the big table. the rows of both tables have the same columns and i have to check if the key ...
3
votes
0answers
73 views

Oracle replicat is abending when oracle is having special char while loading to teradata

I am doing replication from oracle to teradata where oracle is having below charter in one column. ->->-> ACSII value for this char is 26 . I need to replace it with null . In the charmap we have ...
1
vote
1answer
141 views

ORA-01122: database file 1 failed verification check

I moved data file and database started fine but I did the following steps: SQL> shutdown SQL> startup nomount SQL> alter system set control_files='/var/oracle/oradata/control.dbf' ...
1
vote
0answers
25 views

Fail to perfom static registration of nomount instance

I'm trying to make standby to database. I've insalled Oracle 11g database on virtual machine (host - oratest). Standby is on virtual machine too (standbysrv). When I'm trying to connect from source ...
3
votes
1answer
38 views

About Global Temporary Table and Stored Procedure in Oracle

I am confusing with the global temporary table in Oracle: For example, I want to write a SP MySP which can be used by 5 users at the same time. CREATE GLOBAL TEMPORARY TABLE temp (ID NUMBER, ...
2
votes
1answer
42 views

Oracle random number as default column value

I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql create table table_name ( column integer NOT NULL ...
0
votes
1answer
52 views

Read-only user query in Oracle

I have been asked to create a read only user in a Oracle database 12c. The read only user must have read only grants only to the objects of select schemas, which I created and handed over. But now ...
1
vote
2answers
62 views

How can I do the following task in Oracle

I wish to declare a variable, and create a temp table, and insert some data into the table, and show the data. BTW, I don't what this done in a new stored procedure: DECLARE I NUMBER := 1; ...
4
votes
1answer
41 views

Encrypt data with DES_CBC_PKCS5 in DB2

I am using encryption algo DES/CBC/PKCS5Padding. How do I encrypt data using this in DB2? In Pl/SQL I used DBMS_CRYPTO package with DES_CBC_PKCS5 from DBMS_CRYPTO Block Cipher Suites - ...
2
votes
2answers
55 views

Query to identify people exceeding a particular amount within an interval of 6 months

I have tried searching in Google but am just not finding the right post. I have this table with fields trans_date, CID (which is customerid), and amount. How do I find the CID exceeding an amount of ...
4
votes
1answer
62 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...
4
votes
3answers
87 views

Does an order by clause have a negative effect on performance?

I couldn't find the answer by googling. I only over heard a senior dev telling a junior dev that using an order by clause has a negative effect on performance and he shouldn't use it(Oracle). In ...
2
votes
2answers
66 views

ORA-01180 when restoring the database

I get the following error message when trying to restore the database from backup using RMAN. rman target / RMAN> set dbid=130474031 RMAN> configure device type 'sbt_tape' clear; RMAN> ...
0
votes
0answers
42 views

ORA-01180 when restoring the database [duplicate]

I get the following error message when trying to restore the database from backup using RMAN. rman target / RMAN> set dbid=130474031 RMAN> configure device type 'sbt_tape' clear; RMAN> ...
-2
votes
2answers
43 views

Remove oracle home base in windows

How to manually remove multiple the oracle home base in windows inorder to start with a fresh install of oracle server. I had unsuccessful attempts in deleting it using the Universal Installer as it ...
0
votes
1answer
47 views

kind help to correct syntax [closed]

create table first as (SELECT * FROM edge1 h1 inner join edge1 h2 ON ( h1.e1=h2.e1 AND h2.e1 != h1.e2 AND h2.e1 != h1.e3 AND h2.e1 != h1.e4 AND h2.e1 != ...
1
vote
3answers
58 views

What procedure can be used to change system statistics in a production environment

System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first ...
3
votes
1answer
75 views

Create table in Oracle as select from SQL Server - varchar size

I am trying to create table in Oracle 11g as select from a table in SQL Server 2012 via database link. The table in SQL Server contains column: hslakkis varchar(3) In Oracle it created the table ...
0
votes
1answer
32 views

Oracle GROUP BY issue

I have this query: Select To_Char(Order.Date, 'IW') As "Week No", Order.User As "User", Count(Order.OrderNo) As "Total Orders", Sum(Order_Totals.Qty) As "Total Items", From Order ...
3
votes
3answers
87 views

How can I optimise an Update on a large table?

I am using Oracle 11g, I have to update 7 fields on a table with 14 millions of rows, the updated columns get the new value from functions, the update looks like: update hslak set HSLAKSTS = ...
0
votes
2answers
37 views

Disable MEMORY_TARGET after being locked out of database?

I'm setting up a local development environment in a virtual machine in which I installed Oracle XE among other software. When I initially set up the virtual machine, the RAM was set to 4Gb. In this ...
0
votes
1answer
26 views

Assistance creating SQL query in Oracle to split by weeks

I am building a report to show order statistics by week within our company. Here is the SQL query that I've created: Select Sum(ORDER_TOTALS.ORDER_TOT_NET) As "ORDER_TOT_NET", ...
1
vote
0answers
121 views

Why doesn't “As SYSDBA” work from SQL Developer?

I am a SQL Server developer/DBA consultant who is helping out a customer who recently lost their Oracle DBA. I have done development on Oracle, but not much DBA work, and not in multi-homed ...
0
votes
1answer
45 views

Is it possible to connect to Oracle Database through OCI using a reverse proxy?

Is it possible to connect to Oracle Database (11g) through OCI using some kind of reverse proxy? Right now, I have to connect to a virtual machine using RDP, this VM is hosted in my client network, ...
3
votes
1answer
39 views

Error in the code shown below - Why is it

Why does the following code show an error SELECT TO_DATE('Today is the Seventeenth of December, Twenty Fifteen','"Today is the" Ddspth "of" Month, Year') from dual
0
votes
1answer
74 views

Non-RAC single instance with ASM startup query

How to start a non-rac single instance with ASM file system. As in the case of RAC after reboot of server, the ASM and CRS resources startup automatically.. but with non-RAC but with ASM storage, what ...
3
votes
0answers
66 views

Help with query to find intersection between arbitary rows of a table under conditions

CREATE TABLE "SYSTEM"."SAMPLE" ("E1" VARCHAR2(20 BYTE), "E2" VARCHAR2(20 BYTE), "E3" VARCHAR2(20 BYTE), "E4" VARCHAR2(20 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 ...
-1
votes
1answer
39 views

Display Error getting while installing oracle 9i on centos 64 bit

I want to install Oracle 9i on Centos using only command line. I tried to run installer using command line by silent but getting error as below. [oracle@XXXXX]$ ./runInstaller -silent DISPLAY not ...