Saturday, August 05, 2006

Retrieving disk space

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.


3 comments:

Faisal said...

Thank you so much!

This helped me a lot :)

Cheers,
Faisal.

uiyui said...

Youth is not wow gold a time of life;world of warcraft gold it is a state of mind; cheap wow gold it is not a Maple Story Accounts matter of rosy cheeks, red lips and supple knees;mesos it is a matter of the will, a quality of the imagination,wow gold kaufen a vigor of the emotions; it is the freshness wow geld of the deep springs of life.maple story mesos Youth means a tempera-mental predominance of courage over timidity, of the appetite for adventure over the love of ease. This often exists in a man of 60 more than a boy of 20.wow gold farmen Nobody grows old merely by a number of years.maple story money We grow old by deserting our ideals.ms mesos Years may wrinkle the skin, but to give up enthusiasm wrinkles the soul. Worry, fear, self-distrust bows the heart and turns the spring back to dust. Whether 60 or 16, there is in every human being’wow powerleveling s heart the lure of wonder, the unfailing childlike appetite of what’s maple story money next and the joy of the game of living.powerlevel In the center of your heart and my heart there is a wireless station: so long as it receives messages maplestory powerleveling of beauty, hope, cheer,world of warcraft power leveling courage and power from men and from the Infinite, so long are you young. When the aerials are down, and your spirit is covered with snows of cynicism and the ice of pessimism, then you are grown old, even at 20, but as long as your aerials are up, to catch waves of optimism, there is hope you may die young at 80!

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa