RE: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user ifyou have toimport all indexes,constraints to a single tablespacethenfollowing procedurewillwork 4)After importing datachange the default tablespace to index tablespace ,ensure that no other ts quota(except index ts) is given to the schema .Grant sufficient quota on index tablespace . 5) do import with ignore=Y ,rows=no,indexes=y,constraints=y ,It will create indexes and constraints on index ts. 6) Revert back the default tablespace . Rajendra -Original Message-From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user oh - I like that! thanks! -Original Message-From: Choudhary Rajendra (TTL_LKO) [mailto:[EMAIL PROTECTED]]Sent: Friday, November 08, 2002 2:38 AMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user ifyou have toimport all indexes,constraints to a single tablespacethenfollowing procedurewillwork 4)After importing datachange the default tablespace to index tablespace ,ensure that no other ts quota(except index ts) is given to the schema .Grant sufficient quota on index tablespace . 5) do import with ignore=Y ,rows=no,indexes=y,constraints=y ,It will create indexes and constraints on index ts. 6) Revert back the default tablespace . Rajendra -Original Message-From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: full exp/imp of user to new tablespace; same user
Title: RE: full exp/imp of user to new tablespace; same user A quick note to those that downloaded a version of DBATool yesterday following on from my message, and to all those that have a past version as well. The download link on the DBATool web page hadn't been updated for the new version that is on the web site - shoot the webmaster (me)! Following is a link to the latest version of DBATool (which has added support for partitioned table DDL etc.), if you would like to grab the latest download, just pull it down, and install straight over the version you already have.You do not need to de-install the old version, and will not have to re-apply for a registration key. http://www.cool-tools.co.uk/products/downloads/dbatool_install020306.exe Sorry for my stupidity! ;) Mark -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Markham, RichardSent: 07 November 2002 16:14To: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user well now I can say I'm spoiled. I was given more than enough information to to accomplish the task (done) and a new tool to boot! =) Thanks alot everyone. -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 10:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: full exp/imp of user to new tablespace; same user And if all else fails (not that the advice already given will!), we have a tool that can manipulate the DDL for the users objects VERY quickly and easily, through a "rule based" change (e.g. Modify ALL tables storage clauses within USERS schema to TABLESPACE): http://www.cool-tools.co.uk/products/dbatool.html It's free, have at it! HTH Mark -Original Message- Sent: 07 November 2002 14:44 To: Multiple recipients of list ORACLE-L Extra security could be to give zero quota on other tablespaces. -Original Message- Sent: donderdag 7 november 2002 14:59 To: Multiple recipients of list ORACLE-L I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user Choudhary out of curiousity, is itunderstood that you will need todrop the same recently imported indexes from the data TS before running the import into the index TS ? or will subsequent import cause a rebuild on the incoming indexes? -Original Message-From: Choudhary Rajendra (TTL_LKO) [mailto:[EMAIL PROTECTED]]Sent: Friday, November 08, 2002 2:38 AMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user ifyou have toimport all indexes,constraints to a single tablespacethenfollowing procedurewillwork 4)After importing datachange the default tablespace to index tablespace ,ensure that no other ts quota(except index ts) is given to the schema .Grant sufficient quota on index tablespace . 5) do import with ignore=Y ,rows=no,indexes=y,constraints=y ,It will create indexes and constraints on index ts. 6) Revert back the default tablespace . Rajendra -Original Message-From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
Re: full exp/imp of user to new tablespace; same user
Markham, Richard wrote: I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. If all the objects are already stored in a single tablespace the simplest way is enough. Otherwise you are going to have a problem since everything will be created in the default tablespace (if there is enough space). INDEXFILE is not a perfect solution either because it 'forgets' indices associated with constraints (UNIQUE, PRIMARY KEY). The simplest way is, if you can afford it, to size generously your tablespace so that everything fits in. Then move all indices you want elsewhere out through ALTER INDEX ... REBUILD which is reasonably fast and also works on indices associated with constraints. It's easy to generate the query to do it by querying DBA_SEGMENTS. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: full exp/imp of user to new tablespace; same user
-Original Message- I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. I have done many export/imports of user schemas without any problems in getting the entire schema. You shouldn't need to do anything special. To change the objects to a new tablespace: Drop the objects from the old tablespace; set the new tablespace to be the schema's default tablespace; set the schema's quota on the old tablespace(s) to be 0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user Extra security could be to give zero quota on other tablespaces. -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: donderdag 7 november 2002 14:59To: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: full exp/imp of user to new tablespace; same user
And if all else fails (not that the advice already given will!), we have a tool that can manipulate the DDL for the users objects VERY quickly and easily, through a rule based change (e.g. Modify ALL tables storage clauses within USERS schema to TABLESPACE): http://www.cool-tools.co.uk/products/dbatool.html It's free, have at it! HTH Mark -Original Message- Sent: 07 November 2002 14:44 To: Multiple recipients of list ORACLE-L Extra security could be to give zero quota on other tablespaces. -Original Message- Sent: donderdag 7 november 2002 14:59 To: Multiple recipients of list ORACLE-L I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: full exp/imp of user to new tablespace; same user
Title: RE: full exp/imp of user to new tablespace; same user well now I can say I'm spoiled. I was given more than enough information to to accomplish the task (done) and a new tool to boot! =) Thanks alot everyone. -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 10:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: full exp/imp of user to new tablespace; same user And if all else fails (not that the advice already given will!), we have a tool that can manipulate the DDL for the users objects VERY quickly and easily, through a rule based change (e.g. Modify ALL tables storage clauses within USERS schema to TABLESPACE): http://www.cool-tools.co.uk/products/dbatool.html It's free, have at it! HTH Mark -Original Message- Sent: 07 November 2002 14:44 To: Multiple recipients of list ORACLE-L Extra security could be to give zero quota on other tablespaces. -Original Message- Sent: donderdag 7 november 2002 14:59 To: Multiple recipients of list ORACLE-L I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: full exp/imp of user to new tablespace; same user
Hi, is there anything else to do if moved tables have some indexes. Murat Stephen Lee [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: full exp/imp of user to new tablespace; same user 11/07/2002 05:14 PM Please respond to ORACLE-L -Original Message- I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. I have done many export/imports of user schemas without any problems in getting the entire schema. You shouldn't need to do anything special. To change the objects to a new tablespace: Drop the objects from the old tablespace; set the new tablespace to be the schema's default tablespace; set the schema's quota on the old tablespace(s) to be 0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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.com -- Author: MURAT BALKAS 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).
