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
  • 2
  • 2
  • 2
  • +2
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 76

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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
PL SQL Search Across Columns 4 52
Repeat query 13 59
grant user/role question 11 32
Checking for column width 8 28
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

792 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