Reduce tablespace size using Import/Export method
From dbawiki
- more ideas here
Contents
- 1 Situation:
- 2 Goal:
- 2.1 Steps:
- 2.2 Check space in tablespaces
- 2.3 Prepare a script to clean out user objects (after export)
- 2.4 Export everything in the tablespace (only 1 schema in this case)
- 2.5 Run the clean out script
- 2.6 Delete any rubbish lying around
- 2.7 Gather together the empty space
- 2.8 Reduce the datafile size
- 2.9 Import the tablespace contents
- 2.10 Recompile any invalid objects
- 2.11 Check our new space situation
Situation:[edit]
Due to much activity in the tablespace, lots of duplicate imports etc, tablespace has grown much bigger than the data in it.
Goal:[edit]
To bring down the tablespace ( datafile ) size to a reasonable level.
Steps:[edit]
- 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[edit]
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>
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)[edit]
(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;
Export everything in the tablespace (only 1 schema in this case)[edit]
(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
Run the clean out script[edit]
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.
Delete any rubbish lying around[edit]
SYS@ENDD4> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged.
Gather together the empty space[edit]
SYS@ENDD4> ALTER TABLESPACE ENDUR_DEV20_DT COALESCE; Tablespace altered.
Reduce the datafile size[edit]
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>
Import the tablespace contents[edit]
(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
Recompile any invalid objects[edit]
SYS@ENDD4> EXEC DBMS_UTILITY.compile_schema(schema => 'ENDUR_DEV20'); PL/SQL procedure successfully completed.
Check our new space situation[edit]
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>
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.
(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>
And finally we see the datafile has been reduced to 30Gb on the filesystem
Job done.
