oracle建立表空间和用户

1. BLOCKSIZE 8k,单个数据文件最大可达32G

CREATE TABLESPACE ts01 [LOGGING] DATAFILE '/data1/data_ts01_1.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED BLOCKSIZE 8k EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

1.1 增加数据文件无需设定BLOCKSIZE
alter TABLESPACE  ts01 add DATAFILE '/data1/data_ts01_2.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2. BLOCKSIZE 16k,单个数据文件最大可达64G

CREATE TABLESPACE ts02 [LOGGING] DATAFILE '/data2/data_ts02_1.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED BLOCKSIZE 16k EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2.2 增加数据文件无需设定BLOCKSIZE
alter TABLESPACE  ts02 add DATAFILE '/data2/data_ts02_2.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

3. 创建临时数据库文件
create temporary tablespace monitor_temp tempfile 'E:\app\owner\oradata\orcl\monitor_temp.dbf' size 100m autoextend on next 32m maxsize 500m extent management local ;

4. 调整表空间文件大小
alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' resize 15m;

5. 调整表空间文件自动扩展
alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on next 20m maxsize 1g;

6. 创建用户与上面创建的文件形成映射关系（用户名为monitor,密码为monitor）

CREATE USER monitor IDENTIFIED BY monitor DEFAULT TABLESPACE monitor TEMPORARY TABLESPACE monitor_temp;

7. 修改表空间

ALTER USER 用户名  IDENTIFIED BY 口令  DEFAULT TABLESPACE 表空间;

 

三、添加权限

grant connect,resource,dba to monitor;
grant create session to monitor;
 

四、删除数据库

DROP TABLESPACE monitor INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE monitor_temp INCLUDING CONTENTS AND DATAFILES;

 
五、删除用户

drop user monitor cascade;

六.  查看表空间大小和已使用大小

select file_name,bytes/1024/1024||'M' "size" from dba_data_files;

select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"  
from  
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,  
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b  
where  a.tablespace_name=b.tablespace_name  
       order   by   ((a.bytes-b.bytes)/a.bytes)   desc