Questions tagged [oracle]
All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.
7,758
questions
0
votes
1
answer
2k
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
1
answer
85
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
2
answers
14k
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 ":...
1
vote
1
answer
2k
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
4
answers
5k
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
1
answer
143
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
3
answers
28k
views
Exporting schema data (tables, permisions) into another schema on same database
I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.
0
votes
1
answer
3k
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,
...
-2
votes
2
answers
155
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
1
answer
4k
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
vote
3
answers
15k
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: '/data/app/oracle/oradata/MYSRVR/...
0
votes
1
answer
1k
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: '/data/app/oracle/oradata/MYSRVR/...
5
votes
2
answers
3k
views
Alternative to sequence and timestamp: uniquely ordering records in time
Oracle 11gR2 Exadata
I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted ...
0
votes
1
answer
3k
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:
...
1
vote
1
answer
421
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 ...
0
votes
1
answer
3k
views
Oracle transactions deadlock
How to rollback all active transactions on Oracle DB?
I execute query and see 4 transcations in ACTIVE status.
SELECT *
FROM V$TRANSACTION
1
vote
0
answers
184
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 ...
-2
votes
1
answer
46
views
Procedure generates an error [closed]
create or replace procedure buyunits is
mnav number;
mpno varchar2(100);
mtransactioncharge number;
mservicecharge number;
maccno number;
cursor ecursor is
select accno from sipholder where EXTRACT(...
1
vote
1
answer
4k
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 ...
1
vote
3
answers
20k
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 Journey....
4
votes
2
answers
4k
views
Loading XML documents to Oracle 11g DB with control file
I am using Oracle 11g XML database and trying to load XML documents to this DB with a control file and the sqlldr utility. All these XML files have an element that contains a date string with time ...
0
votes
4
answers
85k
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 ...
4
votes
1
answer
6k
views
What is a "partitioned view"?
What is a "partitioned view"? I googled but could not get a satisfactory result.
0
votes
1
answer
877
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 ...
5
votes
1
answer
18k
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 dialog ...
1
vote
1
answer
5k
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 ...
1
vote
2
answers
7k
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
2
answers
23k
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 c....
0
votes
1
answer
2k
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 ...
0
votes
1
answer
3k
views
Exporting and importing a database
I have setup an Oracle 11g Database and have a question about exporting and importing a database.
I am using Enterprise Manager and have browsed to the following: Data Movement->Export to Export ...
6
votes
3
answers
6k
views
How do I execute an Oracle SQL script without sqlplus hanging on me?
For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.:
If the script contains any named substitution variable ...
1
vote
6
answers
36k
views
How can I convert an Oracle dump file into SQL Server?
I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server.
I've searched for related ...
1
vote
0
answers
13k
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 ...
4
votes
2
answers
60k
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 ...
1
vote
1
answer
6k
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 "Jon%"...
8
votes
2
answers
61k
views
Granting privileges to dbms_crypto
I'm trying to grant proper privileges for using DBMS_CRYPTO.
Whenever I call it from package it gives me error:
PLS-00201: identifier 'DBMS_CRYPTO' must be declared
User already has privileges:
...
1
vote
1
answer
2k
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?
3
votes
1
answer
494
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
1
answer
391
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
1
answer
128
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 ...
1
vote
1
answer
1k
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
2
answers
4k
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 ...
0
votes
1
answer
2k
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 ....
3
votes
1
answer
14k
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 backup/...
1
vote
0
answers
277
views
file extension of the file types cdump, incident, trace
I need to know the file extension of the file types cdump, incident, trace, alert.
Actually I want to create files of the above types manually using mkfile command.
There are no existing files of ...
3
votes
1
answer
3k
views
Is it possible to use Oracle XMLTABLE and %type column type selector?
I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE.
I want to know how can i specify a field type according to a table column type when passing ...
0
votes
1
answer
1k
views
Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.
create or ...
0
votes
1
answer
2k
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 ...
4
votes
1
answer
2k
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.
9
votes
2
answers
2k
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, sum(...