Re: Reverse engineer tablespaces.
I'm not sure if this is what your looking for but take a look: spool db_structure.txt select 'create tablespace ' || a.tablespace_name || ' datafile ' || || b.file_name || ||' size ' || b.bytes/(1024 * 1024) || 'M' || \ ' default storage ( initial ??k next ??k pctincrease ? maxextents ?);' from dba_tablespaces a, dba_data_files b where a.tablespace_name = b.tablespace_name; spool off; exit; Roy So, I need to re-create a database to prepare for migration. The current DB is 8.0.6, and I need to create an 8.1.7 copy. Does anyone have a script to reverse engineer the CREATE TABLESPACE commands? I started working on one, but surely I can't be the first to invent the wheel. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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). Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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: How to downgrade database from 8.1.5 to 8.0.6 - Urgent Help Please!
Export/Import this was actually mentioned in chapter 3 in the Oracle8i Migration Release (A76957-01) Hello: Please help me on this crisis! What is the steps and safe procedures to downgrade database from 8.1.5 to 8.0.6 ??? I need to have this done by noon today. I have database 8.1.5 with all schemas and data. But the application can not support 8.1.5. I have to install 8.0.6.(new one) Then I need get all those data and schemas into 8.0.6. I know in some way this can be done, Now matter how risk I have to do at client's request. What is the steps and safe procedures??? Thanks in advance - Do You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell? Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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: Export Split Script
http://www.oracle.com/oramag/code/tips1998/index.html 1/16/98 I tries using a compressed export and the output is still over 2G. Does anyone have the export script that splits the export files into multiple files? Ron Smith Database Administration [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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). Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
Oracle / Informix - sql interface
All, We have an Informix database and looking to setup a sql interface to an Oracle database. Both database servers are Sun E-class servers running O/S 2.6. All assistance is greatly appreciated. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
unix command question
what is the ls command to view only a list of directories? ls -la lists both files and directories...I want to view only a list of directories... environment is sun sparc solaris 2.6 thanks in advance roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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: unix command question
what I would like to see is all directories...not files starting with a particular letter but all directories... ls -d - doesn't work ls -ld p* - doesn't work either Roy, You could do say ls -ld p* to list the directories starting with p. Rgds, raja -- On Tue, 01 May 2001 13:35:46 Roy Ferguson wrote: what is the ls command to view only a list of directories? ls -la lists both files and directories...I want to view only a list of directories... environment is sun sparc solaris 2.6 thanks in advance roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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). Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
Windows vs. UNIX
this is not to bring up a heated debate over the two platforms... I am making a proposal to move the current access 97 databases to an oralce platform running solaris I would like to put in the proposal a brief reason why a UNIX platform will be better than a Windows platform. the current environment: MS Access 97 databases running on Compaq server. Databases are about 20GB in size... proposed solution: Sun E3500 T3 Storage Array Solaris 8 Oracle 8i Just need a couple FACTS as to why one would choose UNIX over Windows Thanks in advance roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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: Windows vs. UNIX
Thanks to those who responded so quickly. I have all the needed information to make a case... roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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: Windows vs. UNIX
this information is actually a proposal that I created for a college course I'm taking...I have a lot of experience with UNIX and very little with Windows...that's why I only needed the facts and no opinions... Roy, Forgive me my lack of understanding, but if you don't know why one would choose Unix over Windows, why are you proposing Unix altogether? Yosi -Original Message- From: Roy Ferguson [mailto:[EMAIL PROTECTED]] Sent: Monday, April 30, 2001 2:46 PM To: Multiple recipients of list ORACLE-L Subject: Windows vs. UNIX this is not to bring up a heated debate over the two platforms... I am making a proposal to move the current access 97 databases to an oralce platform running solaris I would like to put in the proposal a brief reason why a UNIX platform will be better than a Windows platform. the current environment: MS Access 97 databases running on Compaq server. Databases are about 20GB in size... proposed solution: Sun E3500 T3 Storage Array Solaris 8 Oracle 8i Just need a couple FACTS as to why one would choose UNIX over Windows Thanks in advance roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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: Import/Export performance
Thank you very much Kirti for providing such nicely detailed items. This is exactly what I was looking for. Thanks again. Roy Hi Roy, Planning, planning and planning is required to make this a piece-of-cake.. This one is a rather long one.. Sorry.. Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage. 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes, I keep the quota on the target tablespace to 0 to make it fail during import. (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room, should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks fine and okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From:Roy Ferguson [SMTP:[EMAIL PROTECTED]] Sent:Wednesday, April 18, 2001 2:31 PM To: Multiple recipients of list ORACLE-L Subject: Import/Export performance I will be re-building a database using export/import and would like to know what specific things I can look at or do prior to doing the export and the import to increase the time it takes as well as to limit the number of invalid objects. The database is 8.0.5.2.1 on Sun Sparc Solaris 2.6 and has 35,000 objects. I will be importing into an 8.1.6 database on the same O/S. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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). Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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
block_size
I'm in the process of upgrade to 8.1.6 from 8.0.5 and will be changing the block_size by re-building the database via exp/imp. This is on a Sun Sparc Solaris 2.6 server. I noticed that the db block_size can have a maximum value of 16k. I was wondering if anyone has experience in determining the block size that works best in their environment and if you could share how you determined the value. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
Import/Export performance
I will be re-building a database using export/import and would like to know what specific things I can look at or do prior to doing the export and the import to increase the time it takes as well as to limit the number of invalid objects. The database is 8.0.5.2.1 on Sun Sparc Solaris 2.6 and has 35,000 objects. I will be importing into an 8.1.6 database on the same O/S. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
db and apps migration
I will be upgrading our database and application server from 8.0.5.2.1 and 11.0.2 to Oracle 8.1.6 and Applications 11.5.3. I understand from reading the upgrade manuals as well as confirming from Oracle support that the database must have a db_block_size of 8k or larger. We are currently setup with a 2k block size which means that we must re-build the database with an 8k or larger block size and the only why of doing that is via exp/imp. Can anyone share their experience with exporting and importing a large database with thousands of objects? We have about 35k objects in the database and a concern of mine is all the invalid objects that could appear after the import. Are there some key areas I can look at to help reduce the time to import as well as limiting the number of invalid objects? thanks in advance...roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
migrating to 11i
I am in the initial test phase of migrating our Oracle 8.0.5.2.1 database and Oracle Applications 11.0.2 to Oracle 8.1.6 and Applications 11.5.3. We run this on Sun Sparc Solaris 2.6 In reading the Upgrading Oracle Applications Release 11.5.3 documention, I read on page 1-14 that Applications 11i requires an blocksize of 8k or larger and if the current environment is 2k or 4k you must migrate the database to a blocksize of 8k or larger. We currently have a 2k blocksize. Has anyone faced this issue before? Can you give me a overview of your solution path? I know I can re-create the database using imp/exp and setting the blocksize to 8k but is there another method? The documentation said "must migrate the database to a blocksize of 8k or larger". can this be done during the migration process? Assistance is greatly appreciated.. thanks in advance. roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
changing db_block_size
all, I would like to change the db_block_size (currently 2048) of our production database and would be interested in hearing from those that have done this on a not so small database. This is our oracle financials/manufacturing server and is about 30GB in size. Did you recreate the database using imp/exp or another method? Running Oracle 8.0.5 on Sun Solaris 7. thanks in advance...roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
Unix script question
I am looking to resolve an issue we have with a particular script. using a unix script, I need to determine if a file exists and if the size of the file is zero bytes. using ftp, we put a file a file on a server then we use get to retreive the same file back. we do this to ensure that the file has been sent successfully. once the file is gotten back, using get, we compare the original file to the get file. we then use if to see if the file was successfully send and received back. if the file is not gotten back, we email the necessary people of the error. if the files are different sizes, we email the necessary people that the file sizes do not match and that the ftp process needs to be done again. all of the above steps work fine. the issue we face is when we ftp a zero byte file and receive the same file back the if statement we're using sees the zero byte file as none existant and therefore an email is sent saying that the ftp process failed when in fact it didn't. it just sent and received a zero byte file. a zero byte file is not a failed process. finally...the question... can a unix scripting guru assist me in the code that checks if a file is a zero byte file? thanks in advance...Roy below is the current portion of the script that checks for errors: put file1 'file1' get 'file1' check_file bye !EOF # First if statement will check to see if the check file exists if test ! -s "check_file" then # if the file does not exist, send the failed message and exit the program echo Failed to pull a check file back from the FTP echodo /usr/ucb/mail -s ftp_of_file1.FAILED `cat $EMAIL/fail.list` $EMAIL/fail.msg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
