🚀 go-pugleaf

http://searchoracle.techtarget.com/generic/0,295582,sid41_gci1342609,00.html

here's a look back at some of our most popular expert questions and answers 
of 2008.
TABLE OF CONTENTS
   1. What is the ORA-03113 error? 
   2. How to get history of modified data from Oracle databases 
   3. How DDL and DML commands work in Oracle 
   4. How to do a hot backup when Oracle Database is up 
   5. When should an Oracle database be reorganized?
   6. Tips for how to size an Oracle server
   7. When to use CHECK constraints in SQL
   8. When to do a manual upgrade vs. export and import
   9. What is the difference between a database engineer, architect and 
administrator?
   10. How to archive and decommission a database
 What is the ORA-03113 error?
Q: We have an Oracle 10g database and get the ORA-03113 error sporadically. 
This is a science application where we query time-series data from a vb.net 
application. We can query successfully all day with some parameters 
(location, variable identifier, and date range) but when the date range 
changes beyond a (arbitrary?) limit (e.g. 2003 to 2007 works every time, 
but 2002 to 2007 fails every time), this error appears. We get a similar 
error using Oracle's sqldeveloper, though the error message text is 
different. Where do we start looking for answers?

A: ORA-3113 errors mean that your network connection to the database was 
severed. In my experience, there are two problem areas. One, something in 
the network failed between the client and the database. Two, a bug in the 
database caused the session to be terminated and the client thinks a 
network error was the cause. If you are sure your network is fine, then it 
is probably a bug. Make sure you have the 10.2.0.3 patchset installed. 
After that, file a Service Request with Oracle Support. Only they will be 
able to help you with the ORA-3113 error.
Learn more about solving Oracle errors.

 What is the ORA-03113 error?
Q: How can you get the history of data modified from an Oracle database?

A: There are a few options at your disposal. After the fact, you can mine 
your archived redo logs using Oracle's Log Miner utility. Before the fact, 
you can set up auditing to record changes...
Read more about these options from expert Brian Peasland here.

 How DDL and DML commands work in Oracle
Q: What happens in the background when we execute DDL or DML commands?

A: First, Oracle parses the statement and ensures that the statement is 
syntactically correct and semantically correct. Next, Oracle verifies that 
the user has permissions to perform the statement. If the user can execute 
the statement, Oracle obtains all locks necessary for the statement to 
complete. Once the locks have all been obtained, Oracle executes the 
statement. In some cases, results are returned. If results are returned 
from the statement, Oracle fetches those results and places the result set 
in an area called a 'cursor'. The cursor is passed back to the application.
Read more about the definitions of DDL and DML commands

 Hot backup when Oracle Database is up
Q: What is the procedure for doing a hot backup when the database is up?

A: There are two ways. The preferred way with Oracle's databases today is 
to use RMAN. The following will perform a hot backup using RMAN.
Read expert Brian Peasland's recommended steps for using RMAN and learn 
more about RMAN and hot backups.

 When should an Oracle database be reorganized?
Q: When should a database be reorganized?

A: Never. A well-designed database should never need reorganization. It 
used to be that a DBA would perform a weekly or monthly reorg to help 
database performance. But today's Oracle databases do not need regularly 
scheduled reorgs to perform well. Bad design can throw this theory out the 
window though.
Read about some expert tips for good database design.

  Tips for how to size an Oracle server
Q: Do you have any suggestions on where I can find the following 
information, with recommended server hardware specifications for how to 
size an Oracle server? I'll be using storage area network (SAN) storage.
Number of processors
Quantity of RAM

I've searched the Oracle site and even though there are a number of white 
papers, I haven't seen anything like the old 9i sizing guide.

A: I have not seen any sites or other pieces of information that would give 
you the information that you seek. And I would be leery of the information 
presented. The amount of memory, processors, and other system resources are 
not dictated by the total volume of data in the database.
Learn what expert Brian Peasland thinks is the best way to size the server.

 CHECK constraints
Q: How can I create a constraint to not allow a date prior to 1 Jan 2007 to 
be entered?

A: This is accomplished with a CHECK constraint. Here's an example:
    create table documents
    ( id    integer     not null primary key 
    , title varchar(99) not null
    , added date        not null
    , constraint only_new_ones 
         check ( added >= '2007-01-01' )         
    );

Read what else SQL expert Rudy Limeback has to say about CHECK constraints 
and find answers to other SQL questions.

  Manual upgrade vs. export and import
I am ready to upgrade. My database size is 200gb and it is an unsupported 
version of Oracle to upgrade to 10g(8.1.6.2 to 10g). It's O/S is on HP-UX, 
so could you tell me whether the best process for upgrade is to follow the 
manual upgrade process or by using the export & import to the new Oracle 
Database? If I follow the manual method then I have to apply the 8i to 8i 
Patches then to 10g . If I follow exp/imp then I need more down time. Which 
process should I follow?

A: If you perform the manual upgrade process, then you will have to be at 
least at Oracle 8.1.7 before you can begin. So to go from 8.1.6.2 to 10g, 
you will need two upgrades with this path. If you use export/import, then 
you will only need one upgrade. On average, each manual upgrade will take 
about 1 hour, but this time can vary. So without knowing more about your 
environment, the manual upgrade process will require about 2 hours of 
downtime. Using export/import for a 200GB database will probably take 
longer. So the big question to answer here is how much downtime can you 
take for the upgrade? If you had a larger downtime window, then you might 
use exp/imp for the upgrade. The exp/imp method also lets you perform some 
reorganization like moving objects to new tablespaces.
Read more about upgrading in our Upgrading Oracle All-in-one-Guide.

  What is the difference between a database engineer, architect and 
administrator?
Q: Could you please explain the differences between a database engineer, a 
database architect and a database administrator?

A: Great question! It can get pretty confusing as these titles (and many 
more including database developer, data architect, data analyst, data 
modeler, database designer, etc.) are often intermixed, used improperly, or 
redefined by the companies using them. The definition really depends on the 
actual job descriptions as defined by the various companies posting them. I 
will do my best, however, to differentiate and simplify them for you...
Read expert Michael Hillenbrand's descriptions of these three jobs.

  How to archive and decommission a database
Q: What would be the best way to archive and decommission a 5GB Oracle 7.3 
Database? This data needs to be available for at least 20 years from now in 
case a regulatory agency requires it, and we are afraid that in the future 
we won't be able to recover it to a newer version of Oracle.

A: I use Oracle's export utility for this type of task. Your database isn't 
too large, so exp will work. The nice thing about exp is that future 
versions of Oracle will still be able to read the resulting dump file. So 
perform the following:
exp userid=system/manager file=my_db.dmp log=my_db.log full=y

The resulting files can then be written to DVD or some other media for long 
term storage. 
--
Welcome to Database Board
��ӭ������ݿ��
http://bbs.sjtu.edu.cn/bbsdoc?board=database

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.27]