Имеем:1. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
2. Меня в качестве совсем нулевого OraDBA и PL/SQL программиста, который умеет чуть-чуть пользоваться Enterprise Manager и SQL Developer для администрирования базы данных.
Задача:Дефрагментировать и шринкануть табличное пространство в боевой базе данных.
Выполненные действия и результаты:1. Написал (содрал с интернета) скрипт:
--Execute this in Oracle SQL Developer
-- Enable server information output
SET SERVEROUTPUT ON;
-- Create tablespaces
CREATE BIGFILE TABLESPACE "DELETEME" DATAFILE '/storage1/oracle/oradata112/EPMTEST/deleteme01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "DELETEME2" DATAFILE '/storage1/oracle/oradata112/EPMTEST/deleteme2_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- Create users
CREATE USER DELETEME IDENTIFIED BY deleteme DEFAULT TABLESPACE "DELETEME" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO DELETEME;
GRANT CREATE TRIGGER TO DELETEME;
GRANT CREATE INDEXTYPE TO DELETEME;
GRANT CREATE VIEW TO DELETEME;
GRANT CREATE SESSION TO DELETEME;
GRANT CREATE TABLE TO DELETEME;
GRANT CREATE TYPE TO DELETEME;
GRANT CREATE ANY SYNONYM TO DELETEME;
GRANT DROP ANY SYNONYM TO DELETEME;
GRANT CREATE SEQUENCE TO DELETEME;
GRANT UNLIMITED TABLESPACE TO DELETEME;
GRANT CREATE CLUSTER TO DELETEME;
GRANT CREATE PROCEDURE TO DELETEME;
CREATE USER DELETEME2 IDENTIFIED BY deleteme2 DEFAULT TABLESPACE "DELETEME2" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO DELETEME2;
GRANT CREATE TRIGGER TO DELETEME2;
GRANT CREATE INDEXTYPE TO DELETEME2;
GRANT CREATE VIEW TO DELETEME2;
GRANT CREATE SESSION TO DELETEME2;
GRANT CREATE TABLE TO DELETEME2;
GRANT CREATE TYPE TO DELETEME2;
GRANT CREATE ANY SYNONYM TO DELETEME2;
GRANT DROP ANY SYNONYM TO DELETEME2;
GRANT CREATE SEQUENCE TO DELETEME2;
GRANT UNLIMITED TABLESPACE TO DELETEME2;
GRANT CREATE CLUSTER TO DELETEME2;
GRANT CREATE PROCEDURE TO DELETEME2;
-- Create TEST1, TEST2, TEST3 and TEST4 tables with indexes
--CREATE TABLE DELETEME.TEST1 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40), CONSTRAINT TEST1_C_ID_PK PRIMARY KEY (COLUMN1));
--CREATE TABLE DELETEME.TEST2 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40), CONSTRAINT TEST2_C_ID_PK PRIMARY KEY (COLUMN1));
--CREATE TABLE DELETEME.TEST3 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40), CONSTRAINT TEST3_C_ID_PK PRIMARY KEY (COLUMN1));
--CREATE TABLE DELETEME.TEST4 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40), CONSTRAINT TEST4_C_ID_PK PRIMARY KEY (COLUMN1));
-- ...or without indexes
CREATE TABLE DELETEME.TEST1 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40));
CREATE TABLE DELETEME.TEST2 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40));
CREATE TABLE DELETEME.TEST3 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40));
CREATE TABLE DELETEME.TEST4 (COLUMN1 NVARCHAR2(7), COLUMN2 NVARCHAR2(40));
-- Fill all tables with fake data
INSERT INTO DELETEME.TEST1 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
INSERT INTO DELETEME.TEST2 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
INSERT INTO DELETEME.TEST3 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
INSERT INTO DELETEME.TEST4 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
-- Delete tables TEST2 and TEST3. This action creates a 'hole' in tablespace for defragment and shrink experiments
DROP TABLE DELETEME.TEST2 CASCADE CONSTRAINTS PURGE;
DROP TABLE DELETEME.TEST3 CASCADE CONSTRAINTS PURGE;
, при выполнении которого в тестовой базе данных создается ситуация, минимально-схематично имитирующая ситуацию в боевой базе. Для иллюстрации ситуации после выполнения последней команды скрипта приложил скриншот содержимого ТП с дыркой свободного пространства в середине.
2. Далее я пытался различными вариантами команды ALTER TABLE и ALTER TABLESPACE дефрагментировать табличное пространство, чтобы заставить систему переместить таблицу, расположенную в хвосте ТП, в ее начало, чтобы затем обрезать в ТП хвост свободного пространства. Были выполнены следующие варианты команд:
ALTER TABLE DELETEME.TEST1 ENABLE ROW MOVEMENT;
ALTER TABLE DELETEME.TEST4 ENABLE ROW MOVEMENT;
ALTER TABLE DELETEME.TEST1 SHRINK SPACE COMPACT;
ALTER TABLE DELETEME.TEST4 SHRINK SPACE COMPACT;
ALTER TABLE DELETEME.TEST1 ENABLE ROW MOVEMENT;
ALTER TABLE DELETEME.TEST4 ENABLE ROW MOVEMENT;
ALTER TABLE DELETEME.TEST1 SHRINK SPACE;
ALTER TABLE DELETEME.TEST4 SHRINK SPACE;
В принципе, TEST1 можно было бы и не трогать - она и так в начале ТП находится, хочется добиться именно перемещения в начало ТП таблицы TEST4.