9i筆記-最佳化表空間

-- ===============第 7 章 最佳化表空間==================== --

[@more@]

-- =============== 檢視錶空間的使用情況 ================ --
select
free.tablespace_name,
data_files file_cnt,
ceil(tot.total) total_size,
ceil(free.free) free_space,
ceil(tot.total - free.free) used_space,
100 - ceil( (free.free / tot.total) *100) used_pct,
decode(
(ceil(10-(free.free / tot.total) * 10)),
0,'..........',
1,'+.........',
2,'++........',
3,'+++.......',
4,'++++......',
5,'+++++.....',
6,'++++++....',
7,'+++++++...',
8,'++++++++..',
9,'+++++++++.',
10,'++DANGER++'
) usage_charted
from (select tablespace_name,ceil(sum(bytes)/1048576) total,count(*) data_files
from dba_data_files
group by tablespace_name) tot,
(select tablespace_name,ceil(sum(bytes)/1048576) free
from dba_free_space
group by tablespace_name) free
where free.tablespace_name = tot.tablespace_name
order by used_pct
/

-- 下一個盤區報告 是否下一個盤區大於空閒的表空間,如果有返回結果,應該向表空間新增更多的空間
select s.owner,
s.segment_type,
s.segment_name,
s.tablespace_name,
s.next_extent / 1024 / 1024 next_extent,
ts.max_space / 1024 / 1024 max_space
from dba_segments s,
(select tablespace_name, max(bytes) max_space
from dba_free_space
group by tablespace_name) ts
where s.tablespace_name = ts.tablespace_name
and segment_type in
('TABLE', 'INDEX', 'CLUSTER', 'TABLE PARTITION', 'INDEX PARTITION')
and next_extent > max_space
/

-- 查詢某個表空間已使用的和空閒的盤區
select de.owner,
de.segment_name,
df.file_name,
de.block_id,
de.bytes / 1024 / 1024 size_Mb
from dba_extents de, dba_data_files df
where de.tablespace_name = 'USERS'
and de.file_id = df.file_id
union all
select 'FREE', null, null, null, bytes / 1024 / 1024
from dba_free_space
where tablespace_name = 'USERS'
/

-- 查詢資料庫的總容量
select a.df "Data Files Size in Mb",
b.tf "Temp Files Size in Mb",
c.lf "Log files Size in Mb"
from (select sum(bytes / (1024 * 1024)) df from dba_data_files) a,
(select sum(bytes / (1024 * 1024)) tf from dba_temp_files) b,
(select sum(bytes / (1024 * 1024)) lf from v$log) c

-- 查詢資料檔案使用情況
ttitle "Datafile Usage Report|Values are expressed in Mb"
column file_name format a50
set lines 120
set pages 9999

select file_name, total_size, used_size, total_size - used_size free_size
from (select file_id, file_name, bytes / (1024 * 1024) total_size
from dba_data_files) f,
(select file_id, ceil(sum(bytes) / (1024 * 1024)) used_size
from dba_free_space
group by file_id) e
where e.file_id = f.file_id ;

-- ==============資料檔案爭用的診斷 =====================--
-- reads_ratio不能超過20%
select round((select count(*)
from v$filestat
where phyrds >= (select avg(phyrds) from v$filestat)) /
(select count(*) from v$datafile) * 100) as reads_ratio
from dual ;

-- write_ratio的查詢
select round((select count(*)
from v$filestat
where phywrts >= (select avg(phywrts) from v$filestat)) /
(select count(*) from v$datafile) * 100) writes_ratio
from dual

-- 每個資料檔案總的物理寫與物理讀
select df.name file_name, fs.phyrds reads, fs.phywrts writes
from v$datafile df, v$filestat fs
where df.file# = fs.file#

-- ============== 建立外部表 =====================--
1 colors.txt 檔案內容
100,white
200, blue
300,black
400,green
500,yellow
600,brown
700,cyan
800,grey

2 建立目錄
SQL> create directory color_dir as 'C:oracleoradataorcl2';
SQL> grant write,read on directory color_dir to tuner;

3 建立外部表
create table colors( code number(3), desct varchar2(20))
organization external
(type oracle_loader
default directory color_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('colors.txt')
)
reject limit 0
/

4 查詢外部表
select * from volors;

5 以前做過的應用包括:網上在逃人員資訊 漢字拼音等等

-- ============== DBMS_SPACE_ADMIN =====================--
TABLESPACE_MIGRATE_TO_LOCAL
將字典管理的表空間DMT 轉換為 本地管理的表空間LMT
示例如下：

SQL> exec dbms_space_admin.tablespace_migrate_to_local('DD_TS');

PL/SQL procedure successfully completed.

SQL> select tablespace_name,extent_management from dba_tablespaces;

SYSTEM DICTIONARY
UNDOTBS1 LOCAL
TEMP LOCAL
INDX LOCAL
TOOLS LOCAL
USERS LOCAL
DD_TS LOCAL
LM_TS LOCAL

8 rows selected.

SQL> exec dbms_space_admin.tablespace_migrate_from_local('DD_TS');

PL/SQL procedure successfully completed.

SQL> select tablespace_name,extent_management from dba_tablespaces;

SYSTEM DICTIONARY
UNDOTBS1 LOCAL
TEMP LOCAL
INDX LOCAL
TOOLS LOCAL
USERS LOCAL
DD_TS DICTIONARY
LM_TS LOCAL

8 rows selected.

SQL>