oracle

How can I list ALL grants a user received?

I need to see all grants on an Oracle DB. I used the TOAD feature to compare schemas but it does not shows temptable grants etc. so there's my question: How can I list all grants on a Oracle DB? ...

Oracle: How to find out if there is a transaction pending?

I'm looking for a way to find out if there are uncommited INSERT, UPDATE or DELETE statements in the current session. One way would be to check v$lock with the current sid, but that requires read access to v$lock, which is be a problem if the DBA doesn't want to grant it. Any other ways (other than keeping track of all database commands ...

Why are my foreign keys disabled after running sqlldr?

During our build process, we run sqlldr to populate our database with some sample data. On every table that sqlldr touches, the foreign keys are disabled after sqlldr runs. According to this Ask Tom posting: sqlldr will only disable constraints that relate to other tables (eg: foreign keys) NOT the primary key. SQLLDR wil...

Connecting to Oracle 8g database from VB.net 2008

I have all the details to connect to a Oracle database on the network. I am able to connect to the Oracle database using Oracle developer express. How do I connect to Oracle db from VB.net. I added reference to system.data.Oracleclient and specified the connection details and it gave me Listener error. Please help ...

How to manage consecutive column values in table rows

A little presentation for what I want to do: Consider the case where different people from a firm get, once a year, an all expenses paid trip to somewhere. There may be 1000 persons that could qualify for the trip but only 16 places are available. Each of this 16 spots has an associated index which must be from 1 to 16. The ones on th...

Oracle: Set NLS_PARAMETERS with sqlplus

I found out my servers, running Oracle 10g, were not initializing with the same NLS_SORT value when using the sqlplus binary. Is there a way to set the default NLS_SESSION_PARAMETERS for every instance of sqlplus? PS: The idea is not to do an ALTER SESSION at the beginning of every sql script. ...

c# return resultset from Oracle store procedure and fill a DataTable

I'm a c# SQL Server developer new to Oracle programming. The code below works for setting cmdText to: "select * from myTable". I'd like to now put this in a stored procedure, I need help with how to write this Oracle stored procedure and get the results into a DataTable in my c# code. Thanks. Code: private DbProviderFactory DbFactory ...

Query to Linked Server never stops executing

I have created a linked server in SQL Server 2005 to an Oracle DB. When I run a query, The query never stops executing, and never returns results. When I cancel the query, it never completes cancelling. I have to close the window to get it to stop. I have set things up as follows: Installed Oracle Client Tools on SQL Server Ran fol...

Oracle Query Help

I have this table ID Date 1 08/10/2009 11:20:00 2 08/10/2009 12:06:06 3 08/11/2009 13:03:10 4 08/11/2009 01:20:00 5 08/11/2009 15:41:23 6 08/12/2009 14:22:20 I want to return: Date Count 08/10/2009 2 08/11/2009 3 08/12/2009 1 in PL/SQL. Any ideas? ...

Oracle Unicode Support in 10g and 11g Differences

I am running into a problem that I have not yet been able to explain. I have code that takes a number, gets the national character code for the number using NCHR, and then performs a RAWTOHEX conversion on it. It worked in 10g for years. When we upgraded to 11g it started returning different values. I boiled it all down to a few statem...

oracle xml parsing gives invalid character error

Hi, I am using Oracle 11G sql developer's Export Utility to convert the tabular data into XML. To do so, I have used utf-8 encoding. The special characters (0x13) which i see in DB as square boxes, have come into xml as it is. To get rid of this special character, do we need to select some other encoding in Sql Developer? The problem I...

Oracle outer joins - performance

EDIT 9-3-10: I found this blog entry recently that was very enlightening. http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html There are times when one or the other join syntax may in fact perform better. I have also found times when a have noticed a slight performance increase (only noticeable in VLDBs) when choosing ...

Data returned from Oracle Proc in vb.net

Hello, I have a VB.Net function which executes a Oracle Stored Proc and returns a dataset. Below is a list of sample records OrderID OrderDetail Qty Date Supplier Price 1 Books 10 10-Aug-08 ABC Inc 100.00 1 Pens 20 10-Aug-08 ABC Inc 300.00 2 Keys 1 20-Aug-09 Blue c...

Seaching for two columns in three-column composite index - Oracle

Hello, I have a composite index on three column in one of my table. It works find if I have the three columns in the where close of my query. When my search query has only two out of three, things don't seem to be as fast any more! Do you know how can go around this? Thanks, Tam P.S. The table APPL_PERF_STATS has composite index on ...

How can I keep Oracle SQL Developer from closing the DB connection?

Is there any way to keep Oracle SQL Developer from closing my DB connections, or to increase the timeout? Sometimes during a long-running query SQL Dev will just close the connection, leaving my query running on the server but me with no results. If I RDP into the server and run SQL Dev locally it never seems to have this problem. ...

How to detect an unavailable database using PHP PDO?

I am using PHP to create a process that monitors our database and puts up a maintenance page on our website if the database is down. I'm using PDO and Oracle. I am trying to use one database connection and query it once a minute, alerting people if there's a problem. However, if the database goes down, the script waits 15 minutes befo...

How to write a good regex to distinguish between Arabic and English characters in Oracle using the AR8MSWIN1256 encoding?

I've been scouring Google and can't seem to find an answer. I'm running Oracle 10g Enterprise with the following character-set: AR8MSWIN1256 The database holds English and Arabic values, and I need to differentiate between the two from time to time. I wrote this script, which matches on English words but not on Arabic words: create or ...

Oracle: FK on union of two tables?

I have a situation which (simplified) looks like this: Table UNITS has a PK on UNITS.NAME. (unit name, varchar(12)) Table DEPTS has a PK on DEPTS.NAME. (dept name, varchar(12)) I want to create a table with column UNIT_NAME_OR_DEPT_NAME, with the requirement that data in this column must be either a valid unit name or a valid departmen...

JDBC Thin Connection

Hi All, I have installed NetBeans 6.5 with JDK1.6. And I want to connect between oracle with java in NetBeans 6.5. The question is: How do I configure JDBC to java 1.6? Thanks, Sopolin ...

Is there any way to impersonate a specific database engine while running another one?

This is something I would like to see while doing my day today programming works, But I've never seen such application yet. You input is highly appreciated. Lets say we have an application that needs MSSQL server as DBMS. And suppose you just need to install it and do something. (i.e You are not going to deply it in production servers e...