0
votes
0answers
19 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 ...
0
votes
1answer
53 views

Select one single row from table b for each row from table a

I have two tables: A(name VARCHAR2(20), type VARCHAR2(20)) and B (type VARCHAR2(20), ... (20 fields more)) I need to select a single row from B for each distinct name,type from A taking into ...
-3
votes
1answer
28 views

How to avoid repeating values in a column in this case [closed]

Dear Friends help to solve this problem The query is given bellow SELECT inv_val,ind_invno , ind_invdte, ind_csm_cd, csm_name, ind_fgm_cd FROM soh_invoice, sod_invoice, som_party WHERE inh_orm_cd = ...
0
votes
0answers
31 views

Retrieve result set with proportion of some column value

I have a table called PORTFOLIOS in which I have PORTFOLIO_ID, PORTFOLIO_NAME and SCORING_RULE columns. The SCORING_RULE column can have two different values (like double_scoring or single_scoring). ...
0
votes
1answer
67 views

How can I force a bushy tree join in Oracle SQL?

As far as I know the default join is the deep left join. How can I force a bushy tree join on 4 tables?
-3
votes
1answer
24 views

Why DBMS_METADATA.GET_DLL not work with EXEC command ?

Using Oracle 11g I just trying to understand the concept here.... Why the command bellow not work? (executed at SQL Developer) execute dbms_metadata.get_ddl('VIEW', 'VW_CARGA','INT') Error ...
0
votes
0answers
39 views

explain plan ,joins and optimization

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance ...
1
vote
2answers
42 views

Oracle result cache behaviour

Oracle by default caching the query results and function results(Buffer cache). I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next ...
-1
votes
1answer
32 views

Oracle Database XE 11.2 Question on how to get started [closed]

I have installed Oracle Database XE 11.2 on my laptop PC. As I am new to Oracle and to SQL in general, I am finding it difficult to get started. I have searched but couldn't find any "step-by-step" ...
0
votes
0answers
24 views

Oracle Database Gateway for SQL Server - Output changes to multibyte view

I have a really strange problem querying a Microsoft SQL Server through a Oracle Database Gateway for SQL Server. Some query outputs look like this SELECT NAME, RawToHex(NAME1) FROM XY; Output: J ...
4
votes
1answer
88 views

date formatting in Oracle

this query: select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual will return: Eighteenth MARCH , Two Thousand Fourteen i am wondering if there are any additional formatting keywords that ...
0
votes
1answer
44 views

Inserting data into a table with one value as clob and blob

My table has one clob column and one blob column. I need to insert data into it through insert statements. The length of data to be inserted is more than 4000 chars. When I do export of insert ...
0
votes
0answers
37 views

Exception in SQL client but not in stored procedure

I am using Oracle 11g and SQLDeveloper for this scenario. In this scenario, I am trying to simulate an exception within a stored procedure but it is not happening. My DB runs in GMT. My client ...
0
votes
0answers
59 views

I'm trying to run an oracle database in virtual box under oracle linux

I'm trying to run a oracle DB in VirtualBox and access it from my host mac OSX , i am using port forwarding to access my guest, I can telnet my guest on the port, however when i try to connect from ...
0
votes
0answers
24 views

How to generate TPC_DS queries for Oracle?

I have downloaded the DSGEN tool from the TPC-DS web site and already generated the tables and loaded the data into Oracle XE. I am using the following command to generate the SQL statements: ...
0
votes
1answer
63 views

PLS-00049 bad bind variable when compiling a trigger

I'm trying to implement a dedupe trigger to a table. CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP BEFORE INSERT ON USER.ERRORTABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ...
0
votes
1answer
30 views

Why does Oracle store time in DATE column?

I have a table with a date column. I used JDBC to save a java.util.Date object into the table. When I run following query I get date aswell as a time value: select To_CHAR(departure_date,'DD-MM-YYYY ...
2
votes
1answer
100 views

Oracle Standby Database Techniques for faster application of redo logs

I am quite new with the concept of standby databases in Oracle and I am not really a DBA, so help me out. I have been reading a bit on the concept of Oracle standby databases and I am pretty much ...
0
votes
1answer
46 views

rownum won't return records above 100

I have this query: SELECT * FROM ( SELECT v.*, ROWNUM recnum FROM ( SELECT hostaddress, modulename, severity, escalation, classification, ...
-1
votes
1answer
39 views

How to compare a result of a query to a number? [closed]

I have a query that return selects a table and returns one column with a number, and I want to know if the result is > 0. SELECT * FROM Table WHERE (SELECT amount from Table2) > 0 ...
0
votes
1answer
132 views

How index work in oracle? [duplicate]

I want to understand : How index work in oracle. How they are stored in the disk. How they increase the efficiency of a query? By third point I means how Index contribute to the efficiency of ...
0
votes
2answers
49 views

How to store a database table in multiple disks with assigned ratio?

I am going to deploy database tables to multiple disks. My database layout would be like following example: There are 3 tables (Customer, Order, Product) and 4 disks (disk_A, B, C, D). Customer: 5% ...
0
votes
1answer
100 views

ORA-30009: Not enough memory for CONNECT BY operation

I have a Linux VM (with CentOS 64 bit) where I installed Oracle Database 11g Express Edition. The database is running, I can use sqlplus and I can create tables and stuff. However, when I run a ...
1
vote
1answer
48 views

Oracle - Date of Selection on table

how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date
0
votes
2answers
155 views

Finding all sql id, sql text, number of executions,execution time between two AWR snap ids in oracle

AWR report provide the SQL information for top 10-20 SQLs, is it possible to get information on SQLs that do not make it to the top list. How do we find all sql id, sql text, number of ...
-1
votes
1answer
116 views

How to set up a connection in Pentaho Kettle for Oracle using a SERVICE NAME instead of SID?

This error shows up when I try to connect to database string host:port/servicename.
0
votes
2answers
167 views

exporting oracle tables on command line as sql files

I use exp and expdp to export database tables on command line but it generates binary .dmp files. I want to export/import tables as .sql* files on command line like pl/sql developer app can do. Is ...
0
votes
1answer
42 views

“Table or view does not exist” on synonym

I have a database and a USER administrating it. Then I have an additional USER_APP to whom I granted CRUD access to all tables in my oracle data modeler (and then applied these via generated script ) ...
2
votes
1answer
131 views

How oracle Oracle Database 10g used index?

I have table “result”, DDL : CREATE TABLE result (id_graduate NUMBER NOT NULL, id_sml NUMBER NOT NULL, last_update DATE NOT ...
0
votes
2answers
92 views

In Oracle, why would an OR / LIKE(A,B) take far longer than running the same query for each condition separately and unioning

I have a complicated subquery that looks something like this SELECT ... FROM ... WHERE my_column IN (1,3) It takes a long time to run. I tried splitting it out into: SELECT ... FROM ... WHERE ...
2
votes
2answers
101 views

Oracle database Architecture

I need to know to correct order between these following steps, it's hard to find information on the internet on what happens first, i'd be really grateful if someone could give me the answer A. ...
0
votes
2answers
70 views

Return items not found in database in Oracle

I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number. However, the names given are not necessarily exactly as they appear in the ...
0
votes
0answers
31 views

select one record from multiple tables [duplicate]

I'm working in sqlDeveloper program I have two tables table one is "paid" table which include (pcode,pname, quantity) second table is " products" include (pcode, price) when I want to select one ...
5
votes
2answers
220 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
0
votes
0answers
28 views

How to rollback last executed sql script fired as sysdba [duplicate]

I have fired one sql script as sysdba which I have to fire using other oracle user. I have already created some user and each application running on each oracle user. By mistake I had fired a script ...
0
votes
1answer
85 views

How to rollback last executed sql script fired as sysdba

I have fired one sql script as sysdba which I have to fire using other oracle user. I have already created some user and each application running on each oracle user. By mistake I had fired a script ...
0
votes
1answer
88 views

Why doesn't row data show up in SQL Developer?

I'm using Oracle database and I've created a test table "Table1" with a varchar column "column1". I ran the following loop to add some rows: BEGIN FOR i in 1..10 LOOP INSERT INTO TABLE1 ...
1
vote
1answer
82 views

Having trouble with a query. DBMS Assignment [closed]

Working on an assignment for school, was able to figure out all but this one. I need to display the employee name and employee identification number along with their HOD's name and identification ...
3
votes
1answer
282 views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
1
vote
1answer
142 views

Automatically schema backup in Oracle

I am working with an Oracle Database and I have a user (called XY) with permissions R/W. I would like to to do automatic backups of the user schema XY. I don't own the server and I don't know if the ...
0
votes
1answer
2k views

How to fix error ORA-01033: Oracle Initialization Or Shutdown in Progress?

I have to submit my project a day after tomorrow. My project is ready but I am having above mentioned error while connecting to Oracle database. I tried the following solution: Enter user-name: sys ...
0
votes
3answers
95 views

Applying data changes from one database to another

I have an application running on Oracle 9i database and I was looking for something more practical to deploy data changes (not schema changes) from the development database to production. Currently I ...
0
votes
0answers
47 views

Which queries are running and what tables are they accessing? [duplicate]

I am learning Oracle. I am trying to write a query to find out the list of SQL queries and the tables that they access. I tried looking into v$sql, v$sqlarea etc, but was not able to find a column ...
0
votes
3answers
61 views

Get records which doesn't have duplicate records

I have a table with polcies and class codes, I want to get all policies which doesn't have duplicate class codes. Do you have any idea? One policy may have one or more class codes. If any class code ...
1
vote
1answer
54 views

Query Previous Records (Oracle)

Consider an Oracle database that has a person, a date and a value in every record. When I pull a record, I want to very quickly and easily get the previous record where person is the same in both the ...
0
votes
1answer
80 views

Trying to count multiple values and total

SELECT COUNT (DISTINCT competition.genre_id) genrecount, COUNT(DISCTINCT band.band_id), genre.name, genre.genre_id FROM genre, competition_band, competition, band WHERE genre.genre_id = ...
0
votes
1answer
49 views

How to add up a column depending on data from another column

I have a table which has results and a table that has band_name and band_id. Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I ...
0
votes
2answers
120 views

Warning before issuing delete/update command in mysql/oracle , then executes

Is there any utility , function either in mysql /oracle that if i issue some delete/update command wrongly , a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to ...
0
votes
1answer
170 views

Create database link on Oracle Database with 2 Databases on different machines.

I have 2 oracle databases (db1 and db2) on different machines, lets suppose: db1 on 192.168.1.1 and db2 on 192.168.1.2 What I want to achieve is to create a link on db1 that will allow me to perform ...
0
votes
0answers
92 views

How does tablespace sizing works in Oracle

Let's say I have an input of 20 gigabytes of raw data (text file) which in turn I would into an external table and would then swap partition in order to place them on my database. The question now is ...