The only way to completely get rid of a datafile from a tablespace is to
rebuild the tablespace with needed datafiles. Export/import of all of its
contents is one way to do it.
> -----Original Message-----
> From: Guang Mei [SMTP:zlmei_at_hotmail.com]
> Sent: Wednesday, April 26, 2000 1:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: How to reduce number of oracle datafiles?
>
> Hi:
>
> We have an oracle 8.1.5 db on Unix that I would like to reduce the number
> of
> oracle datafiles in order to free some disk space. After cleaning database
>
> up a little bit (deleted some old schemas), I have the following:
>
>
> SQLWKS> select substr(rpad(A.FILE_NAME ,50),1,45) FileName,
> 2> decode(x.online$,1,x.name,
> 3> substr(rpad(x.name,14),1,14)||' OFF') Tablespace,
> 4> round((f.blocks*4096)/(1024*1024)) Total,
> 5> round(sum(s.length*4096)/(1024*1024),1) Used_Mg,
> 6> round( sum(s.length*4096)/(1024*1024)
> 7> / ((f.blocks*4096)/(1024*1024)) * 100, 1) Use_Pct
> 8> from sys.dba_data_files A, sys.uet$ s, sys.file$ f, sys.ts$ X
> 9> where x.ts# = f.ts#
> 10> and x.online$ in (1,2) /* Online
> !!
> */
> 11> and f.status$ = 2 /* Online
> !!
> */
> 12> and f.ts# = s.ts# (+)
> 13> and f.file# = s.file# (+)
> 14> and f.file# = a.file_id
> 15> group by x.name, x.online$, f.blocks, A.file_name
> 16>
> FILENAME TABLESPACE
>
> TOTAL USED_MG USE_PCT
> ---------------------------------------------
> ------------------------------
> ---------- ---------- ----------
> /u01/app/oracle/oradata/areDev00/drsys01.dbf DRSYS
>
> 80 1 1.3
> /u01/app/oracle/oradata/areDev00/indx01.dbf INDX
>
> 50 27.9 55.7
> /u01/app/oracle/oradata/areDev00/oemrep01.dbf OEM_REPOSITORY
>
> 5
> /u01/app/oracle/oradata/areDev00/rbs01.dbf RBS
>
> 43 43.2 100
> /u01/app/oracle/oradata/areDev00/rbs02.dbf RBS
>
> 50 39.9 79.9
> /u01/app/oracle/oradata/areDev00/system01.dbf SYSTEM
>
> 200 120.7 60.4
> /u01/app/oracle/oradata/areDev00/temp01.dbf TEMP
>
> 100 1 1
> /u01/app/oracle/oradata/areDev00/users01.dbf USERS
>
> 500 299.8 60
> /u01/app/oracle/oradata/areDev00/users02.dbf USERS
>
> 500 331.5 66.3
> /u01/app/oracle/oradata/areDev00/users03.dbf USERS
>
> 500 42.8 8.6
> /u01/app/oracle/oradata/areDev00/users04.dbf USERS
>
> 500 363.9 72.8
> 11 rows selected.
>
>
> I would like to reduce the "user01.dbf" ... "user04.dbf" to three files
> instead of four now. Do I have to "export" all the existing schemas and
> "re-import" them so there will fill up "user01.dbf", then "user02.dbf",
> then
> "user03.dbf"? Any other way to re-arrange data so that I can free
> "user04.dbf"?
>
> Thanks.
>
> Guang
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Received on Wed Apr 26 2000 - 15:22:15 CDT
