oracle表空间及用户操作

1.表空间创建和删除

#查询数据文件存放路径
select file_name from dba_Data_Files; 
#查询表空间管理方式
SELECT tablespace_name, extent_management, allocation_type,segment_space_management FROM dba_tablespaces;
#创建表空间，ASSM自动段空间管理,默认段空间管理manual
create tablespace ts_data datafile '/u01/app/oracle/oradata/prod/ts_data01.dbf' size 100m autoextend on next 50m maxsize 1024m extent management local segment space management auto;
#增加数据文件，ASSM自动段空间管理
alter tablespace ts_data add datafile '/u01/app/oracle/oradata/prod/ts_data02.dbf' size 100m autoextend on next 50m maxsize 1024m;
#删除表空间，同时删除内容和数据文件
drop tablespace ts_data including contents and datafiles;

2）用户管理

1）新建用户

create user db_test identified by 123456 default tablespace ts_data;

2）授权用户

grant connect,resource to db_test;
grant dba to db_test;
grant create any table to db_test;
grant delete any table to db_test;
grant insert any table to db_test;
grant update any table to db_test;
grant select any table to db_test;
grant unlimited tablespace to db_test;
grant execute any procedure to db_test;

3）删除用户会很慢，可以先删除用户下的对象，再删除用户

set pages 0 不进行分页显示

spool /home/oracle/drop.sql 开启spool并将输出重定向到一个文件

select 'drop '||object_type||' ' ||owner||'.'||object_name||';' from dba_objects where owner=xx;

spool off 关闭spool

@drop.sql 执行删除语句脚本

drop user db_test cascade;