- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Helping with moving schemas to their own table...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2003 08:12 PM
тАО01-28-2003 08:12 PM
Ok, not too experienced with import or export.
I have a developmental DB with data that needs to go to the pre-production box. There are two schemas there, and each schema has about 140 tables each, and indexes. All tables are in the USERS tablespace.
In order to get better performance on the pre-production box, I have created 4 more table spaces. schemaA_data and index and schemaB_data and index.
I did an export of the developmental box using
exp system/passwd@SID file=./file.dmp fromuser=schemaA,schemaB log=file.log
then, created schemas on pre-production box, wth the proper tablespace defaults and ran the import on the pre-production box, with
imp system/passwd@SID file=file.dmp fromuser=schemaA,schemaB touser=schemaA,schemaB
log=file.logIn
what actually happened,is that all tables ended up on the USERS table space on the pre-production box as well, instead of getting moved to the schema's default tablespaces.
now, my question is, is there an easy eay to get the tables exported, being imported into the default table space for the schemas ? Also, how can I get the data tables into the data table space, and the indexes into the index table space ?
If I can not do this here, I will runa script that will export all tables for both schemas. Edit the sql where the table creations are done, so that i can put the proper table space there, re-create the tables ( some of them with freelists increased ), and re-import the data into these tables. This is a lot of work, and I hope I can get it done using imp and exp !!! POssible ?
thanks,
Henrique Silva
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2003 08:51 PM
тАО01-28-2003 08:51 PM
Re: Helping with moving schemas to their own tablespace ?
Remove the unlimited tablespace option for the user from the tablespace.
Remove the permissions or quota for the tablespace from where it was exported for the user.
So when the import happens,
as it cannot wite it again into USERS it will now choose the default tablespace.
REvert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2003 09:02 PM
тАО01-28-2003 09:02 PM
Re: Helping with moving schemas to their own tablespace ?
If you have created the user with default tablespace set to A...then all the tables and indexes will go to this specified tablespace..I don't understand how it will end up into some other tablespace..I guess u have specified the right default tablespace while creating the user.In oracle user and schema is the same right?
Don't import the indexes during the table import..
You can import the dump to indexfile and change the tablespace for indexes simply by find and replace..and execute the script..That will solve your index tablespace problem..
rgds
nainesh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2003 09:05 PM
тАО01-28-2003 09:05 PM
Re: Helping with moving schemas to their own tablespace ?
Then change the tablespace name in the INDEXFILE and run the INDEXFILE on the database which should solve the problem
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2003 10:12 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-29-2003 04:41 AM
тАО01-29-2003 04:41 AM
Re: Helping with moving schemas to their own tablespace ?
Yup, do not understand how exp/imp work yet. I thought that independent of where the original tables lived, they would be created on the default tablespace for the owners on the receiving box, which are the new tablespaces.
I was going to try putting the USERS tablespace offline,prior to doing the import, but have not had the chance yet.
I am also leaning towards rebuilding all the tables, but need to get a better handlw on the export tool. Not too sure on how to export only the data,or constraints, or indexes or ddl.
I guess I got the DDL here, but if I choose that path, can you give me what I should do next ? Meaning, export only the data, then, only the indexes, then only the constraints, on separate files, and re-import them on the other end, and how to do it on the proper tablespace ? anyways, like I said, I am abit raw on exp/imp. Up until now, I have done whole databases exports and imports, but have not played much with sub-sets of it.
I am out all week on training, so, I will not be able to try these things until tonite.
thanks again guys,
Henrique Silva
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2003 04:19 AM
тАО01-30-2003 04:19 AM
Re: Helping with moving schemas to their own tablespace ?
I have done an export with rows=N, so what happens if I use full=y on the imp ?
ACcording to you here,and to several papers on metalink, the syntax I have used is just fine, so, what am I missing here ?
Thanks,
Henrique Silva
PS.: Error below !!
$ imp file=expnorows.dmp userid=system/manager indexfile=file.sql
Import: Release 8.1.7.4.0 - Production on Thu Jan 30 07:10:33 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2003 05:04 AM
тАО01-30-2003 05:04 AM
Re: Helping with moving schemas to their own tablespace ?
like
$$ imp file=expnorows.dmp userid=system/manager@
fromuser ---->username from which the dump was exported
touser---->usename to which dump will be imported
REvert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2003 03:52 PM
тАО01-30-2003 03:52 PM
Re: Helping with moving schemas to their own tablespace ?
The following should work (might need some changes):
spool movetable.tmp
select 'alter table '||table_name||' move tablespace schemaA_DATA;' from dba_tables where owner = 'SCHEMAA';
select 'alter index '||index_name||' rebuild tablespace schemaA_INDX;' from dba_tables where owner = 'SCHEMAA';
spool off
!grep ^alter movetable.tmp > movetable.sql
@movetable.sql
Hope this helps,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2003 05:16 PM
тАО01-30-2003 05:16 PM
Re: Helping with moving schemas to their own tablespace ?
Hi guys.
Got it to work ONLY after I added the show=y and full=y options on the import !!! Not sure why. Some papers on metalink did not mention that at all.
As far as moving the tables, I also need to add freelists to some of them, so I need the ddl for that.
Anyways, thanks for the help.
Henrique