oracle创建表空间和修改用户默认表空间

-- 查询用户表空间文件的路径，然后在此目录下创建新的表空间
select name from v$datafile;

-- 
select * from dba_directories

-- 查询用户临时表空间文件的路径，然后在此目录下创建新的临时表空间
select name from v$tempfile;

--表空间
--不限制表空间大小用：maxsize unlimited
--限制表空间大小用：maxsize 1024m
create tablespace wdy_data 
logging 
datafile 'D:\oracle\app\oracle\oradata\XE\wdy_data.DBF' 
size 50m 
autoextend on 
next 50m maxsize unlimited
extent management local;

--创建临时表空间
create temporary tablespace wdy_temp 
tempfile 'D:\oracle\app\oracle\oradata\XE\wdy_temp.DBF' 
size 50m 
autoextend on 
next 50m maxsize unlimited
extent management local;
   
 
--2.建用户
create user user_wdy2 identified by user_wdy2_123456 
default tablespace wdy_data 
temporary tablespace wdy_temp;


--3.赋权
grant connect,resource to user_wdy2;
grant create any sequence to user_wdy2;
grant create any table to user_wdy2;
grant delete any table to user_wdy2;
grant insert any table to user_wdy2;
grant select any table to user_wdy2;
grant unlimited tablespace to user_wdy2;
grant execute any procedure to user_wdy2;
grant update any table to user_wdy2;
grant create any view to user_wdy2;


--4.修改用户默认表空间：
alter user user_wdy2 default tablespace wdy_data 

-- 5.删除表空间
drop tablespace  wdy_data  including contents and datafiles CASCADE CONSTRAINTS;
--6.删除临时表空间
drop tablespace wdy_temp including contents and datafiles;
--7.删除用户
drop user user_wdy2cascade;