There are a number of scenarios that can lead to unused space in datafiles. The two most common I see are:
- A lack of housekeeping/maintenance means that one or more tables have grown excessively. After the data is pruned the datafiles contain unused space that needs to be reclaimed.
- One or more segments (tables, partitions or indexes) have been moved to another tablespace leaving empty areas in the datafiles that previously held them.
- Setup Test Environment
- Identify Tablespaces with Free Space
- Export/Import
- Manual Tablespace Reorganization
- Enterprise Manager Tablespace Reorganization
- Shrink?
- Tablespaces with Multiple Datafiles
- Undo Tablespace
- Temp Tablespace
- SYSAUX Tablespace
Remember, reorganizing a table space is a big structural change. You should *always* take backup before doing any structural changes to databases you care about.
Setup Test Environment
Before we can look at the solutions we need to create a test environment so we can clearly see the problem. Each of the solutions presented require that this setup is done first.CONN / AS SYSDBA
-- Create a tablespace and user for the test.
DROP USER reclaim_user CASCADE;
DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE reclaim_ts DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE USER reclaim_user IDENTIFIED BY reclaim_user DEFAULT TABLESPACE reclaim_ts QUOTA UNLIMITED ON reclaim_ts;
GRANT CREATE SESSION, CREATE TABLE TO reclaim_user;
-- Create and populate two tables in the test schema.
CONN reclaim_user/reclaim_user
CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(1000),
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE t2 (
id NUMBER,
description VARCHAR2(1000),
CONSTRAINT t2_pk PRIMARY KEY (id)
);
INSERT /*+append*/ INTO t1
SELECT rownum, RPAD('x',
1000, 'x')
FROM dual
CONNECT BY level <=
10000;
COMMIT;
INSERT /*+append*/ INTO t2
SELECT rownum, RPAD('x',
1000, 'x')
FROM dual
CONNECT BY level <=
10000;
COMMIT;
EXEC
DBMS_STATS.gather_table_stats(USER, 't1');
EXEC
DBMS_STATS.gather_table_stats(USER, 't2');
CONN / AS SYSDBA
COLUMN segment_name FORMAT A30
SELECT segment_type, segment_name, COUNT(*)
FROM dba_extents
WHERE owner = 'RECLAIM_USER'
GROUP BY segment_type, segment_name
ORDER BY segment_type, segment_name;
SEGMENT_TYPE SEGMENT_NAME COUNT(*)
------------------ ------------------------------ ----------
INDEX T1_PK 3
INDEX T2_PK 3
TABLE T1 27
TABLE T2 27
SQL>
SELECT table_name, num_rows, blocks FROM dba_tables WHERE owner = 'RECLAIM_USER';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
T1 10000 1461
T2 10000 1461
SQL>Enterprise Manager gives us a nice image of the contents of the tablespace by doing the following:
- Click on the "Server" tab.
- Click the "Tablespaces" link.
- Select the "RECLAIM_TS" tablespace by clicking the radio button.
- Select the "Show Tablespace Contents" action and click the "Go" button.
- On the resulting page, expand the "Extent Map" section.
Now truncate the "T1" table to simulate a maintenance operation.
CONN reclaim_user/reclaim_user
TRUNCATE TABLE t1;
What we can see now is the "T1" table has a single extent (yellow) and there is lots of free space (green) in the center of the tablespace.
The fact that the majority of the free space is before some of the "T2" extents means we can not shrink the size of the datafile to release the space.
CONN / AS SYSDBA
COLUMN name FORMAT A50
SELECT name, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE name LIKE '%reclaim%';
NAME SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/DB11G/reclaim01.dbf 26
SQL>
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' RESIZE 24M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' RESIZE 24M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
SQL>So this represents the starting point of our problem. We have free space we need to release from the datafile associated with our tablespace.
Before working through any of the methods described below, recreate this situation.
Identify Tablespaces with Free Space
You can easily identify tablespaces with lots of free space using the ts_free_space.sql script.SQL> @ts_free_space.sql
TABLESPACE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB FREE_PCT USED_PCT
------------------------------ ---------- ---------- ----------- ----------- ---------- -----------
EXAMPLE 345 35 345 35 10 XXXXXXXXX-
RECLAIM_TS 25 12 32767 32754 99 ----------
SYSAUX 580 33 580 33 5 XXXXXXXXX-
SYSTEM 720 7 720 7 0 XXXXXXXXXX
UNDOTBS1 70 41 70 41 58 XXXX------
USERS 20 15 20 15 75 XXX-------
6 rows selected.
SQL>
For tablespaces with autoextend enabled, the script calculates the maximum sizes and percentages based on maximum size 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 last four columns will look more representative.
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' AUTOEXTEND OFF;
Database altered.
SQL> @ts_free_space.sql
TABLESPACE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB FREE_PCT USED_PCT
------------------------------ ---------- ---------- ----------- ----------- ---------- -----------
EXAMPLE 345 35 345 35 10 XXXXXXXXX-
RECLAIM_TS 25 12 25 12 48 XXXXX-----
SYSAUX 580 33 580 33 5 XXXXXXXXX-
SYSTEM 720 7 720 7 0 XXXXXXXXXX
UNDOTBS1 70 40 70 40 57 XXXX------
USERS 20 15 20 15 75 XXX-------
6 rows selected.
SQL>
Export/Import
The export/import process looks like this:- Export the schema objects that are stored in the specific tablespace/datafile you want to resize.
- Drop the exported objects.
- Resize the datafiles.
- Import the objects.
- Perform any required maintenance, like grants etc.
First, create a directory object for the export and import to work with.
CONN / AS SYSDBA
CREATE DIRECTORY temp_dir AS '/tmp';
GRANT READ, WRITE ON DIRECTORY temp_dir TO reclaim_user;
Export the objects in question. In this case we are doing the whole schema, but you may be able to get away with a subset of the objects if your schema is spread across several tablespaces.
expdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=expdpRECLAIM_USER.log
Drop the original objects and reduce the size of the datafile.
CONN / AS SYSDBA
DROP TABLE reclaim_user.t1;
DROP TABLE reclaim_user.t2;
PURGE DBA_RECYCLEBIN;
ALTER TABLESPACE reclaim_ts COALESCE;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' RESIZE 5M;
Import the objects back into the schema.
impdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=impdpRECLAIM_USER.log
The tablespace map shows we have removed the large section of free space in the middle of the datafile associated with our tablespace.
We can also see the size of the datafile has been reduced from 26M to 14M.
CONN / AS SYSDBA
COLUMN name FORMAT A50
SELECT name, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE name LIKE '%reclaim%';
NAME SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/DB11G/reclaim01.dbf 14
SQL>
In this example I truncated the first table, so my table segments did not contain much free space internally. If you have segments with lots of internal free space to clean up in addition to the unused space in the datafile, you may want to include the TRANFORM parameter in your import command. Using "TRANSFORM=SEGMENT_ATTRIBUTES:N" setting tells the import command to forget the physical attributes (including the storage clause) of the table when recreating it.
impdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=impdpRECLAIM_USER.log \
transform=segment_attributes:n
Manual Tablespace Reorganization
This method can take one of two forms. It you are happy to change the datafile name do the following:- Create a new tablespace.
- Move the segments to the new tablespace.
- Drop the original tablespace.
- Rename the new tablespace to match the original name. *
If the datafile name must remain the same do the following:
- Create a new tablespace.
- Move the segments to the new tablespace.
- Resize the original datafile.
- Move the segments back to the original tablespace.
- Drop the new tablespace.
The way to move segments depends on the type of segment being moved. Here are a few examples.
-- Move a table segment.
ALTER TABLE tab1 MOVE TABLESPACE new_ts;
-- Move an index segment.
ALTER INDEX ind1 REBUILD TABLESPACE new_ts;
ALTER INDEX ind1 REBUILD TABLESPACE new_ts ONLINE;
-- Move a table partition segment. (Remember to check for unusable indexes)
ALTER TABLE tab1 MOVE PARTITION part_1 TABLESPACE new_ts NOLOGGING;
-- Move an index partition segment.
ALTER INDEX ind1 REBUILD PARTITION ind1_part1 TABLESPACE new_ts;
-- Move LOB segments if we had them.
-- ALTER TABLE tab1 MOVE LOB(lob_column_name) STORE AS (TABLESPACE new_ts);
Of course, the tables and their respective indexes could be moved using the Online Table Redefinition functionality.
The following example performs a manual reorganization where the datafile name is not retained.
Remember to recreate the test environment before starting this example.
First, create a new tablespace to hold the objects.
CONN / AS SYSDBA
CREATE TABLESPACE reclaim_ts_temp DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
ALTER USER reclaim_user QUOTA UNLIMITED ON reclaim_ts_temp;
Move the objects to the new tablespace.
ALTER TABLE reclaim_user.t1 MOVE TABLESPACE reclaim_ts_temp;
ALTER INDEX reclaim_user.t1_pk REBUILD TABLESPACE reclaim_ts_temp;
ALTER TABLE reclaim_user.t2 MOVE TABLESPACE reclaim_ts_temp;
ALTER INDEX reclaim_user.t2_pk REBUILD TABLESPACE reclaim_ts_temp;
Drop the original tablespace and rename the new one back to the original name.
DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE reclaim_ts_temp RENAME TO reclaim_ts;
Once again, the tablespace map shows we have removed the large section of free space in the middle of the datafile associated with our tablespace.
We can also see the size of the datafile has been reduced from 26M to 13M.
CONN / AS SYSDBA
COLUMN name FORMAT A50
SELECT name, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE name LIKE '%reclaim%';
NAME SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/DB11G/reclaim01.dbf 13
SQL>
In Oracle 12c Release 1 (12.1) you can move table partitions and sub-partitions online. In Oracle 12c Release 2 (12.2) you can move regular tables online.
No comments:
Post a Comment