Oracle_创建、增加、删除表空间相关脚本

--创建表空间和数据文件，单个文件10000m，以1024m自动扩展，存储路径，先创建好文件，文件文需要大写
create tablespace TABLESPACE_NAME logging 
datafile 'J:\ORCL_PROJ\TABLESPACE_NAME_1.dbf'size 5000m  --存储地址 初始大小5000m
autoextend on next 1024m MAXSIZE UNLIMITED,              --每次扩展1024M，无限制扩展
'J:\ORCL_PROJ\TABLESPACE_NAME_2.dbf'size 5000m 
autoextend on next 1024m MAXSIZE UNLIMITED,
'J:\ORCL_PROJ\TABLESPACE_NAME_3.dbf'size 5000m 
autoextend on next 1024m MAXSIZE UNLIMITED,
'J:\ORCL_PROJ\TABLESPACE_NAME_4.dbf'size 5000m 
autoextend onnext 1024m MAXSIZE UNLIMITED,
'J:\ORCL_PROJ\TABLESPACE_NAME_5.dbf'size 5000m 
autoextend onnext 1024m MAXSIZE UNLIMITED
extent management local; 

--创建用户，关联表空间
CREATE USER username IDENTIFIED BY userpassword
DEFAULT TABLESPACE "TABLESPACE_NAME"
TEMPORARY TABLESPACE "TEMP"
PROFILE DEFAULT ACCOUNT UNLOCK;

--添加用户权限
GRANT "CONNECT" TO username; 
GRANT "RESOURCE" TO username;
GRANT "DBA" TO username;

--给表空间增加数据文件
alter tablespace TABLESPACE_NAME(表空间名)
add datafile 'j:\orcl_proj\TABLESPACE_NAME_3.dbf'
size 5000m
autoextend 
on next 1024m 
maxsize unlimited;

--删除表空间和对应数据文件
drop tablespace TABLESPACE_NAME including contents and datafiles;

--强制删除用户
drop user username cascade;

--删除关联索引
select segment_name,partition_name,tablespace_name from dba_extents where tablespace_name=upper('TABLESPACE_NAME')

--生成语句
select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U', 'P')
    and (index_owner, index_name) in
       (select owner, segment_name
        from dba_segments
        where tablespace_name = 'TABLESPACE_NAME');