Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...
4
votes
2answers
314 views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
0
votes
2answers
545 views
Trouble setting up Nagios to monitor Oracle services
I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and ...
5
votes
1answer
224 views
What is “SAP on Oracle”
OK, total newb question.
What is "SAP on Oracle", exactly. This link is talking about how SAP can run on Oracle (I think).
I'm not understanding how this is working.
My understanding:
SAP is a ...
0
votes
1answer
32 views
using row data for column headers
I have a table
CREATE TABLE "AUTOMATION"."TESTRESULTS"
(
"BUILDNUMBER" VARCHAR2(30 BYTE),
"TESTNAME" VARCHAR2(100 BYTE),
"STATUS" VARCHAR2(100 BYTE)
)
sample data would be
...
1
vote
1answer
48 views
Summation query with complex grouping criteria
I have a database of coded actions with this structure.
{User, Date, ActionCode}
I need to generate reports based on dates and action codes, and the action codes are not clean because we have to ...
0
votes
1answer
48 views
Efficient way to perform approximated search?
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.
As it happens ...
0
votes
1answer
64 views
Database creation on Linux with dbca: “command not found”
I have installed an Oracle on my machine. When I open a session with VNCServer and type dbca, either as root or as oracle, I get these errors:
-bash : -dbca : Command Not found
Xlib: connection to ...
0
votes
1answer
81 views
Ideal frequency to gather table statistics in Oracle for database of 1GB in size
I want to know what should be the ideal Frequency for Analyzing tables in a schema.
Database size 1GB.
Currently the frequency is set to 7 days, it takes around 40 mins to complete. Data volume is ...
0
votes
0answers
22 views
Oracle Downstream Log transfers
I am attempting to create a downstream capture process for two Oracle 11 databases running on Linux servers. The replication will be one-way and asynchronous using archive logs as the transfer ...
0
votes
4answers
50 views
Selecting the first purchase item of each customer
I have a table like this:
CREATE TABLE SALES
(
CID NUMBER(4),
PDATE DATE,
ITEMCODE NUMBER(4),
ITEMNAME VARCHAR2(20)
);
INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX');
INSERT INTO SALES ...
0
votes
2answers
105 views
How do you correct a corrupted index in Oracle?
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: ...
0
votes
1answer
24 views
Removing the Oracle Instance details from windows machine
Actually, I have installed the Oracle 11g Version 1 in my machine. After that instead of
using universal installer for uninstallation manually I had removed the files from installed location.
Now, ...
-1
votes
1answer
35 views
Partitioning in Oracle 11g
If I alter the partitioning in oracle and trying to retrieval the data,is necessary to mention the partition name or not?
0
votes
1answer
42 views
Combine multiple queries into single query
I am trying to run the following query:
SELECT Freight.Freight_No,
Freight.Available,
Locomotive.Loco_No,
Locomotive.Available,
Driver.Driver_No,
Driver.Name,
...
0
votes
1answer
55 views
How do you correct a corrupted index in Oracle? [duplicate]
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: ...
2
votes
0answers
45 views
SQL - Grouping results by custom 24 hour period [closed]
I need to create an Oracle 11g SQL report showing daily productivity: how many units were shipped during a 24 hour period. Each period starts at 6am and finishes at 5:59am the next day.
How could I ...
0
votes
1answer
94 views
SQLPlus: Cmd vs PowerShell
From cmd.exe, when I run:
C:\oracle\client\BIN\sqlplus.exe username@server
then type my password, I get in. However, if I run the same command from PowerShell and type the same password I get:
...
0
votes
1answer
30 views
Is the data fetched by an explicit cursor immutable in PL/SQL?
This seems like an obvious question, but Googling has so far failed me...
Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a ...
-1
votes
0answers
60 views
Installing Oracle 11g on ubuntu 12.10 [closed]
I am installing Oracle 11g so I can work through practice excerises. I am following the installation walkthrough at link.
In the terminal when I run chown -R oracle:oinstall database
I get Operation ...
-9
votes
0answers
46 views
Creating view and traverse [closed]
Creating and traverse by using n Primary Key and n foreign keys and relations? on oracle 11g
1
vote
0answers
39 views
need the most efficient way to handle unique constraint errors
I am looking for the most efficient way to solve this situation:
We are an Oracle shop. I need to load data from one table to another. My source table, which contains 30 million rows, has a person ...
0
votes
0answers
55 views
How to count number of rows inside Oracle dump file?
I have a huge (38Gb after bzip2) Oracle EXP dump file. It contains one table.
Such big dumps usually end up with an error, but in fact contain the full table.
Earlier I was importing these tables ...
0
votes
3answers
124 views
Oracle SUM - ORA-00937: not a single-group group function
For every journey want to sum them to work out the distance between.
SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE ...
-4
votes
2answers
66 views
Creating an admin user in Oracle
How can I create a "root" (like on MySQL) in Oracle?
I installed Oracle database on my system, for homework, for school (faculty) but it didn't give me the option to make a root user. Well at least I ...
-1
votes
0answers
62 views
What is meant by Clustering index and Multilevel indices? [closed]
What is meant by Clustering Index and Multilevel Indices ?
I could not find much when I googled it.
3
votes
1answer
98 views
What is a partitioned view?
What is a partitioned view? I googled but could not get a satisfactory result.
0
votes
1answer
45 views
trying to execute SQL query on an oracle 10g database using java but getting ORA-00911 error [closed]
I am trying to execute a simple CREATE TABLE query on an oracle10g database using java. This is what I am doing:
Connection conn = //create connection
//open connection. No errors/warnings are ...
1
vote
1answer
114 views
Find who and when changed a specific value in the database
We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake.
we want to find out who and when has done this?
could ...
0
votes
2answers
70 views
How to set Proxy Settings when installing Oracle 11g on Windows 7 Ultimate Edition?
I have downloaded the file - "win32_11gR2_database_1of2.zip" from oracle site.
While installing Oracle 11g, I find this window in the first place:
What to do?
What values should I put on the ...
1
vote
1answer
80 views
Is it possible in Oracle to trace SQL statements that result in errors?
We have Oracle 11g in production. Application system is still under active development. It will be very handy to get SQL statements which cause any error.
Does Oracle provide a standard function to ...
1
vote
2answers
91 views
Oracle SQL, how to filter on a set by a rank?
I am struggling with the following SQL query right now:
http://sqlfiddle.com/#!4/736a7/12
select uc.*
from usercredential uc
inner join users u on u.userid = uc.userid
inner join credential c on ...
0
votes
1answer
103 views
How to permanently delete a row in oracle forever [duplicate]
I want to delete a record from a table and no one should ever be able to recover the deleted data.
I have done delete operation but the record is still in the undo segments. And one can do flashback ...
3
votes
2answers
5k views
Using Oracle client to connect to remote database
Although I can do a telnet to remote oracle port I am unable to connect.
I have a following tnsname.ora connecting to oracle database using sql plus:
remoteOracle =
(DESCRIPTION =
(ADDRESS_LIST ...
5
votes
1answer
117 views
What must be in place to validate a XMLTYPE against a schema?
I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
1
vote
2answers
165 views
How to check if Oracle DB process is running in Unix and where Oracle DB is installed
I am new to Unix and trying to find out a command that checks if Oracle DB is installed in my Unix box. I came across commands ps -ef|grep smon and ps -ef|grep pmon in net. But which is the correct ...
0
votes
2answers
58 views
Oracle database uses wrong parameter file
When I try to startup my Oracle database I get the following error:
LRM-00109: could not open parameter file '/opt/app/oracle/product/11.2.0/db_1/dbs/initjicdbaex.ora'
The /dbs directory in my ...
1
vote
0answers
195 views
impdp failing with ORA-01031: insufficient privileges
I'm trying to import a dmp file provided by our dba to my dev database. I've been able to export the contents to a SQLFILE and the contents look ok. I tried importing the schema and it kept on failing ...
1
vote
1answer
94 views
Multiple like conditions for same column?
How do I write a query for a Oracle database when I have several "like" values for the same column (in my example data "name")?
My example data for table "address":
name like "John"
name like ...
0
votes
0answers
36 views
Temporarly disable cache in oracle 11g
I want to disable the caching for my session in oracle 11g to see how long a query takes.
Is this possible or is there a better way for meassuring the time a query takes to run?
2
votes
1answer
50 views
Oracle Database: View schema without access to data
In Oracle Database 10g, I need to create a user with these permissions:
user can access schema to be able to read table names and columns
user can not select (get) any data from any table
Is this ...
2
votes
1answer
116 views
Is it possible to re-initialize sequence
How do I re-initialize the sequence value without drop and recreating them again
For example, the current sequence value is 3, now I want to restart it with 1 again.
-2
votes
1answer
35 views
How to learn oracle business intelligence enterprise (OBIE) from scratch? [closed]
Hi I am a electronics and communication graduate but i want to learn Oracle Business Intelligence Enterprise(OBIE).I have some theoretical knowledge of RDBMS and SQL which i learnt during my ...
1
vote
1answer
61 views
Can a production Oracle DB server also have a dataguard active standby for a different production database?
If you have a production oracle database (PRIM1) protected using Oracle Dataguard to a standby (STBY1) physical server in a different data centre, the standby server needs to be a similar ...
1
vote
1answer
39 views
Oracle error handling strategy [duplicate]
I am developing a project using oracle. I am writing functions and stored procedures to handle CRUD statements.
My question is in addition to oracle check constraints do i have to check error ...
0
votes
0answers
58 views
Use cases for NoSQL databases: Time series
I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance).
There're also some ...
0
votes
1answer
45 views
Recommended Approach to Programmatically Backing Up/Restoring Oracle DB
Supposing I have an ever-growing Oracle DB on one server, and I want to duplicate this schema and the data on another server - what would be the best approach to achieving this as part of a bespoke ...
0
votes
0answers
27 views
What value should oracle.install.db.config.starterdb.memoryLimit be set to in the Oracle response file for a silent installation?
What happens when oracle.install.db.config.starterdb.memoryLimit is not set in the Oracle response file for a silent installation?
I noticed that when recording the response file for a silent Oracle ...
0
votes
1answer
44 views
Oracle's UTL_FILE
I am learning Oracle pl/sql by myself.
I need to create a procedure that exports a table to a csv file. For that I am thinking of UTL_FILE.Is there any good book or a site to get me started? I did ...
3
votes
1answer
156 views
Oracle: Full database (user) restore requires drop?
In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc.
We are using expdp/impdp to perform our ...
9
votes
2answers
124 views
Why is a “non-single-group group function” allowed in a subselect but not on it's own?
Why does the first query not fail with the same error as the second:
with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual)
select sum(units) from (select product_id, ...
