I access several tables remotely via DB Link. They are very normalized and the data in each is effective-dated. Of the millions of records in each table, only a subset of ~50k are current records. The tables are internally managed by a commercial product that will throw a huge fit if I add indexes or make alterations to its tables in ...
I've got an SQL-script executed by SQL*Plus, that needs to run with Oracle 10g and Oracle 11g. That script gives grants on a package that does not exist before 11g: GRANT EXECUTE ON sys.dbms_result_cache TO my_user; I would like to avoid the exception on 10g, since I want to react to other exceptions in the script. One way is to u...
it's possible to alias all column with a prefix, in a select? I have a group of tables in an Oracle db that I need to join together, and most of them have the same column names. I would like to have something like select MAGIC_ADD_PREFIX("PREFIX", *) from TABLE and have a result like +---------+----------+----------+ |PREFIX_ID|PREFI...
I would like to know if the following steps are possible and how fast this is: Create a partition named part1 in Table A Drop partition part1 in Table B Import the Table A partition part1 into Table B Can you provide me with an example if it is possible indeed? Or any resources I can look at? Note that the tables would have the exac...
Hi, I have table with "varchar2" as primary key. It has about 1 000 000 Transactions per day. My app wakes up every 5 minute to generate text file by querying only new record. It will remember last point and process only new records. Do you have idea how to query with good performance? I am able to add new column if necessary. What ...
I have a .net desktop app that connects to an oracle database. At times, there are very noticable delays. I ran a trace on the code, and it was always in the DataReader.Read(). I turned on sql tracing, and found the following, which corresponds to the delays I'm seeing: (2128) [23-MAR-2010 13:00:07:310] nsprecv: reading from transpor...
I have some Java code that connects to an Oracle database using DriverManager.getConnection(). It works just fine on my Windows XP machine. However, when running the same code on a Solaris machine, I get the following exception. Both machines can reach the database machine on the network. I have included the Oracle trace logs. Mar 2...
How would I store the result of a select statement so I can reuse the results with an in clause for other queries? Here's some pseudo code: declare ids <type?>; begin ids := select id from table_with_ids; select * from table1 where id in (ids); select * from table2 where id in (ids); end; ... or will the optimizer do this for ...
I am trying to build an Oracle stored procedure which will accept a table name as a parameter. The procedure will then rebuild all indexes on the table. My problem is I get an error while using the ALTER command from a stored procedure, as if PLSQL does not allow that command. ...
I am wondering about the performance of this index: I have an "Invalid" varchar(1) column that has 2 values: NULL or 'Y' I have an index on (invalid), as well as (invalid, last_validated) Last_validated is a datetime (this is used for a unrelated SELECT query) I am flagging a small amount of items (1-5%) of rows in the table with thi...
I asked a question yesterday which got answers but didnt answer the main point. I wanted to reduce amount of time it took to do a MINUS operation. Now, I'm thinking about doing MINUS operation in blocks of 5000, appending each iterations results to the cursor and finally returning the cursor. I have following: V_CNT NUMBER ...
I have a script that makes use of a package (PKG_MY_PACKAGE). I will change some of the fields in a query in that package and then recompile it (I don't change or compile any other packages). I run the script and I get an error that looks like ORA-04068: existing state of packages has been discarded ORA-04061: existing state o...
I have some oracle calls that I am porting. Today I came across this code which looks like "procedural" language, but is not declared in a function or anything... My question is: Can postgres handle this in this form? What form does this need to be in? DECLARE BEGIN IF :start_time IS NULL OR :start_date IS NULL OR :end...
Is it possible to get ref cursor from oracle stored procedure by using DAAB from Microsoft Enterprise Library 4.1? ...
I am writing a sql query which involves finding if timestamp falls in particular range of days. I have written that in the postgres but it doesn't works in Oracle and SQL Server: AND creation_date < (CURRENT_TIMESTAMP - interval '5 days') AND creation_date >= (CURRENT_TIMESTAMP - interval '15 days') Is there are common way to compar...
How could I adapt this query to show the previous, say, 61 weeks? select to_char(order_date,'IYYY') as iso_year, to_char(order_date,'IW') as iso_week, sum(sale_amount) from orders where to_char(order_date,'IW') <> to_char(SYSDATE) --exclude this week in progress and to_char(order_date,'IYYY') = 2010 group...
Can i use a proxy service without a business service while configuring message flows in Oracle service bus? If so is what scenarios would we use it? ...
I am getting ORA-02049 occasionally for some long-running and/or intensive transactions. There is seemingly no pattern to this, but it happens on a simple INSERT. I have no clue how to get any sort of information out or Oracle, but there has to be a way? A log over locking or atleast a way to see current locks? ...
I am working on a test server with an Oracle 11g installed. I was wondering if there is anyway I can replicate the database(environment + data) on my local Linux machine. I am using a CentOS 5.3 on Windows XP with SUN Virtual Box. On Windows I am using sqldeveloper client to connect to the 11g database. ...
I am getting an error about the assembly "C:\Ora10g\bin\Zip.exe". The trouble is this solution does NOT use anything in Oracle at all. I could not find a single reference to 10g anywhere in the project. I inherited this from another person who left our group. He never had this issue. Another member of my team said he got this before but ...