--创建表空间和数据文件,单个文件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');