RE: Reorganizing a database
Russ, This is OT relative to your question slightly, but Be sure to take some good response time measurements (10046 level 8) before the rebuild so that you can make objective before/after comparisons. If youre right about your prediction that all this work wont provide much of a performance impact (and I suspect that you are exactly right), this is the only way youre going to be able to prove it. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Brooks, Russ Sent: Tuesday, September 10, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Subject: Reorganizing a database Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ
RE: Reorganizing a database
Russ - Why don't you consider this: - Create a new tablespaces LMT and uniform sizes (please read the documents to understand this concept clearly). - Move tables into the new tablespaces using ALTER TABLE MOVE. This is MUCH faster than export/import. - If you have space issues, just create the tablespaces you have room for, and then drop old tablespaces as you empty them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Message- Sent: Tuesday, September 10, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reorganizing a database
You can drop/re-create users and tablespaces, and import users. Down timealso depend on i/o bus and disk system. inka -Original Message-From: Brooks, Russ [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 11:44 AMTo: Multiple recipients of list ORACLE-LSubject: Reorganizing a database Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ
Re: Reorganizing a database
I've done something similar in the past (move a database to a new machine and go to uniform extents). I didn't find any quick and easy methodology. Basically you have to pre-create the tablespaces where you want them and with the storage parameters that you want. Then import the database to an INDEX_FILE. The table creation statements will be there but they will be remarked out. Edit each table to remove it's storage parameters (so that it will inherit its tablespace's defaults) and run those statements to pre-create the tables where and how you want them. Then do the import with ignore=y but indexes=n. Edit the indexes from the INDEX_FILE to remove the storage parameters and create the indexes. HTH Brooks, RussTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Russ.Brooks cc: @dayzim.com Subject: Reorganizing a database Sent by: root 09/10/2002 11:43 AM Please respond to ORACLE-L Hi, We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory. What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import. About how long should this take (ballpark)? Thanks, Russ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
