一。查詢篇?
1.查詢oracle表空間的使用情況?
select b.file_id　　文件id,?
　　b.tablespace_name　　表空間,?
　　b.file_name　　　　　物理文件名,?
　　b.bytes　　　　　　　總字節(jié)數(shù),?
　　(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系統(tǒng)用戶的默認表空間和臨時表空間?
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; #指定區(qū)尺寸為128k,如不指定，區(qū)尺寸默認為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中查看剛創(chuàng)建的表空間的信息?
二、建立undo表空間?
create undo tablespace test_undo?
datafile 'c:/oracle/oradata/db/test_undo.dbf' size 50m?
undo表空間的extent是由本地管理的，而且在創(chuàng)建時的sql語句中只能使用datafile和extent management子句。?
oracle規(guī)定在任何時刻只能將一個還原表空間賦予數(shù)據庫，即在一個實例中可以有多個還原表空間存在，但只能有一個為活動的?？梢允褂胊lter system命令進行還原表空間的切換。?
sql> alter system set undo_tablespace = test_undo;?
三、建立臨時表空間?
create temporary tablespace test_temp?
tempfile '/oracle/oradata/db/test_temp.dbf' size 50m?
查看系統(tǒng)當前默認的臨時表空間?
select * from dba_properties where property_name like 'default%'?
改變系統(tǒng)默認臨時表空間?
alter database default temporary tablespace test_temp;?
四、改變表空間狀態(tài)?
1.使表空間脫機?
alter tablespace test offline;?
如果是意外刪除了數(shù)據文件，則必須帶有recover選項?
alter tablespace game test for recover;?
2.使表空間聯(lián)機?
alter tablespace test online;?
3.使數(shù)據文件脫機?
alter database datafile 3 offline;?
4.使數(shù)據文件聯(lián)機?
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　子句用來刪除數(shù)據文件?
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.增加數(shù)據文件?
alter tablespace test?
add datafile '/oracle/oradata/db/test02.dbf' size 1000m;?
2.手動增加數(shù)據文件尺寸?
alter database datafile 'c:/oracle/oradata/db/test01.dbf'?
resize 100m;?
3.設定數(shù)據文件自動擴展?
alter database datafile 'c:/oracle/oradata/db/test01.dbf'?
autoextend on next 100m?
maxsize 200m;?
設定后可從dba_tablespace中查看表空間信息，從v$datafile中查看對應的數(shù)據文件信息?
==================================================================================?
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創(chuàng)建表空間 sys用戶在cmd下以dba身份登陸：?
在cmd中打sqlplus /nolog?
然后再?
conn / as sysdba?
//創(chuàng)建臨時表空間?
create temporary tablespace user_temp?
tempfile 'd:\oracle\oradata\oracle9i\user_temp.dbf'?
size 50m?
autoextend on?
next 50m maxsize 20480m?
extent management local;?
//創(chuàng)建數(shù)據表空間?
create tablespace test_data?
logging?
datafile 'd:\oracle\oradata\oracle9i\user_data.dbf'?
size 50m?
autoextend on?
next 50m maxsize 20480m?
extent management local;?
//創(chuàng)建用戶并指定表空間?
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?
#指定區(qū)尺寸為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.查看區(qū)大小與塊大小#區(qū)大小 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.查表空間:#查數(shù)據文件 select * from v$dbfile; #所有表空間 select * from v$tablespace;?
#表空間的數(shù)據文件 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.改變表空間狀態(tài)?
(0.)查看狀態(tài)?
#表空間狀態(tài) select tablespace_name,block_size,status from dba_tablespaces;?
#數(shù)據文件狀態(tài) select name,block_size,status from v$datafile;?
(1.)表空間脫機alter tablespace test offline?
#如果意外刪除了數(shù)據文件 alter tablespace test offline for recover?
(2.)表空間聯(lián)機alter tablespace test online?
(3.)數(shù)據文件脫機select * from v$dbfile; alter database datafile 3 offline?
(4.)數(shù)據文件聯(lián)機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.移動表空間的數(shù)據文件?
#先確定數(shù)據文件據在表空間?
sql>select tablespace_name,file_name from dba_data_files where file_name='/u01/test.dbf';?
#open狀態(tài)?
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狀態(tài) 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.表空間和數(shù)據文件常用的數(shù)據字典與動態(tài)性能視圖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;?
//以后以該用戶登錄，創(chuàng)建的任何數(shù)據庫對象都屬于user_temp 和user_data表空間，?
這就不用在每創(chuàng)建一個對象給其指定表空間了?
撤權：?
revoke 權限... from 用戶名;?
刪除用戶命令?
drop user user_name cascade;?

建立表空間?
create tablespace data01?
datafile '/oracle/oradata/db/data01.dbf' size 500m?
uniform size 128k; #指定區(qū)尺寸為128k,如不指定，區(qū)尺寸默認為64k?

刪除表空間?
drop tablespace data01 incl ing contents and datafiles;?
一、建立表空間?
create tablespace data01?
datafile '/oracle/oradata/db/data01.dbf' size 500m?
uniform size 128k; #指定區(qū)尺寸為128k,如不指定，區(qū)尺寸默認為64k?
二、建立undo表空間?
create undo tablespace undotbs02?
datafile '/oracle/oradata/db/undotbs02.dbf' size 50m?
#注意：在open狀態(tài)下某些時刻只能用一個undo表空間，如果要用新建的表空間，必須切換到該表空間:?
alter system set undo_tablespace=undotbs02;?
三、建立臨時表空間?
create temporary tablespace temp_data?
tempfile '/oracle/oradata/db/temp_data.dbf' size 50m?
四、改變表空間狀態(tài)?
1.使表空間脫機?
alter tablespace game offline;?
如果是意外刪除了數(shù)據文件，則必須帶有recover選項?
alter tablespace game offline for recover;?
2.使表空間聯(lián)機?
alter tablespace game online;?
3.使數(shù)據文件脫機?
alter database datafile 3 offline;?
4.使數(shù)據文件聯(lián)機?
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.增加數(shù)據文件?
alter tablespace game?
add datafile '/oracle/oradata/db/game02.dbf' size 1000m;?
2.手動增加數(shù)據文件尺寸?
alter database datafile '/oracle/oradata/db/game.dbf'?
resize 4000m;?
3.設定數(shù)據文件自動擴展?
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中文網！