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
1answer
54 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 ...
4
votes
1answer
151 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
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
3answers
142 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
145 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
72 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
119 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
37 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
41 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
49 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
61 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
86 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 ...
1
vote
2answers
60 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
41 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
56 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
74 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
85 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
36 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
115 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
66 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
38 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 ...
3
votes
1answer
72 views

In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:

In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable: define my_schema_name = 'schema_1'; CREATE OR REPLACE PROCEDURE ...
0
votes
1answer
24 views

What is the path of oratab on solaris?

What is the path of oratab on solaris? This is actually a question I google a lot and switching from environment to another, I tend to mix them up and forget. Today I googled it again and I wanted ...
2
votes
1answer
72 views

fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

As i am from mathematics background and require to do some huge permutations to start in my work kind help. If i have a table by name sample1 with n columns what is fastest way to get all the ...
2
votes
1answer
41 views

I need to update a Oracle procedure via a .sql script

I need to update a Oracle procedure via a .sql script. I know the procedure works because it is currently running on our server, but I need to update the procedure on a client's box. I need to be ...
1
vote
1answer
62 views

Tracing failed login attempts in Oracle

I have some users who are logging in with a wrong password and their accounts get locked. Now I need to find the source of the failed login attempt. The machine, username etc. Is it possible to find ...
1
vote
3answers
58 views

DB cloning on daily basis

I have a requirement from one of my teams which is database copy (cloning) from production to my test server on daily basis at a particular time. I know this is a very simple process which every DBA ...
2
votes
1answer
35 views

help in correcting the syntax error of the below create table with as with multiple select

Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table. create table newsample AS ( ...
0
votes
0answers
31 views

Oracle 11g databse control with Enterprise manager on Windows 7 64 bit error

I am trying to install Oracle 11g on Windows 7 64 bit. I get Successful installation until when I get to connect to the database I got a message error.I login as a Username "Sys" with password and ...
0
votes
0answers
9 views

How to load a .dmp into PostgreSQL using pgadminIII [duplicate]

I have a dump file from oracle that I wish to load into pgSQL database. I can use psycopg2 or pgAdminIII to accomplish this. Any tutorials or references would be much appreciated as I cant seem to ...
3
votes
2answers
51 views

Oracle import dmp file: ora exceptions

I'm trying to import a .dmp file to my oracle RAC database using the following command: C:\app\ptmp>impdp [user]/[password]@'DEVRAC-SCAN:1521/DB' DUMPFILE=UPG_E_7d291ff.dmp ...
0
votes
0answers
9 views

Oracle Workspace Manager: Accessing specific workspace in different schema

There are specific methods to grant permissions on versioned tables (dbms_wm.GrantWorkspacePriv()), so GRANTs can be given on workspace level. Unless I misunderstood the concept, a workspace is part ...
2
votes
0answers
21 views

Oracle Workspace Manager: How is DDL applied to versioned tables?

We're considering the use of Oracle Workspaces for development/test environments. While its benefits are pretty clear concerning DML and data in general, my Google-Fu obviously wasn't sufficient ...
5
votes
1answer
34 views

Are there any potential problems with running distinct Oracle rman archivelog backups of the same database at the same time?

The scenario is that RMAN archivelog backups are scheduled to tape (TSM) every 2 hours. The scheduling ensures that such backups are run in a mutually exclusive fashion. A separate crontab based ...
3
votes
2answers
67 views

Database not able to startup showing control file is inconsistent with another

Our windows server 2008 R2 is shutdown due to power failure. When I am trying to startup Oracle 11g database its shows an error. SQL> startup ORA-01081: cannot start already-running ORACLE - shut ...
2
votes
0answers
46 views

Is PostgreSQL text really that much faster than Oracle CLOB? [closed]

I have an oracle 10g table with 1 clob column. The clobs aren't very large mostly around 2-8 kb. However reading many rows takes an excruciating amount of time. Even with storage in row enabled. I ...
3
votes
1answer
116 views

I want to change oracle “homedir” how to do?

A good link as answer is ok. I want to change "homedir" or "basedir" of oracle db from /oracle to /var/oracle. How to do? I have set the oracle unix variables($ORACLE_HOME,$ORACLE_BASE) with the new ...
3
votes
0answers
78 views

Oracle index on column with length > maximum varchar2 length

Description: We have a table on PostgreSQL 9.3 and we migrate it (data & structure) to Oracle 11.0.2.4. Here is our table: create table cstb_temp_log ( id NUMBER PRIMARY KEY, log_info ...
0
votes
0answers
26 views

Does RECO always resolve in-doubt transactions ? (Oracle)

I am working on an application which uses a Oracle 11gR2 database in order to fetch and store data. The application is written in C/C++ and some Pro*C, which takes care of everything related to the ...