Subscribe to our newsletter here
Introduction
Oracle Questions
Further Reading/Other resources
We continue our quest to provide the best on-line Oracle training by answering just a few of the many questions which have been sent in and provide a solution. However if you can think of a better solution or you think we've got it wrong, then please tell us. Use the contact form to send us your questions and feedback.
Recommendations for further reading to enhance your knowledge of Oracle and contains links to information on OTN and other places are available in the Oracle resources section.
See our Oracle courses for details about our Oracle training courses and SQL courses for developers and dbas either on site or off site.
New subscribers can access our Oracle 9i training material here and articles on Oracle performance tuning here and other Oracle topics here
From: Oumar DIENG
I am really new within the oracle world. Today, I would like to be able to optimize the database performance I carrying out. I have 57 tables in and each table has indexes throughout a primary key. All the SQL queries are inside the code and today I can't remove them. I can re-write them. Can you give me some good ways to follow in order to reach my aim.
There isn't much to go on here, there could be many reasons why the performance is poor. For example are the queries using the indexes appropriately? The use of the primary-key index may be being prevented by the way the query is written or alternatively the query may be using the index when it would be better for overall performance not to. It is also possible that you're querying on a column that does not have an index in which case you may want to add extra indexes but note that this will slow down update, insert and delete operations as there will be more indexes that have to be kept in line with the data. (See www.smart-soft.co.uk/Oracle/oracle-performance-tuning-tips.htm for more guidance on performance tuning)
The general advice we can offer in answer to this is three fold:
---------
From: Walter Rieppi
In this year i would like to try the Oracle9i exam to pass the CPA certification. I have a subscription to oracle magazine. I heard about another magazine which name is Oracle internal. What do you think about this magazine. Is the article easy for a newbie in Oracle or are so complicated and i can't understand it ???
We havent come across that magazine, so we can't comment on it. However we can say that the best way to learn enough about Oracle to be able to pass the Oracle9i certification exams is to download the software (or buy a cd with it on) from the Oracle Technology Network, (http://technet.oracle.com). You can either buy Oracle9i Personal Edition or get a free 90-day trial licence for any edition. Once you've got the software, play around with it: test backup and recovery, sql, changing database parameters etc.
For some extra help to ensure you pass the exams, you also need to know the theory. To learn this I would recommend you buy yourself a few decent Oracle books, such as : Oracle Essentials which provides an excellent overview of Oracle and is a good place to start (it gets 5 stars on Amazon as the average customer rating).Oracle Essentials by Rick Greenwald, et al
Oracle Essentials from Amazon.co.uk
Oracle Essentials from Amazon.com
Also recommended is The OCP Oracle 9i Certification Kit box set.
This is a boxed set consisting of 4 hardback books covering the whole DBA certification track (including SQL). These books are great for reference as well as learning the necessary material and include lots of practice questions for each chapter as well as self-test software on a cd. The whole curriculum is covered and I would say that it has everything you need to pass the exam (everything except some practical experience for which you need the software).
For some free on-line Oracle training see our web site www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm For more info about the Oracle certification path see http://www.oracle.com/education/certification/ .
---------
From: shiva shankar
Can you please let me know how I can migrate from earlier version of oracle to oracle 9i. Are there any tools available to do this migration.
The short answer is to read this document on OTN, http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90191/title.htm
Thanks for your help. I went through the site. I could get only the migration from Oracle 7 and later versions to Oracle 9i. Is it necessary that versions earlier to Oracle 7 should be first migrated to Oracle 7 or 8 before migrating to Oracle 9i? Also I need information on how applications existing in oracle 2.1 can be migrated to Oracle 9i. Also please let me know how do find the newsletter.
As far as we know, you can't migrate directly from Oracle 6 to Oracle 9i, you may be able to export from an Oracle 6 database and import it to a 9i database but that's doubtful - you would almost certainly have to migrate to Oracle7 before you could migrate to Oracle 9i but for the definitive answer you should check with Oracle support. However Oracle 6 is ancient history in the life of Oracle databases, support for this probably ended about 9 or 10 years ago so you would be more or less on your own with regards to this.
There are of course other ways of doing this, depending on the size of the database. One way is to extract all the information to flat files and then load them into the 9i database using SQL*Loader or as external tables (which amounts to more or less the same thing). Whether or not this is feasible depends on how much data you have.
Another possibility would be to write a custom program that reads data from the old database one row at at time and inserts it into the new database. For this you would need to be able to open a connection to both the Oracle 6 database and the Oracle 9 database from the same program which may not be possible, in which case if you're using UNIX you could use pipes but this might be quite slow.
As for migrating Oracle Applications, again check with Oracle support.
To subscribe to the newsletter go to www.asktheoracle.net/oracle-tips-signup.html
---------
From: R.Lakshmi Narayanan
I want to know about the Extents, Segments. And also how the data is stored in blocks. How the memory allocation is communicated with the operating system.
On the logical view of database storage you have tablespaces, segments and extents, where a tablespace comprises one or more segments (of the same type) which in turn comprise one or more extents.
Tablespaces are logical groupings of database object segments such as table segments or index segments or rollback/undo segments. Database objects such as tables, indexes, LOBs, and rollback data are stored in tablespaces. Each table, index etc will comprise one segment unless it has been partitioned in which case there will be one segment per partition (indexes and tables can be partitioned seperately, so there may not be an equal number).
Each segment contains data from only one object. Therefore an index segment will have data from one index and a table segment will have data from one table. Rollback/undo segments are slightly different in that they hold data from uncommitted transactions.
At the physical level, all data is held in data files which are stored in one or more physical blocks on the disk (although they may be cached in memory). Therefore extents comprise one or more blocks. A tablespace may be stored in one or more files on the disk but each file relates to just one tablespace.
The Oracle server process(es) make requests to the operating system to read data from the disk where necessary. and the background processes (DBWR, LGWR, ARCH) make requests to the operating system to write data to the disk.
This is a simplification of the process, for more detailed explanation see some of the books for dbas recommended in the resources section.
---------------------------------------
Looking for more Oracle tips and tricks ? Take a short cut now and subscribe to our ezin - it's the best place to find them. 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.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses woopra.com to gather statistical information about our visitors. View woopra 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-2009. All rights reserved.