oracle创建表空间及查看表空间和使用情况

创建表空间

1. 创建表空间
create tablespace “tablespace_name”
datafile 'D:\oracle\product\10.2.0\oradata\orcl\SIRM2.dbf' size 1024M --存储地址 初始大小1G
autoextend on next 10M maxsize unlimited   --每次扩展10M，无限制扩展
EXTENT MANAGEMENT local  autoallocate
segment space management auto;
2.创建用户
 create user “user” identified by “password” 
default tablespace “tablespace”
  temporary tablespace TEMP
  profile DEFAULT;
3.授权
grant dba to test;
grant connect to test;
grant resource to test;
 SYS用户在CMD下以DBA身份登陆： 
conn / as sysdba 
//创建临时表空间   
 create temporary tablespace user_temp   
 tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'  
 size 50m   
autoextend on   
 next 50m maxsize 20480m   
 extent management local;   
 //创建数据表空间   
 create tablespace test_data   
logging   
 datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'  
size 50m   
 autoextend on   
 next 50m maxsize 20480m   
 extent management local;   
 //创建用户并指定表空间   
 create user username identified by password   
 default tablespace user_data   
temporary tablespace user_temp;   
 //给用户授予权限   
 grant connect,resource to username;   
 
  //以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间， 
这就不用在每创建一个对象给其指定表空间了  
 
撤销权限：   
revoke   权限...   from  用户名; 
 删除用户命令 
 drop user user_name cascade; 
 建立表空间 
 CREATE TABLESPACE data01 
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M 
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 
 删除表空间 
 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 
一、建立表空间 
 CREATE TABLESPACE data01 
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M 
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 
 二、建立UNDO表空间 
 CREATE UNDO TABLESPACE UNDOTBS02 
 DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M 
 #注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间: 
 ALTER SYSTEM SET undo_tablespace=UNDOTBS02; 
 三、建立临时表空间 
 CREATE TEMPORARY TABLESPACE temp_data 
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M 
四、改变表空间状态 
 1.使表空间脱机 
 ALTER TABLESPACE game OFFLINE; 
 如果是意外删除了数据文件，则必须带有RECOVER选项 
 ALTER TABLESPACE game OFFLINE FOR RECOVER; 
 2.使表空间联机 
 ALTER TABLESPACE game ONLINE; 
3.使数据文件脱机 
 ALTER DATABASE DATAFILE 3 OFFLINE; 
 4.使数据文件联机 
 ALTER DATABASE DATAFILE 3 ONLINE; 
5.使表空间只读 
 ALTER TABLESPACE game READ ONLY; 
6.使表空间可读写 
 ALTER TABLESPACE game READ WRITE; 
 五、删除表空间 
 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 
六、扩展表空间 
 首先查看表空间的名字和所属文件 
 select tablespace_name, file_id, file_name, 
 round(bytes/(1024*1024),0) total_space 
 from dba_data_files 
order by tablespace_name; 
 1.增加数据文件 
 ALTER TABLESPACE game 
 ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M; 
 2.手动增加数据文件尺寸 
 ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
 RESIZE 4000M; 
 3.设定数据文件自动扩展 
 ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf 
 AUTOEXTEND ON NEXT 100M 
 MAXSIZE 10000M; 
 设定后查看表空间信息 
 SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, 
 (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
 FROM SYS.SMTS_AVAIL A,SYS.SMTSA​VAILA,SYS.SMTS_USED B,SYS.SM$TS_FREE C 
 WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE 

查看表空间使用

--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;

--4、查看控制文件
SELECT NAME FROM v$controlfile;

--5、查看日志文件
SELECT MEMBER FROM v$logfile;

--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;

--8、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';

--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;

检查表空间使用率

1.查询不包含临时表空间的使用率

SELECT Upper(F.TABLESPACE_NAME) "TablespaceName",
D.TOT_GROOTTE_MB "Total(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "Used(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "Used%",
F.TOTAL_BYTES "Free(M)",
F.MAX_BYTES "max_block(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1

2.查看临时表空间使用率

select c.tablespace_name,
to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name, sum(bytes_cached) bytes_used
from v$temp_extent_pool
group by tablespace_name) d
where c.tablespace_name = d.tablespace_name

3.查看临时表空间 用户使用情况

select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage;