oracle表空間日常操作管理

建立表空間
　　CREATE TABLESPACE data01
　　DATAFILE ‘/oracle/oradata/db/DATA01.dbf‘ SIZE 500M
　　UNIFORM. SIZE 128k; #指定區尺寸為128k,如不指定，區尺寸預設為64k
　　刪除表空間
　　DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
　　一、建立表空間
　　CREATE TABLESPACE data01
　　DATAFILE ‘/oracle/oradata/db/DATA01.dbf‘ SIZE 500M
　　UNIFORM. SIZE 128k; #指定區尺寸為128k,如不指定，區尺寸預設為64k
　　二、建立UNDO表空間
　　CREATE UNDO TABLESPACE UNDOTBS02
　　DATAFILE ‘/oracle/oradata/db/UNDOTBS02.dbf‘ SIZE 50M
　　#注意：在OPEN狀態下某些時刻只能用一個UNDO表空間，如果要用新建的表空間，必須切換到該表空間:
　　ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
　　三、建立臨時表空間
　　CREATE TEMPORARY TABLESPACE temp_data
　　TEMPFILE ‘/oracle/oradata/db/TEMP_DATA.dbf‘ SIZE 50M
　　四、改變表空間狀態
　　1.使表空間離線
　　ALTER TABLESPACE game OFFLINE;
　　如果是意外刪除了資料檔案，則必須帶有RECOVER選項
　　ALTER TABLESPACE game OFFLINE FOR RECOVER;
　　2.使表空間聯機
　　ALTER TABLESPACE game ONLINE;
　　3.使資料檔案離線
　　ALTER DATABASE DATAFILE 3 OFFLINE;
　　4.使資料檔案聯機
　　ALTER DATABASE DATAFILE 3 ONLINE;
　　5.使表空間只讀
　　ALTER TABLESPACE game READ ONLY;
　　6.使表空間可讀寫
　　ALTER TABLESPACE game READ WRITE;
　　五、刪除表空間
　　DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
　　六、擴充套件表空間
　　首先檢視錶空間的名字和所屬檔案
　　select tablespace_name, file_id, file_name,
　　round(bytes/(1024*1024),0) total_space
　　from dba_data_files
　　order by tablespace_name;
　　1.增加資料檔案
　　ALTER TABLESPACE game
　　ADD DATAFILE ‘/oracle/oradata/db/GAME02.dbf‘ SIZE 1000M;
　　2.手動增加資料檔案尺寸
　　ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf‘
　　RESIZE 4000M;
   3.刪除表空間的一個資料檔案
     alter tablspace XXXX　offline;
      alter database datafile '......' drop including datafile;
      alter tablespace xxx online;
　　4.設定資料檔案自動擴充套件
　　ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf
　　AUTOEXTEND ON NEXT 100M
　　MAXSIZE 10000M;
　　設定後檢視錶空間資訊
　　SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
　　(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
　　FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
　　WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
查詢表空間相關資訊的SQL:
1.檢視錶空間及檔案大小（含undo表空間）
set linesize 300
set pagesize 500
col file_name format a80
col TABLESPACE_NAME for a30
select tablespace_name,file_id,status,bytes/1024/1024 FileSizeM,file_name
from dba_data_files order by tablespace_name;
 
2.檢視錶空間大小及利用率
set linesize 200
set pages 2000
col TABLESPACENAME for a30
select substr(a.TABLESPACE_NAME,1,15) TablespaceName,
sum(a.bytes/1024/1024) as "Totle_size(M)",
sum(nvl(b.free_space1/1024/1024,0)) as "Free_space(M)",
sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0)) as "Used_space(M)",
round((sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0)))
*100/sum(a.bytes/1024/1024),2) as "Used_percent%" from dba_data_files a,
(select sum(nvl(bytes,0)) free_space1,file_id from dba_free_space
group by file_id) b where a.file_id = b.file_id(+) group by a.TABLESPACE_NAME
order by "Used_percent%";
3.檢視臨時表空間對應的檔名和大小
col file_name for a50
col TABLESPACE_NAME for a20
select FILE_NAME,TABLESPACE_NAME,BYTES,USER_BYTES from dba_temp_files;
 
4.檢視臨時表空間對應的檔名和大小
4-1
set lines 200
set pages 2000
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)
USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
 
4-2
set lines 200
set pages 2000
select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header ;
 
5.檢視使用者所使用的臨時表空間
select username,default_tablespace,temporary_tablespace from dba_users;
6，system表空間擴充
alter database datafile'/opt/app/oracle/oradata/eomshn/system01.dbf'autoextend on;
alter database datafile'/opt/app/oracle/oradata/eomshn/system01.dbf'resize 1024M;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29063131/viewspace-768835/，如需轉載，請註明出處，否則將追究法律責任。