-
Is it possible to merge two tablespaces into one?
Hello everyone,
We have an instance which was migrated from Windows to RedHat by export/import. The result generated two tablespace names which are quite similar, but not identical. I would like to merge the data in them in order to keep only one tablespace instead of two.
Here is how it looks. We have the tablespace NUM_DATA01 which is in the /u05/oradata/desref/num_data01.dbf datafile.
Next we have the NUM_DATA tablespace which lives in the /u01/app/oracle/product/920/dbs/D:ORADATADESREFNUM_DATA01.DBF datafile (which you can see it's Windows origin in the file name).
The goal is to keep the NUM_DATA tablespace and destroy the NUM_DATA01 tablespace.
Question is: Can we merge the data that is in NUM_DATA01 into NUM_DATA? (I don't even know if it contains data, but I don't want to take chances).
I would then rename /u01/app/oracle/product/920/dbs/D:ORADATADESREFNUM_DATA01.DBF into /u05/oradata/desref/num_data.dbf. Finally, drop the /u05/oradata/desref/num_data01.dbf datafile.
I know how to proceed in order to rename datafiles, but it's the data merge of NUM_DATA01 into NUM_DATA that puzzles me?!??
Is this possible? Am I dreaming here? Are there better ways of doing this?
Many thanks!
David
-
Use "ALTER TABLE ..MOVE TABLESPACE ..." command to move tables from one tablespace to another tablespace, and rebuild the indexes.
Then, drop the unwanted tablespace.
Tamil
-
Hi Tamil,
Thanks for the info. I'll RTFM the exact commands in the Oracle documentation and see if I can come up with something good
Cheers,
David
-
Hello again,
I need to drop a tablespace. But before I do so, I'm trying to figure out if a tablespace has any data in it with this:
SQL> select * from dba_extents where tablespace_name = 'NUM_DATA01';
no rows selected
Does that mean that the tablespace NUM_DATA01 has no data in it and therefore it is safe to destroy?
Many thanks,
David
-
-
Great, thanks again Tamil.
David
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
|