登录  /  注册
首页 > php教程 > PHP开发 > 正文

Oracle 表空间查询与操作方法

高洛峰
发布: 2017-01-06 13:28:34
原创
1266人浏览过

一。查询篇 
1.查询oracle表空间的使用情况 
select b.file_id  文件id, 
  b.tablespace_name  表空间, 
  b.file_name     物理文件名, 
  b.bytes       总字节数, 
  (b.bytes-sum(nvl(a.bytes,0)))   已使用, 
  sum(nvl(a.bytes,0))        剩余, 
  sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 
  from dba_free_space a,dba_data_files b 
  where a.file_id=b.file_id 
  group by b.tablespace_name,b.file_name,b.file_id,b.bytes 
  order by b.tablespace_name 
2.查询oracle系统用户的默认表空间和临时表空间 
select default_tablespace,temporary_tablespace from dba_users 
3.查询单张表的使用情况 
select segment_name,bytes from dba_segments where segment_name = 're_stdevt_fact_day' and owner = user 
re_stdevt_fact_day是您要查询的表名称 
4.查询所有用户表使用大小的前三十名 
select * from (select segment_name,bytes from dba_segments where owner = user order by bytes desc ) where rownum 5.查询当前用户默认表空间的使用情况 
select tablespacename,sum(totalcontent),sum(usecontent),sum(sparecontent),avg(sparepercent) 
from 

select b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalcontent,(b.bytes-sum(nvl(a.bytes,0))) as usecontent,sum(nvl(a.bytes,0)) as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id and b.tablespace_name = (select default_tablespace from dba_users where username = user) 
group by b.tablespace_name,b.file_name,b.file_id,b.bytes 

group by tablespacename 
6.查询用户表空间的表 
select * from user_tables 
================================================================================== 
一、建立表空间 
create tablespace test 
datafile 'c:/oracle/oradata/db/test01.dbf' size 50m 
uniform size 1m; #指定区尺寸为128k,如不指定,区尺寸默认为64k 
或 
create tablespace test 
datafile 'c:/oracle/oradata/db/test01.dbf' size 50m 
minimum extent 50k extent management local 
default storage (initial 50k next 50k maxextents 100 pctincrease 0); 
可从dba_tablespaces中查看刚创建的表空间的信息 
二、建立undo表空间 
create undo tablespace test_undo 
datafile 'c:/oracle/oradata/db/test_undo.dbf' size 50m 
undo表空间的extent是由本地管理的,而且在创建时的sql语句中只能使用datafile和extent management子句。 
oracle规定在任何时刻只能将一个还原表空间赋予数据库,即在一个实例中可以有多个还原表空间存在,但只能有一个为活动的。可以使用alter system命令进行还原表空间的切换。 
sql> alter system set undo_tablespace = test_undo; 
三、建立临时表空间 
create temporary tablespace test_temp 
tempfile '/oracle/oradata/db/test_temp.dbf' size 50m 
查看系统当前默认的临时表空间 
select * from dba_properties where property_name like 'default%' 
改变系统默认临时表空间 
alter database default temporary tablespace test_temp; 
四、改变表空间状态 
1.使表空间脱机 
alter tablespace test offline; 
如果是意外删除了数据文件,则必须带有recover选项 
alter tablespace game test for recover; 
2.使表空间联机 
alter tablespace test online; 
3.使数据文件脱机 
alter database datafile 3 offline; 
4.使数据文件联机 
alter database datafile 3 online; 
5.使表空间只读 
alter tablespace test read only; 
6.使表空间可读写 
alter tablespace test read write; 
五、删除表空间 
drop tablespace test incl ing contents and datafiles cascade constraints; 
drop tablespace 表空间名 [incl ing contents [and datafiles] [cascade constraints]] 
1. incl ing contents 子句用来删除段 
2. and datafiles 子句用来删除数据文件 
3. cascade constraints 子句用来删除所有的引用完整性约束 

六、扩展表空间 
首先查看表空间的名字和所属文件 
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 test 
add datafile '/oracle/oradata/db/test02.dbf' size 1000m; 
2.手动增加数据文件尺寸 
alter database datafile 'c:/oracle/oradata/db/test01.dbf' 
resize 100m; 
3.设定数据文件自动扩展 
alter database datafile 'c:/oracle/oradata/db/test01.dbf' 
autoextend on next 100m 
maxsize 200m; 
设定后可从dba_tablespace中查看表空间信息,从v$datafile中查看对应的数据文件信息 
================================================================================== 
create tablespace scgl 
datafile 'e:\oracle\prod t\10.1.0\oradata\orcl\scgl2.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
create tablespace test_data 
logging 
datafile 'e:\oracle\prod t\10.1.0\oradata\orcl\user_data.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
create user scgl identified by qwer1234 
default tablespace scgl 
temporary tablespace scgl_temp; 
tempfile 'e:\oracle\prod t\10.1.0\oradata\orcl\user_temp.dbf' 
create temporary tablespace scgl_temp 
tempfile 'e:\oracle\prod t\10.1.0\oradata\orcl\scgl_temp.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
grant connect,resource, dba to scgl; 
oracle创建表空间 sys用户在cmd下以dba身份登陆: 
在cmd中打sqlplus /nolog 
然后再 
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; 
查询表空间使用情况 
select upper(f.tablespace_name) "表空间名", 
d.tot_grootte_mb "表空间大小(m)", 
d.tot_grootte_mb - f.total_bytes "已使用空间(m)", 
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') || '%' "使用比", 
f.total_bytes "空闲空间(m)", 
f.max_bytes "最大块(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 
查询表空间的free space 
select tablespace_name, 
count(*) as extends, 
round(sum(bytes) / 1024 / 1024, 2) as mb, 
sum(blocks) as blocks 
from dba_free_space 
group by tablespace_name; 
--查询表空间的总容量 
select tablespace_name, sum(bytes) / 1024 / 1024 as mb 
from dba_data_files 
group by tablespace_name; 
查询表空间使用率 
select total.tablespace_name, 
round(total.mb, 2) as total_mb, 
round(total.mb - free.mb, 2) as used_mb, 
round((1 - free.mb / total.mb) * 100, 2) || '%' as used_pct 
from (select tablespace_name, sum(bytes) / 1024 / 1024 as mb 
from dba_free_space 
group by tablespace_name) free, 
(select tablespace_name, sum(bytes) / 1024 / 1024 as mb 
from dba_data_files 
group by tablespace_name) total 
where free.tablespace_name = total.tablespace_name; 
----------------------------------------------------------------------------------------------------------------------------- 
1.建立表空间:create tablespace test datafile '/u01/test.dbf' size 10m uniform size 128k 
#指定区尺寸为128k ,块大小为默认8k 
#大文件表空间 create bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf ' size 100g 
2.建非标准表show parameter db alter system set db_2k_cache_size=10m create tablespace test datafile '/u01/test.dbf' size 10m blocksize 2k uniform size 128k 
#常见错误 
sql> alter system set db_2k_cache_size=2m; alter system set db_2k_cache_size=2m error at line 1: ora-02097: parameter cannot be modified because specified value is invalid ora-00384: insufficient memory to grow cache 
#解决 
sql> alter system set sga_max_size=400m scope=spfile; sql> shutdown immediate; sql> startup sql> alter system set db_2k_cache_size=10m; system altered. 
3.查看区大小与块大小#区大小 conn y / 123 create table t(i number) tablespace test; insert into t values(10) select bytes/1024 from user_segments where segment_name=upper('t'); 
#块大小 show parameter block(默认64k) 
#非标准表空间的blocksize sql> select * from v$dbfile; sql> select name,block_size,status from v$datafile; sql> select block_size from v$datafile where file#=14; 
4.删除表空间drop tablespace test including contents and datafiles 
5.查表空间:#查数据文件 select * from v$dbfile; #所有表空间 select * from v$tablespace; 
#表空间的数据文件 select file_name,tablespace_name from dba_data_files; 
6.建立undo表空间create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf' size 5m; 
#切换到新建的undo表空间 alter system set undo_tablespace=undotbs01; 
7.建立临时表空间create temporary tablespace temp_data tempfile '/u01/temp.db' size 5m; create bigfile temporary tablespace bigtem tempfile '/u01/bigtemp.db' size 5m; 
8.改变表空间状态 
(0.)查看状态 
#表空间状态 select tablespace_name,block_size,status from dba_tablespaces; 
#数据文件状态 select name,block_size,status from v$datafile; 
(1.)表空间脱机alter tablespace test offline 
#如果意外删除了数据文件 alter tablespace test offline for recover 
(2.)表空间联机alter tablespace test online 
(3.)数据文件脱机select * from v$dbfile; alter database datafile 3 offline 
(4.)数据文件联机recover datafile 3; alter database datafile 3 online; 
(5.)使表空间只读alter tablespace test read only 
(6.)使表空间可读写alter tablespace test read write; 
9.扩展表空间#首先查看表空间的名字和所属文件及空间 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 test add datafile '/u01/test02.dbf' size 10m(自动加一个datafile) 
2.alter database datafile '/u01/test.dbf' resize 20m; 
3.alter database datafile '/u01/test.dbf' autoextend on next 10m maxsize 1g; 
#设定后查看表空间信息 
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; 
10.移动表空间的数据文件 
#先确定数据文件据在表空间 
sql>select tablespace_name,file_name from dba_data_files where file_name='/u01/test.dbf'; 
#open状态 
sql>alter tablespace test offline; sql>host move /u01/test.dbf /u01/oracle/test.dbf; sql>alter tablespace test rename datafile '/u01/test.dbf' to '/u01/oracle/test.dbf'; sql>alter tablespace test offline; 
#mount状态 sql>shutdown immediate; sql>startup mount sql>host move /u01/test.dbf /u01/oracle/test.dbf; sql>alter database rename file '/u01/test.dbf' to '/u01/oracle/test.dbf'; 
11.表空间和数据文件常用的数据字典与动态性能视图v$dbfile v$datafile dba_segments user_segments dba_data_files v$tablespace dba_tablespaces user_tablespaces 
--查询表空间使用情况 
select upper(f.tablespace_name) "表空间名", 
d.tot_grootte_mb "表空间大小(m)", 
d.tot_grootte_mb - f.total_bytes "已使用空间(m)", 
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') || '%' "使用比", 
f.total_bytes "空闲空间(m)", 
f.max_bytes "最大块(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 
--查询表空间的free space 
select tablespace_name, 
count(*) as extends, 
round(sum(bytes) / 1024 / 1024, 2) as mb, 
sum(blocks) as blocks 
from dba_free_space 
group by tablespace_name; 
--查询表空间的总容量 
select tablespace_name, sum(bytes) / 1024 / 1024 as mb 
from dba_data_files 
group by tablespace_name; 
--查询表空间使用率 
select total.tablespace_name, 
round(total.mb, 2) as total_mb, 
round(total.mb - free.mb, 2) as used_mb, 
round((1 - free.mb / total.mb) * 100, 2) || '%' as used_pct 
from (select tablespace_name, sum(bytes) / 1024 / 1024 as mb 
from dba_free_space 
group by tablespace_name) free, 
(select tablespace_name, sum(bytes) / 1024 / 1024 as mb 
from dba_data_files 
group by tablespace_name) total 
where free.tablespace_name = total.tablespace_name; 
//给用户授予权限 
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 incl ing 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 incl ing 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.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

更多Oracle 表空间查询与操作方法相关文章请关注PHP中文网!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:php中文网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号