All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

0
votes
1answer
9 views

Trouble With Basic Query Searching

Not sure if the this is the right place to ask but i have a question with beginner sql. I have the table dept and emp which include: SQL> desc dept; Name ...
0
votes
0answers
31 views

Oracle SQL: Outer Join Not Working As Expected

I am joining two tables, T_ADMSTU_PROG_ENROLL (renamed prog_enroll) and T_ADMGRAD_STUDENT (renamed grad). I would expect the result to have the following columns: TERM_CODE, STU_INST_UID, MAJOR_DESC, ...
0
votes
0answers
32 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
0
votes
1answer
21 views

ora-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair During AWR Creattion

When I'm create AWR Report then following error occurred plz help. Cause: An attempt was made to perform operation with an invalid Start/End Snapshot Pair.
1
vote
0answers
23 views

Find Bottleneck of Store procedure in Oracle 11g

I written a store procedure which retrieve data from 6 to 7 table using join and used sys-refcursor. Now problem is data fatch approximate 700 to 750 rows at 18 seconds but in some case take more ...
0
votes
0answers
34 views

Deleting a Row in Table- Best way to also delete all rows it affects? [on hold]

I have created a very simple SQL script (see below) that deletes rows in my table if a column name is a certain value. DELETE FROM table_name WHERE some_column=some_value; However, this table ...
0
votes
0answers
24 views

Next date to be displayed [on hold]

train depature and arival arv dep day 13:40 pm day 1 20:45 pm 23:20 pm day 1 02:20 am day 2 I need the output as (give input as ...
0
votes
1answer
59 views

What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment?

I see some files in there like an init.ora and opctrn11.h, but what is the purpose of this directory, if we use %ORACLE_HOME%/database to store the p/spfile instead?
0
votes
1answer
29 views

ORA 01722: Invalid Number caused by different comparison

The following causes the above error: SELECT * FROM ( SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ...
0
votes
0answers
34 views

What is the best ways to filter data on the fly

We are implementing a new plan for ETL database, but we do not want to store the intermediate table on our local sql server (only want those records that we need). any body know the best ways to do ...
1
vote
1answer
47 views

Get ORA-01157 when startup database

I created a datafile /tmp/encrypted_ts.dbf. When the machine started again, the datafile was gone. When I started it I got ORA-01157, the details are: ORA-01157: cannot identify/lock data file 54 - ...
2
votes
1answer
47 views

How to compare dates on minute level in plsql?

If I'm correct, then date comparison on seconds level is as simple as SELECT 1 FROM sample_table WHERE sysdate <= sample_table_date_field; but haven't found any simple or stable solution for ...
-3
votes
1answer
70 views

Problem with procedure in plsql [on hold]

Create a stored procedure called UPD_JOBSAL to update the minimum and maximum salaries for a specific job ID in the JOBS table. The procedure should provide three parameters: the job ID, a new ...
0
votes
1answer
53 views

Oracle over-indexed tables

I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more ...
0
votes
0answers
19 views

ORA-01745: invalid host/bind variable name using ORACLE [duplicate]

With my below debugged query, I got error as ORA-01745: invalid host/bind variable name Here is the query, SELECT DISTINCT sv.mkey, vehicle_no, TO_CHAR (date_in, 'dd-MM-yyyy') || ' & ...
1
vote
2answers
60 views

Join SQL Server table in OPENQUERY on multiple keys

I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys: SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A INNER JOIN ...
1
vote
1answer
40 views

Does the function inside the Select clause evaluate the passed in columns before the where clause?

I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question. select ...
0
votes
1answer
32 views

Drop partition and rebuild global indexes “online”

I am using Oracle 11g. I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt. ...
5
votes
1answer
112 views

Recursive Join to get Spans

I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their ...
1
vote
1answer
48 views

Conflict resolution in versioned tables

I'm trying to merge two workspaces that contain version-enabled tables. I can't figure out how to resolve conflicts, because I have a lot of rows with the same ID value. They differ only in time ...
0
votes
0answers
80 views

How to improve a sql query

If the query is taking 50 seconds needed to execute but taking 100% CPU and load increased on database. So will need to tune? If possible how to monitor to suggest tune team. I am monitoring an ...
1
vote
2answers
54 views

Updating duplicate records with different data

I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this ...
0
votes
1answer
40 views

Backup and Restore with RMAN Oracle 12c

I'm trying using RMAN to create and restore backups. I'm getting an error stack trace when trying to restore to one of the backups that I have made. The error is RMAN-00571: ...
0
votes
1answer
42 views

I don't want to have the error when I drop a table although the table doesn't exist [closed]

I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist. I did this set echo on set serveroutput on alter session set ...
1
vote
2answers
52 views

Serialize :new in trigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new. What I am looking for ...
0
votes
2answers
64 views

Can Oracle 11g Express Edition Work on Parallels

I am using a Mac and need to use Oracle 11g. I have Parallels installed with Windows 8.1 on it. Do I need to download anything else or just download Oracle 11g Express Edition and run it?
0
votes
0answers
43 views

How to tell if Oracle Client was loaded as Administrator

We're implementing a new system. Oracle 11g databases on Solaris. Their application run on Windows2008R. The windows server requires an Oracle client to generate its reports. It gives a DB ...
0
votes
1answer
37 views

difference between assingment and : [closed]

what is the difference between these two ,second is arithmetic expression assigne to sal , first one is ? how they differ exactly. if salary has intial value of 10 both result same answer. when to ...
-1
votes
1answer
37 views

function execution related question

Create or replace function funleap(a number) return varchar2 is b2 number(5); b3 number(5); b4 number(5); begin b2:=mod(a,4); b3:=mod(a,100); b4:=mod(a,400); if ((b2=0 and ...
0
votes
1answer
34 views

ORA-01031: insufficient privileges while connecting to standby

I configured standby database and it worked for a while but now I noticed that redo logs thread on standby much less that on the primary. After researching alert log and trace files, I figured out ...
0
votes
1answer
57 views

Oracle index usage

I came across V$SEGMENT_STATISTICS dynamic view. What statistic should I consider to tell if index block is being updated from time to time (on the contrary to scenario where index block is created ...
-1
votes
0answers
61 views

How to run an exe from an oracle trigger

I am new to Oracle came from SQL Background. I want to run an exe using trigger in oracle. As there is no direct way to do this, I have created a java procedure to run a trigger. my code is as ...
-1
votes
2answers
29 views

In out parameter : how to execute [closed]

Create or replace procedure sma11( a in number ,b1 out varchar2) is begin b1:=mod(&a,2); if b1=0 then dbms_output.put_line('b1'||'is even'); else ...
1
vote
0answers
78 views

Unexpected SYSDATE behavior

I have a SQL trigger which adds the old value ,new value and time of update for column based on update, insert, update or delete. To update the time we used SYSDATEand inserted record from trigger. ...
2
votes
2answers
46 views

how login other account by sysdba in oracle?

I forget hr's password, but I have sysdba role. Can I use su hr like in linux. I don't hope alter hr's password.
0
votes
1answer
40 views

function return data type?

create or replace function Total_sal return varchar2 is Total number; begin select sum(salary)into total from employees; return total; end; / BEGIN DBMS_OUTPUT.PUT_LINE(TOTAL_SAL); END; ...
-1
votes
1answer
35 views

how to execute this basic parameter

how to execute this one in plsql.. create or replace procedure p11(a in number, b out number) is begin b:=a*a; dbms_output.put_line(b); end; variable a number; variable b number; what statement I ...
0
votes
1answer
23 views

How to get capacity specifications for a particular Oracle release?

How to get capacity specifications for new Oracle release(Oracle 11.2.0.4)? ie, information like How much schema we can create?, How much tables a schema can accommodate? How much rows total ...
1
vote
0answers
37 views

Oracle Database Change Notifications only sent every 15 minutes, not immediately

I'm registering a listener for Oracle Database Change Notifications through JDBC (using the EclipseLink implementation of JPA, Oracle 11.2.0.3, Oracle JDBC thin driver 11.2.0.4). I'm getting ...
0
votes
2answers
58 views

Database not able to startup showing an error in reading control file

On Windows Server 2008 R2, when I am trying to startup Oracle 11g database it shows an error: SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size ...
0
votes
1answer
34 views

Datapump error in log file

When I do this: DECLARE hdnl NUMBER; BEGIN hdnl := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'TABLE', job_name=>null); DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ...
1
vote
1answer
20 views

Data Pump throws an error

I am trying to do datapump. DECLARE hdnl NUMBER; BEGIN hdnl := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'TABLE', job_name=>null); DBMS_DATAPUMP.ADD_FILE( handle => hdnl, ...
2
votes
2answers
39 views

In Oracle what is the difference between STDEV and STDEV_SAMP functions?

We have a query that should return NULL when doing standard deviations in groups that have only 1 row. The normal STDDEV function returns zero. However I found another function called STDDEV_SAMP ...
-1
votes
3answers
161 views

Incorrect query filter according to the requirement

My requirement wholly depends on the QUERY. I am filtering my gridview on the basis of three parameters. Project_name (Always required) Date (Not always) Vehicle_No (Not always) So, what my ...
1
vote
1answer
59 views

Oracle automatically unmount

I have a weird issue in my Oracle database, after 10 - 15 minutes of not using it (Not performing any query, insert on SQL Developer tool) then it will say ORA-27101: shared memory realm does not ...
0
votes
0answers
34 views

Characterset in SQL Server - Migrating data from Oracle 8i to SQL Server 2012

I'm migrating data from Oracle 8i to a new MS SQL Server instance (2012). My Oracle database has: NLS_CHARACTERSET = US7ASCII I'm completely new to SQL Server - how do I configure the SQL Server ...
0
votes
2answers
65 views

Insufficient Privileges When Connect to SYS and SYSTEM [on hold]

I have installed EBS VISION instance on my virtual machine. After installation from my windows machine I am able to connect to schemas such as APPS, HR, however, unable to connect to SYS and SYSTEM ...
0
votes
2answers
47 views

unable to connect to oracle in linux terminal in oracle VM ,TNS listner not connected

i installed oracle VM , new to oracle vm. Can some help me in connecting to oracle in Linux termial and TNS listner is not responding with IO failure on sql developer when pressing test button ...
1
vote
3answers
61 views

Retrieving the top row of data

I have very limited SQL knowledge and need to report on the top most current row of data within a table. The table holds Effective Date, Information and a Renewal Date. select DATA_KEY_ID, ...
0
votes
1answer
41 views

Synchronous data transfer from SQL Server to Oracle

I have a requirement like: The table is present both in a SQL Server Database and an Oracle database with appropriate data types. Whenever a record is inserted into or updated in SQL server ...