Tagged Questions
-1
votes
1answer
30 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
44 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
21 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
62 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
47 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
54 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
50 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
37 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
75 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
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
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
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
40 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
0answers
40 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
3answers
113 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
89 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
148 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
92 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
1answer
94 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
44 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
73 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
65 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
245 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
60 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 - ...
6
votes
2answers
309 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
162 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
73 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
233 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
77 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
45 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
59 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
389 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
35 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
185 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
199 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
111 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
93 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
103 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
101 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
472 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
102 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 ...
-1
votes
1answer
51 views
Select query from two tables which only specified rows [closed]
I want to try querying two tables, like this:
Table A
user_id primary_indicator
------- -----------------
9012445 Y
9012445 N
9012442 N
9012332 Y
9012332 N
9012445
9012332
Table B
...
1
vote
2answers
96 views
Count events on “user based” sliding window
So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be ...
4
votes
1answer
366 views
Oracle extract slow function call from a WHERE clause
I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this:
CREATE TABLE MY_TABLE
(
SOMETHING NUMBER(15,1) NOT NULL,
...
1
vote
2answers
181 views
Factoring out subselect (with clause) ends in ORA-00904
I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again.
This is my base query which works quite well:
select tka.id, tka.name, txt.text as langname, ...
2
votes
1answer
178 views
Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queries
I'm looking to make sure that all queries in a php web application have proper use of bind variables to minimize parsing of the queries.
I'm wondering how Oracle parses queries that compares a ...
1
vote
0answers
53 views
Oracle update query sequence of actions on DB side
I searched online but could not find particularly consolidated source of what happens when you execute an update query. I know that there are a few actions happening like create redo data and logs ...
0
votes
0answers
42 views
Oracle Sql Query Mutiple rows [closed]
Table name = Table-Name
ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog ...
