表空间操作常用语句

创建表空间T_WORK

create tablespace T_WORK_DATA datafile '/u01/app/oracle/oradata/airforecast/t_work01.dbf' SIZE 200m AUTOEXTEND ON NEXT 32m MAXSIZE 32767M EXTENT MANAGEMENT LOCAL;
alter tablespace T_WORK_DATA add datafile '/u01/app/oracle/oradata/airforecast/t_work02.dbf' size 5M autoextend on maxsize 32767M;

创建临时表空间

CREATE TEMPORARY TABLESPACE T_WORK_TEMP TEMPFILE '/u01/app/oracle/oradata/airforecast/t_work_tmp01.dbf' SIZE 200m AUTOEXTEND ON NEXT 32m MAXSIZE 2048m EXTENT MANAGEMENT LOCAL;

创建T_WORK用户

CREATE USER T_WORK IDENTIFIED BY solution#123 DEFAULT TABLESPACE T_WORK_DATA TEMPORARY TABLESPACE T_WORK_TEMP;

赋予权限

GRANT CONNECT,RESOURCE TO T_WORK;

修改用户表所属表空间

alter table TABLE_NAME move tablespace TABLESPACENAME

删除表空间

DROP USER T_WORK CASCADE;
DROP TABLESPACE T_WORK_DATA INCLUDING CONTENTS AND DATAFILES;

删除临时表空间

DROP TABLESPACE T_WORK_TEMP INCLUDING CONTENTS AND DATAFILES;

开启自动扩展功能语法

alter database datafile '对应的数据文件路径信息' autoextend on;

关闭自动扩展功能语法

alter database datafile '对应的数据文件路径信息' autoextend off;

查看默认数据表空间

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES;

查看默认临时表空间

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

查看普通用户的默认表空间和临时表空间

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS;
SELECT DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='T_WORK';

修改表空间名称

alter tablespace T_WORK RENAME TO T_WORK_DATA;