Oracle日常维护操作

目 录
1 数据库检查 6
1.1 数据库备份检查 6
1.2 操作系统检查 6
1.2.1 操作系统资源使用检查 6
1.2.2 操作系统日志检查 6
1.3 listener的状态检查 6
1.4 实例检查 6
1.5 数据库信息检查 7
1.5.1 数据库属性 7
1.5.2 日志信息 7
1.5.3 表空间 8
1.5.4 数据库对象信息 10
1.5.5 作业的运行状况 16
1.6 数据库使用信息 16
1.7 数据库的性能信息 17
1.7.1 共享内存的命中率 17
1.7.2 等待事件信息 21
2 数据库常用操作 22
2.1 日志维护 22
2.1.1 强制日志文件切换 22
2.1.2 强制checkpoints 22
2.1.3 添加日志组 22
2.1.4 添加日志文件到日志组 22
2.1.5 修改日志文件的名称 22
2.1.6 删除日志组 22
2.1.7 删除日志文件 22
2.1.8 清除日志文件内容 23
2.1.9 使用logminer分析日志文件 23
2.1.10 日志操作语句语法 23
2.1.11 相关视图 23
2.1.12 相关参数 24
2.1.13 14. 归档日志文件名格式 24
2.1.14 15. 查询数据库日志模式 24
2.1.15 16. nologging的操作： 24
2.1.16 when successfully archived: 24
2.1.17 归档进程是否启动 25
2.2 表空间维护 25
2.2.1 建立表空间 25
2.2.2 建立本地管理表空间 25
2.2.3 临时表空间 25
2.2.4 更改表空间的online/offline状态 25
2.2.5 更表空间的读写状态 26
2.2.6 删除表空间 26
2.2.7 数据文件自动增长 26
2.2.8 增大表空间的容量 26
2.2.9 移动数据文件 27
2.2.10 迁移字典管理表空间为本地管理表空间 27
2.2.11 建立undo表空间 27
2.2.12 临时表空间的限制 27
2.2.13 默认临时表空间的限制 27
2.2.14 修改存储参数 28
2.2.15 使用OMF管理表空间 28
2.2.16 相关视图 28
2.3 表维护 29
2.3.1 建立表 29
2.3.2 复制表 29
2.3.3 建立临时表 29
2.3.4 pctfree 和pctused 含义 29
2.3.5 修改存储相关参数 29
2.3.6 分配extents 29
2.3.7 回收未使用的空间 30
2.3.8 Truncate表 30
2.3.9 删除表 30
2.3.10 删除column 30
2.3.11 标识列不可用 30
2.3.12 获取vsegment使用的block和extent 30
2.3.13 column重命名 31
2.3.14 相关视图 31
2.4 索引维护 31
2.4.1 建立函数索引 31
2.4.2 建立b树索引 31
2.4.3 pctfree含义 31
2.4.4 建立reverse key索引 31
2.4.5 建立位图索引 31
2.4.6 修改索引的存储参数 32
2.4.7 回收未使用的空间 32
2.4.8 索引重建 32
2.4.9 索引接合 32
2.4.10 确认索引的有效性 32
2.4.11 删除索引 32
2.4.12 确认未使用过的索引 33
2.4.13 相关视图 33
2.5 约束维护 33
2.5.1 设置会话约束性质 33
2.5.2 删除约束 33
2.5.3 定义约束 33
2.5.4 enable约束 34
2.5.5 immediate/ deferred 34
2.5.6 约束修改语法 34
2.5.7 约束重命名 35
2.5.8 exceptions table的使用 35
2.5.9 获取约束的信息 36
2.5.10 约束使用已有的索引 36
2.6 用户管理 37
2.6.1 建立用户 37
2.6.2 修改用户信息 37
2.6.3 删除用户 38
2.6.4 相关视图 38
2.7 权限管理 38
2.7.1 授予系统权限 38
2.7.2 sysdba和sysoper 的权限范围 38
2.7.3 限制对数据字典的访问 38
2.7.4 回收系统权限 38
2.7.5 授予对象权限 39
2.7.6 回收对象的权限 39
2.7.7 相关视图 39


1 数据库检查
1.1 数据库备份检查
检查上次数据库的备份脚本的输入日志。
1.2 操作系统检查
检查操作系统，确认操作系统运行正常。
1.2.1 操作系统资源使用检查
1.2.1.1 CPU、内存、IO检查
使用top、vmstat、iostat等工具确认cpu、memory、io的使用以及运行队列里等待的进程数。
top命令的load average显示的是过去1分钟、5分钟、15分钟内的平均在进程运行队列里运行的进程数，如果大于cpu的个数以为着系统CPU资源紧缺；需要注意cpu的使用分布，如果是user mode使用特别高，需要查看是什么进程消耗了这些cpu资源；如果system mode使用特别高，说明系统的资源调用消耗过多cpu，一般情况下小于10%，如果大于10%很有可能磁盘io读写频繁所致。
1.2.1.2 磁盘空间检测
使用df -k命令确认可用磁盘的空间；
使用命令du –sh，统计一个目录下文件大小的总和。
1.2.2 操作系统日志检查
检查操作系统的日志信息，确认系统没有异常。系统日志的查看方法视操作系统而定。
1.3 listener的状态检查
使用命令lsnrctl status确认listener已启动，并使用tnsping tns_name，确认能够连接到listener。
1.4 实例检查
1.4.1.1 alert日志文件检查
检查数据库的alert日志文件，确认实例运行正常，如果有错误日志，查看dump文件，诊断定位原因。
1.4.1.2 进程检查
使用ps | grep ora检查实例的后台进程
1.4.1.3 共享内存段检查
使用ipcs查看共享内存段的信息，确认无异常。
1.4.1.4 实例的状态信息
select instance_number, instance_name, host_name, version,
startup_time, status, archiver, shutdown_pending, database_status,
instance_role, active_state from v$instance;
1.5 数据库信息检查
1.5.1 数据库属性
包括临时表空间、字符集、GLOBAL_DB_NAME、时区等信息：
col property_value format a20;
col property_value format a20;
col description format a40;
select * from database_properties;
1.5.2 日志信息
是否处于归档模式查询：
select log_mode from v$database;
或者
archive log list;
日志组以及成员信息：
col member format a100;
select * from v$logfile;
日志文件的状态、是否归档、开始时间、CHANGE#等信息：
select * from v$log;
系统日志的历史使用信息：
select * from v$log_history;
1.5.3 表空间
1.5.3.1 表空间信息
表空间的属性：
column force_logging head 'FORCE|LOGGING';
column extent_management head 'EXTENT|MANAGEMENT';
column allocation_type head 'ALLOCATION|TYPE';
column plugged_in head 'PLUGGED|IN';
column segment_space_management head 'SEGMENT|SPACE|MANAGEMNET';
column def_tab_compression head 'DEFAULT|TAB|COMPRESSION';
select tablespace_name, block_size, initial_extent, next_extent,
min_extents, max_extents, pct_increase,min_extlen
from dba_tablespaces
/
-- 注意表空间的status，确认都是online
select tablespace_name,status,contents,logging,
rpad(force_logging,4,' ') force_logging, extent_management,
allocation_type,rpad(plugged_in, 4, ' ') plugged_in,
segment_space_management,def_tab_compression
from dba_tablespaces
/
表空间的空间使用信息：
select tablespace_name, max_m,
count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') || '%' as pct_free
from
( select tablespace_name,sum(bytes)/1024/1024 as sum_m
from dba_data_files group by tablespace_name),
( select tablespace_name as fs_ts_name,
max(bytes)/1024/1024 as max_m, count(blocks) as count_blocks,
sum(bytes/1024/1024) as sum_free_m from dba_free_space group by tablespace_name )
where tablespace_name = fs_ts_name
/
1.5.3.2 数据文件信息
数据文件的空间使用信息：
column tablespace_name format a16;
column file_id format 99;
select a.tablespace_name, a.file_id, a.autoextensible, a.max_mbytes,
a.total_mbytes, a.total_mbytes - nvl (b.free_mbytes, 0) used_mbytes,
nvl (b.free_mbytes, 0) free_mbytes,
trunc (nvl (b.free_mbytes, 0) / a.total_mbytes * 100, 2) pct_free
from (select file_id, tablespace_name, autoextensible,
maxbytes / 1024 / 1024 max_mbytes,
bytes / 1024 / 1024 total_mbytes
from dba_data_files) a,
(select file_id, sum (bytes) / 1024 / 1024 free_mbytes
from dba_free_space
group by file_id) b
where a.file_id = b.file_id(+)
/
数据文件的存放路径信息：
column file_name format a60;
select tablespace_name, file_id, file_name from dba_data_files
/
临时表空间信息：
col file_name format a40
col tablespace_name format a15
col autoextensible format a3
col autoextensible format 99999999
select tablespace_name,file_id,file_name,bytes/1024/1024 Mbytes,
blocks,maxbytes/1024/1024 MmaxBytes,maxblocks, autoextensible,
increment_by, status
from dba_temp_files
/
col PCT_FREE format 99.99
prompt space usage:
select TABLESPACE_NAME, BYTES_USED/1024/1024 AS "BYTES USED(MB)", BYTES_FREE/1024/1024 AS "BYTES FREE(MB)",
BYTES_FREE*100/(BYTES_USED+BYTES_FREE) PCT_FREE
from V$TEMP_SPACE_HEADER;

undo表空间信息：
col value for a20;
select name, value from v$parameter where name = 'undo_tablespace'
/
1.5.4 数据库对象信息
1.5.4.1 Segment的分布信息
Segment的分布概况：
prompt report indexes and tables that exist for each user for all tablespaces.

set pagesize 60 echo off
spool tfstsusr.lst
column tablespace_name format a20
column owner format a20
column objects format a20
break on tablespace_name on owner
select substr(tablespace_name,1,32) tablespace_name,
substr(owner,1,20) owner,
count(*)||decode(count(*),1,' table',' tables') objects
from sys.dba_tables
group by substr(tablespace_name,1,32) , substr(owner,1,20)
union
select substr(tablespace_name,1,32) tablespace_name,
substr(owner,1,20) owner,
count(*)||decode(count(*),1,' index',' indexes') objects
from sys.dba_indexes
group by substr(tablespace_name,1,32) , substr(owner,1,20)
/
spool off
set echo on

表空间存放的segment信息：
col owner format a10
col segment_name heading 'segment|name' format a30
col tablespace_name heading 'tablespace|name' format a20
col partition_name heading 'partition|name' format a15
col Mbytes format 99999999
col blocks format 99999999
col initial_extent heading 'initial|extent'
select owner,segment_name,tablespace_name,bytes/1024/1024 Mbytes,blocks,extents,initial_extent
from dba_segments
where tablespace_name = upper('&tbs_name')
order by 4 desc
/
1.5.4.2 查找占有大量空间的segment
prompt the Largest Segments in the Database


SET ECHO OFF
set newpage 0
ttitle cen 'Report of the Largest Segments in the Database' -
right 'Page:' format 999 sql.pno skip skip
col ow format a18 heading 'Owner'
col ty format a13 heading 'Type'
col na format a30 heading 'Name'
col K format 999,999,999 heading 'Size K'
accept min_K prompt 'Minimum Segment Size to print in K [default=100] '
set verify off
set termout off
column bls new_value BLOCK_SIZE
select blocksize bls
from sys.ts$
where name='SYSTEM'
/
set termout on
set verify on
set feedback off
btitle off
column nline newline
set pagesize 54
set linesize 78
set heading off
set embedded off
set verify off
accept report_comment char prompt 'Enter a comment to identify system: '
select 'Date - '||to_char(sysdate,'Day Ddth Month YYYY HH24:MI:SS'),
'At - '||'&&report_comment' nline,
'Username - '||USER nline
from sys.dual
/
prompt
set embedded on

set feedback 6
set heading off
select 'Minimum sized segment printed = '||
to_char(decode('&&min_K',null,100,to_number('&&min_K')))||' K'
from sys.dual
/
set heading on
select us.name ow,
'Table' ty,
obj.name na,
seg.blocks*&&BLOCK_SIZE/1024 K
from sys.user$ us,
sys.obj$ obj,
sys.seg$ seg,
sys.tab$ tab
where seg.blocks*&&BLOCK_SIZE/1024 >=
decode('&&min_K',null,100,to_number('&&min_K'))
and us.user# = seg.user#
and obj.obj# = tab.obj#
and tab.file# = seg.file#
and tab.block# = seg.block#
union
select us.name ow,
'Tbl Partition' ty,
obj.name na,
seg.blocks*&&BLOCK_SIZE/1024 K
from sys.user$ us,
sys.obj$ obj,
sys.seg$ seg,
sys.tabpart$ ptab
where seg.blocks*&&BLOCK_SIZE/1024 >=
decode('&&min_K',null,100,to_number('&&min_K'))
and us.user# = seg.user#
and obj.obj# = ptab.obj#
and ptab.file# = seg.file#
and ptab.block# = seg.block#
union
select us.name ow,
'Index' ty,
obj.name na,
seg.blocks*&&BLOCK_SIZE/1024 K
from sys.user$ us,
sys.obj$ obj,
sys.seg$ seg,
sys.ind$ ind
where seg.blocks*&&BLOCK_SIZE/1024 >=
decode('&&min_K',null,100,to_number('&&min_K'))
and us.user# = seg.user#
and obj.obj# = ind.obj#
and ind.file# = seg.file#
and ind.block# = seg.block#
union
select us.name ow,
'Idx Partition' ty,
obj.name na,
seg.blocks*&&BLOCK_SIZE/1024 K
from sys.user$ us,
sys.obj$ obj,
sys.seg$ seg,
sys.indpart$ pind
where seg.blocks*&&BLOCK_SIZE/1024 >=
decode('&&min_K',null,100,to_number('&&min_K'))
and us.user# = seg.user#
and obj.obj# = pind.obj#
and pind.file# = seg.file#
and pind.block# = seg.block#
union
select us.name ow,
'Rollback' ty,
undo.name na,
seg.blocks*&&BLOCK_SIZE/1024 K
from sys.user$ us,
sys.seg$ seg,
sys.undo$ undo
where seg.blocks*&&BLOCK_SIZE/1024 >=
decode('&&min_K',null,100,to_number('&&min_K'))
and us.user# = undo.user#
and undo.file# = seg.file#
and undo.block# = seg.block#
union
select us.name ow,
'Cluster' ty,
obj.name na,
seg.blocks*&&BLOCK_SIZE/1024 K
from sys.user$ us,
sys.obj$ obj,
sys.seg$ seg,
sys.clu$ clu
where seg.blocks*&&BLOCK_SIZE/1024 >=
decode('&&min_K',null,100,to_number('&&min_K'))
and us.user# = seg.user#
and obj.obj# = clu.obj#
and clu.file# = seg.file#
and clu.block# = seg.block#
order by 4 desc
/
prompt End of Report
spool off
ttitle off
clear breaks
clear columns
clear computes
set verify on
1.5.4.3 数据库对象的状态
数据的对象的有效性检查：
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status=’INVALID’

确认索引的状态：
select OWNER, INDEX_NAME, INDEX_TYPE, STATUS
from dba_indexes where status not in ('VALID', 'N/A');

trigger是否都处于enable状态：
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED’

约束是否都处于enable状态：
SELECT owner, constraint_name, table_name,
constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLED'

1.5.5 作业的运行状况
col log_user format a15
col job format 999999
col last_date format a20
col next_date format a20
col interval format a25
col broken format a5
col failures format 999999
col total_time heading 'total|time' format 99999999
col what format a120
select job,log_user,to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date,
to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date,interval,broken,failures,total_time,what
from dba_jobs
/
1.6 数据库使用信息
session信息：
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address
-- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
-- ACTVE TRANSACTION ON THAT MOMENT */
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece
/
1.7 数据库的性能信息
1.7.1 共享内存的命中率
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
DECLARE
v_value NUMBER;

FUNCTION Format(p_value IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';
END;

BEGIN

-- --------------------------
-- Dictionary Cache Hit Ratio
-- --------------------------
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
INTO v_value
FROM v$rowcache;

DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));
IF v_value < 90 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;

-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
INTO v_value
FROM v$librarycache;

DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));
IF v_value < 99 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;

-- -------------------------------
-- DB Block Buffer Cache Hit Ratio
-- -------------------------------
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
INTO v_value
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';

DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));
IF v_value < 89 THEN
DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;

-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
INTO v_value
FROM v$latch;

DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));
IF v_value < 98 THEN
DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;

-- -----------------------
-- Disk Sort Ratio
-- -----------------------
SELECT (disk.value/mem.value) * 100
INTO v_value
FROM v$sysstat disk,
v$sysstat mem
WHERE disk.name = 'sorts (disk)'
AND mem.name = 'sorts (memory)';

DBMS_Output.Put('Disk Sort Ratio : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;

-- ----------------------
-- Rollback Segment Waits
-- ----------------------
SELECT (Sum(waits) / Sum(gets)) * 100
INTO v_value
FROM v$rollstat;

DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;

-- -------------------
-- Dispatcher Workload
-- -------------------
SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
INTO v_value
FROM v$dispatcher;

DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));
IF v_value > 50 THEN
DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;

END;
/

PROMPT
SET FEEDBACK ON
1.7.2 等待事件信息
set pagesize 25
col event heading 'event name' format a30
col total_waits heading 'total|waits'
col total_timeouts heading ' total |timouts'
col time_waited heading ' time |waited'
col average_wait heading 'average| wait '
col time_waited_micro heading ' time waited | micro seconds'
select event,total_waits,total_timeouts,time_waited,average_wait,time_waited_micro
from (
select event,total_waits,total_timeouts,time_waited,average_wait,time_waited_micro
from v$system_event a
where event not in (select event from perfstat.stats$idle_event)
order by time_waited desc
)
where rownum <= &lines
/
2 数据库常用操作
2.1 日志维护
2.1.1 强制日志文件切换
sql> alter system switch logfile;
2.1.2 强制checkpoints
sql> alter system checkpoint;
2.1.3 添加日志组
sql> alter database add logfile group 4
sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;
2.1.4 添加日志文件到日志组
sql> alter database add logfile member
sql> '/disk3/log1b.rdo' to group 1,
sql> '/disk4/log2b.rdo' to group 2;
2.1.5 修改日志文件的名称
sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'
sql> to 'c:/oracle/oradata/redo01.log';
2.1.6 删除日志组
sql> alter database drop logfile group 3;
2.1.7 删除日志文件
sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log';
2.1.8 清除日志文件内容
sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo';
2.1.9 使用logminer分析日志文件
a. in the init.ora specify utl_file_dir = ' c:\oracle\oradb\log'
b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log');
c. [optional:excute dbms_logmnr.revovefile to remove file]
sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log', dbms_logmnr.new);
d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',
dbms_logmnr.addfile);
e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');
f. sql> select *
sql> from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters,v$logmnr_logs);
[pay attention to sqlredo/sqlundo columns]
g. sql> execute dbms_logmnr.end_logmnr;
2.1.10 日志操作语句语法
alter database [database] add logfile [group integer] filespec [, [group integer] filespec]...]

alter database drop logfile {group integer|(’filename’[,’filename’]...)}
[,{group integer|(’filename’[,’filename’]...)}]...

alter database [database] add logfile member
[ ’filename’ [reuse] [, ’filename’ [reuse]]... to {group integer
|(’filename’[, ’filename’]...) } ]...
2.1.11 相关视图
v$log, v$logfile v$log_history
2.1.12 相关参数
fast_start_mttr_target
log_checkpoint_timeout,
log_checkpoint_interval
log_archive_start
2.1.13 14. 归档日志文件名格式
归档日志文件名格式由初始化参数log_archive_format设置，默认是arc%s_%r.%t。文件名中可以使用以下宏变量：
%s %s 日志序号
%t %t 线程号
%r 重置日志id（用于高级恢复功能）
%d 数据库的id
2.1.14 15. 查询数据库日志模式
sql>select log_mode from v$database;
sql>archive log list
2.1.15 16. nologging的操作：
create table … nologging as select 语句
insert /*+append*/ into <表> nologging select语句
insert /*+ parallel(<表>,<n>)达式*/ into <表> nologging select语句
sql*loader的direct方法
2.1.16 when successfully archived:
an entry in the control file is made
records: archive log name, log sequence number, and
high and low system change number (scn)
2.1.17 归档进程是否启动
sql> select archiver from v$instance;
2.2 表空间维护
2.2.1 建立表空间
sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m,
sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k
sql> [logging/nologging] extent management dictionary
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary]
sql> [extent_management_clause][segment_management_clause]
sql>extent_namagement_clause: extent management [dictionary | local
sql> [ autoallocate | uniform [ size integer [k|m] ] ] ]
sql> segment_management_clause: segment space management auto
2.2.2 建立本地管理表空间
sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf'
sql> size 500m extent management local uniform size 10m;
2.2.3 临时表空间
sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf'
sql> size 500m extent management local uniform size 10m;
alter database default temporary tablespace temp;
alter database default temporary tablespace default_temp2;
to find the default temporary tablespace for the database query database_properties:
select * from database_properties;
2.2.4 更改表空间的online/offline状态
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;
alter tablespace tablespace {online |offline [normal|temporary|immediate|for recover]}
2.2.5 更表空间的读写状态
sql> alter tablespace app_data read only|write;

– causes a checkpoint
– data available only for read operations
– objects can be dropped from tablespace
2.2.6 删除表空间
sql> drop tablespace app_data including contents and datafile [cascade constraints];
including contents: delete segments
datafile: delete datafile
2.2.7 数据文件自动增长
sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m
sql> autoextend on next 10m maxsize 500m;
2.2.8 增大表空间的容量
增加数据文件的大小：
sql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m;
使数据文件自动增长：
sql> create tablespace userdata02 datafile '/u01/oradata/userdata02.dbf' size 5m
autoextend on next 2m maxsize 200m;
sql> alter database
datafile '/u01/oradata/userdata02.dbf'autoextend on next 2m;
添加数据文件：
sql> alter tablespace userdata02
sql>add datafile '/u01/oradata/userdata03.dbf'size 5m;
2.2.9 移动数据文件
sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf'
sql> to 'c:\oracle\app_data.dbf';

sql> alter database rename file 'c:\oracle\oradata\app_data.dbf'
sql> to 'c:\oracle\app_data.dbf';
2.2.10 迁移字典管理表空间为本地管理表空间
dbms_space_admin.tablespace_migrate_to_local('system');
2.2.11 建立undo表空间
create undo tablespace undo01 datafile 'path_to_file' size 40m
2.2.12 临时表空间的限制
tempfiles are always set to the nologging mode.
you cannot make a tempfile read-only.
you cannot rename a temp file.
you cannot create a tempfile with the alter database command.
temp files are required for read-only databases.
media recovery does not recover tempfiles. --10g可以了
to optimize the performance of a sort in a temporary tablespace, set the uniform size to be
a multiple of the parameter sort_area_size.
2.2.13 默认临时表空间的限制
default temporary tablespaces cannot be:
dropped until after a new default is made available
taken offline
altered to a permanent tablespace
2.2.14 修改存储参数
using alter tablespace command to change storage settings:
alter tablespace userdata minimum extent 2m;
alter tablespace userdata default storage (initial 2m next 2m maxextents 999);
storage settings for locally managed tablespaces cannot be altered.
2.2.15 使用OMF管理表空间
define the db_create_file_dest parameter in one of the following ways:
initialization parameter file
set dynamically using alter system command
alter system set db_create_file_dest = ’/u01/oradata/dba01’;
when creating the tablespace:
data file is automatically created and located in db_create_file_dest
default size is 100 mb
autoextend is set to unlimited
creating an omf tablespace:
create tablespace text_data datafile size 20m;
adding an omf data file to an existing tablespace:
alter tablespace text_data add datafile;
dynamically changing default file location:
alter system set db_create_file_dest = ’/u01/oradata/dba01’;
dropping a tablespace includes deleting os files
2.2.16 相关视图
dba_tablespaces
v$tablespace
dba_data_files
v$datafile
dba_temp_files
v$tempfile
2.3 表维护
2.3.1 建立表
sql> create table table_name (column datatype,column datatype)....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache] [blocksize nk]
2.3.2 复制表
sql> create table table_name [logging|nologging] as subquery
2.3.3 建立临时表
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
on commit delete rows: to specify that rows are only visible within the
transaction. this is the default.
on commit preserve rows: to specify that rows are visible for the entire session.
2.3.4 pctfree 和pctused 含义
pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
2.3.5 修改存储相关参数
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);
2.3.6 分配extents
sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');
move/reorganize tablespace
sql> alter table employee move tablespace users;
2.3.7 回收未使用的空间
sql> alter table table_name deallocate unused [keep integer]
2.3.8 Truncate表
sql> truncate table table_name;
2.3.9 删除表
sql> drop table table_name [cascade constraints];
2.3.10 删除column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
2.3.11 标识列不可用
alter table table_name set unused column comments cascade constraints
alter table table_name drop unused columns checkpoint 1000
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs
2.3.12 获取vsegment使用的block和extent
select segment_name, tablespace_name, extents, blocks
from dba_segments where owner = 'hr';
select extent_id, file_id, block_id, blocks from dba_extents
where owner='hr' and segment_name='employees';
select tablespace_name, count(*), max(blocks), sum(blocks)
from dba_free_space group by tablespace_name;
2.3.13 column重命名
alter table hr.employees rename column hire_date to start_date;
2.3.14 相关视图
dba_tables,
dba_objects

2.4 索引维护
2.4.1 建立函数索引
sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);
2.4.2 建立b树索引
sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql> maxextents 50);
2.4.3 pctfree含义
pctfree(index) = (maximum number of rows-initial number of rows)*100/maximum number of rows
2.4.4 建立reverse key索引
sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql> next 200k pctincrease 0 maxextents 50) tablespace indx;
2.4.5 建立位图索引
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql> pctincrease 0 maxextents 50) tablespace indx;
create_bitmap_area_size
2.4.6 修改索引的存储参数
sql> alter index xay_id storage (next 400k maxextents 100);
分配extent
sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');
2.4.7 回收未使用的空间
alter index xay_id deallocate unused;
index space is deallocated when the table is truncated. truncating a table results in truncation of the associated index
2.4.8 索引重建
alter index index_name rebuild [ tablespace tablespace ] [ pctfree integer ] [ initrans integer ] [ maxtrans integer ]
[ storage-clause ] [ logging| nologging ] [ reverse | noreverse ] ;
alter index name rebuild online;
2.4.9 索引接合
alter index orders_id_index coalesce;
2.4.10 确认索引的有效性
analyze index name validate structure;
query index_stats to obtain information.
reorganize the index if it has a high proportion of deleted rows, for example, when the
ratio of del_lf_rows to lf_rows exceeds 30%.
2.4.11 删除索引
drop index name;
2.4.12 确认未使用过的索引
alter index name monitering/nomonitering usage; query v$object_usage to obtain information.
2.4.13 相关视图
dba_indexes
dba_ind_columns
dba_ind_expressions provide information on function based indexes;

2.5 约束维护
2.5.1 设置会话约束性质
alter session set constraint[s] = immediate/deferred/default;
set constraint[s] constraint_name/all immediate/deferred;
2.5.2 删除约束
sql> drop table table_name cascade constraints
sql> drop tablespace tablespace_name including contents cascade constraints
sql> alter table name disable constraint fk_name
2.5.3 定义约束
sql> create table xay(id number(7) constraint xay_id primary key deferrable
sql> using index storage(initial 100k next 100k) tablespace indx);
primary key/unique/references table(column)/check
alter table name add constraint name primary key(key_name);
alter table name add constraint name unique(name1, name2);
alter table name add constraint pk_name primary key(name);
alter table name add constraint fk_name foreign key(name) references table(column);
alter table name add constraint ck_name check (column_name like '%sd') enable novalidate;
alter table name add constraint name … initially defered/immediate;
[constraint constraint]
{[not] null | unique [using index index_clause] | primary key
[using index index_clause]|references [schema.]table [(column)]
[on delete cascade] |check (condition)}
constraint_state :== [not deferrable|deferrable
[initially {immediate|deferred}]] [disable|enable [validate|novalidate]]
2.5.4 enable约束
sql> alter table xay enable novalidate constraint xay_id;
sql> alter table xay enable validate constraint xay_id;

2.5.5 immediate/ deferred
a constraint that is defined as deferrable can be specified as one of the following:
initially immediate specifies that by default it should function as an immediate
constraint, unless explicitly set otherwise.
initially deferred specifies that by default the constraint should be enforced only at the
end of the transaction.
defining a constraint from the type not null after creating a table is possible only with:
alter table name modify clumn constraint constraint not null;
if a table contains a self-referencing foreign key, use one of the following methods to load data:
- define or enable the foreign key after the initial load.
- define the constraint as a deferrable constraint.
- nullallowable
2.5.6 约束修改语法
alter table [ schema. ] table enable [ validate ]{constraint constraint | primary key| unique ( column [, column ] ... )} [ using index index_clause ] [ exceptions into [ schema. ] table ]
2.5.7 约束重命名
alter table name rename constraint name to new_name;
2.5.8 exceptions table的使用
a. create exceptions table: @?/rdbms/admin/utlexp.sql
b. execute the alter table command using the exceptions clause:
alter table hr.employee
enable validate constraint employee_dept_id_fk
exceptions into system.exceptions;
alter table hr.employee
*
ora-02298: cannot enable (hr.employee_dept_id_fk) -
parent keys not found
if the exceptions table is not qualified with the name of the owner, it must belong to the owner of the table that is being altered.
rows are inserted into the exceptions table. if you are rerunning the command, truncate the exceptions table to remove all existing rows.
c. identify invalid data by using a subquery on the exceptions table:
sql> select rowid, id, last_name, dept_id
2 from hr.employee
3 where rowid in (select row_id
4 from exceptions)
5 for update;
rowid id last_name dept_id
------------------ ---- --------- -------
aaaaeyaadaaaaa1aaa 1003 pirie 50
1 row selected.
d. correct the errors in the data:
sql> update hr.employee
2 set dept_id=10
3 where rowid='aaaaeyaadaaaaa1aaa';
1 row processed.
e. truncate the exceptions table and reenable the constraint:
sql> truncate table exceptions;
statement processed.
sql> alter table hr.employee
2 enable validate constraint employee_dept_id_fk
3 exceptions into system.exceptions;
2.5.9 获取约束的信息
dba_constraints/dba_cons_columns
select constraint_name, constraint_type, deferrable,deferred, validated
from dba_constraints where owner='hr' and table_name='employees;
select c.constraint_name, c.constraint_type, cc.column_name
from dba_constraints c, dba_cons_columns cc
where c.owner='hr' and c.table_name='employees'
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
select c.constraint_name as "foreign key",
p.constraint_name as "referenced key",p.constraint_type,
p.owner, p.table_name
from dba_constraints c, dba_constraints p
where c.owner='hr' and c.table_name='employees'
and c.constraint_type='r' and c.r_owner=p.owner
and c.r_constraint_name = p.constraint_name;
2.5.10 约束使用已有的索引
alter table [ schema. ] table enable novalidate
{constraint constraint | primary key | unique ( column [, column ] ... ) }
[ using index index_clause ]
the using index clause is applicable only for primary key or unique constraints that
were created as deferrable, and when one of the following is true:
the constraints were created disabled.
the constraints were disabled and the index was dropped.
2.6 用户管理
2.6.1 建立用户
sql> create user juncky identified by oracle default tablespace users
sql> temporary tablespace temp quota 10m on data password expire
sql> [account lock|unlock] [profile profilename|default];

create user user
identified {by password | externally | globally as
external name}
[ default tablespace tablespace ]
[ temporary tablespace tablespace ]
[ quota {integer [k | m ] | unlimited } on tablespace
[ quota {integer [k | m ] | unlimited } on tablespace
]...]
[ password expire ]
[ account { lock | unlock }]
[ profile { profile | default }]

2.6.2 修改用户信息
sql> alter user juncky quota 0 on users;

alter user user
[ default tablespace tablespace]
[ temporary tablespace tablespace]
[ quota {integer [k | m] | unlimited } on tablespace
[ quota {integer [k | m] | unlimited } on tablespace ]
...]

2.6.3 删除用户
sql> drop user juncky [cascade];
2.6.4 相关视图
dba_users
dba_ts_quotas

2.7 权限管理
2.7.1 授予系统权限
sql> grant create session,create table to managers;
sql> grant create session to scott with admin option;
with admin option can grant or revoke privilege from any user or role;
2.7.2 sysdba和sysoper 的权限范围
sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
alter tablespace begin/end backup,recover database
alter database archivelog,restricted session

sysdba: sysoper privileges with admin option,create database,recover database until
2.7.3 限制对数据字典的访问
o7_dictionary_accessibility =true
2.7.4 回收系统权限
sql> revoke create table from karen;
sql> revoke create session from scott;
2.7.5 授予对象权限
sql> grant execute on dbms_pipe to public;
sql> grant update(first_name,salary) on employee to karen with grant option;
2.7.6 回收对象的权限
sql> revoke execute on dbms_pipe from scott [cascade constraints];
2.7.7 相关视图
system privileges: view => system_privilege_map ,dba_sys_privs,session_privs
display object privilege : view => dba_tab_privs, dba_col_privs, dba_sys_privs, session_privs

2.8 数据库备份和恢复
2.8.1 recovery catalog建立
步骤一：建立表空间
sql> create tablespace rman_ts
2> datafile ’<directory>/<name>’
3> size 20m
4> extent management local uniform size 128k;
步骤二：建立用户
sql> create user rman_db01 identified by rman_db01
2> default tablespace rman_ts
3> quota unlimited on rman_ts;
步骤三：给用户授权
sql> grant recovery_catalog_owner to rman_db01;
sql> grant connect, resource to rman_db01;
步骤四：建立catalog
% rman catalog rman_db1/rman_db1@catdb log = catalog.log
create catalog tablespace rman_ts;
exit;
步骤五：注册目标数据库
% rman target sys/oracle@db01
connected to target database: db01 (dbid=472633597)
rman> connect catalog rman_db01/rman_db01@catdb
connected to recovery catalog database
rman> register database;
2.8.2 数据库备份
2.8.2.1 全备份脚本
#!/usr/bin/sh
set -x

# rman的format语句可能需要修改其中的”\”，有些系统需要”\”，有些系统不需要”\”

. ~/.profile
RMAN=/oracle/product/9.2.0/bin/rman
BACKUP_BASE=/backup/rman
TMS=`date +%y%m%d`
BACKUP_DIR=$BACKUP_BASE/$TMS

CATLOG="catalog rman/******@repdb"
TARGET="target /"


[ -d $BACKUP_DIR ] || mkdir $BACKUP_DIR
if [ $# -lt 2 -a $1 -ge 0 ]
then
LEVEL=$1
else
WEEKDAY=`date +%w`
if [ $WEEKDAY -eq 0 ]
then
LEVEL=0
elif [ `expr $WEEKDAY % 3` -lt 0 ]
then
LEVEL=2
else
LEVEL=1
fi
fi
echo "BACKUP LEVEL = ${LEVEL}"


DB_FILE_FORMAT="$BACKUP_DIR/level${LEVEL}_%d_%T_%s_%p.bak"
ARC_FORMAT="$BACKUP_DIR/arc_%d_%T_%s_%p.bak"

$RMAN << EOF
connect ${CATLOG}
connect $TARGET
run {
allocate channel c1 type disk maxpiecesize=2G;
allocate channel c2 type disk maxpiecesize=2G;
allocate channel c3 type disk maxpiecesize=2G;
allocate channel c4 type disk maxpiecesize=2G;
backup incremental level ${LEVEL} tag 'level${LEVEL}' database
filesperset 2
format \"${DB_FILE_FORMAT}\"
include current controlfile;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
sql 'alter system archive log current';
backup
format \"${ARC_FORMAT}\"
archivelog all delete input;
}
EOF

2.8.2.2 增量备份脚本
#!/usr/bin/sh
set –x

# rman的format语句可能需要修改其中的”\”，有些系统需要”\”，有些系统不需要”\”


. ~/.profile
BACKUP_BASE=/backup/rman
TMS=`date +%y%m%d`
BACKUP_DIR=$BACKUP_BASE/$TMS

CATLOG="catalog rman/******@repdb"
TARGET="target /"

[ -d $BACKUP_DIR ] || mkdir $BACKUP_DIR

DB_FILE_FORMAT="$BACKUP_DIR/full_%d_%T_%s_%p.bak"
ARC_FORMAT="$BACKUP_DIR/arc_%d_%T_%s_%p.bak"

rman << EOF
connect ${CATLOG}
connect $TARGET
run {
allocate channel c1 type disk maxpiecesize=2G;
allocate channel c2 type disk maxpiecesize=2G;
allocate channel c3 type disk maxpiecesize=2G;
allocate channel c4 type disk maxpiecesize=2G;
backup full database tag 'full'
filesperset 2
format \"${DB_FILE_FORMAT}\"
include current controlfile;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
sql 'alter system archive log current';
backup
format \"${ARC_FORMAT}\"
archivelog all delete input;
}
EOF