RE: Large Export Problem ......
Title: RE: Large Export Problem ..
Yes, that was a typographical error in my response. I meant IGNORE=N.
-Original Message-
From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]]
Jacques,
Your response is helpful. Thanks for your suggestions. I think I should import with IGNORE=N and not Y. I think it is typo in your response. If not, please clarify. I would utilize your script as an extra precaution.
-Original Message-
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]
(see comments below)
-Original Message-
From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]]
Thanks for the caution. Does any one know if I export with
owner=schema
Name rows=N, then drop a package and import from the export file with
IGNORE=N (which is default), will it restore the dropped
package and error
out on all other objects as they are already exist?
Is this the right way of restoring the dropped package or
will it have any
problems? There are plenty of the tables in the database and
error list will
be too long in this case. Any side effects? or Is there any
other way to
import a dropped pakage?
A little bit uncomfortable seeing big error list for
restoring one package.
Any ideas or will I have to live with this in case the need
arises? Iam
afraid of any side effects.
Answering your question: if you import a schema with IGNORE=Y after dropping only one package, I would think it should work (meaning only import the missing package and show errors for every other object), but I haven't tried it. However, have you considered the following?
Are these packages written in-house or part of some third-party software?
If they are written in-house then I would suggest some version control system to keep track of which version in the source code is in the database. Then you should be able to recreate any dropped package from the latest version in your version control system.
If they are part of some third-party software and you want to back them up, you could always try a custom select ... from dba_source script to back them up.
example: save_source.sql
set linesize 4000
set pagesize 0
set echo off
set feedback off
set verify off
set trimspool on
column sort_column noprint
column owner noprint
column name noprint
column type noprint
column line noprint
accept username char prompt Enter schema name (case sensitive)
set termout off
spool c:\mydir\db_source_username..sql
select
1 as sort_column,
object_name as name,
object_type as type,
to_number (null) as line,
'create ' as sql_text
from dba_objects
where owner = 'username'
and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
union all
select
2 as sort_column,
name as name,
type as type,
line as line,
text as sql_text
from dba_source
where owner = 'username'
union all
select
3 as sort_column,
object_name as name,
object_type as type,
to_number (null) as line,
'/' as sql_text
from dba_objects
where owner = 'username'
and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
order by name, type, sort_column, line ;
spool off
exit
RE: Large Export Problem ......
Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: Large Export Problem ......
I go even one step further here. Each object is required to have it's own creation script. A package will have two scripts, one for the header and one for the body. Also the script name has the package revision included in it as well. For example: hyp_packages.115.00.sql hyp_packageb.115.00.sql hyp_packageb.115.01.sql etc... This makes reverting code and tracking changes much easier. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .org To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Large Export Problem .. 03/26/2003 11:39 AM Please respond to ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size
RE: Large Export Problem ......
Ron, It seems to be the correct answer I believe. If the package is lost I should ask the developer to get it from the version control system and I should compile it. This seems to be the right approach rather than importing the dump file and get a big list of errors. After seeing the responses from the list members, I got a clear idea how to handle the Large Export Problem. I will break up the export into 4 types(full export with norows, static, non-static and the rest), schedule them to fit our schedule, and use the version control system approach for the packages/stored procs. Importing the dumpfile to restore the package as last resort. Thanks for all those who replied. -- Janardhana -Original Message- Sent: Wednesday, March 26, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Large Export Problem ......
I like RCS for this. Any version you need can be retrieved. If a large number of packages are used for an app, you could take it a step further and use CVS. This would allow app level releases as well. Jared Ron Thomas [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 11:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Large Export Problem .. I go even one step further here. Each object is required to have it's own creation script. A package will have two scripts, one for the header and one for the body. Also the script name has the package revision included in it as well. For example: hyp_packages.115.00.sql hyp_packageb.115.00.sql hyp_packageb.115.01.sql etc... This makes reverting code and tracking changes much easier. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .org To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Large Export Problem .. 03/26/2003 11:39 AM Please respond to ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem
Re: Large Export Problem ......
you might also want to capture the database outline in OEM (in change manager) then if you happen to lose a procedure - u can ask oem to do a diff between yr database and the stored outline and then ask it to generate a script to sync them. babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:34 PM Ron, It seems to be the correct answer I believe. If the package is lost I should ask the developer to get it from the version control system and I should compile it. This seems to be the right approach rather than importing the dump file and get a big list of errors. After seeing the responses from the list members, I got a clear idea how to handle the Large Export Problem. I will break up the export into 4 types(full export with norows, static, non-static and the rest), schedule them to fit our schedule, and use the version control system approach for the packages/stored procs. Importing the dumpfile to restore the package as last resort. Thanks for all those who replied. -- Janardhana -Original Message- Sent: Wednesday, March 26, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu
RE: Large Export Problem ......
FWIR, CVS doesn't handle binaries tho. We do the same thing for rdf's (report files) and fmb's (forms files) too. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] ys.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Large Export Problem .. 03/26/2003 12:48 PM Please respond to ORACLE-L I like RCS for this. Any version you need can be retrieved. If a large number of packages are used for an app, you could take it a step further and use CVS. This would allow app level releases as well. Jared Ron Thomas [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 11:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Large Export Problem .. I go even one step further here. Each object is required to have it's own creation script. A package will have two scripts, one for the header and one for the body. Also the script name has the package revision included in it as well. For example: hyp_packages.115.00.sql hyp_packageb.115.00.sql hyp_packageb.115.01.sql etc... This makes reverting code and tracking changes much easier. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .org To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Large Export Problem .. 03/26/2003 11:39 AM Please respond to ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients
RE: Large Export Problem ......
No, it handles binary files. It's been awhile since you used it. :) http://ccvs.cvshome.org/fom//cache/223.html Jared Ron Thomas [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 12:53 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Large Export Problem .. FWIR, CVS doesn't handle binaries tho. We do the same thing for rdf's (report files) and fmb's (forms files) too. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] ys.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Large Export Problem .. 03/26/2003 12:48 PM Please respond to ORACLE-L I like RCS for this. Any version you need can be retrieved. If a large number of packages are used for an app, you could take it a step further and use CVS. This would allow app level releases as well. Jared Ron Thomas [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 11:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Large Export Problem .. I go even one step further here. Each object is required to have it's own creation script. A package will have two scripts, one for the header and one for the body. Also the script name has the package revision included in it as well. For example: hyp_packages.115.00.sql hyp_packageb.115.00.sql hyp_packageb.115.01.sql etc... This makes reverting code and tracking changes much easier. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .org To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Large Export Problem .. 03/26/2003 11:39 AM Please respond to ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron [EMAIL PROTECTED] 03/25/03 07:33PM Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior
RE: Large Export Problem ......
Darrel, Thanks for your response. It is DSS database and so we would like to maintain unarchived mode. If something goes wrong, we will restore the DB from previous weeks coldbackup and apply the transactions to make the DB current. Archived log DB is not necessary in this case. I don't gain anything by using incremental export. Nightly loads will touch every partitioned table and so incremental export will export the complete tables and there won't be any difference between full export and incremental export in this case. I need additional help in resolving my large export problem. Thanks, -- Babu -Original Message- Sent: Tuesday, March 25, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Babu, First, if it were me, I'd put this thing in archive log mode. If we may need to recover between full backups, that is the tried and true means. But, on to your question. I'd look at a plan utilizing incremental exports. You start with a 'base' full export (weekly, monthly, whichever), and do daily incremental or cumulative exports. I'm not going to offer too much detail here because I've never actually used this and because you really should read all of the oracle documentation on this before implementing it ... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/toc. htm Two things I'll point out from that document: 1) A quote which directly addresses one of your issues... You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y). 2) A caveat of using this method: Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release Please let us know how this turns out for you or if additional help is needed. Thanks, Darrell [EMAIL PROTECTED] 03/25/03 01:59PM Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Large Export Problem ......
2) A caveat of using this method: Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release I think this has already happened with 9i... I would suggest going to RMAN and taking incremental cold backups and taking weekly/daily 1. full export w/o data (to get the structure) 2. data export of non-static data using (tablespaces=(list) - a 9i feature but i think u can install 9i anduse the exp binary against the 8i db you have. i have not tried it though) monthly/whatever 1. export of static data (this can be run during the day with consistent=n to minimize downtime) Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 3:44 PM Babu, First, if it were me, I'd put this thing in archive log mode. If we may need to recover between full backups, that is the tried and true means. But, on to your question. I'd look at a plan utilizing incremental exports. You start with a 'base' full export (weekly, monthly, whichever), and do daily incremental or cumulative exports. I'm not going to offer too much detail here because I've never actually used this and because you really should read all of the oracle documentation on this before implementing it ... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/toc. htm Two things I'll point out from that document: 1) A quote which directly addresses one of your issues... You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y). 2) A caveat of using this method: Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release Please let us know how this turns out for you or if additional help is needed. Thanks, Darrell [EMAIL PROTECTED] 03/25/03 01:59PM Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City
RE: Large Export Problem ......
Babu Another thing to consider. Have you tried to import one of these monster tables? A recovery that takes days may not be acceptable. [1] How can I reconstruct a database using this type of export if needed? Consider the real purpose of a logical backup to restore selected tables or other objects, not an entire database. If your system tablespace gets nailed you will probably use your cold backup to recover. [2] How can I simulate full export in this type (Table Mode) of export? Export to /dev/null? [3] How can I export packages/stored procs and import to new DB if necessary? USER export with rows=n [4] Is there any other way to export the full database and eliminate the static partitions in a single step? You'll need to list tables and omit certain tables. You could export based on a SQL statement such that you omit the data you don't want to export. [5] What is the best way to solve my export problem?? I think you're on the right track. You might also want to look at TRANSPORTABLE TABLESPACES. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 25, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Large Export Problem ......
Babu, To answer part of your questions. 1. Yes. If you use the FULL=Y , ROWS=N you get the skeleton of the database(tablespaces,tables,etc) check with the DataBee tool to see if it supplies all of your needs. 2.You can simulate (time the export) by having the output file be /dev/null. Search the mail archives for doing this. 3.To export the packages you have to export the owner of the packages USER=BABU. This will of course export the tables that BABU ownes so use the ROWS=N option. 4. I don't know about this one. 5. If you have the space to store the exports why not use the QUERY option and export portions of the tables. Then you would only need to export what has changed sence the last export query option. The import could be controlled with a script that uses a commit=Y and buffer= options to help with the possible redo log problems. The IMP script could execute the imports one after another until they are all imported. Ron [EMAIL PROTECTED] 03/25/03 02:59PM Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Large Export Problem ......
Dennis, Thanks for your reply. Iam not clear about exporting/importing packages/stored procs, specifically importing them. If I need to export packages, I could use owner=FINANCE and rows=N. It will export the structure of the complete schema tables also. If I need to import one package (lost/dropped for some reason) owned by say FINANCE schema, how can I import it? Thanks, -- Babu -Original Message- Sent: Tuesday, March 25, 2003 2:02 PM To: Multiple recipients of list ORACLE-L Babu Another thing to consider. Have you tried to import one of these monster tables? A recovery that takes days may not be acceptable. [1] How can I reconstruct a database using this type of export if needed? Consider the real purpose of a logical backup to restore selected tables or other objects, not an entire database. If your system tablespace gets nailed you will probably use your cold backup to recover. [2] How can I simulate full export in this type (Table Mode) of export? Export to /dev/null? [3] How can I export packages/stored procs and import to new DB if necessary? USER export with rows=n [4] Is there any other way to export the full database and eliminate the static partitions in a single step? You'll need to list tables and omit certain tables. You could export based on a SQL statement such that you omit the data you don't want to export. [5] What is the best way to solve my export problem?? I think you're on the right track. You might also want to look at TRANSPORTABLE TABLESPACES. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 25, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message
RE: Large Export Problem ......
Babu I've never done this either. Maybe someone else on the list has. 1) You can edit the export file. Use the Unix strings and fold commands to make it more manageable, then create a SQL procedure that will create the object. Just did that this afternoon with a table definition. 2) If you import the user with IGNORE=N, it should error out on existing objects and create objects that don't exist. Never tried that one, would need to test first. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 25, 2003 4:39 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Thanks for your reply. Iam not clear about exporting/importing packages/stored procs, specifically importing them. If I need to export packages, I could use owner=FINANCE and rows=N. It will export the structure of the complete schema tables also. If I need to import one package (lost/dropped for some reason) owned by say FINANCE schema, how can I import it? Thanks, -- Babu -Original Message- Sent: Tuesday, March 25, 2003 2:02 PM To: Multiple recipients of list ORACLE-L Babu Another thing to consider. Have you tried to import one of these monster tables? A recovery that takes days may not be acceptable. [1] How can I reconstruct a database using this type of export if needed? Consider the real purpose of a logical backup to restore selected tables or other objects, not an entire database. If your system tablespace gets nailed you will probably use your cold backup to recover. [2] How can I simulate full export in this type (Table Mode) of export? Export to /dev/null? [3] How can I export packages/stored procs and import to new DB if necessary? USER export with rows=n [4] Is there any other way to export the full database and eliminate the static partitions in a single step? You'll need to list tables and omit certain tables. You could export based on a SQL statement such that you omit the data you don't want to export. [5] What is the best way to solve my export problem?? I think you're on the right track. You might also want to look at TRANSPORTABLE TABLESPACES. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 25, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED]
Re: Large Export Problem ......
Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Large Export Problem ......
Thanks for your reply. I encounterd lots of bugs earliar and since then not been using DIRECT=Y option. However, exporting 150gig of static data every week will be of no use either way. -- Babu -Original Message- Sent: Tuesday, March 25, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Large Export Problem ......
The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Large Export Problem ......
Thanks for the caution. Does any one know if I export with owner=schema Name rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: Large Export Problem ......
Title: RE: Large Export Problem ..
(see comments below)
-Original Message-
From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]]
Thanks for the caution. Does any one know if I export with
owner=schema
Name rows=N, then drop a package and import from the export file with
IGNORE=N (which is default), will it restore the dropped
package and error
out on all other objects as they are already exist?
Is this the right way of restoring the dropped package or
will it have any
problems? There are plenty of the tables in the database and
error list will
be too long in this case. Any side effects? or Is there any
other way to
import a dropped pakage?
A little bit uncomfortable seeing big error list for
restoring one package.
Any ideas or will I have to live with this in case the need
arises? Iam
afraid of any side effects.
Answering your question: if you import a schema with IGNORE=Y after dropping only one package, I would think it should work (meaning only import the missing package and show errors for every other object), but I haven't tried it. However, have you considered the following?
Are these packages written in-house or part of some third-party software?
If they are written in-house then I would suggest some version control system to keep track of which version in the source code is in the database. Then you should be able to recreate any dropped package from the latest version in your version control system.
If they are part of some third-party software and you want to back them up, you could always try a custom select ... from dba_source script to back them up.
example: save_source.sql
set linesize 4000
set pagesize 0
set echo off
set feedback off
set verify off
set trimspool on
column sort_column noprint
column owner noprint
column name noprint
column type noprint
column line noprint
accept username char prompt Enter schema name (case sensitive)
set termout off
spool c:\mydir\db_source_username..sql
select
1 as sort_column,
object_name as name,
object_type as type,
to_number (null) as line,
'create ' as sql_text
from dba_objects
where owner = 'username'
and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
union all
select
2 as sort_column,
name as name,
type as type,
line as line,
text as sql_text
from dba_source
where owner = 'username'
union all
select
3 as sort_column,
object_name as name,
object_type as type,
to_number (null) as line,
'/' as sql_text
from dba_objects
where owner = 'username'
and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
order by name, type, sort_column, line ;
spool off
exit
