Tagged Questions
-1
votes
0answers
54 views
2 Outer Joins on Same Table? [closed]
Here is a question which has been boggling me for few days now, and I searched and searched but couldn't find any convincing answer !
Simple question, why is it restricted to have 2 Outer Joins in ...
3
votes
1answer
73 views
Index on foreign key makes query extremely slow
We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.
The queried table (table3) has an indexed PK, three FK with indexes ...
1
vote
0answers
37 views
What is a good way to Merge 2 range sets into a single view
The real purpose of this has to do with effectivity dates and contains additional keys and fields, but I simplified it for the sake of, well, simplicity.
Sample:
test_Widget Table
theFirst ...
0
votes
1answer
62 views
Different dates Oracle 11g with TOAD
I have the following queries:
SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL;
/*this returns 2/1/2049. */
SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL;
...
0
votes
1answer
45 views
Changing the SGA and/or PGA in Oracle 11g affects the performance of the queries?
I am making a practice for school where one of the questions is if changing the SGA/PGA size would affect the queries performance and in what sense. I have been looking around for how to do that and ...
2
votes
4answers
129 views
Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?
I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I ...
0
votes
0answers
34 views
Longest prefix search in Oracle
I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi).
I need to efficiently find a longest prefix that matches given number PHONE_NR.
I use ...
0
votes
2answers
71 views
How to forcibly flush the undo log/tablespace of UNDOTBS1
I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
-1
votes
1answer
54 views
Adding a column at a specific position using DBMS_REDEFINITION
Can anybody tell me how I can add a column to table at a specific position using the DBMS_REDEFINITION package from Oracle?
-5
votes
0answers
51 views
Query for Oracle database [closed]
I have a problem in writing a Database Query
Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS
Data: REQUEST_STATUS may contains ...
-4
votes
0answers
42 views
oracle database query [closed]
I have a problem in writing a Database Query
Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS
Data: REQUEST_STATUS may contains ...
0
votes
2answers
38 views
copy package from schema B to schema C
I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) ...
0
votes
2answers
86 views
Use an Alias in Where Clause Subquery
I need to show some fields from another table in Oracle. Here is my query:
SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
...
-1
votes
1answer
52 views
populate table header and data in another table column [closed]
I have 2 tables: Departments and Employees, where Department_Id is primary key in the departments table and a foreign key in Employees table.
Departments Table:
Department_Id Department_Name
10 ...
1
vote
1answer
59 views
Using a Having clause with an outer join
I want to use a "having" clause with an outer join, using a column that is used in the outer join.
This is my query:
SELECT
I.CARD_BIN BIN
FROM
GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION ...
1
vote
3answers
78 views
Transfer data from DB2 to Oracle
I want to transfer data from an old DB2 system to a new Oracle database.
How should I go about doing this?
0
votes
1answer
60 views
Find Underlying Data Type (int - integer) of Oracle Table instead of number
I can create an example table using following syntax.
CREATE TABLE "TABLE2" (
"COLUMN_INT" INT NOT NULL,
"COLUMN_INTEGER" INTEGER NOT NULL,
"COLUMN_SMALLINT" SMALLINT NOT NULL,
...
2
votes
2answers
220 views
how to check which background process are running in my oracle database?
To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ?
Thanks
1
vote
1answer
49 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
4answers
54 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
1answer
51 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
45 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
3answers
195 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 ...
1
vote
2answers
110 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 ...
1
vote
2answers
343 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
1answer
100 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
2answers
152 views
Oracle Undo tablespace users
Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.
0
votes
0answers
53 views
Oracle Alternative to EXISTS EXCEPT
I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here:
Comparing Columns that can contain NULLS - is there a more elegant way?
I can't get this ...
-1
votes
2answers
75 views
Combine two SQL statements to a single statement [closed]
My question is related to SQL. I need to pull values from two different tables. I am not successful in doing that. I am able to join the two tables with only a single condition but not many values ...
3
votes
1answer
85 views
Convert SQL Server UPDATE Statement for Use in Oracle
I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server.
I am looking for some guidance on how to convert it.
UPDATE
SOB
SET
COA = CASE
...
3
votes
1answer
332 views
Oracle insert through dblink performance issue
I'm trying to do an insert as select to a table via dblink. The query looks like this:
Insert into MARS_SUPERVISORS_PUB@EDHPUB
select
{...}
from edhper a, edhper b
where ...
0
votes
1answer
86 views
ORA-01435: user does not exist
I am trying to run the SQL queries coded on my friend's laptop onto mine. I have imported the .sql file into SQL developer and I'm trying to run it.However, I keep getting the error:
01435. 00000 - ...
7
votes
2answers
448 views
Oracle 11g: performance improvements of inserts
I have a table of 500 millions of rows (and growing)
I did the following to improve performance of inserts:
On database side:
dropped all indexes and constraints
disabled logging
On application ...
5
votes
2answers
168 views
Defining constraints in `CREATE TABLE` statements
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
3
votes
3answers
85 views
Sum Up values in a query based on other information
I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:
I get these results by running this statement:
select distinct a.eventnum, ...
7
votes
1answer
263 views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select ...
2
votes
2answers
80 views
SQL Max() revision number
This SQL returns a table with everything unique, which is what I want. However, if there are two (or more) event numbers, I need it to return the line with the highest rev_num for that eventnum.
I ...
0
votes
0answers
46 views
Database too slow after CHARSET Conversion
I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset.
The conversion is made without any ...
1
vote
1answer
74 views
Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view
I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization"
It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
3
votes
1answer
743 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
1
vote
1answer
38 views
Hard Parse on Non-Identical Statements?
Let's say I have this query :
SELECT X FROM Y
If I run it for the first time then it would do a hard parse.
After that, I run this query :
select x from y
With just a difference in alphabetical ...
3
votes
1answer
50 views
Special ordering
I have a hierarchical query but I don't manage to order it as I want.
I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly.
This a ...
0
votes
0answers
193 views
Understanding the time difference between data grid initial display and export/full display using Toad for Oracle
I am trying to run a query on a local Oracle DB and output the results to a .CSV file. The query runs in a decent amount of time (usually less than 5 minutes), but returns tens of thousands (possibly ...
2
votes
2answers
258 views
Selecting data from multiple rows into a single row
I have a table that has columns like following. Batch number is a database design thing like row number
demand_id batch_number debit status customer
34 1 ...
1
vote
2answers
137 views
Oracle insert max from two date
I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like
Insert into MYTABLE (DATE_INSERT)
VALUE ...
4
votes
1answer
100 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
1
vote
1answer
105 views
Struggling with complicated date query
So I have been tasked to write a query involving dates for a contract management system we have. The database stores the date the contract's primary terms expired, the frequency that the contract ...
0
votes
1answer
124 views
How to return different statements in where clause ? (oracle)
I mean:
CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER) RETURN NUMBER
BEGIN
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
WHERE COLUMN1<= Y AND (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE ...
4
votes
1answer
843 views
temporary table inside procedure oracle
I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g.
Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these ...
0
votes
1answer
104 views
inconsistent Data Type -ORA-00932
I know there is question related to inconsistent datatypes but that comes from a query. while
I am getting this error on database side when we try to access user defined datatype on our schema that ...
