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

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
43 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
67 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
59 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
402 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
161 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
304 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
140 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
523 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
46 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
38 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
698 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
41 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
324 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
71 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
616 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
31 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
49 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
82 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
218 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
492 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
41 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
63 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
737 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
261 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
346 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
37 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
40 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
119 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
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, ...
0
votes
1answer
134 views

“Credential retrieval failed” during recovery of an Oracle database

When starting up automatic instance INSTANCE: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== ...
0
votes
2answers
169 views

ORA-01410 Error

I am getting ORA-01410 and ORA-06512 for the following block of code in a trigger im firing for deletion of a record in my table. Are the calls correct ? getlong ('xxx', 'YYY', :OLD.ROWID), getlong ...
0
votes
1answer
125 views

Oracle Text: query scoring with CTXCAT index?

If a text column is indexed with a CTXSYS.CONTEXT index it can return a score showing how well the text matches the query. create index ix on t(text) indextype is ctxsys.context parameters (''); ...
2
votes
2answers
40 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 ...
0
votes
3answers
121 views

lsnrctl is not working on Ubuntu 14.04

I have managed to install Oracle 11g on Ubuntu 14.04 through lots of pain. I managed to install it in the end but listener does not seem to be working and even responding any command. My ...
2
votes
2answers
576 views

exporting DDL of all views Oracle

How can I go about exporting DDL of all my schema views in single .sql file ? I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case. I know how to ...
1
vote
1answer
670 views

Restoring Oracle prod backup to test server with new database name

I need to restore an Oracle production database (aaprod) to a test server that already is home to a test database (aatest) under a new name (bbtest). I have a cold backup of the production database ...
1
vote
1answer
2k views

Not receiving archivelogs from production to remote side

I have a scenario in which we have Designed data guard. Where we have 2 standby sides. One standby is located locally and 2nd standby is located on remote side. The distance between local and remote ...
0
votes
1answer
40 views

UTL_TCP how to monitor number of connections?

I have a leak somewhere in the number of UTL_SMTP connections being made and closed. There is an underlying limit of 16 from its reliance on UTL_TCP. I'd like to be able to monitor the connection ...
1
vote
1answer
3k views

Oracle Select with xmlquery() error - How to ignore errors?

In trying to extract a subset of data from a Oracle DB table, one of the columns is XMLType, which is extracted with xmlquery() for relevant information. The problem is that the XML data is from an ...