oracle 新增儲存自動擴充套件資料檔案流程（auto）

1、查哪些需要關自動擴充套件（先寫好，最後再執行）

datafile

set linesize 300

col file_name for a70

select file_name,tablespace_name,autoextensible from dba_data_files where autoextensible='YES' order by tablespace_name;

tempfile

set linesize 300

col file_name for a70

select file_name,tablespace_name,autoextensible from dba_temp_files where autoextensible='YES' order by tablespace_name;

關datafile的自動擴充套件

alter database datafile '/home/oracle/app/oradata/jxyth/system11.dbf' autoextend off;

關tempfile的自動擴充套件

alter database tempfile '/home/oracle/app/oradata/jxyth/system11.dbf' autoextend off;

2、檢視哪些表空間需要新建資料檔案

set linesize 300

col file_name for a70

select file_name,tablespace_name,autoextensible from dba_data_files order by tablespace_name;

set linesize 300

col file_name for a70

select file_name,tablespace_name,autoextensible from dba_temp_files order by tablespace_name;

3、新新增表空間資料檔案

datafile

alter tablespace PORATAL_HIS add datafile '/newdata/data/poratal_his84.dbf' size 1g autoextend on;

tempfile

alter tablespace TEMP add tempfile '/newdata/data/temp04.dbf' size 1g autoextend on;

4、關老資料檔案的自動擴充套件

用的1寫好的語句

5、再檢查一遍

datafile

set linesize 300

col file_name for a70

select file_name,tablespace_name,autoextensible from dba_data_files where autoextensible='YES' order by tablespace_name;

tempfile

set linesize 300

col file_name for a70

select file_name,tablespace_name,autoextensible from dba_temp_files where autoextensible='YES' order by tablespace_name;

6、檢視錶空間

col tablespace_name format a8

col status format a7

col extent_management format a5

col segment_space_management format a6

col contents format a9

select tpsname,status,mgr,maxsize,c_userd,max_used  from 

        (

SELECT  d.tablespace_name  tpsname,d.status status,

        d.segment_space_management mgr, d.contents type,

        TO_CHAR(NVL(trunc(A.maxbytes / 1024 / 1024), 0),'99G999G990') maxsize,

        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990D00') c_userd,

        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0),'990D00') max_used

        FROM sys.dba_tablespaces d,

        (SELECT tablespace_name,sum(bytes) bytes,SUM(case autoextensible when  'NO'  then BYTES when  'YES' then MAXBYTES else null end ) maxbytes   

        FROM dba_data_files  GROUP BY tablespace_name) a,

        (SELECT tablespace_name,SUM(bytes) bytes, MAX(bytes) largest_free   FROM dba_free_space   GROUP BY tablespace_name) f  

        WHERE d.tablespace_name = a.tablespace_name   AND d.tablespace_name = f.tablespace_name(+)

        )

        where max_used>0

    order by max_used desc;