ORACLE資料表空間操作執行個體

標籤：recover   建立資料表空間   oracle資料表空間   write   查詢   lin   pac   dbf   user   

    本文主要介紹oracle資料表空間常見的操作執行個體，包括建立、查詢、增加、刪除、修改。資料表空間和資料檔案常用的資料字典和動態效能檢視包括v$dbfile、v$datafile、dba_segments、user_segments、dba_data_files、v$tablespace、dba_tablespaces、user_tablespaces。

建立資料表空間

1、建立資料資料表空間

CREATE TABLESPACE test DATAFILE ‘/opt/oracle/oradata/test.dbf‘ SIZE 1024M;

2、建立undo資料表空間

CREATE UNDO TABLESPACE undotbs DATAFILE ‘/opt/oracle/oradata/undotbs.dbf‘ SIZE 1024M;

3、建立暫存資料表空間

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘/opt/oracle/oradata/temp.dbf‘ SIZE 1024M;

查詢資料表空間

1、查詢oracle系統使用者的預設資料表空間和暫存資料表空間

select default_tablespace,temporary_tablespace from dba_users where username = ‘USER‘;

2、查看系統當前預設的資料資料表空間和暫存資料表空間

select * from dba_properties where property_name like ‘DEFAULT%‘ ; #查詢alter database default  tablespace DATATBS2; #修改預設資料資料表空間alter database default temporary tablespace TEMP2; #修改預設暫存資料表空間

3、查看所有資料表空間、資料檔案及資料表空間的資料檔案

select * from v$datafile; #查看資料檔案select * from v$tempfile; #查看臨時檔案select * from v$tablespace; #查詢所有資料表空間select file_name,tablespace_name from dba_data_files; #查看錶空間對應的資料檔案select sum(d.bytes)/1024/1024/1024 ||‘G‘ as total_bytes,d.tablespace_name from dba_datafiles d where d.tablespace_name = ‘UNDOTBS‘ group by d.tablespace_name; #查看錶空間檔案大小select bytes/1024/1024/1024 as "Size(G)",name from v$tempfile order by bytes; #查看臨時檔案大小

4、查看使用者表佔用空間大小

#USER_SEGMENTSSELECT SEGMENT_NAME TABLE_NAME,SUM(BYTES)/(1024*1024)   "TABLE_SIZE[MB]"FROM USER_SEGMENTSWHERE  SEGMENT_TYPE=‘TABLE‘ AND SEGMENT_NAME=‘TABLE_NAME‘GROUP BY SEGMENT_NAME; #DBA_SEGMENTS,shell編程中常用SELECT SEGMENT_NAME  TABLE_NAME,SUM(BYTES)/(1024*1024)  "TABLE_SIZE[MB]"FROM DBA_SEGMENTSWHERE  SEGMENT_TYPE=‘TABLE‘ AND SEGMENT_NAME=‘TABLE_NAME‘GROUP BY SEGMENT_NAME;

5、查詢單張表佔用空間大小

select segment_name,bytes from dba_segments where segment_name = ‘TABLE_NAME‘ and owner = ‘USER‘;

6、查詢所有使用者表佔用空間的前十名

select * from (select segment_name,bytes from dba_segments where owner = ‘USER‘ order by bytes desc ) where rownum <= 10;

7、查詢資料表空間使用方式

select total.tablespace_name, round(total.MB, 2) as Total_MB, round(total.MB - free.MB, 2) as Used_MB, round((1 - free.MB / total.MB) * 100, 2) || ‘%‘ as Used_Pct from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name;

8、查詢資料表空間總大小

select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name; 

9、查詢資料表空間剩餘大小

select tablespace_name, count(*) as extends, round(sum(bytes) / 1024 / 1024, 2) as MB, sum(blocks) as blocks from dba_free_space group by tablespace_name; 

修改資料表空間狀態

 

SELECT TABLESPACE_NAME,BLOCK_SIEZE,STATUS FROM DBA_TABLESPACE; #查看錶空間狀態SLEECT NAME,BLOKC_SIZE,STATUS FROM V$DATAFILE; #查看資料檔案狀態ALTER TABLESPACE DATATBS OFFLINE; #使資料表空間離線 ALTER TABLESPACE DATATBS OFFLINE FOR RECOVER;  #如果是意外刪除了資料檔案，則必須帶有RECOVER選項ALTER TABLESPACE DATATBS ONLINE; #使資料表空間聯機ALTER DATABASE DATAFILE 3 OFFLINE; #使資料檔案離線 ALTER DATABASE DATAFILE 3 ONLINE; #使資料檔案聯機 ALTER TABLESPACE DATATBS READ WRITE; #使資料表空間可讀寫

 

擴充資料表空間

SQL> alter tablespace tablespacename add datafile ‘/home/oracle/add_tablespacename.dbf‘ size 1024M; #增加資料檔案方式，不允許自動成長SQL> alter tablespace tablespacename add datafile ‘/home/oracle/add_tablespacename.dbf‘ size 1024M autoextend on next 5M maxsize 2048M; #增加資料檔案方式且允許自動成長SQL> alter database datafile ‘/home/oracle/tablespace.dbf‘ autoextend on next 5M maxsize 2048M; #允許已存在的資料檔案自動擴充SQL> alter database datafile ‘/home/oracle/tablespace.dbf‘ resize 2048M; # 改變當前資料檔案大小#調整暫存資料表空間檔案SQL> alter tablespace temp add tempfile ‘/home/oracle/temp2.dbf‘ size 2048M; #擴充暫存資料表空間大小，預設autoextend offSQL> alter tablespace temp add tempfile ‘/home/oracle/temp2.dbf‘ size 1024M antoextend on next 128M maxsize 2048M; #擴充暫存資料表空間大小SQL> alter database tempfile ‘/home/oracle/temp.dbf‘ resize 2048M;

 刪除資料表空間

DROP TABLESPACE DATATBS INCLUDING CONTENTS AND DATAFILES; 

說明：including contents 字句用來刪除段，and datafiles 字句用來刪除資料檔案，cascade constraints 字句用來刪除所有的參考完整性約束

相關執行個體

1、移動資料表空間資料檔案

SQL> select tablespace_name,file_name from dba_data_files where file_name=‘/opt/oracle/oradata/datatbs.dbf‘; SQL> alter tablespace datatbs offline; SQL> host move /opt/oracle/oradata/datatbs.dbf /home/oracle/datatbs.dbf; SQL> alter tablespace datatbs rename datafile ‘/opt/oracle/oradata/datatbs.dbf‘ to ‘/home/oracle/datatbs.dbf‘; SQL> alter tablespace datatbs online; 

 

ORACLE資料表空間操作執行個體