Alternative to temporary tables
11/01/2018 11:47
The situation: I currently have stored procedures that use global temp tables to holddiffering type of query criteria that is passed in to the procedure.

This enables me to easily do a join of these temp tables with variouspermanent tables to find data a user is interested in. However, I amrunning oracle 9.2 and am running into the unresolved bug in which ahuge amount of redo data is generated when inserting into temp tables.

This has caused havoc in our database with lots of other applicationsfailing due to no rollback space.

I know I could parse out the criteria and create some complicated whereclauses to include the criteria directly but I think there must be aneasier way. Can anyone suggest a replacement for temporary tables?I've not used many of the pl/sql collections yet.

I am not willing to upgrade to 10 just to solve this problem so anyadvice will be greatly appreciated.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
11/01/2018 11:47 - sueschoch@gmail.com schreef: Of course they are - just add the session id as a column.

Looks like you do not want a solution?- you do not want to upgrade to a version not suffering from this- you do not want a reasonable simple workaround As last resort: ask for a backport

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
11/01/2018 11:47 - Sorry, Frank, I guess I was a little too terse.

Upgrading a 24/7 production system is not a simple task. I need asolution now.

I tried your suggestion of using a permanent table in otherapplications. It does work well. The problem is if anything fails andleaves rows in the permanent table, eventually the same data can befound again if the unique id is reused resulting in errors which areextremely hard to debug. There are probably ways to get around this,but I am really looking for something that is pretty easy to implementwithout a lot of overhead of redo/undo data. In fact, I moved thatapplication from permanent tables to temp tables just for this reason.

It works very well because the application runs pretty quickly thenexits releasing any rollback. However, the application that is havingtemp table problems right now is a server that never exits. Therefore,the rollback space is never released and continually grows until ittakes up all the rollback space.

Backports have not been considered mainly for the same reasons we won'tupgrade anytime soon.

Thanks for your suggestions Frank.

Sue

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
11/01/2018 11:47 - Ooops, I misinterpreted your response. Permanent tables are not anoption as I need the contents of the table to be unique to the processaccessing the table.

Sue

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
11/01/2018 11:47 - Yes, nologging. There is a documented bug (2874489 ) in version 9.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
11/01/2018 11:47 - sueschoch@gmail.com schreef: permanent tables with the nologging option?

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below