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
33 views

How to select all rows from column including two specific numbers within a set of digits, but not in beginning or end?

I'm attempting to retrieve all rows that include two specific characters within a set of characters corresponding to the attributable column of data. However, the specified characters can't be ...
1
vote
2answers
39 views

How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

This query: select distinct profile from dba_profiles; Returns 3 rows: default default1 default2. I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query ...
0
votes
1answer
34 views

Regarding Analyze table then performance improve

I used function which contain 3 table like a,b,c and table a contain index like ind1 on two column. Now I call store procedure which contain code and also get data from above function. And I also ...
0
votes
0answers
39 views

create table in a different schema

I am trying to create a table on a different schema without using "create any table" privilege. I have an application user/schema. TABLESPACE_NAME USEDMB MAXMB ...
1
vote
1answer
48 views

understanding ORA-01792 maximum number of columns in a table or view is 1000

We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error: ORA-01792: maximum number of columns ...
2
votes
2answers
32 views

Modify linked server definition (Oracle) in SQL Server 2012

I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works ...
0
votes
0answers
11 views

Trouble With Basic Query Searching [migrated]

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
1answer
56 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
40 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
22 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.
0
votes
0answers
30 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 [closed]

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 ...
1
vote
1answer
62 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
36 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
54 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
53 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
71 views

Problem with procedure in plsql [closed]

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
55 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
20 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
64 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
41 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
36 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
114 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
49 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
81 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
55 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
44 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
54 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
65 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
44 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
37 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
64 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
80 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
47 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
60 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
35 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
163 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 ...