How to reduce number of oracle datafiles?

From: Guang Mei <zlmei_at_hotmail.com>
Date: Wed, 26 Apr 2000 16:49:29 GMT
Message-Id: <10479.104206@fatcity.com>

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 - 11:49:29 CDT