Oralce之表空间

知识在于积累

厚积薄发靠的便是梳理和总结，fighting~

 --------------------------------------------------表空间--------------------------------------------------------

*、查看oracle表空间的名称及其已使用大小（M）

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size_M 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 

 

*、查看oracle表空间名称对应的物理文件路径及其已使用大小

SELECT tablespace_name, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space_M
FROM dba_data_files 
ORDER BY tablespace_name; 

 

*、查询oracle表空间的空闲大小M

SELECT SUM(bytes) / (1024 * 1024) AS free_space_M, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 

 

*、查询oracle表空间的占用比例

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; 

 

*、查询oracle库对象的表分区、表、视图、序列、存储过程、触发器、java资源等等信息

SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status; 

 

*、查询oracle及其组件的版本信息

SELECT product,version from product_component_version;

 

*、查看oracle的创建日期及其归档方式

SELECT created, log_mode, log_mode FROM v$database; 

 

*、eg:查看表空间的使用情况（引用）

SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name 

 

*、表空间的dbf单文件大小依赖于block_size（默认8k）也就是32G

即oracle的dbf单文件大小并不是无限的，需要考虑block_size的配置，可提前根据数量的大小设置block_size值，后续若要更改，不建议修改block_size,可以从添加dbf文件的方式，alter tablespace users add datafile '/u01/data/orcl/users03.dbf' size 1G autoextend on;

 

*、oracle创建表空间

create tablespace caetestspace  
datafile 'D:\App\oracle11r2\caedbf\caetestspace.dbf'   
size 1500M   
autoextend on next 50M maxsize 3000M;

 

*、oracle创建临时表空间（oracle自带的无法满足需求）

create temporary tablespace caetesttempspace  
tempfile 'D:\App\oracle11r2\caedbf\caetesttempspace.dbf'   
size 500M   
autoextend on next 50M maxsize unlimited;

*、查询oracle某个表空间对应的物理文件及其位置等信息

select * from dba_data_files where tablespace_name='USERS';

 

*、oacle表空间增加数据文件

 alter tablespace caetest add datafile 'd:\newtablespacefile.dbf' size 500m;

 

*、oracle删除（临时）表空间

drop tablespace testtempspace including contents and datafiles cascade constraints;

    没有drop temporyary的用法

 

 

  --------------------------------------------------表空间和表的关系--------------------------------------------------------

*、oracle查询某个表空间下有哪些表

select tablespace_name,table_name from user_tables;

 

  --------------------------------------------------数据库用户--------------------------------------------------------

*、oracle管理员查询数据库中用户信息

select username from dba_users;

 

*、oracle创建用户

create user caetest identified by caetest;

 

*、 oracle查看用户拥有的权限

select * from dba_role_privs;
select * from user_role_privs;

 

*、oracle用户授予权限

grant resource , connect to caetest;

 

*、oracle用户收回权限

revoke resource , connect from caetest;

 

*、oracle为已存在的用户赋予表空间以及临时表空间（临时可有可无，但需要时必须这样写）

alter user caetest identified by caetest default tablespace caetestspace temporary tablespace caetesttempspace;

 

*、oracle查看当前用户默认表空间

select username,default_tablespace from user_users;

 

*、oracle查看所有用户默认表空间

select username,default_tablespace from dba_users;

 

*、oracle删除用户

drop user caetest cascade;

 

   --------------------------------------------------资源链接--------------------------------------------------------

感谢以下资源的分享者（更详尽）：

oracle用户、权限、角色的梳理

http://www.cnblogs.com/chenleiustc/archive/2009/07/29/1534110.html（访问速度快）

用户及表空间的管理

http://blog.csdn.net/starnight_cbj/article/details/6792364

临时表空间的增删查改以及PGA和临时表空间的使用策略

http://www.cnblogs.com/guanjie20/p/3858480.html

oracle实例的内存（SGA和PGA）的查询和调整

http://log-cd.iteye.com/blog/562052

修改oracle服务端字符集编码（慎用）

http://blog.csdn.net/fred_yang2013/article/details/46679931