--再此已经创建过一个表空间gxldjctest001 ,又删除了,但是物理文件没有删除
--查询表空间
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
--查询指定用户表空间
select default_tablespace from dba_users where username='xxx'
--1、创建表空间
create tablespace xxx datafile '/u01/app/oracle/oradata/sjld/xxx_001.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
--2、根据需要添加一定数量的数据文件
alter tablespace xxx add datafile '/u01/app/oracle/oradata/sjld/xxx_002.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
alter tablespace xxx add datafile '/u01/app/oracle/oradata/sjld/xxx_003.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
alter tablespace xxx add datafile '/u01/app/oracle/oradata/sjld/xxx_004.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
--3、创建临时空间
create temporary tablespace xxx_temp tempfile '/u01/app/oracle/oradata/sjld/xxx_temp.dbf' size 10g autoextend ON next 100m maxsize 30G;
--4、创建用户并指定表空间和临时表空间
create user xxx identified by xxx default tablespace xxx temporary tablespace xxx_temp;
--5、分配权限
grant connect,resource,dba to xxx;
--删除表空间以及对应的表空间文件
drop tablespace xxx INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
--删除用户
drop user xxx cascade;