dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
  *  more ideas [[http://oracle-base.com/articles/misc/reclaiming-unused-space.php|here]]
==== Situation: ====
Due to much activity in the tablespace, lots of duplicate imports etc, tablespace has grown much bigger than the data in it.

==== Goal: ====
To bring down the tablespace ( datafile ) size to a reasonable level.

=== Steps: ===
  *  Export the schema objects stored in the tablesace (datafile)
  *  Drop those schema objects
  *  Purge the recyclebin
  *  Coalesce the tablespace
  *  Resize the datafile
  *  Import the objects



=== Check space in tablespaces ===
<code>
SYS@ENDD4> @util_ts_free_space.sql

TABLESPACE_NAME                   SIZE_MB    FREE_MB MAX_SIZE_MB MAX_FREE_MB   FREE_PCT MAX_FREE_PCT USED_PCT    MAX_USED_PC
------------------------------ ---------- ---------- ----------- ----------- ---------- ------------ ----------- -----------
AUDIT_DT                              600         54        3000        2454          9           81  XXXXXXXXX-  XX--------
ENDUR_DEV16_DT                      64960      34896    33554431    33524367         53           99  XXXXX-----  ----------
ENDUR_DEV17_DT                      49360      18217    33554431    33523288         36           99  XXXXXX----  ----------
ENDUR_DEV18_DT                      53700      23116    33554431    33523847         43           99  XXXXXX----  ----------
ENDUR_DEV19_DT                      48760      18060    33554431    33523731         37           99  XXXXXX----  ----------
ENDUR_DEV20_DT  *reduce this one*  155920     125435    33554431    33523946         80           99  XX--------  ----------
ENDUR_DS_DT                           100         99       10240       10239         99           99  ----------  ----------
SYSAUX                               1640         92       32767       31219          5           95  XXXXXXXXX-  ----------
SYSTEM                               1630        315       32767       31452         19           95  XXXXXXXX--  ----------
TIVOLI                                100         98       10240       10238         98           99  ----------  ----------
UNDOTBS1                            12045      11883       32767       32605         98           99  ----------  ----------
USERS                                1351        768       32767       32184         56           98  XXXX------  ----------

12 rows selected.

SYS@ENDD4>
</code>
Here we see that the tablespace is 156Gb but most of it is free space (156 - 125 = 31Gb used).

=== Prepare a script to clean out user objects (after export) ===
<code>
(0) ENDD4 oraibm@udevdba31:/home/ibmtools/scripts/oracle> ./util_drop_user_objects.ksh ENDUR_DEV20
drop FUNCTION ENDUR_DEV20.GET_INSTANCE_VERSION;
drop FUNCTION ENDUR_DEV20.TRAN_COUNT;
drop FUNCTION ENDUR_DEV20.VERSION_COMPARE;
drop FUNCTION ENDUR_DEV20.VERSION_COMPARE_MOD;
drop PACKAGE ENDUR_DEV20.ACCT_LOAD_SUBLEDGER_ENTRIESP;
drop PACKAGE ENDUR_DEV20.GET_NEXT_ANE_MESSAGEP;
drop PACKAGE ENDUR_DEV20.OLF_CURSORP;
drop PACKAGE ENDUR_DEV20.ORA_TRAN_COUNT;
drop PACKAGE ENDUR_DEV20.OTHER_PROCESSP;
drop PROCEDURE ENDUR_DEV20.ABACUS_FILE_SYSTEM_HIST_PURGE;
drop PROCEDURE ENDUR_DEV20.ACCEPT_NOVATION;
drop PROCEDURE ENDUR_DEV20.ACCOUNT_CLASS_HISTORY_PURGE;
drop PROCEDURE ENDUR_DEV20.ACCOUNT_HISTORY_PURGE;
drop PROCEDURE ENDUR_DEV20.ACCT_CLEAN_PROC_STATS;
drop PROCEDURE ENDUR_DEV20.ACCT_CLEAR_BAL_SHEET_XREFS;
drop PROCEDURE ENDUR_DEV20.ACCT_CLEAR_SUBLEDGER_XREFS;
drop PROCEDURE ENDUR_DEV20.ACCT_CLEAR_SUBL_GRP_XREFS;
drop PROCEDURE ENDUR_DEV20.ACCT_CONFIG_HISTORY_PURGE;
drop PROCEDURE ENDUR_DEV20.ACCT_COUNT_GENERATED_SIMS;
...

...
drop TABLE ENDUR_DEV20.WFLOW_WAITING CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.WORKDAY_DETAIL CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.WORKDAY_HDR CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.WORK_RESULT CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.XF_NAMESPACES CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.YIELD_BASIS CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.YIELD_CALC_METHOD CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.YIELD_COMP_FREQ CASCADE CONSTRAINTS PURGE;
drop TABLE ENDUR_DEV20.YIELD_TO_METHOD CASCADE CONSTRAINTS PURGE;
drop TRIGGER ENDUR_DEV20.TRG_INSERT_INTERNAL_ORDER;
drop VIEW ENDUR_DEV20.AB_TRAN_ACCOUNT_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_AGREEMENT_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_DOCUMENT_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_EVENT_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_HISTORY_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_INFO_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_POSITION_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_PROVISIONAL_VIEW;
drop VIEW ENDUR_DEV20.AB_TRAN_SETTLE_ACCOUNT_VIEW;
</code>


=== Export everything in the tablespace (only 1 schema in this case) ===
<code>
(0) ENDD4 oraibm@udevdba31:/home/ibmtools/scripts/oracle> expdp \\'/ as sysdba\\' dumpfile=expdp_ENDD4_ENDUR_DEV20_20131113.dmp job_name=expdp_ENDD4_ENDUR_DEV20_20131113 logfile=expdp_ENDD4_ENDUR_DEV20_20131113.log schemas=ENDUR_DEV20

Export: Release 11.2.0.3.0 - Production on Wed Nov 13 13:08:03 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."expdp_ENDD4_ENDUR_DEV20_201311":  "/******** AS SYSDBA" dumpfile=expdp_ENDD4_ENDUR_DEV20_20131113.dmp job_name=expdp_ENDD4_ENDUR_DEV20_20131113 logfile=expdp_ENDD4_ENDUR_DEV20_20131113.log schemas=ENDUR_DEV20
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.28 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
...

...
. . exported "ENDUR_DEV20"."VOL_INFO"                        0 KB       0 rows
. . exported "ENDUR_DEV20"."VOL_INFO_H"                      0 KB       0 rows
. . exported "ENDUR_DEV20"."VOL_INFO_TYPES"                  0 KB       0 rows
. . exported "ENDUR_DEV20"."VOL_INFO_TYPES_H"                0 KB       0 rows
. . exported "ENDUR_DEV20"."WFLD_STATUS"                     0 KB       0 rows
. . exported "ENDUR_DEV20"."WFLOW_WAITING"                   0 KB       0 rows
. . exported "ENDUR_DEV20"."WORK_RESULT"                     0 KB       0 rows
Master table "SYS"."expdp_ENDD4_ENDUR_DEV20_201311" successfully loaded/unloaded
          * *************************************************************************
Dump file set for SYS.expdp_ENDD4_ENDUR_DEV20_201311 is:
  /oracle/shared/udevdba31/export/ENDD4/expdp_ENDD4_ENDUR_DEV20_20131113.dmp
Job "SYS"."expdp_ENDD4_ENDUR_DEV20_201311" successfully completed at 13:30:13
</code>


=== Run the clean out script ===
<code>
SYS@ENDD4> @clean_out_ENDUR_DEV20.sql
Function dropped.


Function dropped.


Function dropped.


Function dropped.


Package dropped.


Package dropped.


Package dropped.


Table dropped.


Table dropped.

...

...

Table dropped.


Table dropped.


View dropped.


View dropped.


View dropped.


View dropped.


View dropped.


View dropped.


View dropped.
</code>

=== Delete any rubbish lying around ===
<code>

SYS@ENDD4> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.
</code>

=== Gather together the empty space ===
<code>
SYS@ENDD4> ALTER TABLESPACE ENDUR_DEV20_DT COALESCE;

Tablespace altered.
</code>

=== Reduce the datafile size ===
<code>
SYS@ENDD4> select file_name from dba_data_files where tablespace_name = 'ENDUR_DEV20_DT';

FILE_NAME
-------------------------------------------------------------------------------------
/oracle/ENDD4/oradata3/ENDUR_DEV20_DT.dbf

SYS@ENDD4> ALTER DATABASE DATAFILE '/oracle/ENDD4/oradata3/ENDUR_DEV20_DT.dbf' RESIZE 30000M;

Database altered.

SYS@ENDD4>
</code>

=== Import the tablespace contents ===
<code>
(0) ENDD4 oraibm@udevdba31:/oracle/ENDD4/oradata3> impdp \\'/ as sysdba\\' dumpfile=expdp_ENDD4_ENDUR_DEV20_20131113.dmp job_name=impdp_ENDD4_ENDUR_DEV20_20131113 logfile=impdp_ENDD4_ENDUR_DEV20_20131113.log

Import: Release 11.2.0.3.0 - Production on Wed Nov 13 13:56:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."impdp_ENDD4_ENDUR_DEV20_201311" successfully loaded/unloaded
Starting "SYS"."impdp_ENDD4_ENDUR_DEV20_201311":  "/******** AS SYSDBA" dumpfile=expdp_ENDD4_ENDUR_DEV20_20131113.dmp job_name=impdp_ENDD4_ENDUR_DEV20_20131113 logfile=impdp_ENDD4_ENDUR_DEV20_20131113.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ENDUR_DEV20" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ENDUR_DEV20"."TFE_MSG_LOG_HIST"            5.966 GB 49975406 rows
. . imported "ENDUR_DEV20"."TFE_BATCH_TIMINGS_HIST"      2.981 GB 33591036 rows
. . imported "ENDUR_DEV20"."OP_SERVICES_LOG"             947.2 MB  165705 rows
. . imported "ENDUR_DEV20"."DW_PNL_DETAIL_RESULTS"       1.110 GB 6722206 rows
. . imported "ENDUR_DEV20"."RESET"                       626.0 MB 8004880 rows
. . imported "ENDUR_DEV20"."DW_TRAN_LEG_RESULTS"         421.6 MB 4805191 rows
. . imported "ENDUR_DEV20"."STLDOC_INFO_H"               410.6 MB 7336027 rows
. . imported "ENDUR_DEV20"."VOLUME_TYPE_USAGE_H"             0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_DATA_ARCHIVE"                0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_DEF_DOMAIN"                  0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_DOMAIN_DIMENSION_TYPES_H"      0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_INFO"                        0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_INFO_H"                      0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_INFO_TYPES"                  0 KB       0 rows
. . imported "ENDUR_DEV20"."VOL_INFO_TYPES_H"                0 KB       0 rows
. . imported "ENDUR_DEV20"."WFLD_STATUS"                     0 KB       0 rows
. . imported "ENDUR_DEV20"."WFLOW_WAITING"                   0 KB       0 rows
. . imported "ENDUR_DEV20"."WORK_RESULT"                     0 KB       0 rows
...

...
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
</code>

=== Recompile any invalid objects ===
<code>
SYS@ENDD4> EXEC DBMS_UTILITY.compile_schema(schema => 'ENDUR_DEV20');

PL/SQL procedure successfully completed.
</code>

=== Check our new space situation ===
<code>
SYS@ENDD4> @util_ts_free_space.sql

TABLESPACE_NAME                   SIZE_MB    FREE_MB MAX_SIZE_MB MAX_FREE_MB   FREE_PCT MAX_FREE_PCT USED_PCT    MAX_USED_PC
------------------------------ ---------- ---------- ----------- ----------- ---------- ------------ ----------- -----------
AUDIT_DT                              600         54        3000        2454          9           81  XXXXXXXXX-  XX--------
ENDUR_DEV16_DT                      64960      34896    33554431    33524367         53           99  XXXXX-----  ----------
ENDUR_DEV17_DT                      49360      18217    33554431    33523288         36           99  XXXXXX----  ----------
ENDUR_DEV18_DT                      53700      23116    33554431    33523847         43           99  XXXXXX----  ----------
ENDUR_DEV19_DT                      48760      18060    33554431    33523731         37           99  XXXXXX----  ----------
ENDUR_DEV20_DT                      32000       1544    33554431    33523975          4           99  XXXXXXXXXX  ----------
ENDUR_DS_DT                           100         99       10240       10239         99           99  ----------  ----------
SYSAUX                               1640         86       32767       31213          5           95  XXXXXXXXX-  ----------
SYSTEM                               1630        393       32767       31530         24           96  XXXXXXXX--  ----------
TIVOLI                                100         98       10240       10238         98           99  ----------  ----------
UNDOTBS1                            12045      11321       32767       32043         93           97  X---------  ----------
USERS                                1351        768       32767       32184         56           98  XXXX------  ----------

12 rows selected.

SYS@ENDD4>
</code>
Here we see we have reduced the tablespace size down from 156Gb to about 30Gb and almost all the free space has gone - only 1.5Gb free space - but autoextend is on so it can grow.


<code>
(0) ENDD4 oraibm@udevdba31:/oracle/ENDD4/oradata3> ll
total 533686984
drwxr-xr-x    3 oracle   dba            4096 May 22 16:45 .
drwxr-xr-x   14 oracle   dba            4096 May 08 2013  ..
-rw-r-----    1 oracle   dba      68115505152 Nov 13 13:49 ENDUR_DEV16_DT.dbf
-rw-r-----    1 oracle   dba      51757719552 Nov 13 13:49 ENDUR_DEV17_DT.dbf
-rw-r-----    1 oracle   dba      56308539392 Nov 13 13:53 ENDUR_DEV18_DT.dbf
-rw-r-----    1 oracle   dba      51128573952 Nov 13 13:49 ENDUR_DEV19_DT.dbf
-rw-r-----    1 oracle   dba  ==> 31457288192 Nov 13 13:51 ENDUR_DEV20_DT.dbf
-rw-r-----    1 oracle   dba        12173312 Nov 13 13:54 control03.ctl
-rw-r-----    1 oracle   dba       104865792 Nov 13 13:49 endur_ds_dt.dbf
drwxr-xr-x    2 oracle   dba             256 May 07 2013  lost+found
-rw-r-----    1 oracle   dba       209715712 May 22 16:49 redo31.log
-rw-r-----    1 oracle   dba       104865792 Nov 13 13:49 tivoli.dbf
-rw-r-----    1 oracle   dba      12630106112 Nov 13 13:54 undotbs01.dbf
-rw-r-----    1 oracle   dba      1416896512 Nov 13 13:53 users01.dbf
(0) ENDD4 oraibm@udevdba31:/oracle/ENDD4/oradata3>
</code>

And finally we see the datafile has been reduced to 30Gb on the filesystem\\

Job done.