7 Replies Latest reply: Jul 18, 2014 11:25 AM by jgarry RSS

    merging oracle tablesapces

    hishamawad


      Hi,

       

      I am using oracle databse 11.2. Currenty my schema uses 20 diffrent oracle tablespaces (TS01-TS20). I have a requiremnt to combine all these 20 tablespaces into just one tablespace (TS01).

      a- My schema uses around 200GB.

      b- Actually, I will be taking an export of schema on one machine with TS01-TS20 and importing it on another machine with only TS01.

      Any suggestions?

       

      Hisham

        • 1. Re: merging oracle tablesapces
          sb92075

          hishamawad wrote:

           


          Hi,

           

          I am using oracle databse 11.2. Currenty my schema uses 20 diffrent oracle tablespaces (TS01-TS20). I have a requiremnt to combine all these 20 tablespaces into just one tablespace (TS01).

          a- My schema uses around 200GB.

          b- Actually, I will be taking an export of schema on one machine with TS01-TS20 and importing it on another machine with only TS01.

          Any suggestions?

           

          Hisham

           

          what about DML changes to source schema between the time the export starts & the import completes?

          • 2. Re: merging oracle tablesapces
            sybrand_b

            Wny export? Why not just 'alter table .... move tablespace <new tablespace>

            On how to do it using expdp/impdp read the expdp/impdp documentation.

             


            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: merging oracle tablesapces
              jgarry

              Consider using network option of impdp

              Consider turning off archivelog mode during import, turning back on after and then taking a baseline backup.

              Consider pre-creating objects.

              Consider checking for DDL that is obsolete, including oversized initial.

              Consider if some objects should temporarily have a different pctfree for initial load.

              • 4. Re: merging oracle tablesapces
                rp0428
                I am using oracle databse 11.2. Currenty my schema uses 20 diffrent oracle tablespaces (TS01-TS20). I have a requiremnt to combine all these 20 tablespaces into just one tablespace (TS01).

                 

                Why?

                 

                What PROBLEM are you trying to solve.

                 

                Objects will take us the same amount of space no matter what tablespace they are in.

                a- My schema uses around 200GB.

                How did you determine that? Is that USED space of just allocated space?

                b- Actually, I will be taking an export of schema on one machine with TS01-TS20 and importing it on another machine with only TS01.

                That is NOT combining tablespaces which is what you said you wanted to do.

                 

                Why are you using another machine? Are you creating a new database on that other machine?

                • 5. Re: merging oracle tablesapces
                  hishamawad

                  Team,

                  Thanks for your responses. Here answers to your questions.

                  >what about DML changes to source schema between the time the export starts & the import completes?
                  This part of product upgrade. The application will be down. Hence, no DMLs.


                  >Wny export? Why not just 'alter table .... move tablespace <new tablespace>
                  The problem is the available disk space. Lets assume my available disk space is 200GB and all of it is alocated to TS01-TS20.
                  Also, assume table1 uses TS02. To move table1 to TS01, I need to make TS01 bigger. But I do not have more disk space to add to TS01. 


                  >On how to do it using expdp/impdp read the expdp/impdp documentation
                  >Consider using network option of impdp
                  Thanks. I am in the process of reading expdp/impdp documentation. I am just looking for experts advice.


                  >Consider turning off archivelog mode during import, turning back on after and then taking a baseline backup.
                  Thanks. Very good idea.


                  >Consider pre-creating objects.
                  Yes, I was trying tp precreate the objects in the new tablespace then doing the import with ignore=y.


                  >Consider checking for DDL that is obsolete, including oversized initial.
                  Would you please provide more info about how to do the above.


                  >Consider if some objects should temporarily have a different pctfree for initial load.
                  Thanks.

                   

                  >Why? What PROBLEM are you trying to solve.Objects will take us the same amount of space no matter what tablespace they are in.
                  Actually is what you stated "Objects will take us the same amount of space no matter what tablespace they are in". Why have multiple tablespaces?

                   

                  >How did you determine that? Is that USED space of just allocated space?
                  It is the used space. I used the following sqlplus statement
                  select substr(a.tablespace_name,1,20) tableSpaceName,
                          round(sum(a.tots)/1024/1024) Tot_Size,
                          round(sum(a.sumb)/1024/1024) Tot_Free,
                          round((sum(a.tots)/1024/1024) - (sum(a.sumb)/1024/1024)) Used,
                          round(sum(a.sumb)*100/sum(a.tots)) Pct_Free,
                          round(sum(a.largest)) Max_Free,
                          round(sum(a.chunks)) Chunks_Free
                  from    (   select tablespace_name,0 tots,sum(bytes) sumb,
                          max(bytes) largest,count(*) chunks
                          from dba_free_space a
                          group by tablespace_name
                  union
                          select tablespace_name,sum(bytes) tots,0,0,0
                          from   dba_data_files
                          group by tablespace_name) a
                  group by a.tablespace_name;


                  >That is NOT combining tablespaces which is what you said you wanted to do.
                  Sorry. I was not sure what word to use. Maybe "merging tablespaces" is better.

                  >Why are you using another machine? Are you creating a new database on that other machine?
                  It is part of our application upgrade. We are moving the application to newer version of machine.


                  Thanks,

                  Hisham

                  • 6. Re: merging oracle tablesapces
                    rp0428
                    It is part of our application upgrade. We are moving the application to newer version of machine.

                    Then you might want to consider the possibility of remapping the tablespaces when you do the actual import. You could do a normal export but then take care of the tablespace issue as part of the import.

                     

                    See the utilities doc:

                    http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_import.htm#sthref534

                    REMAP_TABLESPACE

                    Default: There is no default

                    Purpose

                    Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.

                    Syntax and Description

                    REMAP_TABLESPACE=source_tablespace:target_tablespace  

                    Multiple REMAP_TABLESPACE parameters can be specified, but no two can have the same source tablespace. The target schema must have sufficient quota in the target tablespace.

                    Note that use of the REMAP_TABLESPACE parameter is the only way to remap a tablespace in Data Pump Import. This is a simpler and cleaner method than the one provided in the original Import utility. That method was subject to many restrictions (including the number of tablespace subclauses) which sometimes resulted in the failure of some DDL commands.

                    By contrast, the Data Pump Import method of using the REMAP_TABLESPACE parameter works for all objects, including the user, and it works regardless of how many tablespace subclauses are in the DDL statement.

                    Restrictions

                    •   Data Pump Import can only remap tablespaces for transportable imports in databases where the compatibility level is 10.1 or later.
                    •   Only objects created by the Import will be remapped. In particular, the tablespaces for preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to SKIP, TRUNCATE or APPEND.
                    • 7. Re: merging oracle tablesapces
                      jgarry

                      >> Consider checking for DDL that is obsolete, including oversized initial.

                      > Would you please provide more info about how to do the above.

                       

                      I was just referring to basic administration here.  What I've noticed often with major app upgrades is there is a lot of deferred maintenance.  Since the app is down and there is a lot of moving and testing of things anyways, it's a good time to perform tasks that ordinarily might be avoided since they don't make enough problems for people to complain.   What those tasks might be requires some analysis.

                       

                      So some obvious things to look for are:

                       

                      Tables that aren't used (this is less of an issue than in previous versions, it is what deferred segment creation can be most useful for when apps, say, use only a fraction of ten thousand tables).

                       

                      Tablespaces that have been converted from DMT.  In such a case, older data may still have less than ideal distribution, even fragmentation.

                       

                      Data density and scatter.  This is a risk and benefit of any table reorganization, there may be implicit physical ordering of data that comes simply from the way it is added or manipulated.  Changing that may make a performance difference, good, close to neutral, or bad.  Differences from plan changes usually make a more noticeable difference, but review what people have to say about cluster factor too.  Some aspects of this include pctfree adjustment and row migration.

                       

                      Tables that have had much more data but won't again any time soon.  So when you create them (or let impdp create them) using the current DDL, they still get big.  App upgrades sometimes add archive tables or some other splitting of data into two or more tables.

                       

                      Column order.  This may not be a big deal on normalized heap tables, may make a difference if you have strange things like large numbers of unused columns or large columns (hey, it wasn't that long ago I converted a database worth of CHAR to VARCHAR2...).

                       

                      LOB's may have issues.  That would be another thread.

                       

                      There are many approaches to these issues, I've used things like Tablespace HWM | Oracle Scratchpad to get an idea what might be suspicious.

                       

                      Of course, one must be careful not to fall into a "Compulsive Tuning Disorder" trap with this, but usually an upgrade has time limits that at least make you consider what is important.