Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

10.15. Oracle 表空間

10.15.1. 查詢空閒表空間

select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;
		
		
SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/orcl/users01.dbf
/opt/oracle/oradata/orcl/undotbs01.dbf
/opt/oracle/oradata/orcl/sysaux01.dbf
/opt/oracle/oradata/orcl/system01.dbf
/opt/oracle/oradata/orcl/example01.dbf
/opt/oracle/oradata/orcl/neo.dbf

6 rows selected.		
		
		

10.15.2. 創建表空間

create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 8M
autoextend on
next 5M
maxsize 10M;
		

maxsize unlimited 是大小不受限制

create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize unlimited
		

unform表示區的大小相同,預設為1M

create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local uniform;
		

unform size 500K表示區的大小相同,為500K

create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local uniform size 500K;
		

autoallocate 表示區的大小由隨表的大小自動動態改變

create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local autoallocate;
		

temporary 創建字典管理臨時表空間

create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
temporary;
		

例 10.2. 創建表空間實例

			
SQL> create tablespace ts_b01_def datafile '/opt/oracle/oradata/orcl/ts_b01_def.dbf' size 100m autoextend on;

Tablespace created.

SQL> create tablespace ts_b01_idx datafile '/opt/oracle/oradata/orcl/ts_b01_idx.dbf' size 100m autoextend on;

Tablespace created.			
			
			

10.15.2.1. 臨時表空間

創建臨時表空間,語句中的datafile都換為tempfile

create temporary tablespace test
tempfile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
			

10.15.3. 更改表空間屬性

更改自動擴展屬性

alter database datafile
    '/opt/app/oracle/oradata/test.dbf',
    '/opt/app/oracle/oradata/test01.dbf'
    '/opt/app/oracle/oradata/test02.dbf'
    autoextend off;
		

10.15.3.1. 修改表空間大小

先查詢數據檔案名稱、大小和路徑的信息,語句如下:

select tablespace_name,file_id,bytes,file_name from dba_data_files;

增加表空間,修改檔案大小語句如下

alter database datafile '需要增加的數據檔案路徑,即上面查詢出來的路徑 ' resize 800M;
			

10.15.4. 刪除表空間

drop tablespace "空間名" including contents and datafiles

drop tablespace test including contents and datafiles