Oracle Training - Make Your Oracle

Systems Your Most Valuable Asset

An good way to learn Oracle is to see the questions other people have asked and here we answer a few of these questions. To send us your questions (or feedback) use the contact form. To subscribe just click here. Our resources page has recommendations for further reading as well as links OTN and other useful sites. More Oracle tips and tricks are available here and we also offer formal training for developers and dbas.                                -----------------------------------------------  In this issue: • Oracle database recovery • Storing frequently accessed results in the Oracle database From: Anurag -  I'm using Oracle on Windows. Please let me know how to recover database if I could not backed it up (running in archive log mode) and a table has been dropped by mistake. Thanks in Advance If you don't have any backup of your Oracle database, you can't restore the table, no matter what mode the database was running in. If you do have a backup and have been running in archivelog mode, restore the data file that contained the dropped table, start and mount the database (but don't open it) then at the sql prompt enter: recover database until time  <time before table dropped> or find the highest system change number before the table was dropped and issue the following command after starting and mounting the database as before: recover database until change <highest scn before table was dropped> If you haven't been running in archivelog mode, then you can only restore to the time of the last cold backup.                                                             --------- From: Paramesh in Singapore - We are using oracle.I have some problem in SQL. We are retrieving data from 6 tables using join .(apprx'mtly returns 500 rows). What happens more than one user call the function. Each and every time it retrieve data from tables (same data) . Some time System time out will occur. Is it possible to store somewhere instead table ie some object? Whenever u want u can retrieve data from object instead Table directly. How to store in object in Oracle? thkx Param There are a few possible solutions to this problem depending on which version of Oracle you’re using. First, if the same data is being requested several times by the same user  then storing the result of the query in a pl/sql collection is one choice. This will ensure the data is stored in memory and be quickly accessible to subsequent queries by the same user. An alternative is to use materialised views which are effectively pre-run queries with the results of the query is stored in a special table. Materialised views have the advantage that they can be set to be refreshed automatically by Oracle whenever any of the underlying tables change, or they can be refreshed on a pre-determined frequency or refreshed manually on demand. Note that the initialisation parameter QUERY_REWRITE_ENABLED must be set for the optimizer to be able to dynamically rewrite queries to use any materialized views. The 3rd option is to re-design your Oracle database so that fewer tables have to be joined to get the information you want! This might sound like a glib answer, but if your application often has to join many tables, you might want to denormailize some of them. The 4th option is to run explain plan for the query and make sure the optimiser is generating a good execution plan. This should happen automatically but it's worth checking. You could also use hints to produce a better execution plan if necessary. If you’re using Oracle 11g or above, there is a 5th option which is to set aside some memory in the SGA to be used for caching query results and/or caching PL/SQL function results by specifying a non-zero value for the database initialisation parameter RESULT_CACHE_MAX_SIZE. Set the associated parameter  RESULT_CACHE_MODE to MANUAL if you want to select which results are cached or FORCE if you want as many results as possible to be cached (depending on the size of the result cache). For more Oracle help, see the many free articles and tutorials on our site. We also offer formal Oracle training for developers and dbas both on and off site  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our Oracle training schedule, or tell us your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England  Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.    View our privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.