ORACLE之扩展表空间
1、创建表空间
CREATE TABLESPACE TABLESPACE_NAME
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\TABLESPACE_NAME' size 1G
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE INDEX_TABLESPACE_NAME
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\INDEX_TABLESPACE_NAME' size 512M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2、删除表空间
DROP TABLESPACE TABLESPACE_NAME INCLUDING CONTENTS AND DATAFILES;
3、查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
Round(D.TOT_GROOTTE_MB /1024 , 2) "表空间大小(G)",
Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/1024, 2) "已使用空间(G)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
Round(F.TOTAL_BYTES/1024 , 2) "空闲空间(G)",
Round(F.MAX_BYTES/1024 , 2) "最大块(G)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY ( D.TOT_GROOTTE_MB - F.TOTAL_BYTES )/D.TOT_GROOTTE_MB desc
4、扩展表空间
4.1、设置表空间自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
ALTER DATABASE DATAFILE 'file_name' AUTOEXTEND ON;
ALTER DATABASE DATAFILE 'file_name' AUTOEXTEND ON NEXT 256M ;
ALTER DATABASE DATAFILE 'file_name' AUTOEXTEND ON NEXT 256M MAXSIZE 1024M;
4.2、更改当前表空间大小
ALTER DATABASE DATAFILE 'file_name' RESIZE 10240M;
4.3、给表空间增加数据文件
ALTER TABLESPACE new_file_name ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\new_file_name.DBF' SIZE 1024M;
ALTER TABLESPACE new_file_name ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\new_file_name.DBF' SIZE 1024M
AUTOEXTEND ON NEXT 256M;
ALTER TABLESPACE new_file_name ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\new_file_name.DBF' SIZE 1024M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G;