The database, user, tablespace and tables listed in this issue are an simulation of real situation which I have at working environment. I created user/tablespace/tables/indexes, after I deleted some objects (2 tables from 4). After this step tablespace has hole in it's space and I want to defragment and shrink it. But if I try to 'Reorganize' my tablespace in Oracle Database Enterprise Manager Database Control, the system prints me the error 'Tablespace YYYY contains object table YYYY.XXXX. Reorganization of object tables is not supported. Reorganization of this tablespace is not supported' on ALL objects of this tablespace.
So my question is 'How to shrink tablespace in Oracle Database'?
The simulating script listed below:
--Execute this in Oracle SQL Developer
-- Create TEST1, TEST2, TEST3 and TEST4 tables
CREATE TABLE TEST1 (Column1 NVARCHAR2(7), Column2 NVARCHAR2(40), CONSTRAINT TEST1_C_ID_PK PRIMARY KEY (Column1));
CREATE TABLE TEST2 (Column1 NVARCHAR2(7), Column2 NVARCHAR2(40), CONSTRAINT TEST2_C_ID_PK PRIMARY KEY (Column1));
CREATE TABLE TEST3 (Column1 NVARCHAR2(7), Column2 NVARCHAR2(40), CONSTRAINT TEST3_C_ID_PK PRIMARY KEY (Column1));
CREATE TABLE TEST4 (Column1 NVARCHAR2(7), Column2 NVARCHAR2(40), CONSTRAINT TEST4_C_ID_PK PRIMARY KEY (Column1));
-- Fill all tables with fake data
INSERT INTO TEST1 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
INSERT INTO TEST2 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
INSERT INTO TEST3 SELECT ROWNUM, 'Name'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100000;
INSERT INTO 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 TEST2 CASCADE CONSTRAINTS PURGE;
DROP TABLE TEST3 CASCADE CONSTRAINTS PURGE;
Please see attached screenshots.
P.S. I am not OraDBA, but I need to resolve this problem. I tried to create tables without indexes, but I have got same error.
We have this version of Oracle Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
NLSRTL Version 11.2.0.3.0 - Production