1. ??
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 dba_free_space a,dba_data_files b
?? ?? ???
??? a.file_id=b.file_id
b.tablespace_name,b.file_name?? ???, b. file_id,b.bytes
b.tablespace_name?
2. Oracle ??? ???? ?? ??????? ? ?? ???????? ?????.
dba_users?? default_tablespace,temporary_tablespace? ?????.
3. >select Segment_name,bytes from dba_segments ??? Segment_name = 'RE_STDEVT_FACT_DAY' ? ??? = USER
RE_STDEVT_FACT_DAY? ????? ???? ?????.
4 ?? ??? ???? ?? 30? ???? ?????.
select * from(dba_segments?? ????_??,??? ??, ??? = USER ??? ?? desc) ??? rownum <= 30
5. ?? ???? ?? ??????? ???? ?????.
select tablespacename,sum(totalContent ), sum(usecontent),sum(sparecontent),avg(sparepercent)
from
(
SELECT b.file_id? id?, b.tablespace_name? tablespacename??, b.bytes? 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
a.file_id=b.file_id ? b.tablespace_name = (??? ?? = user? dba_users?? default_tablespace ??)
b.tablespace_name,b.file_name,b .file_id? ??? ,b.bytes
)
GROUP BY tablespacename
6. ??? ???????? ??? ??
user_tables?? * ??
============= = ================================================= = ==================
1. ??????? ??
CREATE TABLESPACE ???
DATAFILE 'c:/oracle/oradata/db/test01.dbf' SIZE 50M
UNIFORM SIZE 1M; # ???? ?? ?? ?? ??? 128k???.
??
CREATE TABLESPACE ???
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?? ?? ??? ???????? ??? ? ? ????
II , UNDO ??????? ??
CREATE UNDO TABLESPACE test_undo
DATAFILE 'c:/oracle/oradata/db/test_undo.dbf' SIZE 50M
UNDO ???????? ??? ???? ????, ?? ? SQL ???? DATAFILE ? EXTENT MANAGEMENT ?? ??? ? ????.
ORACLE??? ???? ??? ?? ???????? ??????? ??? ? ??? ?????. ?, ?????? ?? ?? ?? ???????? ??? ? ??? ??? ???? ? ????. ALTER SYSTEM ??? ???? ??? ???????? ??? ? ????.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = test_undo;
3. ?? ??? ?? ??
CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE '/oracle/oradata/db/test_temp.dbf' SIZE 50M
???? ?? ?? ?? ???????
select * from dba_properties where property_name like 'DEFAULT%'
???? ?? ?? ??????? ??
alter ?????? ?? ?? ??????? test_temp
4.
1. ??? ??? ?????? ??
ALTER TABLESPACE ??? OFFLINE
??? ??? ??? ??? ?? RECOVER ??
ALTER TABLESPACE ?? ??? FOR RECOVER? ??? ???. ????? ??? ?? ??
ALTER TABLESPACE ONLINE;
3. ??? ??? ?????? ??
ALTER DATABASE DATAFILE 3 OFFLINE
4. ALTER DATABASE DATAFILE 3 ONLINE;
5. ??? ??? ?? ???? ????.
6. ??? ??? ?? ? ?? ???? ????.
ALTER TABLESPACE ???
5. space
DROP TABLESPACE ??? 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 ???
ADD DATAFILE '/oracle/oradata/db/test02.dbf' SIZE 1000M
2. ???? ??? ?? ?? ???
ALTER DATABASE DATA '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?? ?? ??? ?? ??? ? ? ????.
========= ================================================= =======================
??????? scgl ??
??? ?? 'E:ORACLEPROD T10.1.0ORADATAORCLscgl2.dbf'
?? 50m

?? 50m ?? ?? 20480m
?? ??,
?? 50m ?? ?? 20480m
?? ?? ??
qwer1234? ???? ??? scgl ??
?? ??????? scgl >?? ??????? scgl_temp;
tempfile 'E:ORACLEPROD T10.1.0ORADATAORCLuser_ temp .dbf'
?? ??????? scgl_temp
tempfile 'E:ORACLEPROD T10.1.0ORADATAORCLscgl_temp.dbf'
?? 50m
autoextend on
?? 50m maxsize 20480m
extent Management local;
grant connect,resource, dba to scgl;
oracle? ??? ??? ???? SYS ???? CMD?? DBA? ??????.
sqlplus? ?????. CMD? /nolog
?? ??
conn / as sysdba
//?? ??????? ??
?? ??????? user_temp
tempfile 'D:oracleoradataOracle9iuser_temp.dbf'
?? 50m

?? 50m ?? ?? 20480m
?? ?? ?? ;
//??? ??????? ??
??????? ?? test_data
??
??? ?? 'D:oracleoradataOracle9iuser_data.dbf'
?? 50m
?? ??
?? 50m ?? ?? 20480m
?? ?? ??;
//??? ?? ? ??? ?? ??
????? ???? ??? ??? ?? ??
?? ??? ?? user_data
?? ??? ?? 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
??? ?? ?? ?? ??
select tablespace_name,
count(*) as ??,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum (??)
from dba_free_space
group by tablespace_name
--??? ??? ? ?? ??
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
tablespace_name?? ???;
?? ??? ?? ???
total.tablespace_name,
round(total.MB, 2)? 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, ??(???) / 1024 / 1024 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
???? free???. tablespace_name = total.tablespace_name
-- -------------------------- ----------- -------------------------- ----------- ----------
1. ??????? ??: ??????? ??? ??? ?? '/u01/ ?? test.dbf' ?? 10M ?? ?? 128k
#??? ?? ??? 128k?? ?? ??? ????? 8K???.
#? ?? ??????? create bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf' ?? 100G
2.建不標準表show ???? db alter system set db_2k_cache_size=10M create tablespace test datafile '/u01/test.dbf' size 10M blocksize 2K ?? ?? 128k?
#常見錯誤?
SQL> ??? ?? db_2k_cache_size=2M ??; alter system set db_2k_cache_size=2M ?? 1? ??: ORA-02097: ??? ?? ???? ?? ??? ????? ??? ? ????. ORA-00384: ??? ?? ???? ?????.
#解決?
SQL> ??? ?? ?? sga_max_size=400M ??=spfile; SQL> ?? ??; SQL> ?? SQL> ??? ?? db_2k_cache_size=10M ??; ???? ???????.?
3.查看區(qū)大小與塊大小#區(qū)大小 conn y / 123 ??? ?? t(i number) ??????? ???; t ?(10)? ???? user_segments?? bytes/1024? ?????. ??? ????_??=upper('t');? v$datafile?? ??, ?? ??, ??? ?????. SQL> ??#=14? v$datafile?? block_size? ?????.?
4.刪除表空間??? ? ??? ??? ??? ??????? ?? ???
5.查表空間:#查數(shù)據文件 select * from v$dbfile; #所有表空間 v$tablespace?? *? ?????.?
#表空間的數(shù)據文件 dba_data_files?? file_name,tablespace_name? ?????.?
6.建立undo表空間?? ?? ??????? ?? undotbs01 ??? ?? '/u01/undotbs01.dbf' ?? 5M;?
#切換到新建的undo表空間 alter system set undo_tablespace=undotbs01;?
7.建立臨時表空間?? ??????? ?? temp_data ?? ?? '/u01/temp.db' ?? 5M; bigfile ?? ??????? ?? bigtem tempfile '/u01/bigtemp.db' ?? 5M;?#數(shù)據文件狀態(tài) v$datafile?? ??, ?? ??, ??? ?????.?
(1.)表空間脫機alter tablespace ??? ?????
#如果意?刪除了數(shù)據文件 alter tablespace ??? ???? ??? ???
(2.)表空間聯(lián)機alter ??????? ??? ???
(3 .) v$dbfile?? *? ?????. ?????? ??? ?? 3? ?????? ???
(4.)數(shù)據文件聯(lián)機recover datafile 3; ?????? ????? 3? ????? ?????.?
(5.)使表空間只讀alter ??????? ??? ?? ???
(6.)使表空間可讀寫alter ??????? ??? ?? ??;?
9. ?? ?? # ??? ??? ??? ???? tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space? dba_data_files?? tablespace_name??? ?????. #三種擴 ?? ???
1.alter ??????? ??? ?? ??? ?? '/u01/test02.dbf' ?? 10M(自動加一個datafile)?
2.alter ?????? ??? ?? '/u01/test.dbf' ?? ?? 20M;?
3.alter ?????? ??? ?? '/u01/test.dbf'? ?? 10M ?? ?? 1G?? ?? ?????.?
#設定后查看表空間信息?
a.tablespace_name,a.bytes ?,b.bytes ??,c.bytes ??,(b.bytes*100)/a.bytes "% ??", (c.bytes*100)/a.bytes sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c? "% free" ??? a.tablespace_name=b.tablespace_name ? a.tablespace_name=c .???????_??;?#open狀態(tài)?
SQL>alter ??????? ???? ???; SQL>??? ?? /u01/test.dbf /u01/oracle/test.dbf; SQL> ??????? ?? ??? ??? ?? '/u01/test.dbf'? ??? '/u01/oracle/test.dbf'? ?????. SQL> ??????? ?? ???? ???;?
#mount狀態(tài) SQL>?? ??; SQL> ?? ??? SQL> ??? ?? /u01/test.dbf /u01/oracle/test.dbf; SQL> ?????? ?? ?? '/u01/test.dbf'? '/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?
--查詢表空間使用情況?
?? ??(F. TABLESPACE_NAME) "表空間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?
???? D .TABLESPACE_NAME = F.TABLESPACE_NAME?
ORDER BY 1?
--查詢表空間? ?? ???
tablespace_name ??,?
count(*)? ????,
round(sum(bytes) / 1024 / 1024, 2) MB?,
??(??)? ????
dba_free_space??
group by tablespace_name;?
--查詢表????
tablespace_name, ??(???) / 1024/1024? MB? ??
dba_data_files??
tablespace_name?? ???;?
--查詢表空間使用率?
total.tablespace_name? ????,?
round(total.MB, 2)? Total_MB?,?
round(total.MB - free.MB, 2)? 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 MB
dba_data_files??
tablespace_name?? ???) total
??? free.tablespace_name = total.tablespace_name;?
//給用戶授予權限?
??? ??? ??, ???? ?????.?
//以后以該用戶登錄，創(chuàng)建的任何數(shù)據庫對象屬于user_temp ?user_data表空間，?
指定表空間了?
撤權：?
權限...? 用戶name?? ????;?
刪除用戶命令?
??? user_name ??? ??;?
刪除表空間?
???? ??? ??? ???? TABLESPACE data01? ?????.? 2、建立UNDO表間?
CREATE UNDO TABLESPACE UNDOTBS02?
DATA FILE '/oracle/oradata/db/UNDOTBS02. DBF' ?? 50M
#????: OPEN狀態(tài)下某些時刻只能用一個UNDO表空間,如果要用新建的表空間,必須切換到該表間:
ALTER SYSTEM SET undo_tablespace =UNDOTBS02;?ALTER TABLESPACE ?? ????;?
如果是意外刪除了數(shù)據文件，則必須帶有RECOVER選項?
??? ?? ??????? ??? ?????? ?????.?
2.使表空間聯(lián)機?
ALTER TABLESPACE ?? ???;?
3.使數(shù)據文件脫機?
?????? ????? 3? ?????? ?????.?
4.使數(shù)據文件聯(lián)機?
????? ?????? ??? ?? 3 ??;?
5.使表空間只讀?
ALTER TABLESPACE ?? ?? ??;?
6.使表空間可讀寫?
ALTER TABLESPACE ?? ?? ??;?
五、刪除表空間?
???? ??? ??? ???? ??????? ???01? ?????.?dba_data_files??
tablespace_name?? ??;?
1.增加數(shù)據文件?
ALTER TABLESPACE ???
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;?
2.手動增加數(shù)據文件尺寸?
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'?
??? 4000M? ?????.?FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C?
???? A. TABLESPACE_NAME=B.TABLESPACE_NAME ? A.TABLESPACE_NAME=C.TABLESPACE

? ?? Oracle 表空間查詢與操???關文章請關注PHP中文網!