把一个目录下的datafile都改为不自动扩展

--------------------------------------------
--以下为把一个目录都改为不能自动扩展的步骤
--------------------------------------------
--如果把一个目录中所有datafile都不能自动扩展，需要加datafile的表空间找出所有表空间的datafile都不能自动扩展
select distinct tablespace_name from dba_data_files
minus
select distinct tablespace_name from dba_data_files
where autoextensible = 'YES' and file_name not like '/u03%'
and bytes <= 26843545600;

select tablespace_name, round(sum(bytes/1024/1024/1024)) from dba_free_space group by tablespace_name order by 2 desc;

--表空间加datafile
ALTER TABLESPACE VPOSATF ADD DATAFILE '/u06/oradata/billdb/vposatf_2012073101.dbf' SIZE 1000M 
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

--一般datafile(1G)
select 'ALTER TABLESPACE '|| tablespace_name ||' ADD DATAFILE ''/u09/oradata/billdb/' || tablespace_name || '_2013071801.dbf'' SIZE 1000M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;'
 from dba_tablespaces
where tablespace_name in ('ACCOUNTING','DDP','UNDOTBS1')
and tablespace_name not in ('DUMPOFDBA','DUMPOFDBA_IDX','PARTITION_INDX3','MEMBERINFO','MSGATEWAY','IVR','MARKETING','SSO','GL','MAILBILL','ORDERQUERY','POPUP','PRODUCT','EBPP_IDX','INTRAPORTAL','PARTITION_INDX2','BGW_IDX','LOGNEW','METADATA','BGW','FX','MEMBERAPP','DDP_IDX','PSE_IDX','SYSAUX','MASPOS','MOBILEITEMLINE','PDCQUEUE','IBR','MEMBERAPP_IDX','DOWNTXN_IDX','ETAPI','OAS_IDX','AUTHORIZATION','SSO_IDX','SEASHELLGWPREPAY','VPOSATF','VPOS','OAS','ACCT_INFO','FUND','DEAL_OLD_2007','CAV','GATEWAYORDER_IDX','CARDBIN','DEAL_OLD_200606','DEAL_OLD_200609','DEAL_OLD_200709','FUNDIN','DEAL_OLD_200703','DEAL_OLD_2006','USERS','PKIUSER','DEAL_OLD_200706','BDB','AUTHENTICATION','FUNDINGSRC','COS','CAV_IDX','ACCT','ATFA','SEASHELL','PAYMENTORDER_IDX','SZX_INDEX','DIRECTPAY','EBPP','ACCOUNTING','DEAL_IDX','ENTRY_IDX','DDP','TBL_03','FUNDOUT_IDX');
--小的datafile(100M)
select 'ALTER TABLESPACE '|| tablespace_name ||' ADD DATAFILE ''/u09/oradata/billdb/' || tablespace_name || '_2013071801.dbf'' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;'
 from dba_tablespaces
where tablespace_name in ('ACCT','ACCT_DIARY','ACCT_INFO','DEAL','DOWNTXN','EBPP','FUNDIN','FUNDOUT','GATEWAYORDER','MONITOR','PARTITION_INDX1','SYSAUX','SYSTEM','TBL_08','TBL_09','TBL_10','TBL_12','USERS')
and tablespace_name in ('DUMPOFDBA','DUMPOFDBA_IDX','PARTITION_INDX3','MEMBERINFO','MSGATEWAY','IVR','MARKETING','SSO','GL','MAILBILL','ORDERQUERY','POPUP','PRODUCT','EBPP_IDX','INTRAPORTAL','PARTITION_INDX2','BGW_IDX','LOGNEW','METADATA','BGW','FX','MEMBERAPP','DDP_IDX','PSE_IDX','SYSAUX','MASPOS','MOBILEITEMLINE','PDCQUEUE','IBR','MEMBERAPP_IDX','DOWNTXN_IDX','ETAPI','OAS_IDX','AUTHORIZATION','SSO_IDX','SEASHELLGWPREPAY','VPOSATF','VPOS','OAS','ACCT_INFO','FUND','DEAL_OLD_2007','CAV','GATEWAYORDER_IDX','CARDBIN','DEAL_OLD_200606','DEAL_OLD_200609','DEAL_OLD_200709','FUNDIN','DEAL_OLD_200703','DEAL_OLD_2006','USERS','PKIUSER','DEAL_OLD_200706','BDB','AUTHENTICATION','FUNDINGSRC','COS','CAV_IDX','ACCT','ATFA','SEASHELL','PAYMENTORDER_IDX','SZX_INDEX','DIRECTPAY','EBPP','ACCOUNTING','DEAL_IDX','ENTRY_IDX','DDP','TBL_03','FUNDOUT_IDX');


--检查关闭一个目录的autoextend后各表空间的使用率（不要小于90%）
select t1.tablespace_name, (t1.ts_size - NVL(t2.ts_free, 0)) used,
       t1.ts_size, t3.ts_max_size, round((t1.ts_size - NVL(t2.ts_free, 0))/ t1.ts_size * 100) used_percent,
       round((t1.ts_size - NVL(t2.ts_free, 0))/ t3.ts_max_size * 100) used_max_percent
  from (select tablespace_name, round(sum(bytes / 1024 / 1024)) ts_size
           from dba_data_files
          group by tablespace_name) t1,
       (select tablespace_name, round(sum(bytes / 1024 / 1024)) ts_free
           from dba_free_space
          group by tablespace_name) t2,
       (select tablespace_name,
                round(sum(case autoextensible
                             when 'YES' then
                              32 * 1024
                             else
                              bytes / 1024 / 1024
                           end)) ts_max_size
           from dba_data_files
          group by tablespace_name) t3
 where t1.tablespace_name = t2.tablespace_name(+)
   and t1.tablespace_name = t3.tablespace_name
   order by 6 desc;

--关闭一个目录中所有datafile自动扩展状态
select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND OFF;'
from dba_data_files where file_name like '/u03%' and autoextensible = 'YES';