Oracle表空间管理

1、创建用户和表空间

sqlplus /nolog; --启动sqlplus不登录
connect sys/root as sysdba --通过sys登录
select instance_name from v$instance; --查看数据库实例
create tablespace table_space datafile ‘E:\table_space.dbf’ size 100M; --创建表空间
create user test identified by ‘123456’ default tablespace table_space; --创建用户并指定
grant dba to test; --给用户授予dba的权限
创建表空间
–创建表空间
create tablespace SXMDATA datafile ‘E:\ORACLE\ORADATA\ORCDB\DATA_1.DBF’ size 200M;
create tablespace SXMDATA_temp datafile ‘E:\ORACLE\ORADATA\ORCDB\DATA_temp_1.DBF’ size 200M autoextend on maxsize unlimited;

–创建用户并指定默认表空间
create user GTADATA identified by GTADATA default tablespace SXMDATA temporary tablespace SXMDATA_temp;
–用户赋权
GRANT connect, resource TO GTADATA;

2、查看表空间

–查看用户对应的表空间
select username,default_tablespace from dba_users;
select * from v$tablespace --查看表空间

–查看表空间大小
SELECT FILE_NAME as “数据文件”,TABLESPACE_NAME as “表空间名称”,AUTOEXTENSIBLE as “自动扩展”,STATUS as “状态”,MAXBYTES as “可扩展最大值”,
USER_BYTES as “已使用大小”,INCREMENT_BY as “自动扩展增量”
FROM dba_data_files;

3、修改表空间

–修改用户默认表空间
alter user GTADATA default tablespace SXMDATA;
alter user GTADATA temporary tablespace SXMDATA_temp;

–扩展空间，将数据文件扩大至5000MB
alter database datafile ‘E:\ORACLE\ORADATA\ORCDB\DATA_1.DBF’ resize 500m;

–自动增长，表空间不足时增加200MB，最大扩展5000MB
alter database datafile ‘E:\ORACLE\ORADATA\ORCDB\DATA_1.DBF’ autoextend on next 200m maxsize 5000m;

–扩展无限大空间
alter database DATAFILE ‘E:\ORACLE\ORADATA\ORCDB\DATA_1.DBF’ autoextend on maxsize unlimited;

–增加数据文件，SXMDATA表空间增加文件
alter tablespace SXMDATA add datafile ‘E:\ORACLE\ORADATA\ORCDB\DATA_2.DBF’ size 1024m;

4、删除表空间

–删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;