There are always questions from folks asking how to retrieve disk space from database datafiles that have grown too large usually by accident.
This can be a time consuming task, usually involving using import and export and can lead to large headaches. For the really large jobs, that is probably still the best way to go. For the smaller jobs I use the following method.
I put it together into a hopefully complete test. Sorry for the use of a Windoze DB as a test bed. But VM on my laptop took a nose dive the other day and I haven't been able to recover my Suse VM guest from the bowels of WindowsXP.
SQL> SET TIMING ON
SQL> COLUMN MEG FORMAT 999,999,999
SQL> COLUMN SEGMENT_NAME FORMAT A25 TRUNCATE
SQL> COLUMN FILE_NAME FORMAT A30 TRUNCATE
SQL> COLUMN TABLESPACE_NAME FORMAT A25 TRUNCATE
SQL> SET LINES 130
SQL> set echo on
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> DROP TABLESPACE SIZETESTING1 INCLUDING CONTENTS;
Tablespace dropped.
Elapsed: 00:00:02.15
SQL> CREATE TABLESPACE SIZETESTING1 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF'
2 SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M SEGMENT SPACE MANAGEMENT AUTO AUTOALLOCATE;
Tablespace created.
Elapsed: 00:00:04.34
SQL> CREATE TABLE THEBIGONE (THEDATA VARCHAR2(3000)) TABLESPACE SIZETESTING1;
Table created.
Elapsed: 00:00:00.03
SQL> CREATE TABLE THESMALLONE (THEDATA VARCHAR2(3000)) TABLESPACE SIZETESTING1;
Table created.
Elapsed: 00:00:00.00
I just want to put some data into the big table then insert some data into the small table.
SQL>
SQL>
SQL> DECLARE
2 L_THERECORD VARCHAR2(3000);
3
4 BEGIN
5
6 L_THERECORD := DBMS_RANDOM.STRING('U',2999);
7 DBMS_OUTPUT.PUT_LINE(L_THERECORD);
8 FOR Y IN 1..3 LOOP
9
10 FOR X IN 1..10000 LOOP
11 INSERT INTO THEBIGONE (THEDATA) VALUES (L_THERECORD);
12 END LOOP;
13 COMMIT;
14 END LOOP;
15 COMMIT;
16 END;
17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.63
SQL>
SQL> SELECT COUNT(1) FROM THEBIGONE;
COUNT(1)
----------
30000
Elapsed: 00:00:03.62
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;
SEGMENT_NAME MEG
------------------------- ------------
THESMALLONE 0
THEBIGONE 120
Elapsed: 00:00:00.14
SQL>
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;
49787 rows created.
Elapsed: 00:00:03.39
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL>
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;
SEGMENT_NAME MEG
------------------------- ------------
THESMALLONE 2
THEBIGONE 120
Elapsed: 00:00:00.03
SQL> SELECT FILE_NAME,BYTES/1024/1024 MEG FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SIZETESTING1';
FILE_NAME MEG
------------------------------ ------------
C:\ORACLE\PRODUCT\10.2.0\ORADA 130
Elapsed: 00:00:00.05
Now, lets fill the big table up so it blows the storage parameters.
SQL>
SQL> DECLARE
2 L_THERECORD VARCHAR2(3000);
3
4 BEGIN
5
6 L_THERECORD := DBMS_RANDOM.STRING('U',2999);
7 DBMS_OUTPUT.PUT_LINE(L_THERECORD);
8 FOR Y IN 1..1000 LOOP
9
10 FOR X IN 1..1000 LOOP
11 INSERT INTO THEBIGONE (THEDATA) VALUES (L_THERECORD);
12 END LOOP;
13 COMMIT;
14 END LOOP;
15 COMMIT;
16 END;
17 /
DECLARE
*
ERROR at line 1:
ORA-01653: unable to extend table SYSTEM.THEBIGONE by 1024 in tablespace SIZETESTING1
ORA-06512: at line 11
Elapsed: 00:00:08.76
SQL>
SQL> SELECT COUNT(1) FROM THEBIGONE;
COUNT(1)
----------
48000
Elapsed: 00:00:04.35
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;
SEGMENT_NAME MEG
------------------------- ------------
THESMALLONE 2
THEBIGONE 192
Elapsed: 00:00:00.26
SQL> SELECT FILE_NAME,BYTES/1024/1024 MEG FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SIZETESTING1';
FILE_NAME MEG
------------------------------ ------------
C:\ORACLE\PRODUCT\10.2.0\ORADA 200
Elapsed: 00:00:00.03
Lets get rid of the big table and add some more rows to the small table.
SQL> TRUNCATE TABLE THEBIGONE;
Table truncated.
Elapsed: 00:00:03.16
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;
49787 rows created.
Elapsed: 00:00:03.13
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;
49787 rows created.
Elapsed: 00:00:03.11
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.02
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;
49787 rows created.
Elapsed: 00:00:03.19
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.02
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;
SEGMENT_NAME MEG
------------------------- ------------
THESMALLONE 7
THEBIGONE 0
Elapsed: 00:00:00.03
SQL>
SQL> SELECT * FROM( SELECT file_name, ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) smallest, ceil( blocks*C.VALUE/1024/1024) currsize,
2 ceil( blocks*C.VALUE/1024/1024) - ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) savings
3 ,a.autoextensible ,a.maxbytes from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm
4 from dba_extents group by file_id ) b ,(select value from v$parameter where name = 'db_block_size') C
5 where a.file_id = b.file_id(+) AND a.file_name='C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF'
6 ) WHERE 1=1 ;
FILE_NAME SMALLEST CURRSIZE SAVINGS AUT MAXBYTES
------------------------------ ---------- ---------- ---------- --- ----------
C:\ORACLE\PRODUCT\10.2.0\ORADA 123 200 77 YES 209715200
Elapsed: 00:00:16.80
Our smallest we can shrink the datafile is down to 123m due to the small table having segments. So lets set up a tablespace to move the small table too while we clean up.
SQL>
SQL>
SQL> DROP TABLESPACE HOLDING INCLUDING CONTENTS;
Tablespace dropped.
Elapsed: 00:00:00.88
SQL> CREATE TABLESPACE HOLDING DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\HOLDING.DBF'
2 SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M SEGMENT SPACE MANAGEMENT AUTO AUTOALLOCATE;
Tablespace created.
Elapsed: 00:00:01.10
And lets move it.
SQL>
SQL> ALTER TABLE THESMALLONE MOVE TABLESPACE HOLDING;
Table altered.
Elapsed: 00:00:00.67
SQL>
SQL> SELECT * FROM( SELECT file_name, ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) smallest, ceil( blocks*C.VALUE/1024/1024) currsize,
2 ceil( blocks*C.VALUE/1024/1024) - ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) savings
3 ,a.autoextensible ,a.maxbytes from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm
4 from dba_extents group by file_id ) b ,(select value from v$parameter where name = 'db_block_size') C
5 where a.file_id = b.file_id(+) AND a.file_name='C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF'
6 ) WHERE 1=1 ;
FILE_NAME SMALLEST CURRSIZE SAVINGS AUT MAXBYTES
------------------------------ ---------- ---------- ---------- --- ----------
C:\ORACLE\PRODUCT\10.2.0\ORADA 1 200 199 YES 209715200
Elapsed: 00:00:16.68
There we go, shrink it down to 1M. So, lets do it.
SQL> ALTER database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF' resize 1m;
Database altered.
Elapsed: 00:00:00.49
Move the table back .
SQL> ALTER TABLE THESMALLONE MOVE TABLESPACE SIZETESTING1;
Table altered.
Elapsed: 00:00:01.29
SQL> SELECT TABLESPACE_NAME,SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY TABLESPACE_NAME,SEGMENT_NAME;
TABLESPACE_NAME SEGMENT_NAME MEG
------------------------- ------------------------- ------------
SIZETESTING1 THEBIGONE 0
SIZETESTING1 THESMALLONE 7
Elapsed: 00:00:00.03
SQL> SELECT FILE_NAME,BYTES/1024/1024 MEG FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SIZETESTING1';
FILE_NAME MEG
------------------------------ ----------
C:\ORACLE\PRODUCT\10.2.0\ORADA 11
Elapsed: 00:00:00.03
SQL> SPOOL OFF;
Lets take a look at what it looks like on disk
C:\oracle\product\10.2.0\oradata\LAPTOP>DIR SIZE*
Volume in drive C has no label.
Directory of C:\oracle\product\10.2.0\oradata\LAPTOP
08/05/2006 02:46 PM 11,542,528 SIZETESTING1.DBF
1 File(s) 11,542,528 bytes
0 Dir(s) 85,625,917,440 bytes free
This works well and can be expanded to cover more tables similar to "THESMALLONE". Be careful moving tables online and check the documents first, and don't take my word for it. Test Test Test.
1 comment:
Thank you so much!
This helped me a lot :)
Cheers,
Faisal.
Post a Comment