I'm in the process of building a database storage for my app consisting on a single table with a huge data volume (hundreds of millions of records). I'm planning on having an index on the date field, since I'll be doing a batch recovery of all the records in a given period of time every now and then (for example, retrieving all records f...
I have a DATE column that I want to round to the next-lower 10 minute interval in a query (see example below). I managed to do it by truncating the seconds and then subtracting the last digit of minutes. WITH test_data AS ( SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-0...
How to do correct conversion of data from Oracle VARCHAR2 type to MySQL TEXT type? I want insert it into mysql table in field with TEXT type through DBLink. ...
In Oracle I can grant privileges on my schema to another user so that the other user has access to my schema (and in turn objects in my schema). How do I programmatically ,using ADO.NET (either MS or Oracle's provider), obtain a list of schemas to which a particular user has access? ...
I need to change temporarly (in order to run a proccess) the archiving mode to noarchivelog. After switching back to archivelog, do I need to open the database with resetlogs option? ...
When I go to run the t4 templates, the result comes out "Compiling transformation: The name 'TableSpaceName' does not exist in the current context" -- any ideas on this? I thought it might be a namespace/reference issue, but it doesn't seem to be part of the ODP. This is one of those "it has to be something stupid" ... why? I've got su...
I have a date in the format of: 27-MAY-09 12.00.00.000000 AM I want to convert it to: 05/27/2009 I did to_char(my_date_variable, 'MM/DD/YYYY') however that gives me character to number conversion error what can I do to convert this date? my_date_variable is declared as: my_date_variable VARCHAR2(40); ...
My organization is thinking on using an open source DBMS (free if possible, but not mandatory) for all new apps that we develop. Since we already have quite a few apps that connect to an oracle server, we would like to use a DBMS that is as compatible as possible with Oracle. So we can port the apps to oracle with the less work in the ca...
An index on two columns can be created with either of the statements create index foo_ix on foo(a,b); create index foo_ix on foo(b,a); How does this affect the operational (runtime) characteristics of using the index? How does this affect the layout (physical) characteristics of the index? Are either (1) or (2) affected by the types/...
I have to query all_objects table where last_ddl_time='01 jan 2010' but it refuses the date format... Any body give me the exact format to query? ...
Hi, I've a table in an Oracle (10g XE) database, and I'm going to clean it up and only keep the three recent records of each account. Here is what I'm doing right now: CREATE TABLE ACCOUNT_TRANSACTION_TMP NOLOGGING AS SELECT * FROM ACCOUNT_TRANSACTION WHERE 1=2; DECLARE CURSOR mbsacc_cur (account_id_var account_transaction.account...
I am using SQLloader in ASP.NET appliaction to automate the bulk upload data to oracle db from CSV/EXCEL. Sqlloader creates a log file that shows the result of import that is created on server. I want to show some info to my users How many rows read? How many successfully imported? on an aspx page How do I do that? ...
I have a basic sql statement that looks up a user and returns one record but when I run a block of code that says if(myReader.Read()) it returns false. I have stepped through the code and examined the reader object and it does in fact contain one record. below is the code. sql: SELECT user_name, user_password, user_state FROM users W...
I have a one-to-many mapping that works in sql lite, but then blows up in Oracle. If I make a slight change, it will work in Oracle, but not in SQLITE. Here's my mappings: This works in SQL Lite <many-to-one class="NHibernate.Spike.Data.Entities.ClientRecord, NHibernate.Spike.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nul...
As stated in the title, when designing databases, what is the preferred way to handle tables that have multiple columns that are just storing true / false values as just a single either or value (e.g. "Y/N: or "0/1")? Likewise, are there some issues that might arise between different databases (e.g. Oracle and SQL Server) that might affe...
Is there a equivalent clause to CONNECT BY of Oracle in SQL Server. The requirement to build a category tree using a parentId field. ...
I had am maintaining an query that is as follows: select field_1, field_2 from source_table minus select field_1, field_2 from source_table where status_code in (3, 600); When I looked at this query, I immediately thought, "That's lame. Why not just use a 'NOT IN' and remove the MINUS business. So I re-wrote it as so: select field_...
I'm trying to build a mailbox where we can group the messages in x. If you put x to 20 you'll see messages 1-20 on the first page, opening the second page will show message 21-40 etc. How do I efficiently query this? The best I could come up with is this: select top 20 * from tbl_messages where tnr_id not in ( select top 40 tnr_i...
When programming directly in JDBC against an Oracle database you can call stmt.setFetchSize(fetchSize) on the statement to determine the max number of records to fetch in one round trip from the small default value. I'd like to do this from withing Hibernate 3.2. Can someone tell me where this would be set? ...
Using Oracle 10g. I'm writing a script to apply the delta changes made on our development system to our stage system. Is there a way to modify or insert an individual procedure within a package without including the entire package contents in my script? I think the answer is no, but I wanted to be sure because it's going to be pretty ...