Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Training - Tips and Tricks To Make Your Oracle
Systems Your Most Valuable Asset
One good way of learning Oracle is to see the questions other people have about the technology 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
The resources section has recommendations for further
reading to enhance your knowledge of Oracle and
contains links to information on OTN and other places.
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
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
recover database until time <time before loss>
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
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 scheduled Oracle training courses, or let us know 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
Tel: 0845 003 1320
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
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.