Helping with moving schemas to their own tablespace ?

[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