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

d27m11y
d27m11y used Ask the Experts™
on
Hi,

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
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?

Author

Commented:
How about CLOb and BLob
Most Valuable Expert 2012
Distinguished Expert 2018
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:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11003
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
do we have to consider iot_type is null and iot_type not null  with partitioned = 'NO' when we select tables. Please advise
Most Valuable Expert 2012
Distinguished Expert 2018
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.

Author

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 ?
Most Valuable Expert 2012
Distinguished Expert 2018
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.

Author

Commented:
Thank you !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial