Reorganising tablespaces using EXP/IMP

We have many approaches to perform reorganising tablespaces to claim unused space in datafiles. Here in this article, will show you reorganising tablespaces with using EXP/IMP method.

There are number of scenarios that can lead to unused space in datafiles.
The most common I see are - One or more tables/indexes are truncated/deleted huge records from tables, during that time leaving empty areas in the datafiles that previously accommodated them.

Please remember, take backup before you perform reorganising a tablespace because its a big structural change.

Follow below DEMO for reorganising tablespaces using EXP/IMP method:

Step 1: Create new tablespace 'reorg_ts'  and and user 'reorg_user'.  Also create two dummy tables and insert some bunch of records to that tables. After that gather the tables statistics.

Step 2:  Now we can see both table segments are made up of multiple extents, each extent being made up of multiple blocks.

Step 3: Connect to reorg_user and truncate the REORG_T1 table.

Step 4: Even after truncated the REORG_T1 table, there is no difference on REORG_TS tablespace size and also tried to resize the datafile.

Step 5: Here we can see REORG_TS tablespace with lots of free space. For tablespaces with autoextend enabled, the script calculates the maximum sizes the datafiles can grow to, but the "FREE_MB" column is based on the current file size, so use that figure for tablespaces with datafiles set to autoextend. If we switch off autoextend for the relevant datafile, the output will look more clear.

Step 6: Now start with Export/Import process:

• Create directory object for the export and import to work
• Export the REORG_USER schema
• Drop the REORG_USER
• Resize the datafile to 5MB
• Import the REORG_USER schema

Now we can see the size of the datafile has been reduced from 58M to 29M.

Thats it. Hope this article will help you :)

Thanks,

Chowdari          

Find Tablespace Freespace in ORACLE 11g

Below query is useful to displays a list of tablespaces and their used/full status.

COL tspace form a25 Heading "Tablespace"
COL tot_ts_size form 99999999999999 Heading "Size (Mb)"
COL free_ts_size form 99999999999999 Heading "Free (Mb)"
COL ts_pct form 9999 Heading "% Free"
COL ts_pct1 form 9999 Heading "% Used"
BREAK on report
COMPUTE sum of free_ts_size on report
COMPUTE sum of tot_ts_size on report
SELECT                                                            /* + RULE */
         df.tablespace_name tspace, df.BYTES / (1024 * 1024) tot_ts_size,
         SUM (fs.BYTES) / (1024 * 1024) free_ts_size,
         NVL (ROUND (SUM (fs.BYTES) * 100 / df.BYTES), 1) ts_pct,
         ROUND ((df.BYTES - SUM (fs.BYTES)) * 100 / df.BYTES) ts_pct1
    FROM dba_free_space fs,
         (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.BYTES
UNION ALL
SELECT                                                            /* + RULE */
         df.tablespace_name tspace, fs.BYTES / (1024 * 1024) tot_ts_size,
         SUM (df.bytes_free) / (1024 * 1024) free_ts_size,
         NVL (ROUND ((SUM (fs.BYTES) - df.bytes_used) * 100 / fs.BYTES),
              1
             ) ts_pct,
         ROUND ((SUM (fs.BYTES) - df.bytes_free) * 100 / fs.BYTES) ts_pct1
    FROM dba_temp_files fs,
         (SELECT   tablespace_name, bytes_free, bytes_used
              FROM v$temp_space_header
          GROUP BY tablespace_name, bytes_free, bytes_used) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.BYTES, df.bytes_free, df.bytes_used
ORDER BY 4 DESC
/

Thanks,
Chowdari