Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.

learn more… | top users | synonyms

1
vote
1answer
35 views

Nested, inline pivot table (oracle)

I'm trying to go from a table like this: ID | NAME | METADATA ------------------------------- 1 | test | '{"a":"x","b":"y"}' where the metadata column is a JSON string. What I'd like to end up ...
0
votes
1answer
232 views

How do I create an Oracle 11g user with read access to all tables/schemas?

I need to create a user that has read access to all the schemas. Do they just need Select priviliges or is there more that needs to be done?
0
votes
1answer
41 views

Cannot create user using administrator credentials in Oracle

I am trying to get some hands-on experience with ORACLE and I installed Oracle Database 11g Express Edition on my PC at home. I logged into the 'Application Express' by entering TEST in the Workspace ...
1
vote
2answers
43 views

How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

This query: select distinct profile from dba_profiles; Returns 3 rows: default default1 default2. I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query ...
0
votes
1answer
13 views

Pointing DB to new Oracle home in primary and standby oracle database

I needed to apply a security patch to production database. To minimize the outage time, I cloned the oracle home and applied the patch to this newly created oracle home. With production DB there is a ...
2
votes
3answers
1k views

Why do shadow processes exist?

Oracle architecture has two types of process: server process or foreground process and background process, but: Some people use it to refer to foreground processes, others use it for background ...
0
votes
2answers
66 views

Can Oracle 11g Express Edition Work on Parallels

I am using a Mac and need to use Oracle 11g. I have Parallels installed with Windows 8.1 on it. Do I need to download anything else or just download Oracle 11g Express Edition and run it?
1
vote
2answers
55 views

Updating duplicate records with different data

I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this ...
2
votes
1answer
2k views

Insert into table from a query to a linked database hangs

I have a schema with some database links to other schemas that we use to pull data from. We pull the data into a staging table, massage it, and then do some merges into our schema/database. The data ...
0
votes
1answer
38 views

ORA-01031: insufficient privileges while connecting to standby

I configured standby database and it worked for a while but now I noticed that redo logs thread on standby much less that on the primary. After researching alert log and trace files, I figured out ...
0
votes
0answers
36 views

When the query was last executed?

Is it possible to find which queries were run a fortnight ago, and the time they were executed in Oracle 11g? (answers for MySQL also welcome) I found this when I searched: How to know when a query ...
0
votes
1answer
40 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 ...
-2
votes
1answer
27 views

plsql block not executing

I am unable to execute this block. it just compiles. what statement I have togive to see output. where I am doing error ? I clicked on output button also
0
votes
2answers
160 views

Database link from Local Oracle 11g to Oracle Database Cloud Service 11g

We are using Oracle database from Oracle Database Cloud Service, now we are not able to connect Cloud db from Local oracle db. Somehow we created link using below commnd, CREATE DATABASE LINK link_1 ...
2
votes
1answer
153 views

Oracle Ref Partitioning: Deadlock due to child table row migration

My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc. ORDERS table is LIST partitioned on STATUS column and all ...
0
votes
0answers
21 views

Error while connecting from shell script, oracle user not found, even if it's already defined [closed]

su - oracle -c "sqlplus "$i"@C5FTP1 < /home/tomcat/sc1/run.sql" I got oracle user doesn't exist error, while I tried to run the script.
1
vote
2answers
55 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 ...
2
votes
2answers
1k views

Unable to start Oracle Database Server : Get an error as I try to start it

I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop : but as I click this icon I see this dialog box : What is it ? How do I ...
2
votes
1answer
2k views

Replicate or Call java encryption function in Oracle pl/sql

I am trying to replicate encryption/decryption method present in java in Oracle DB, so that data encrypted in java can be decrypted through Oracle Function. Below is java code: package com.encr; ...
2
votes
1answer
567 views

Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

We are using Oracle 11g, 11.2.0.3. We know a record exists in a table but a select is not returning it for some odd reason. The execution plans the general query, not forcing any index, shows that ...
0
votes
1answer
35 views

How can sqlplus knows where ORACLE_SID point?

As I understand, SID is unique value to identify an Oracle database instance. And I can set default database instance sqlplus use by changing ORACLE_SID environment variable. But how can sqlplus ...
1
vote
1answer
58 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
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
2answers
504 views

Using output value of column from function for another column in SQL*Loader

I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here. Please ...
0
votes
0answers
27 views

How does Toad detect installed client?

I installed Oracle client and Toad and have worked fine. But When I install Oracle server also in the same system. The server and the client seem to conflict on the path something. I decided to ...
6
votes
2answers
152 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 ...
0
votes
2answers
46 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 ...
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 = ...
0
votes
0answers
28 views

Linked Server to Oracle seems to be slow

we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider. The enabled options for provider are: Dynamic parameter Nested queries ...
0
votes
0answers
32 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 ...
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 ...
0
votes
1answer
16 views

Oracle connection through visual studio

I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the ...
1
vote
2answers
27 views

Oracle how do i see who connected to my database

I am using oracle 11g and I would like to see who connected to my database some hours ago. In what file can I see those who have connected to my database through a network connection? Thanks
1
vote
1answer
180 views

sqlplus not connecting to Oracle XE 11g

I installed Oracle XE 11g on my Ubuntu in vmware fusion. The oracle installation is all went fine but I am not able to connect the oracle database using sqlplus. I am getting this exception: $ ...
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 ...
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
56 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 ...
0
votes
2answers
224 views

Control file creation failed

I lost all the control files and I don't have backup for it. Control file creation failed SQL> CREATE CONTROLFILE SET DATABASE "UAT02" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 ...
1
vote
1answer
203 views

Keeping messages in a backup table after dequeue is completed

I would like to transfer the enqueued messages to a backup table after they have been dequeued. Currently we are using DBMS_AQ.DEQUEUE with mode dequeue_mode := DBMS_AQ.REMOVE. So after the message ...
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 ...
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
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
1answer
58 views

ALTER SESSION from a Control File

I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by ...
0
votes
1answer
48 views

Problem using sample schemas by Oracle in Oracle APEX

I am new to Oracle and Oracle APEX. I have been practicing to make Oracle APEX database application using "hr" schema of the database. Before doing so, I unlocked the schema, along with schemas ...
3
votes
3answers
11k views

Oracle 11g SYSDBA login

I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use SQLPLUS "/ AS SYSDBA". But I want to ...
9
votes
3answers
17k views

Is there any difference between Rem and — comments for SQL*Plus?

On SQL*Plus prompt, both Rem and -- qualify as comment indicators: Rem this is a comment -- this is also a comment create table emp ( id number primary key, name cvarchar2(40)); Is there any ...
0
votes
1answer
11 views

Oracle generated column aliases different on two DB's

We have a legacy application we cannot modify that connects to Oracle 11g and at some point runs a query and returns a result. The application however is using the "generated" column name from Oracle ...
-1
votes
1answer
40 views

Oracle - Service Account vs Database Account?

I'm a developer and not a DBA, specifically not an Oracle DBA. I have a Java application that is connecting to a Oracle 11g R2 database. I have an account and schema called "OBR". I am being told the ...
0
votes
0answers
15 views

How do you schedule the times that a material view can refresh in oracle 11.2g?

Currently we schedule MV refresh's hourly, but this is causing a problem due to conflicting timeslots with the overnight routines. Is it possible to either block refreshes between certain times of the ...
0
votes
0answers
7 views

Oracle Database keeps JDBC pooled connections on DRCP configured port as dedicated

Our developers have had an application in java that connects to the database and JDBC was controlling their connection pools. To comply with pooling requirements for our PHP developers, we enabled ...