Re: How to reduce number of oracle datafiles?

yes, export the schemas (backup the db first as well just in case), drop tablepaces and datafiles, recreate new tablespace with new storage params and import the users back in. cant think of a better way.

john

At 10:03 AM 4/26/00 -0800, Guang Mei wrote:
>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
>
>--
>Author: Guang Mei
> INET: zlmei_at_hotmail.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
Received on Wed Apr 26 2000 - 15:28:52 CDT