RE: Export question on excluding certain schema
Thanks Kirti ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, September 05, 2002 9:26 AM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Raj, The answer is: Yes. You will need to edit catexp.sql to rebuild internal view to exclude interested owner#. This is as simple as it gets! - Kirti -Original Message- Sent: Thursday, September 05, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Export question on excluding certain schema
Raj, The answer is: Yes. You will need to edit catexp.sql to rebuild internal view to exclude interested owner#. This is as simple as it gets! - Kirti -Original Message- Sent: Thursday, September 05, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- 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).
RE: Export question on excluding certain schema
Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- 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: Export question on excluding certain schema
Title: RE: Export question on excluding certain schema Dennis, I received a reply from Kirti that by tweaking catexp81.sql file we could just achieve that. This is possible because all we have to do is tweak a view that dictates what will be exported. It looks like that will solve my problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Subject: RE: Export question on excluding certain schema Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Export question on excluding certain schema
Raj - Great! That one is beyond me, but Kirti is certainly the expert, so were I you, I'd try his suggestion. Good luck. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Dennis, I received a reply from Kirti that by tweaking catexp81.sql file we could just achieve that. This is possible because all we have to do is tweak a view that dictates what will be exported. It looks like that will solve my problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: Export question on excluding certain schema
NO! NO! NO! Please... I am not an expert. I just remembered this trick that I learned from the real experts (like Anjo, X$KGopal ...) I simply passed on what I had learned from others. Cheers! - Kirti -Original Message- Sent: Thursday, September 05, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Raj - Great! That one is beyond me, but Kirti is certainly the expert, so were I you, I'd try his suggestion. Good luck. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Dennis, I received a reply from Kirti that by tweaking catexp81.sql file we could just achieve that. This is possible because all we have to do is tweak a view that dictates what will be exported. It looks like that will solve my problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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). -- 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).
RE: Export question on excluding certain schema
Title: RE: Export question on excluding certain schema Also, by default, in Oracle 8.1.6 MDSYS and CTXSYS are already excluded from the export, so no editing of catexp.sql is needed to do what the original poster wanted to do. # grep MDSYS $ORACLE_HOME/rdbms/admin/catexp.sql u$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') u1$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') 'ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') ue$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') s$.owner not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') AND u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] The answer is: Yes. You will need to edit catexp.sql to rebuild internal view to exclude interested owner#. -Original Message- Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export.
RE: Export question on excluding certain schema
Title: RE: Export question on excluding certain schema Thanks for the info. Learned something new. Dennis : That proves I am no expert ;) - Kirti -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 2:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Export question on excluding certain schema Also, by default, in Oracle 8.1.6 MDSYS and CTXSYS are already excluded from the export, so no editing of catexp.sql is needed to do what the original poster wanted to do. # grep MDSYS $ORACLE_HOME/rdbms/admin/catexp.sql u$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') u1$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') 'ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') ue$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') s$.owner not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') AND u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') -Original Message- href="mailto:[EMAIL PROTECTED]">mailto:[EMAIL PROTECTED]] The answer is: Yes. You will need to edit catexp.sql to rebuild internal view to exclude interested owner#. -Original Message- Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export.
RE: Export question on excluding certain schema
Title: RE: Export question on excluding certain schema Missed that too ... Thanks Jacques Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 4:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Export question on excluding certain schema Thanks for the info. Learned something new. Dennis : That proves I am no expert ;) - Kirti -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 2:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Export question on excluding certain schema Also, by default, in Oracle 8.1.6 MDSYS and CTXSYS are already excluded from the export, so no editing of catexp.sql is needed to do what the original poster wanted to do. # grep MDSYS $ORACLE_HOME/rdbms/admin/catexp.sql u$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') u1$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') 'ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') ue$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') s$.owner not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') AND u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS') *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Export question - appending date to dmp file
Try the following example: TMD=`date '+%Y/%m/%d'` TMS=`date '+%Y%m%d%H%M%S'` sqlplus -s internal whisky set verif off pages 0 echo off feed off term off lines 200 spool $SHELLSRCDIR/dbcontents.$TMS.log select 'alter database rename file '''||name||''' to '''||name||''';' fromv\$datafile; column member format a50 select 'alter database rename file '''||member||''' to '''||member||''';' from v\$logfile; spool off whisky Regards Peter Lomax (Oracle DBA) Expertise Oracle SG/DSI/SIMBAD/ATP OrangeFrance Bureau: *: - [EMAIL PROTECTED] (: (+33) (0)1 55 22 59 13 fax: (+33) (0)1 55 22 39 69 -Message d'origine- De: ASCI [mailto:[EMAIL PROTECTED]] Date: mardi 4 décembre 2001 17:21 À: Multiple recipients of list ORACLE-L Objet: Export question - appending date to dmp file Hello All, I am trying to append date to dmp file while exporting it , no luck any ideas.. exp userid=user/passwd imp file=EXPDATA$DATE.dmp (I need to have system date and if possible time (TIMESTAMP ) appended to it .) TIA Cheers RK -- 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).
RE: Export question - appending date to dmp file
RK - The system nicely timestamps the file automatically for you. Isn't that good enough? Another idea - on Unix, tar several files and it will keep the system timestamps on them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 04, 2001 10:21 AM To: Multiple recipients of list ORACLE-L Hello All, I am trying to append date to dmp file while exporting it , no luck any ideas.. exp userid=user/passwd imp file=EXPDATA$DATE.dmp (I need to have system date and if possible time (TIMESTAMP ) appended to it .) TIA Cheers RK -- 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: Export question - appending date to dmp file
Try this: DUMPFILE=EXPDATA_$(date '+%m%d%Y%H%M').dmp echo $DUMPFILE should return something like: EXPDATA_120420011626.dmp Which means export started on Dec. 4, 2001 at 4:26 PM. do 'man date' to customize it further for your specific needs HTH Ed __ _ == -o)/ / (_)__ __ __ | Ed Sherman Oracle Certified Professional | /\\ /__/ / _ \/ // /\ \/ / | ECC International Corporation | _\_v __/_/_//_/\_,_/ /_/\_\ | Tel: (407) 859-7410 x2708 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] | == * * * Supporter of the coalition for grumpiness where grumpiness is due * * * -Original Message- Sent: Tuesday, December 04, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Yes, I agree, but I have a valid reason for doing this in this way.. any sugesstions.. Ramana - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Tuesday, December 04, 2001 12:01 PM RK - The system nicely timestamps the file automatically for you. Isn't that good enough? Another idea - on Unix, tar several files and it will keep the system timestamps on them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 04, 2001 10:21 AM To: Multiple recipients of list ORACLE-L Hello All, I am trying to append date to dmp file while exporting it , no luck any ideas.. exp userid=user/passwd imp file=EXPDATA$DATE.dmp (I need to have system date and if possible time (TIMESTAMP ) appended to it .) TIA Cheers RK -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] mailto:[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] mailto:[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). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552(b)(4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward 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 question - appending date to dmp file
Yes, I agree, but I have a valid reason for doing this in this way.. any sugesstions.. Ramana - Original Message - From: DENNIS WILLIAMS To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 04, 2001 12:01 PM Subject: RE: Export question - appending date to dmp file RK - The system nicely timestamps the file automatically for you. Isn't thatgood enough? Another idea - on Unix, tar several files and it will keep thesystem timestamps on them.Dennis WilliamsDBALifetouch, Inc.[EMAIL PROTECTED]-Original Message-Sent: Tuesday, December 04, 2001 10:21 AMTo: Multiple recipients of list ORACLE-LHello All,I am trying to append date to dmp file while exporting it , no luck anyideas..exp userid=user/passwd imp file=EXPDATA$DATE.dmp(I need to have system date and if possible time (TIMESTAMP ) appended to it.)TIACheersRK--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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Export question - appending date to dmp file
Try exp ... file=expdat.`date +%j`.dmp ... or whatever format is desired. Note that those are NOT quote marks, but grave marks. They mean the result of whatever command is inside them. You might want to do something like this in a script: JDATE=`date +%j' exp ... file=expdat.${JDATE}.dmp ... Of course, Julian date (%j) is not what you asked for. See: $ man date The possibities are endless. -Don Granaman [OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 04, 2001 11:01 AM RK - The system nicely timestamps the file automatically for you. Isn't that good enough? Another idea - on Unix, tar several files and it will keep the system timestamps on them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 04, 2001 10:21 AM To: Multiple recipients of list ORACLE-L Hello All, I am trying to append date to dmp file while exporting it , no luck any ideas.. exp userid=user/passwd imp file=EXPDATA$DATE.dmp (I need to have system date and if possible time (TIMESTAMP ) appended to it .) TIA Cheers RK -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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 question - appending date to dmp file
file=expdata`date '+%y%m%d'`.dmp -Original Message-From: ASCI [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 04, 2001 2:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Export question - appending date to dmp file Yes, I agree, but I have a valid reason for doing this in this way.. any sugesstions.. Ramana - Original Message - From: DENNIS WILLIAMS To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 04, 2001 12:01 PM Subject: RE: Export question - appending date to dmp file RK - The system nicely timestamps the file automatically for you. Isn't thatgood enough? Another idea - on Unix, tar several files and it will keep thesystem timestamps on them.Dennis WilliamsDBALifetouch, Inc.[EMAIL PROTECTED]-Original Message-Sent: Tuesday, December 04, 2001 10:21 AMTo: Multiple recipients of list ORACLE-LHello All,I am trying to append date to dmp file while exporting it , no luck anyideas..exp userid=user/passwd imp file=EXPDATA$DATE.dmp(I need to have system date and if possible time (TIMESTAMP ) appended to it.)TIACheersRK--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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Export question.
Well whaddya know... Analyzed the table with estimates statistics (sampled 30 percent) and bingo. My table is now reporting that it is 4.4Gb and not 330Mb. Avg row length went from 6 bytes to 81. On a 50+ million row table that made some difference to the calculation. Many thanks Anita (and to all who replied). Yet another reason to get off this crappy bug ridden 8.0.5.0.0 Lee -Original Message- Sent: 15 May 2001 16:06 To: Multiple recipients of list ORACLE-L Lee, Definitely sounds like bug 766095. It isn't listed in the patchset readme file for 8.0.5.2.1 so it doesn't appear to be fixed in 8.0.5, although there might be a patchset exception for your platform (check with support). The workaround is to estimate statistics using a percentage which brings the total amount of data analyzed under 4GB to avoid the overflow issue which results in the incorrect value, keeping in mind that the value produced by estimating may not be 100% accurate either. HTH, -- Anita Lee, Definitely sounds like bug 766095. It isn't listed in the patchset readme file for 8.0.5.2.1 so it doesn't appear to be fixed in 8.0.5, although there might be a patchset exception for your platform (check with support). The workaround is to estimate statistics using a percentage which brings the total amount of data analyzed under 4GB to avoid the overflow issue which results in the incorrect value, keeping in mind that the value produced by estimating may not be 100% accurate either. HTH, -- Anita --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: OK here goes and thanks for the response Definitely one table and from the top :-) DB Block size = 8192 Avg. Row Size = 6 Num Rows = 57351031 Pct free = 0 Pct Used = 40 Table definition (column names removed) = Type NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NUMBER(9) CHAR(2) CHAR(3) CHAR(4) VARCHAR2(100) VARCHAR2(60) VARCHAR2(50) VARCHAR2(60) VARCHAR2(7) NUMBER(6) NUMBER(6) NUMBER(9) DATE VARCHAR2(50) A - ha I think the penny drops - how on earth could this be right - average row length of 6 with a table definition like this. Is this the problem. The average row length must be wrong, yet the table was definitely analyzed with compute statistics. Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL
RE: Export question.
Well back down to earth with a bang again, back to work and the weather is miserable. I didn't see any replies to this last mail so I will repost. Any help or insight would be greatly appreciated. Regards Lee -Original Message- Sent: 11 May 2001 15:51 To: Multiple recipients of list ORACLE-L OK, To answer a couple of questions, I did use compute statistics to get the stats. I did not use compress=y. The table should have been 420Mb according to the avg_row_len*num_rows calculation. The export file came out at 5.7Gb. When I imported it fell over at 1Gb. I set my initial extent to what I calculated it at earlier and next extent to 4Mb and tablespace (1Gb) filled up. I'm still confused as to how this happened. Never mind its the weekend, out to a top class restaurant tonight, its the English FA Cup Final on Saturday (lots of beer) and the weather is glorious.. Oracle ? Oracle Who Regards Lee -Original Message- Sent: 10 May 2001 17:01 To: Multiple recipients of list ORACLE-L When you export it do not! use compress=Y. When you import the table be sure to prebuild the table with the initial and next extent sizes to be appropriate for this table. This will reorg it unstead of putting it back the way it was. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 10, 2001 11:31 AM All, I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenly noticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Regards Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Robertson Lee - lerobe 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: Robertson Lee - lerobe 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 question.
Ok, I'll pretend I'm from Missouri (the Show Me State). What's the database block size? What's the average row size? How many rows? What are the pctused and pctfree values? Are you using any weird data types (like LONG, RAW, that kind of stuff). Are you sure you are only exporting the one table? Show us everything please, as I sip my morning coffee... [EMAIL PROTECTED] 05/15/01 05:31AM Well back down to earth with a bang again, back to work and the weather is miserable. I didn't see any replies to this last mail so I will repost. Any help or insight would be greatly appreciated. Regards Lee -Original Message- Sent: 11 May 2001 15:51 To: Multiple recipients of list ORACLE-L OK, To answer a couple of questions, I did use compute statistics to get the stats. I did not use compress=y. The table should have been 420Mb according to the avg_row_len*num_rows calculation. The export file came out at 5.7Gb. When I imported it fell over at 1Gb. I set my initial extent to what I calculated it at earlier and next extent to 4Mb and tablespace (1Gb) filled up. I'm still confused as to how this happened. Never mind its the weekend, out to a top class restaurant tonight, its the English FA Cup Final on Saturday (lots of beer) and the weather is glorious.. Oracle ? Oracle Who Regards Lee -Original Message- Sent: 10 May 2001 17:01 To: Multiple recipients of list ORACLE-L When you export it do not! use compress=Y. When you import the table be sure to prebuild the table with the initial and next extent sizes to be appropriate for this table. This will reorg it unstead of putting it back the way it was. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 10, 2001 11:31 AM All, I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenly noticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Regards Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Robertson Lee - lerobe 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: Robertson Lee - lerobe 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: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City
RE: Export question.
OK here goes and thanks for the response Definitely one table and from the top :-) DB Block size = 8192 Avg. Row Size = 6 Num Rows = 57351031 Pct free = 0 Pct Used = 40 Table definition (column names removed) = Type NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NUMBER(9) CHAR(2) CHAR(3) CHAR(4) VARCHAR2(100) VARCHAR2(60) VARCHAR2(50) VARCHAR2(60) VARCHAR2(7) NUMBER(6) NUMBER(6) NUMBER(9) DATE VARCHAR2(50) A - ha I think the penny drops - how on earth could this be right - average row length of 6 with a table definition like this. Is this the problem. The average row length must be wrong, yet the table was definitely analyzed with compute statistics. Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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 question.
Lee, What version of the db is this? Bug 766095 generates inaccurate avg_row_len for tables over 4GB (fixed in 8.0.6). Is this a partitioned table and are you using FGAC (fine grained access control)? There's a bug where the entire table gets exported in the partition. I can't remember whether it's only when exporting individual partitions or the entire table though. HTH, -- Anita --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: Well back down to earth with a bang again, back to work and the weather is miserable. I didn't see any replies to this last mail so I will repost. Any help or insight would be greatly appreciated. Regards Lee -Original Message- Sent: 11 May 2001 15:51 To: Multiple recipients of list ORACLE-L OK, To answer a couple of questions, I did use compute statistics to get the stats. I did not use compress=y. The table should have been 420Mb according to the avg_row_len*num_rows calculation. The export file came out at 5.7Gb. When I imported it fell over at 1Gb. I set my initial extent to what I calculated it at earlier and next extent to 4Mb and tablespace (1Gb) filled up. I'm still confused as to how this happened. Never mind its the weekend, out to a top class restaurant tonight, its the English FA Cup Final on Saturday (lots of beer) and the weather is glorious.. Oracle ? Oracle Who Regards Lee -Original Message- Sent: 10 May 2001 17:01 To: Multiple recipients of list ORACLE-L When you export it do not! use compress=Y. When you import the table be sure to prebuild the table with the initial and next extent sizes to be appropriate for this table. This will reorg it unstead of putting it back the way it was. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 10, 2001 11:31 AM All, I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenly noticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Regards Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Robertson Lee - lerobe 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: Robertson Lee - lerobe 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
RE: Export question.
No kidding...it's got to be at least 38. 38 * 57351031 = 2.03 gigabytes, and I'm sure it's bigger than that... [EMAIL PROTECTED] 05/15/01 10:25AM OK here goes and thanks for the response Definitely one table and from the top :-) DB Block size = 8192 Avg. Row Size = 6 Num Rows = 57351031 Pct free = 0 Pct Used = 40 Table definition (column names removed) = Type NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NUMBER(9) CHAR(2) CHAR(3) CHAR(4) VARCHAR2(100) VARCHAR2(60) VARCHAR2(50) VARCHAR2(60) VARCHAR2(7) NUMBER(6) NUMBER(6) NUMBER(9) DATE VARCHAR2(50) A - ha I think the penny drops - how on earth could this be right - average row length of 6 with a table definition like this. Is this the problem. The average row length must be wrong, yet the table was definitely analyzed with compute statistics. Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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: Tim Sawmiller 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 question.
Lee, Definitely sounds like bug 766095. It isn't listed in the patchset readme file for 8.0.5.2.1 so it doesn't appear to be fixed in 8.0.5, although there might be a patchset exception for your platform (check with support). The workaround is to estimate statistics using a percentage which brings the total amount of data analyzed under 4GB to avoid the overflow issue which results in the incorrect value, keeping in mind that the value produced by estimating may not be 100% accurate either. HTH, -- Anita Lee, Definitely sounds like bug 766095. It isn't listed in the patchset readme file for 8.0.5.2.1 so it doesn't appear to be fixed in 8.0.5, although there might be a patchset exception for your platform (check with support). The workaround is to estimate statistics using a percentage which brings the total amount of data analyzed under 4GB to avoid the overflow issue which results in the incorrect value, keeping in mind that the value produced by estimating may not be 100% accurate either. HTH, -- Anita --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: OK here goes and thanks for the response Definitely one table and from the top :-) DB Block size = 8192 Avg. Row Size = 6 Num Rows = 57351031 Pct free = 0 Pct Used = 40 Table definition (column names removed) = Type NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NUMBER(9) CHAR(2) CHAR(3) CHAR(4) VARCHAR2(100) VARCHAR2(60) VARCHAR2(50) VARCHAR2(60) VARCHAR2(7) NUMBER(6) NUMBER(6) NUMBER(9) DATE VARCHAR2(50) A - ha I think the penny drops - how on earth could this be right - average row length of 6 with a table definition like this. Is this the problem. The average row length must be wrong, yet the table was definitely analyzed with compute statistics. Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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 question.
Many thanks, I will give this a try. Regards Lee -Original Message- Sent: 15 May 2001 15:04 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Lee, Definitely sounds like bug 766095. It isn't listed in the patchset readme file for 8.0.5.2.1 so it doesn't appear to be fixed in 8.0.5, although there might be a patchset exception for your platform (check with support). The workaround is to estimate statistics using a percentage which brings the total amount of data analyzed under 4GB to avoid the overflow issue which results in the incorrect value, keeping in mind that the value produced by estimating may not be 100% accurate either. HTH, -- Anita --- Robertson Lee - lerobe [EMAIL PROTECTED] wrote: OK here goes and thanks for the response Definitely one table and from the top :-) DB Block size = 8192 Avg. Row Size = 6 Num Rows = 57351031 Pct free = 0 Pct Used = 40 Table definition (column names removed) = Type NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NOT NULL NUMBER(9) NUMBER(9) CHAR(2) CHAR(3) CHAR(4) VARCHAR2(100) VARCHAR2(60) VARCHAR2(50) VARCHAR2(60) VARCHAR2(7) NUMBER(6) NUMBER(6) NUMBER(9) DATE VARCHAR2(50) A - ha I think the penny drops - how on earth could this be right - average row length of 6 with a table definition like this. Is this the problem. The average row length must be wrong, yet the table was definitely analyzed with compute statistics. Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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 question.
OK, To answer a couple of questions, I did use compute statistics to get the stats. I did not use compress=y. The table should have been 420Mb according to the avg_row_len*num_rows calculation. The export file came out at 5.7Gb. When I imported it fell over at 1Gb. I set my initial extent to what I calculated it at earlier and next extent to 4Mb and tablespace (1Gb) filled up. I'm still confused as to how this happened. Never mind its the weekend, out to a top class restaurant tonight, its the English FA Cup Final on Saturday (lots of beer) and the weather is glorious.. Oracle ? Oracle Who Regards Lee -Original Message- Sent: 10 May 2001 17:01 To: Multiple recipients of list ORACLE-L When you export it do not! use compress=Y. When you import the table be sure to prebuild the table with the initial and next extent sizes to be appropriate for this table. This will reorg it unstead of putting it back the way it was. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 10, 2001 11:31 AM All, I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenly noticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Regards Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Robertson Lee - lerobe 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 question.
You should check size of table from dba_segments like select segment_name,bytes from dba_segment where segment_name = 'TABLE_NAME'; HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 11 May 2001 06:51:17 -0800 OK, To answer a couple of questions, I did use compute statistics to get the stats. I did not use compress=y. The table should have been 420Mb according to the avg_row_len*num_rows calculation. The export file came out at 5.7Gb. When I imported it fell over at 1Gb. I set my initial extent to what I calculated it at earlier and next extent to 4Mb and tablespace (1Gb) filled up. I'm still confused as to how this happened. Never mind its the weekend, out to a top class restaurant tonight, its the English FA Cup Final on Saturday (lots of beer) and the weather is glorious.. Oracle ? Oracle Who Regards Lee -Original Message- Sent: 10 May 2001 17:01 To: Multiple recipients of list ORACLE-L When you export it do not! use compress=Y. When you import the table be sure to prebuild the table with the initial and next extent sizes to be appropriate for this table. This will reorg it unstead of putting it back the way it was. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 10, 2001 11:31 AM All, I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenly noticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Regards Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Robertson Lee - lerobe 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 your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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 question.
Title: RE: Export question. -Original Message- From: Robertson Lee - lerobe [mailto:[EMAIL PROTECTED]] I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenlyn oticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Export only backs up the data (and the DDL needed to recreate objects). Was the analyze done using compute or estimate? The estimage of avg_row_len could have been off.
Re: Export question.
When you export it do not! use compress=Y. When you import the table be sure to prebuild the table with the initial and next extent sizes to be appropriate for this table. This will reorg it unstead of putting it back the way it was. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 10, 2001 11:31 AM All, I have recently ran an analysis on a table and when calculating the table size using num_rows*avg_row_len this came out at about 420Mb. Nothing strange about that, however the amount of space this table is taking up is about 4.6Gb ! Instant case for a reorg I thought, however, I am currently exporting this table and suddenly noticed that the dump file is now in excess of 1.5Gb and still going strong. Does export merely backup the data or does it also backup other stuff (unused blocks, preallocated etc.) Regards Lee Robertson The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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).