Oracle表空间管理与优化-优快云博客

-- ===============第 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