Restructuring of tablespaces.

We have a database established for the processing of xmldata. Basically, xml data is loaded into staging tables then parsed out into relational target tables. The database has been in existence for a number of years and when established was done so with uniformed managed tablespaces. When the xsd.xmlindex was later introduced, we wanted to take advantage of it, but found that such domain indexes required being in system managed tablespaces. Some system managed tablespaces were created for us to test the use of xml indexes, so we ended up with a tablespace structure like the following:
Tablespace       Size(MB)  Allocation Type
---------------- -------- ---------------
SYSAUX             35,840   SYSTEM
SYSTEM            102,400   SYSTEM
UNDO               20,840   SYSTEM
XMLDATA           532,480   UNIFORM
XMLDATA_AUTO      307,200   SYSTEM

The XMLDATA tablespace is now 99% used and, since the bulk of the staging tables exist in this tablespace, grows with every periodic load, and will surely exceed capacity with the next load. The XMLDATA_AUTO tablespace is currently only 10% used. What we would like to get accomplished is to "merge" the two tablespaces (XMLDATA and XMLDATA_AUTO) into one tablespace (named XMLDATA) that is system managed. Is there any way to accomplish that without having to add datafiles (i.e. no overall increase in storage space)?
LVL 33
awking00Information Technology SpecialistAsked:
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:
Only way I know to do this is MOVE the objects from one tablespace to the other.

Obviously this requires the storage to hold the new objects.

I don't know of a way to do this without some extra storage until you can drop the old tablespace once it has been cleaned up.

You might be able to reorganize the 'from' tablespace as you go and resize it smaller as you move the objects out BUT, I'm sure you know how hard it is to actually resize smaller...

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
There is no way to directly merge tablespaces, you can shrink files in one and extend in the other but that's about it until you move the objects
to help with resizing of files, go to dba_segments and work from the end of the file backwards, moving objects from the end of the files to the other tablespace so you can shrink as you go.

This might help you identify the end-of-file candidates

    FROM (SELECT e.owner,
                 ROW_NUMBER() OVER(PARTITION BY e.file_id ORDER BY block_id DESC) rn
            FROM dba_extents e, dba_data_files f
           WHERE f.file_id = e.file_id AND e.tablespace_name = 'XMLDATA')
   WHERE rn = 1
ORDER BY file_name;

--- or look at all of them, but sorted from end-to-beginning

  SELECT e.owner,
         MAX(e.block_id) last_block
    FROM dba_extents e, dba_data_files f
   WHERE f.file_id = e.file_id AND e.tablespace_name = 'XMLDATA'
GROUP BY e.owner,
ORDER BY f.file_name, last_block DESC;

These won't necessarily be the biggest or the smallest objects, but they will be those that prevent resizing the files to smaller amounts
Determine the Perfect Price for Your IT Services

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

slightwv (䄆 Netminder) Commented:
Resizing smaller is a HUGE pain.

Disk is cheap.

Can't get new disks, plug in a USB drive, create a tablespace on it, move everything over, drop the two you have, create a new tablespace there, move everything back or just offline it, move the datafile, alter to change the file name and online it.

Never tried putting a datafile on a USB drive but from a quick search, looks like some people have been able to.
If you have some 600G available for backups, use datapump to export the data, drop/create tablespaces and import the data.
awking00Information Technology SpecialistAuthor Commented:
Thanks for all of the prompt responses. I am not a dba so I will be mulling over the possibilities with our dbas as they will have to actually perform the tasks and get back to you shortly (I hope).
Steve WalesSenior Database AdministratorCommented:
I've requested that this question be closed as follows:

Accepted answer: 168 points for slightwv's comment #a40424580
Assisted answer: 166 points for sdstuber's comment #a40424623
Assisted answer: 166 points for slightwv's comment #a40424644

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
awking00Information Technology SpecialistAuthor Commented:
I apologize for not having closed this question earlier. I just plain forgot about it since the effort has been put on the back burner after the dba was able to add data files to the xmldata tablespace. The information you provided will still be valuable when the actual time comes to restructure the tablespaces.

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.