-5
votes
1answer
38 views

How to fix the SQL from SQL Server to Oracle? [on hold]

There is the SQL using in SQL Server: select l.depart_code, l.depart_name, y.HR_NO, y.HR_NAME, m.PAY_SCOPE, x._per, x._com from psr_main m, hr_depart_data n, hr_depart_data l, (select ...
1
vote
2answers
56 views

How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
1
vote
2answers
39 views

Optimize rows to columns conversion

I have a big Oracle table (around 40 million rows) that looks like this: ID Name Question Answer Reason 3 Name1 1 Yes blah blah 3 Name1 2 No NA 3 Name1 3 ...
0
votes
2answers
30 views

Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others

I am working on data in oracle 11g as below : subscriber_id | count_s | count_t | min_count | diff | negative_diff | positive_diff 137606401 3 3 3 0 ...
1
vote
2answers
49 views

Aggregate function / query in Oracle 11g

I am working in Oracle 11g. I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert) I ...
3
votes
3answers
174 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
0
votes
2answers
66 views

Difference Between SELECT and select in oracle sql

The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. Means The SELECT instead of select statement will be parsed again ...
-1
votes
3answers
39 views

Trouble quering a join in oracle sql

Theres a question on an exercise which asks me to write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. I have to use a ...
0
votes
3answers
62 views

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I am using Oracle 11g and want to export a table using below statement C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER ...
0
votes
1answer
30 views

Output another word instead of the word in the table in a query

Suppose I have a table where I have a field which can have to values: "enabled" and "disabled". Suppose want my query to output "unblocked" instead of "enabled" and "blocked" instead of "disabled". ...
1
vote
1answer
99 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
1
vote
2answers
46 views

Adding an extra character to my column in select statement

id varchar(10) 1 2 3 I need to display the contents as id 1A 2A 3A .. but it should not affect data in the table..
0
votes
0answers
27 views

Oracle right outer join SELECT statement with a table

I am trying to find out the tables that are in MONITORING.USAGE_TRACKING table in first place, along with the other tables that need to be analyzed. SELECT A.JOB_NAME, A.START_DATE from ...
0
votes
1answer
32 views

Oracle: Replace corrupted first datafile

A datafile associated with a tablespace has been corrupted (got truncated to size 0). I don't mind losing the data, because I have a full DB dump which I can import. Now I want to replace this ...
0
votes
2answers
42 views

Oracle conditional statements

I am making a report, and I am trying to do following: If the value of the field is NULL, output empty string. Another words, I don't need the word "null" to be output to the user, just empty string. ...
0
votes
1answer
23 views

What is the “other miscellaneous information” in the Shared Global Area (oracle database)?

I'm studying Oracle database ,and it says here that the SGA contains these data structures : Database buffer cache Redo log buffer Shared pool Java pool Large pool (optional) Data dictionary cache ...
0
votes
1answer
39 views

The best way to query a partitioned table in Oracle

I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better: making ...
0
votes
1answer
52 views

ORA-00907: missing right parenthesis on query with subqueries

I am getting ORA-00907: missing right parenthesis for the following query: SELECT PEOPLE.NAME FROM CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L WHERE NOT EXISTS ( (SELECT DISTINCT ARTIST.ID_U ...
1
vote
2answers
42 views

Time elapsed Calculation

I need to calculated time elapsed for each user to perform his part in a transaction. The sequence is: 0 -1 - B2 or 0-B3 The table looks like the following: ...
2
votes
1answer
61 views

Error Creating Foreign Key in Oracle

I have 2 tables: PRODUCTS PREFIX SUFFIX ATTR1 ATTR2 ----------------------------------------------------- 001 AAA 50 100 001 BBB 20 300 010 ...
0
votes
1answer
42 views

SQL query for all combinations without repetition

I need a function for calculating combinations without repetition from a list. Example: I have a list of names e.g. Tom Bob Paul Tina Now I need a list of 3 names out of the 4 without having a ...
0
votes
0answers
20 views

Low performance oci_execute

I work with a PHP application that connects to an Oracle database. Sometimes, the PHP executes a large query from a large database the page loads for ages (more than 200s). But I've seen something ...
1
vote
2answers
44 views

Oracle patching

Hi everyone I'm a student and I need a help with Oracle patch's. My DB 10.2.0.5.0 I don't understand how to check which of CPU and PSU patch's were installed. When I try to use sql select * from ...
1
vote
0answers
38 views

Testing equivalence in strings with different character sets

I have to synchronize a number of tables with string columns between two databases: the source Oracle 10.2.0.3 NLS_CHARACTERSET WE8MSWIN1252, NLS_LENGTH_SEMANTICS BYTE the destination Oracle ...
2
votes
2answers
205 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
0
votes
1answer
50 views

How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously

I have two servers, Oracle and SQL-Server. I have same database on both the servers. I want to perform DML (insert,update,delete). How can I perform same DML statements on both the servers ...
3
votes
0answers
48 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
1
vote
1answer
53 views

Insert with different key values to avoid contraint violation

I have a set of Inserts traced of a database and I want to issue them into another similar one. The problem is that I'm getting contraint violation errors due the similar database already has the keys ...
0
votes
1answer
33 views

Efficient matching rows across two tables with huge data set

I have two tables with about 300+ columns and 20k+ rows each. Each row is characterized by a primary key which both tables have.I have to find the identical rows, rows with changes,and rows without a ...
0
votes
1answer
65 views

Couldn't find the long running SQL under v$session_longops

I have following SQL running from a long time. I couldn't see the status When I tried to look the time remaining for the query to complete. Can someone explain me why I couldn't find the SQL status ...
1
vote
1answer
40 views

How to use variable and drop in test script?

I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C. They provided me with some "Test" scripts that I shoud be able to run to test that my ...
0
votes
2answers
63 views

Dual table default value?

This link clear some of the queries about dual table. But now I am having following doubt is the execution of two queries on dual table: Query 1 Select * from dual; output: D - X Query2 ...
0
votes
1answer
209 views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
0
votes
2answers
55 views

Decode Function in Oracle

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow if(trans_type = 'AR','AD','AF','AG') then trans_type:='A'; elsif trans_type = ...
0
votes
0answers
47 views

How to find the estimated time for ORACLE SQL Query

Help to find out the following I have 8 million rows in an Oracle table, and this is being queried by a BI tool every day and taking more time. Is there any like explain plan that will give me a ...
0
votes
0answers
53 views

Find out profile creation date

Our DBA says that a specific profile was created but we cannot find out its name (and it wasn't provided by the DBA). We want to find out by looking at the profile creation date. How does one find ...
1
vote
1answer
60 views

Is it possible to do flashback after truncate?

Is there any possibility of doing flashback after truncate ? Thank you in advance.
1
vote
1answer
817 views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
0
votes
1answer
139 views

How to calculate time difference in sql

I have one table named test. In that table I have a column named test_time of type DATE. I want to calculate the time difference between test_time and sysdate in minutes. select 1440 * ...
1
vote
1answer
94 views

SQL Query Rewrite - Help Needed (Oracle)

Need help in re-writing the below SQL. The Query works fine for smaller database below 400 GB, but spends too much time for larger database above 600 GB. Oracle - 11.2.0.4 Database - Oracle ...
0
votes
2answers
60 views

How can I limit a hierarchical query to the point where there are multiple children?

This question is related to my previous question about hierarchical queries. I have a hierarchical query in Oracle 11gR2 that returns something like this: - Level 1 - Level 2 - Level 3 - ...
1
vote
0answers
19 views

How can I select rows from a hierarchical query with the lowest level? [duplicate]

Apologies for the cross-post from StackExchange DBA - I wasn't sure which site was best for this question. I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent ...
2
votes
2answers
213 views

How can I select rows from a hierarchical query with the lowest level?

I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent (Level 1) Child (Level 2) Grandchild (Level 3) Child (Level 2) Grandchild (Level 3) Grandchild (Level 3) ...
2
votes
2answers
86 views

SQL query for next occurrence of specific date

I am working on a function for our database that contains contract data. Each contract expires on a certain date, and then each contract has an expiration type that determines what happens after that ...
3
votes
2answers
238 views

ORA-00979: not a group by expression

I'm getting the error: 'ORA-00979: not a group by expression' when I try to run this query. select empno, empname from emp.employee group by empno, empname having empmsal > avg(empmsal); I'm ...
2
votes
3answers
452 views

Oracle 12c in-memory option

I've been told that there's an oracle in-memory option for 12c and that it uses columnar compression to get some great query speeds. I'd like to leverage it for some business intelligence stuff. I've ...
1
vote
1answer
26 views

Locating a schema that owns a role

Let's say we've logged on under the A schema. I am able to execute the following statement grant select on wph_pden to LANDING_R; However, the LANDING_R object can not be located in the schema A. ...
1
vote
1answer
87 views

Why does this simple select statement require quotes?

Why does the following statement select * from expertrule where rule_id = '1'; Return this error ORA-00904: "RULE_ID": invalid identifier While select * from expertrule where "rule_id" = '1'; ...
0
votes
0answers
25 views

How to make a role from schema A “grantable” in schema B

There are two schemas in the same database: A and B. Schema A has a role A_ROLE. I want to be able to give grants to A_ROLE from schema B ( so that the users with A_ROLE were able to query tables from ...
0
votes
0answers
30 views

how to use joins in hierarchical queries

how to use joins in hierarchical queries my try SELECT cust_id AS child_id FROM customer c,cust_assoc_dnorm d on d.dsend_id=c.cust_id WHERE connect_by_isleaf=1 and ...