Solved

Restructuring of tablespaces.

Posted on 2014-11-05
9
164 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
[X]
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
9 Comments
 
LVL 77

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 74

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 74

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
Independent Software Vendors: 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.
0
 
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 32

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 32

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
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

733 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