Restructuring of tablespaces.

Posted on 2014-11-05
Last Modified: 2015-06-11
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)?
Question by:awking00
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 250 total points
ID: 40424580
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...
LVL 74

Expert Comment

ID: 40424587
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
LVL 74

Assisted Solution

sdstuber earned 250 total points
ID: 40424623
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40424644
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.
LVL 29

Expert Comment

ID: 40424719
If you have some 600G available for backups, use datapump to export the data, drop/create tablespaces and import the data.
LVL 32

Author Comment

ID: 40426031
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).
LVL 22

Expert Comment

by:Steve Wales
ID: 40824743
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.
LVL 32

Author Closing Comment

ID: 40824744
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.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question