John Seaman, Editor, To subscribe just go to www.asktheoracle.net/oracle-tips-signup.html
Introduction
Oracle Questions
Further Reading/Other resources
It's been quite a while since we've published any questions and answers, so we figured this would be a good time to answer some of the questions which have been sent in over the past few months and if you can think of a better solution please let us know.
The resources section has recommendations
for further reading to enhance your knowledge of Oracle and contains links
to information on OTN and other places. For specific articles about Oracle
9i go to http://www.smart-soft.co.uk/Oracle/oracle9i.htm
and for advice on general Oracle performance tuning go to
http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm
From: niti joshi
In Oracle, How can I make a Procedure work like a Function?
Perhaps I'm missing something, here, but the short answer is "You can't!".
A function must return a value. Trying to exit a function without providing a return value causes an exception "ORA-6503: PL/SQL: Function returned without value". As functions always return a value, it can be used wherever an expression can be used, which means that it must be part of an executable PL/SQL statement.
Procedures, on the other hand, are not obliged to return a value (via a parameter), they are also standalone executable statements in PL/SQL, which means that they can't be used in an expression or be part of a PL/SQL statement.
An excellent guide and reference to PL/SQL is "Oracle PL/SQL Programming" by Steven Feuerstein which I have personally found to be invaluable when developing in Oracle.
Oracle PL/SQL Programming at Amazon UK
For an excellent book on the whole aspect of designing systems around an Oracle database, have a look at "Oracle Design" . Although over 5 years old, this book should still be on every DBA's and developer's reading list because the book is focused on the principles of good system design, which haven't changed much, rather than implementation details, which have changed. It is an easy read, but explains and explores fundamental principles that everyone involved with Oracle databases should know.
---------
From: Waqas Chaudhry
Today I got chance to read your article about optimizing joins which was quite informative for me. Actually we have one query that we need to run quite often in our database and it takes up all the resources of our system. I am sending you that query. I shall be very thankful to you, if you can help me in optimizing joins used in that query. There are 13 tables we are accessing in that query. I am also sending you info about number of rows in that table.
This is a fairly complicated query and it's little surprise therefore that it takes up all the resources in the system. The first thing that struck me about this query was the number of sums that are being performed and the fact that this query is run quite frequently.
One way to avoid this is to create summary/intermediate tables which can be updated as required. This would avoid the need to calculate SUMs every time. If you're running Oracle8i materialized views do the same thing. They also have the advantage that you can specify how often and when these materialised views are refreshed and the referesh will be performed automatically by the database.
The other thing that struck me about this query was the sheer complexity of it, which makes it difficult to understand and therefore difficult to understand. If faced with a view like this, my instinct would be to break it up into views and/or PL/SQL functions. Both of these techniques would enable you to hide some of the complexity of this query, however using PL/SQL functions would enable you to have full control over the way the query operates and in Oracle 9i you can make a function look like a table by making use of the PIPELINED facility. This means the function can return rows of data rather than single elements and thus be queried exactly as if it were a table.
One very good book on performance tuning is "Oracle Performance Tuning" by Mark Gurry and Peter Corrigan. This book gets 4.5 stars on Amazon as the average customer rating and is a book I've used for several years.
For more Oracle performance tuning tips irresepective of which version of the Oracle database you're using, hop over to http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm
---------
From: Raj Masih
This is Raj (Programmer) from Christian Medical College & Hospital, Ludhiana (India). Sir, recently we've upgraded our network from foxpro to VB n Oracle n we're not much fluent in Oracle. So, I would like to ask that how do we restore the backup data files in Oracle 9i in Unix Platform n DOS as well. Thanx.
No mention has been made as to what sort of backup was taken, so I will assume you made a full cold backup, by which I mean that the database was shut down normally (that is by one of the commands "shutdown normal", "shutdown immediate" or "shutdown transanctional" and not due to an instance failure) before the files were backed up.
If this is the case, then as long as the backup includes all the data files, all the control files, all the redo logs and init.ora/spfile.ora (the parameter file) then you will be able to restore the database by copying the files back to their original locations (which should be accomnplished automatically by your backup software). Then you can restart the database by running sql*plus (or svrmgr if you're using Oracle 7 or 8), connecting to the target database, and entering "startup". After a short delay the database will be ready for use.
---------
From: RvNair
Hello, i know that priamry key means not null & unique key means i can store null value,but then why is primary key considered better than unique key?plz help me thanx in advance, rgds, rekha
The other major difference between a primary key and a unique key is that a table can have only 1 primary key, although this may be a composite key (composed of more than one column), but can have any number of unique keys. The primary key constraint is supported automatically by a unique index and represents the primary access route for the table, secondary indexes can be unique or non-unique. For example the table "department" might have a primary key of "id" and might have a secondary unique index on "name". These indexes would ensure that the department id is unique and not null and that the department name is unique.
---------
From: Narayan
would u please help me to take backup& recovery in oracle9i I'd be more grateful.
The easiest way to take a backup of an Oracle database irrespective of which version you're using is to do a full cold backup, alos known as an offline backup. This involves shutting down the database cleanly ("shutdown normal", "shutdown immediate" or "shutdown transanctional") and then using the operating system commands to copy the data files, control files, redo log files and the parameter file to another disk or to tape. In the event of a disaster requiring recovery of the database, you will be able to restore it to the point at which the last backup was taken.
Other backup methods include on-line backups, made whilst the database is still open, by puting a tablepsace into backup mode temporarily (to suspend writes to objects in that tablespace) so that the files belonging to the tablespace can be copied. This also requires the database to be running in archivelog mode, so that the redo logs are archived as they are filled before they are overwritten. Recovery in this case, involves restoring the lost/corrupted data files and then recovering the database using the archived log files.
Both of these methods are physical backups. Logical backups can be made using the export utility and database objects (tables,indexes,constraints etc) recovered using import. Export can be used to backup the whole database, the objects belonging to one or more specified users, or a specified set of tables (and associated indexes). In Oracle 9i, export can be used to backup all the objects in a specified tablespace.
For recommendations on books explaining backup and recovery and other subjects, see the resources section.
---------
From: Hatem AHRIZ
Is the EXPLAIN PLAN command only available to DBAs?
PS. The utlxplan.sql script is only available on the server.
Explain plan is available to everyone as long as the DBA's have created and granted access to the plan table (and created a public synonym for it). Type "Desc plan_table" in sql*plus or whatever tool you use to access the database. If it doesn't exist ask the DBA to create it and/or grant access to it and/or create a synonym for it. The utlxplan.sql script is used to create the plan table in the user's schema.
---------------------------------------
Looking for more Oracle tips and tricks ?
If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.