Solved

Restructuring of tablespaces.

Posted on 2014-11-05
9
151 Views
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)?
0
Comment
Question by:awking00
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 76

Accepted Solution

by:
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...
0
 
LVL 73

Expert Comment

by:sdstuber
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
0
 
LVL 73

Assisted Solution

by:sdstuber
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

  SELECT *
    FROM (SELECT e.owner,
                 e.segment_name,
                 e.partition_name,
                 e.file_id,
                 f.file_name,
                 e.block_id,
                 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,
         e.segment_name,
         e.partition_name,
         e.file_id,
         f.file_name,
         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,
         e.segment_name,
         e.partition_name,
         e.file_id,
         f.file_name
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
0
 
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.
0
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 29

Expert Comment

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

Author Comment

by:awking00
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).
0
 
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.
0
 
LVL 31

Author Closing Comment

by:awking00
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.

Thanks,
Alan
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now