I'm trying to connect to an Oracle 10g database using the built in Microsoft ODBC for Oracle driver. I want to use an dnsless connection, so I grab my connection string from www.connectionstrings.com. Ideally I won't have to setup a DNS entry or an Oracle TNS entry, and I may be mistaken, but I thought the above would do that for me. ...
This is referring to the question i asked before, and got a really quick answaer (http://stackoverflow.com/questions/751156/max-count-together-in-an-sql-query). The Problemset is similar, but the solution in the prevous question would force me to access a db in a loop which will cause performance problems. So what i have now, after some ...
By looking into the Open JPA website i've found that i can log the generated SQL by using the following: <property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO"/> If i try to add the above property to my persistence.xml i get the following warning from weblogic: <Warning> <J2EE> <BEA-160202> <You have specifie...
A bit of background first. My company is evaluating whether or not we will migrate our Informix database to Oracle 10g. We have several ESQL/C programs. I've run some through the Oracle Migration workbench and have been muddling through some testing. Now I've come to realize a few things. First, we have dynamic sql statements that are n...
I have a SQL query that looks something like this: SELECT * FROM( SELECT ..., row_number() OVER(ORDER BY ID) rn FROM ... ) WHERE rn between :start and :end Essentially, it's the ORDER BY part that's slowing things down. If I were to remove it, the EXPLAIN cost goes down by an order of magnitude (over 1...
On an Oracle 10g table which has heavy read/write we occassionally get a huge spike in concurrency quoting "latch library cache" using all the CPU. What approaches can we take to lessen the load within the database. We've seen stuff about free lists and increasing those. Any other opinions. ...
Are there any good ways to objectively measure a query's performance in Oracle 10g? There's one particular query that I've been tuning for a few days. I've gotten a version that seems to be running faster (at least based on my initial tests), but the EXPLAIN cost is roughly the same. How likely is it that the EXPLAIN cost is missing ...
I have the following SQL querry: select ID, COLUMN1, COLUMN2 from (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE) where NO between 0 and 100 What I am trying to do is to select the first 100 records of the query select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC And here are the ...
I have a program for the company This company is more than one section and the database is the same status in each section Ardid and that the imported data from the branches every day or as needed to the database in the Head Office and sent via Aldata Balimiol for example, how can I, and no error message that the data already exists o...
On oracle 10gr2, i have several sql queries that i am comparing performance, but after first run, the v$sql table has the execution plan stored for caching, so for one of the queries i go from 28 seconds on first run to .5 seconds after. I've tried ALTER SYSTEM FLUSH BUFFER_CACHE; -- after running this, the query consistently runs at ...
Hello, I've tried to install "Oracle10g Release 2 ODAC (64-bit) 10.2.0.3 for Windows x64" from: http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html on Windows 2008 server 64bit, but it crushes right after i run the setup.exe with the following error: Problem signature: Problem Event Name: APPCRASH ...
I'm trying to copy data from one Oracle schema (CORE_DATA) into another (MY_DATA) using an 'INSERT INTO (...)' SQL statement. What would the SQL statement look like? Thanks, ...
Oracle 10g has Profiles that allow various resources to be limited. Here are some references for clarity - orafaq.com, Oracle Documentation. I am particularly interested in limiting CPU_PER_CALL, LOGICAL_READS_PER_CALL, and COMPOSITE_LIMIT with the goal of preventing a poorly formed statement from destroying performance for every other...
Hi I have following data in the table as below and I am looking for a way to group the continuous time intervals for each id to return: CREATE TABLE DUMMY ( ID VARCHAR2(10 BYTE), TIME_STAMP VARCHAR2(8 BYTE), NAME VARCHAR2(255 BYTE) ); ID, min(TIME_STAMP) "startDate", max(TIME_STAMP) "endDate", NAME group by ID ,...
I am new to the Oracle 10g Resource Manager and am looking for guidance on how to put together a plan to meet my goals. I will test the plan, but I'm hoping for some guidance so I don't have to try hundreds of different plan configurations. I have the following goals: Don't let non-sys sessions significantly slow down sys sessions. D...
here's the stored procedure i wrote.In this proc "p_subjectid" is an array of numbers passed from the front end. PROCEDURE getsubjects(p_subjectid subjectid_tab,p_subjects out refCursor) as BEGIN open p_subjects for select * from empsubject where subject_id in (select column_value from table(...
Hi, I was trying to create new role that will have all privileges of the PUBLIC role and then remove all of the privileges from PUBLIC role afterwards. This is for security purposes. This is the problem. I couldn't grant SYS./1005bd30_LnkdConstant, and others with the same format, to my new role. sample: SYS./10076b23_OraCustomDatumCl...
I've been looking at switching many of our Oracle tablespaces over to use automatic segment space management. I know that it helps by managing freelists and the like, but I'm curious if anyone has seen any disadvantages to using this option? Major advantages? Our environment is somewhat a hybrid between an OLTP and Data Warehousing w...
I've been using Oracle for quite some time since Oracle 8i was released. I was new to the database at that time and was taught that it was best to use constant sized extent sizes when defining tablespaces. From what I have read, it seems that today using 10/11g, that Oracle can manage these extent sizes for you automatically and that i...
I'm trying to get the percent space used of flash recovery area (FRA) of my databases. I tried to get the percent space used of FRA of one database in OS level using VB script and I get around 3.02MB usage of the allocated 100GB (shown by db_recovery_file_dest) which is less than 1% space used. The script just get the folder size from D...