|
-
move datas from tablespace to another
Hi,
I've a tablespace with 36 datafiles. Each datafile size is about 30 Gb.
I've to move OLAP objects from this tablespace to another with datafiles less size then 30Gb (about 8Gb) because the policie by the client is to have a DF no more then 8 Gb.
How can I move this staff?
Thank's
Bensmail
-
You can use Oracle Data Pump. First export your tablespace with 'content=metadata_only' (This option only exports metadata not the data itself), and import it to new destination. After that resize your datafiles and this time export old tablespace with 'content=data_only' and import to new destination.
You can google it for datapump commands, it's not that difficult to execute that commands.
Good luck
-
Hi bensmail,
Another option is just create same tablespace with 8 gb of data files at new location and use data pump with network_link option. For this you must create a database link.
Btw, where is the other tablespace, on the same machine or another one? If it is another machine what kind of network connection do you have between those machines?
-
So you want to move some tables and indexes from one tablespace to another? in the same database?
How about: alter table move command? then rebuild your indexes.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Almost like 1 TB worth of data to be relocated. Alter table move tablespace parallel should work. Parallelize the operations based on the downtime allowed on the objects and dont forget to rebuild indexes in the new tablespace if they too fall under 30GB restriction.
-
 Originally Posted by bensmail
Hi,
I've a tablespace with 36 datafiles. Each datafile size is about 30 Gb.
I've to move OLAP objects from this tablespace to another with datafiles less size then 30Gb (about 8Gb) because the policie by the client is to have a DF no more then 8 Gb.
How can I move this staff?
Thank's
Bensmail
Make sure you create smallfile tablespace to let you create multiple datafiles. Also, mention MAXSIZE 8G while adding new datafiles.
HTH
gtcol
-
Originally Posted by sunil_kandi
...dont forget to rebuild indexes in the new tablespace if they too fall under 30GB restriction.
You gotta rebuild invalidated indexes anyway, don't you? 
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
You gotta rebuild invalidated indexes anyway, don't you?
What I meant is make sure of rebuilding the indexes in the "new tablespace" if the tablespaces holding indexes too fall under 8 GB restriction. Just a heads up to avoid rebuilding twice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|
Reply With Quote