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

[Yogeeraj_1]

hi,

try

alter table t move storage ( freelists 4
freelist groups 2 );



hope this helps!

Best Regards
Yogeeraj

[Henrique Silva_3]

Ok, will try that.

I was able to move all the table last night, using the exp/imp command, but all my index for one schema still ended up on the same tablespace as the data. At least it did not go to the old tablespace.

is there an easy way to drop ALL indexes on a schema, since I have the indexfile fromthe export already edited out to create all teh indexes on their own tablespace ?

Cheers,

Henrique

[Yogeeraj_1]

hi,

You can use the following SQL statement to generate the script to drop all the indexes:
select 'drop index '||index_name||';'
from user_indexes;


However, it will be much easier to run the moveall script that i posted previously to generate the script and modify it accordingly so that you can rebuild your indexes:
E.g. of code generated:
alter INDEX PK_DEPT rebuild
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

Hope this helps!

Best Regards
Yogeeraj

[Henrique Silva_3]

Hi Yogeeraj, thanks for the help.

I ran the selects you mention against both the dba_tables and dba_indexes, and was able to get the sql script that I will use tonite to move the indexes.

However, looking at the syntax for alter index, I do not see any way to add freelists to it on the rebuild,and we are missing the coalesce option as well. The new tablespace is locally managed, so I think the only problem might be that the HWM might be already set high, and I do not think that moving the indexes will make it go down ( I think SMON willdo that, not sure ). So, I think I still need to drop the indexes and recreate them, witht he freelist option. I can use the little sql you passed me, but have another question.
When you drop indexes, does it effect any constraints at all ? I do not think so, since they should be bound to the tables right ? ( constraints at this level, is still a bit confusing to me ).

Anyways, my concerns are that if I drop the indexes, I should all drop constraints and recreate them later. I am not suer if this is necessary. I do not think so. ALso, I have this 20 GB tablespace for data and a 10 GB for the indexes, and since all data and indexes went to the data tablespace ( about 9 GB now ), and 0 on index. Once I move the indexes, we will have about 7 gb of data and 2 on the index tablespace, but the HWM on the data tablespace might still be at the 10 GB mark.

any comments with these concerns ?

thanks,

Henrique

[Henrique Silva_3]

looking at the syntax for alter index ... rebuild, I do not see any storage clauses.

this is being built on a locally managed tablespace as well.

Henrique

[Yogeeraj_1]

use LMTS => never put a storage clause on an object ever again

[Henrique Silva_3]

Done that.

I do not see an option to rebuild a table and change its structure. The only way that I am aware of, on changing the freelists is to drop it and recreated andre-import the data.

thanks,

henrique

[Yogeeraj_1]

hi,

can you consider the option below?

CREATE TABLE new_table as select from old_table;

index new_table
grant on new table
add constraints on new_table
etc on new_table

drop table old_table
rename new_table to old_table;


you can do that using parallel query, with nologging on most operations
generating very little redo and no undo at all -- in a fraction of the time it would take to update the data.


alter table new_table move storage ( freelists 4
freelist groups 2 );

Also, another way to generate the script to help you move you tables based on their size would be:


select 'ALTER TABLE ' || ds.segment_name || ' MOVE TABLESPACE ' ||
case when dt.blocks*vp.value <= 100000 then 'TS_SMALL'
when dt.blocks*vp.value <= NNNNNNN then 'TS_MED'
else 'TS_BIG'
end || ';'
.....


Hope this helps!

Best Regards
Yogeeraj