Oracle: Script para Desfragmentação

Nada de mais aqui pessoal. Simples, agressivo e eficiente. Peguei um Script do Tim Hall, e alterei algumas coisas.
Este script faz MOVE de todas as tabelas de um SCHEMA, REBUILD de todos os índices, e em seguida coleta as estatísticas do SCHEMA.
Use por sua conta e risco – ARCHIVED REDO LOGs, espaço na TABLESPACE, indisponibilidade por conta do MOVE, Estatísticas, etc.

Script:

ALTER SESSION SET CURRENT_SCHEMA = &2;

SET PAGESIZE 0
SET VERIFY OFF
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SPOOL tempMOVE.sql
SELECT 'ALTER TABLE ' || A.TABLE_NAME || ' MOVE;' FROM ALL_TABLES A WHERE TABLE_NAME = DECODE(UPPER('&1'),'ALL',A.TABLE_NAME,UPPER('&1')) AND OWNER = UPPER('&2') ORDER BY 1;
SPOOL OFF
SET FEEDBACK ON
SET TIMING ON
SET ECHO ON
SPOOL resultadoMOVE.sql
@tempMOVE.sql
SPOOL OFF

SET PAGESIZE 0
SET VERIFY OFF
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SPOOL tempREBUILD.sql
SELECT 'ALTER INDEX ' || A.INDEX_NAME || ' REBUILD;' FROM ALL_INDEXES A WHERE INDEX_NAME = DECODE(UPPER('&1'),'ALL',A.INDEX_NAME,UPPER('&1')) AND TABLE_OWNER = UPPER('&2') ORDER BY 1;
SPOOL OFF
SET FEEDBACK ON
SET TIMING ON
SET ECHO ON
SPOOL resultadoREBUILD.sql
@tempREBUILD.sql
SPOOL OFF


EXEC DBMS_STATS.GATHER_SCHEMA_STATS('&2', OPTIONS=>'GATHER', CASCADE=>TRUE, METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY', NO_INVALIDATE=>FALSE);

Como usar:

[oracle@localhost ~]$ rlwrap sqlplus SYSTEM/Nerv2017@PROD

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 3 15:13:32 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Aug 03 2017 14:32:31 -03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

15:13:34 SYSTEM@PROD> @MOVEANDREBUILD.sql ALL SOE
old   1: ALTER SESSION SET CURRENT_SCHEMA = &2
new   1: ALTER SESSION SET CURRENT_SCHEMA = SOE

Session altered.

Elapsed: 00:00:00.00
ALTER TABLE ADDRESSES MOVE;
ALTER TABLE CARD_DETAILS MOVE;
ALTER TABLE CUSTOMERS MOVE;
ALTER TABLE INVENTORIES MOVE;
ALTER TABLE LOGON MOVE;
ALTER TABLE ORDERENTRY_METADATA MOVE;
ALTER TABLE ORDERS MOVE;
ALTER TABLE ORDER_ITEMS MOVE;
ALTER TABLE PRODUCT_DESCRIPTIONS MOVE;
ALTER TABLE PRODUCT_INFORMATION MOVE;
ALTER TABLE WAREHOUSES MOVE;
15:13:39 SYSTEM@PROD> @tempMOVE.sql
15:13:39 SYSTEM@PROD> ALTER TABLE ADDRESSES MOVE;

Table altered.

Elapsed: 00:00:04.67
15:13:43 SYSTEM@PROD> ALTER TABLE CARD_DETAILS MOVE;

Table altered.

Elapsed: 00:00:01.58
15:13:45 SYSTEM@PROD> ALTER TABLE CUSTOMERS MOVE;

Table altered.

Elapsed: 00:00:06.51
15:13:51 SYSTEM@PROD> ALTER TABLE INVENTORIES MOVE;

Table altered.

Elapsed: 00:00:25.00
15:14:16 SYSTEM@PROD> ALTER TABLE LOGON MOVE;

Table altered.

Elapsed: 00:00:06.18
15:14:22 SYSTEM@PROD> ALTER TABLE ORDERENTRY_METADATA MOVE;

Table altered.

Elapsed: 00:00:00.34
15:14:23 SYSTEM@PROD> ALTER TABLE ORDERS MOVE;

Table altered.

Elapsed: 00:00:22.45
15:14:45 SYSTEM@PROD> ALTER TABLE ORDER_ITEMS MOVE;

Table altered.

Elapsed: 00:00:19.44
15:15:05 SYSTEM@PROD> ALTER TABLE PRODUCT_DESCRIPTIONS MOVE;

Table altered.

Elapsed: 00:00:00.96
15:15:06 SYSTEM@PROD> ALTER TABLE PRODUCT_INFORMATION MOVE;

Table altered.

Elapsed: 00:00:00.21
15:15:06 SYSTEM@PROD> ALTER TABLE WAREHOUSES MOVE;

Table altered.

Elapsed: 00:00:00.35
15:15:06 SYSTEM@PROD>
15:15:06 SYSTEM@PROD> SET PAGESIZE 0
15:15:06 SYSTEM@PROD> SET VERIFY OFF
15:15:06 SYSTEM@PROD> SET FEEDBACK OFF
15:15:06 SYSTEM@PROD> SET TIMING OFF
15:15:06 SYSTEM@PROD> SET ECHO OFF
ALTER INDEX ADDRESS_CUST_IX REBUILD;
ALTER INDEX ADDRESS_PK REBUILD;
ALTER INDEX CARDDETAILS_CUST_IX REBUILD;
ALTER INDEX CARD_DETAILS_PK REBUILD;
ALTER INDEX CUSTOMERS_PK REBUILD;
ALTER INDEX CUST_ACCOUNT_MANAGER_IX REBUILD;
ALTER INDEX CUST_DOB_IX REBUILD;
ALTER INDEX CUST_EMAIL_IX REBUILD;
ALTER INDEX CUST_FUNC_LOWER_NAME_IX REBUILD;
ALTER INDEX INVENTORY_PK REBUILD;
ALTER INDEX INV_PRODUCT_IX REBUILD;
ALTER INDEX INV_WAREHOUSE_IX REBUILD;
ALTER INDEX ITEM_ORDER_IX REBUILD;
ALTER INDEX ITEM_PRODUCT_IX REBUILD;
ALTER INDEX ORDER_ITEMS_PK REBUILD;
ALTER INDEX ORDER_PK REBUILD;
ALTER INDEX ORD_CUSTOMER_IX REBUILD;
ALTER INDEX ORD_ORDER_DATE_IX REBUILD;
ALTER INDEX ORD_SALES_REP_IX REBUILD;
ALTER INDEX ORD_WAREHOUSE_IX REBUILD;
ALTER INDEX PRD_DESC_PK REBUILD;
ALTER INDEX PRODUCT_INFORMATION_PK REBUILD;
ALTER INDEX PROD_CATEGORY_IX REBUILD;
ALTER INDEX PROD_NAME_IX REBUILD;
ALTER INDEX PROD_SUPPLIER_IX REBUILD;
ALTER INDEX WAREHOUSES_PK REBUILD;
ALTER INDEX WHS_LOCATION_IX REBUILD;
15:15:07 SYSTEM@PROD> @tempREBUILD.sql
15:15:07 SYSTEM@PROD> ALTER INDEX ADDRESS_CUST_IX REBUILD;

Index altered.

Elapsed: 00:00:19.53
15:15:27 SYSTEM@PROD> ALTER INDEX ADDRESS_PK REBUILD;

Index altered.

Elapsed: 00:00:04.91
15:15:31 SYSTEM@PROD> ALTER INDEX CARDDETAILS_CUST_IX REBUILD;

Index altered.

Elapsed: 00:00:13.83
15:15:45 SYSTEM@PROD> ALTER INDEX CARD_DETAILS_PK REBUILD;

Index altered.

Elapsed: 00:00:03.16
15:15:48 SYSTEM@PROD> ALTER INDEX CUSTOMERS_PK REBUILD;

Index altered.

Elapsed: 00:00:15.31
15:16:04 SYSTEM@PROD> ALTER INDEX CUST_ACCOUNT_MANAGER_IX REBUILD;

Index altered.

Elapsed: 00:00:01.99
15:16:06 SYSTEM@PROD> ALTER INDEX CUST_DOB_IX REBUILD;

Index altered.

Elapsed: 00:00:01.68
15:16:07 SYSTEM@PROD> ALTER INDEX CUST_EMAIL_IX REBUILD;

Index altered.

Elapsed: 00:00:02.41
15:16:10 SYSTEM@PROD> ALTER INDEX CUST_FUNC_LOWER_NAME_IX REBUILD;

Index altered.

Elapsed: 00:00:07.07
15:16:17 SYSTEM@PROD> ALTER INDEX INVENTORY_PK REBUILD;

Index altered.

Elapsed: 00:00:19.05
15:16:36 SYSTEM@PROD> ALTER INDEX INV_PRODUCT_IX REBUILD;

Index altered.

Elapsed: 00:00:02.95
15:16:39 SYSTEM@PROD> ALTER INDEX INV_WAREHOUSE_IX REBUILD;

Index altered.

Elapsed: 00:00:02.57
15:16:41 SYSTEM@PROD> ALTER INDEX ITEM_ORDER_IX REBUILD;

Index altered.

Elapsed: 00:00:15.44
15:16:57 SYSTEM@PROD> ALTER INDEX ITEM_PRODUCT_IX REBUILD;

Index altered.

Elapsed: 00:00:16.82
15:17:14 SYSTEM@PROD> ALTER INDEX ORDER_ITEMS_PK REBUILD;

Index altered.

Elapsed: 00:00:07.39
15:17:21 SYSTEM@PROD> ALTER INDEX ORDER_PK REBUILD;

Index altered.

Elapsed: 00:00:47.29
15:18:08 SYSTEM@PROD> ALTER INDEX ORD_CUSTOMER_IX REBUILD;

Index altered.

Elapsed: 00:00:02.23
15:18:11 SYSTEM@PROD> ALTER INDEX ORD_ORDER_DATE_IX REBUILD;

Index altered.

Elapsed: 00:00:02.35
15:18:13 SYSTEM@PROD> ALTER INDEX ORD_SALES_REP_IX REBUILD;

Index altered.

Elapsed: 00:00:02.81
15:18:16 SYSTEM@PROD> ALTER INDEX ORD_WAREHOUSE_IX REBUILD;

Index altered.

Elapsed: 00:00:02.99
15:18:19 SYSTEM@PROD> ALTER INDEX PRD_DESC_PK REBUILD;

Index altered.

Elapsed: 00:00:00.83
15:18:20 SYSTEM@PROD> ALTER INDEX PRODUCT_INFORMATION_PK REBUILD;

Index altered.

Elapsed: 00:00:00.31
15:18:20 SYSTEM@PROD> ALTER INDEX PROD_CATEGORY_IX REBUILD;

Index altered.

Elapsed: 00:00:00.01
15:18:20 SYSTEM@PROD> ALTER INDEX PROD_NAME_IX REBUILD;

Index altered.

Elapsed: 00:00:00.01
15:18:20 SYSTEM@PROD> ALTER INDEX PROD_SUPPLIER_IX REBUILD;

Index altered.

Elapsed: 00:00:00.01
15:18:20 SYSTEM@PROD> ALTER INDEX WAREHOUSES_PK REBUILD;

Index altered.

Elapsed: 00:00:00.16
15:18:20 SYSTEM@PROD> ALTER INDEX WHS_LOCATION_IX REBUILD;

Index altered.

Elapsed: 00:00:00.01
15:18:20 SYSTEM@PROD>
15:18:24 SYSTEM@PROD> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('&2', OPTIONS=>'GATHER', CASCADE=>TRUE, METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY', NO_INVALIDATE=>FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:02:50.37
15:21:15 SYSTEM@PROD>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.