move DB objects from one table space to the other in multiple schemes improve performance


Need your help.

Planning to move DB Objects from one table space to the other.  This table space is being used across different schemas in the same Oracledatabase.
How do I move DB objects.

This is a high priority issue.

Please help me
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What object types?

If tables, pretty simple:
alter table some_table move tablespace new_tablespace;

Indexes, rebuild online in new tablespace.

As far as performance goes, what makes the new tablespace perform better?
d27m11yAuthor Commented:
How about CLOb and BLob
slightwv (䄆 Netminder) Commented:
Same alter table with a small syntax change.

alter table some_table move lob(lob_column) store as (tablespace new_tablespace);

This moves both the lob segment and lob index.

There is an example in the documentation:
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

d27m11yAuthor Commented:
do we have to consider iot_type is null and iot_type not null  with partitioned = 'NO' when we select tables. Please advise
slightwv (䄆 Netminder) Commented:
null and not null are constraints.  They shouldn't be affected by tablespaces.

I don't know what partitioned='NO' means but I'm guessing it means the table is not partitioned.  If it isn't, then no need to worry about it.

If it is partitioned and each partition has it's own tablespace then the move commands will likely have no effect on the location of the actual data.

You will just have to set up a test on your development server and try it.
d27m11yAuthor Commented:
Am a bit confused here. If each partition is in a different table space, I have to write separate scripts for the tables that are partitioned in different tablespaces. Is that right ?
slightwv (䄆 Netminder) Commented:
I've not done much with partitioning.  

I know that you can set up a different tablespace per partition.
I know you can tell a CLOB to be stored in a specific tablespace.

How that is handled when you combine the two, I have no idea.

I would suggest setting up a quick and simple test case and see.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d27m11yAuthor Commented:
Thank you !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.