What table/view do you query against to select all the table names in a schema in Oracle?
What object do you query against to select all the table names in a schema in Oracle? ...
oracle
How do I reset a sequence in Oracle?
In postgresql I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there a oracle equivalent? ...
Oracle lost sysdba password
We are working with an oracle database in which the person that set it up is "long gone" and thus do not know the sysdba password, but need it. We have root access to the box (its on linux). Is there any way to recover or change the sys passwords? ...
Hibernate crops clob values oddly
I have a one to many relationship between two tables. The many table contains a clob column. The clob column looks like this in hibernate: @CollectionOfElements(fetch = EAGER) @JoinTable(name = NOTE_JOIN_TABLE, joinColumns = @JoinColumn(name = "note")) @Column(name = "substitution") @IndexColumn(name = "listIndex", base = 0) @Lob privat...
Getting hibernate to log clob parameters
(see here for the problem I'm trying to solve) How do you get hibernate to log clob values it's going to insert. It is logging other value types, such as Integer etc. I have the following in my log4j config: log4j.logger.net.sf.hibernate.SQL=DEBUG log4j.logger.org.hibernate.SQL=DEBUG log4j.logger.net.sf.hibernate.type=DEBUG log4j.logg...
using DBMS_APPLICATION_INFO with Jboss
Does anyone have examples of how to use DBMS_APPLICATION_INFO package with JBOSS? We have a various applications which run within JBOSS and share db pools. I would like, at the start of each session these applications to identify themselves to the database using DBMS_APPLICATION_INFO so I can more easily track which sections of the app...
How to resolve ORA-011033: ORACLE initialization or shutdown in progress
When trying to connect to an ORACLE user via TOAD (Quest Software) or any other means (Oracle Enterprise Manager) I simply get the error: ORA-011033: ORACLE initialization or shutdown in progress [in the spirit of StackOverflow I will answer this one myself - at least I'll relate how I resolved it in my case - please also relat...
How can I kill all sessions connecting to my oracle database?
I need an sqlplus script to quickly kill off all external sessions connecting to my oracle database without the supervision of and administrator. ...
How to see the actual Oracle SQL statement that is being executed
I'm using a custom-built inhouse application that generates a standard set of reports on a weekly basis. I have no access to the source code of the application, and everyone tells me there is no documentation available for the Oracle database schema. (Aargh!) I've been asked to define the specs for a variant of an existing report (e.g.,...
Double Quotes in Oracle Column Aliases
Ok, this is bit of an obscure question, but hopefully someone can help me out with it. The system I'm working on builds a dynamic SQL string for execution inside a stored procedure, and part of that dynamic SQL defining column aliases, which themselves are actually values retrieved from another table of user generated data. So, for exa...
Proving SQL query equivalency
How would you go about proving that two queries are functionally equivalent, eg they will always both return the same result set. As I had a specific query in mind when I was doing this, I ended up doing as @dougman suggested, over about 10% of rows the tables concerned and comparing the results, ensuring there was no out of place res...
Oracle from .Net with a 64 bit client
Has anyone had any luck of using Oracle from .Net on a 64 bit machine, and using the UDT capabilities of Oracle? I've been able to use an x64 ODP.Net client, but cannot find one with any support for UDTs. Thanks Nick [Edit] I've posted an answer below. The latest (as of December 2008) release is 11.1.0.7. This has support for 64 bit ...
SQL/Oracle: when indexes on multiple columns can be used
If I create an index on columns (A, B, C), in that order, my understanding is that the database will be able to use it even if I search only on (A), or (A and B), or (A and B and C), but not if I search only on (B), or (C), or (B and C). Is this correct? ...
SSIS Oracle Parameter Mapping
When trying to enter a SQL query with parameters using the Oracle OLE DB provider I get the following error: Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command i...
Ambiguity in Left joins (oracle only?)
My boss found a bug in a query I created, and I don't understand the reasoning behind the bug, although the query results prove he's correct. Here's the query (simplified version) before the fix: select PTNO,PTNM,CATCD from PARTS left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); and here it is after the fix: select PTNO,PTNM,P...
How do i find duplicate values in a table in Oracle?
What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table? For example: I have a JOBS table with the column JOB_NUMBER - how can I find out if I have any duplicate JOB_NUMBERs, and how many times they're duplicated? ...
Wrap an Oracle schema update in a transaction
I've got a program that periodically updates its database schema. Sometimes, one of the DDL statements might fail and if it does, I want to roll back all the changes. I wrap the update in a transaction like so: BEGIN TRAN; CREATE TABLE A (PKey int NOT NULL IDENTITY, NewFieldKey int NULL, CONSTRAINT PK_A PRIMARY KEY (PKey)); CREATE IN...
Howto import an oracle dump in an different tablespace
I want to import an oracle dump into a different tablespace. I have a tablespace A used by User A. I've revoked DBA on this user and given him the grants connect and resource. Then I've dumped everything with the command exp a/*** owner=a file=oracledump.dmp log=log.log compress=y Now I want to import the dump into the tablespace ...
What's the best way to save and retrieve binary files with Oracle 10g?
I'm about to implement a feature in our application that allows the user to 'upload' a PDF or Microsoft PowerPoint document, which the application will then make available to other users in a viewer (so they don't get to 'download' it in the 'Save as..' sense). I already know how to save and retrieve arbitrary binary information in data...
Batch insert using JPA/Toplink
I have a web application that receives messages through an HTTP interface, e.g.: http://server/application?source=123&destination=234&text=hello This request contains the ID of the sender, the ID of the recipient and the text of the message. This message should be processed like: finding the matching User object for both th...