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
2answers
3k views

SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle

I am trying to Export Data from SQL Server to Oracle using the "Microsoft OLEDB Provider for Oracle". I am using "Export Data..." option from Task menu. I get to connect to the source database (SQL ...
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
3k views

SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

Inside of our SQL Server, we have created a linked server to an Oracle server. We ave mapped several users to the Oracle server and specified the credentials. On the Linked Server 'Security' ...
0
votes
1answer
33 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. ...
2
votes
3answers
386 views

Archive Logs filling up Flash Recovery Area (FRA) space

I got hired to fill a position of DBA that I wasn't quite ready to fill, and while the training is getting me there, I am running close to a potential issue. My oracle 11g database's Flash Recovery ...
16
votes
13answers
5k views

Comfortable sqlplus interface?

I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available. What is a ...
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 ...
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
577 views

Running Oracle Export Utility within a Shell script

I have wasted a lot of time on this. Any ideas of what I am doing wrong here? Script in bash #!/usr/bin/bash . . . /export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \ ...
1
vote
1answer
548 views

SQLDeveloper.exe itself is getting access denied

Installed Oracle 11g and the 11g client. Everytime I start SQLDeveloper it prompts for the java.exe and again which files to associate. It must be trying to save this in registry (or maybe disk). ...
1
vote
1answer
204 views

Grant execute on specific type in Oracle

I have created a package and defined a type for collections as follows: create or replace package <schema>.<package> as ------------------------------------- type <type> is ...
0
votes
1answer
41 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: ...
1
vote
2answers
53 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 ...
7
votes
1answer
2k views

Use LDAP for name look up with Oracle Instant Client

Partly as a result of having problems with conflicting versions or bitness with the full installation of Oracle client, my development team (of which I'm a member) has been moving towards using Oracle ...
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
11k views

Trigger to automatically add values to a table in Oracle

I am new to Oracle. I am looking for a trigger which helps to automatically insert values into a table when data is entered into another table in the same database. I have tried a lot and didn't ...
-1
votes
0answers
63 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 ...
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 ...
4
votes
1answer
401 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
2
votes
1answer
132 views

ORA-12546: TNS:permission denied, on Windows through Task Scheduler

I am trying to export all objects of specific oracle user by executing a batch file through Windows Task Scheduler: exp.exe myuser/pass@dbname owner=myuser log=mylog.txt The Oracle 11.2.0.4 x64 and ...
0
votes
1answer
297 views

PL/SQL recursive lookup with cursor

I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin ...
1
vote
2answers
139 views

ORA-00257: archiver error

I ran into this error earlier today and requested that the extra logs be purged from the server. It was done but it still claims that there isn't any room. Is there a way to notify the server that it ...
1
vote
3answers
518 views

Oracle Compatible Parameter to set

Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?
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 ...
2
votes
1answer
2k views

Insert into table from a query to a linked database hangs

I have a schema with some database links to other schemas that we use to pull data from. We pull the data into a staging table, massage it, and then do some merges into our schema/database. The data ...
-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 ...
4
votes
2answers
690 views

Check if an index exists by comparing columns

We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order. ...
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 ...
1
vote
2answers
320 views

Notification email when deadlock is encountered in Oracle alert log

How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log) Node 1: Mon Sep ...
1
vote
2answers
12k 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
2answers
69 views

Select max(pk) instead of sequence.nextval

I have a client/superior who keeps suggesting that I select max(primary_key) when I insert a new row. I gut says it's VERY wrong but I don't have exact reasons to support my suggestion. The only ...
5
votes
1answer
610 views

ASM diskgroup drop from another ASM instance

I have created long time back one ASM RAC setup and I created one diskgroup with some name say xyz. I think that instance is gone but the diskgroup was not dropped exclusively using command: drop ...
-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 ...
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.
1
vote
1answer
4k views

Root cause for Invalid arguments in call (ORA-17068)

We are getting Invalid arguments in call when we try to establish the connection with Oracle DB occasionally ( One hour in a day). I did some research about this in Internet, still i am not getting ...
1
vote
0answers
79 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. ...
0
votes
1answer
216 views

Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error: ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1" ORA-06512: ...
2
votes
1answer
489 views

Reorganize Objects in OEM - Host Credentials do not have enough privileges

When I try to re-organize objects from OEM (11g) and enter my host credentials (not "oracle"), I get the message: "An error occurred verifying the host credentials. Make sure the credentials are ...
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; ...
0
votes
2answers
59 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
731 views

Toad for Oracle 64-bit : 1603 - Fatal error during installation

I'm trying to install Toad for oracle client, my Toad version is 12.1 for 64 bit system, i have succeeded to install oracle client 12c for 64 bit system, but i have stumbled by a problem when ...
3
votes
1answer
259 views

sql error on script execution. try deleting repository before creating repository

I want to migrate my mysql databse to oracle. I have followed the migration demo from this url http://www.oracle.com/technetwork/database/migration/connect-step-mysql-1946352.html but I got this ...
2
votes
1answer
2k views

Invalid date format in SSIS

I am using BIDS to export data from Oracle to SQL server. I am using OLEDB source and destination. I have around 6 datetime fields in ORACLE out of which two fields give no problem while 4 fields ...
0
votes
2answers
331 views

Oracle systimestamp result differs in db server and client

I am getting some strange output while executing the below command on db server and on client machine connected to same db server. On DB server SQL> select systimestamp from dual; systimestamp ...
1
vote
1answer
112 views

SqlDeveloper: ora-12505 TNS listener does not currently know of SID given in connect descriptor

Info connection name: ziv username:sys password: xxxxxxx connection type: basic role: sysdba host name : localhost.localdomain port: 1522 sid: ziv The QUAST is to connect to the database ...
1
vote
1answer
4k views

netca failed during installation of Oracle 12c on Windows

I have installed Oracle 12c on Windows Server 2008 and during installation it shows the following warning: [INS-20802] oracle net configuration assistant failed When I click retry it continues ...
-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 ...