Helping with moving schemas to their own tablespace ?

[Henrique Silva_3]

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

[T G Manikandan]

One thing what you can do is

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

[BLADE_1]

hi henrique,

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

[T G Manikandan]

Another way would be doing a import using a INDEXFILE option of imp.

Then change the tablespace name in the INDEXFILE and run the INDEXFILE on the database which should solve the problem


Thanks

[Henrique Silva_3]

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

[Henrique Silva_3]

OK, imp with indexfile should not be rocket science, but I am getting an error below. It is asking me to use FULL=Y or from table to table, fromuser to user, but I do not want to move the data in.

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

[T G Manikandan]

Yes,you can use fromuser/touser option here

like

$$ imp file=expnorows.dmp userid=system/manager@ fromuser= touser= indexfile=file.sql


fromuser ---->username from which the dump was exported
touser---->usename to which dump will be imported


REvert

[Brian Crabtree]

Actually, rather than try to use exp and imp (not a good way to do it), the easiest option is to rebuild the tables and indexes over to the new area.

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

[Henrique Silva_3]

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