Oracle数据库查询表空间使用情况及创建表空间

--查询表空间使用状况

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;

--创建表空间

示例一：

CREATE TABLESPACE TS_HIS3BZ

DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\HIS401.ORA' SIZE 100M AUTOEXTEND ON NEXT 16M MAXSIZE 10G,

'D:\APP\ADMINISTRATOR\ORADATA\ORCL\HIS402.ORA' SIZE 100M AUTOEXTEND ON NEXT 16M MAXSIZE 20G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

示例二：

CREATE TABLESPACE TS_EMR

DATAFILE 'G:\Oracle\data\EMR01.ORA' SIZE 100M AUTOEXTEND ON NEXT 16M MAXSIZE 20G,

'G:\Oracle\data\EASYHRIP02.ORA' SIZE 100M AUTOEXTEND ON NEXT 16M MAXSIZE 20G,

'G:\Oracle\data\EASYHRIP03.ORA' SIZE 100M AUTOEXTEND ON NEXT 16M MAXSIZE 20G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

示例三：

CREATE TABLESPACE TS_EMR

DATAFILE 'G:\Oracle\data\EMR01.ORA' SIZE 100M AUTOEXTEND ON NEXT 512M MAXSIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;