Oracle DBA的日常工作(zt)

最新推荐文章于 2025-09-29 15:53:38 发布
转载 最新推荐文章于 2025-09-29 15:53:38 发布 · 85 阅读 · 0

作者： Thomas B. Cox, with Christine Choi
目的： 这篇文档有很详细的资料记录着对一个甚至更多的ORACLE数据库
每天的，每月的，每年的运行的状态的结果及检查的结果，在文档的附录中你将会看到所有检查，修改的SQL和PL/SQL代码。
修改笔记：1.1在Steve DeNunzio 的'existext.sql'中所识别的类型
1.2固定的类型
1.3 增加的Gnu Public License ；在重建index中增加pctincr 0
1.4
加了一个最新的观点，是从GEOCITIES的主页上得到的。 http://www.geocities.com/tbcox23


目录
1， 日常维护程序
A． 检查已起的所有实例
B． 查找一些新的警告日志
C． 检查DBSNMP是否在运行
D． 检查数据库备份是否正确
E． 检查备份到磁带中的文件是否正确
F． 检查数据库的性能是否正常合理，是否有足够的空间和资源
G． 将文档日志复制到备份的数据库中
H． 要常看DBA用户手册
2、晚间维护程序
A．收集 VOLUMETRIC 的数据
3、每周维护工作
A． 查找那些破坏规则的OBJECT
B． 查找是否有违反安全策略的问题
C． 查看错误地方的SQL*NET日志
D． 将所有的警告日志存档
E． 经常访问供应商的主页
4．月维护程序
A． 查看对数据库会产生危害的增长速度
B． 回顾以前数据库优化性能的调整
C． 查看I/O的屏颈问题
D． 回顾 FRAGMENTATION
E． 将来的执行计划
F． 查看调整点和维护
5 附录
A． 月维护过程
B． 晚间维护过程
C． 周维护过程
6 参考文献

一．日维护过程
A．查看所有的实例是否已起
确定数据库是可用的，把每个实例写入日志并且运行日报告或是运行测试文件。当然有一些操作我们是希望它能自动运行的。
可选择执行：用ORACLE管理器中的'PROBE'事件来查看


B。查找新的警告日志文件
1. 联接每一个操作管理系统
2. 使用'TELNET'或是可比较程序
3. 对每一个管理实例，经常的执行$ORACLE_BASE//bdump操作，并使其能回退到控制数据库的SID。
4.
在提示下，使用UNIX中的'TAIL'命令查看alert_.log，或是用其他方式检查文件中最近时期的警告日志
5.
如果以前出现过的一些ORA_ERRORS又出现，将它记录到数据库恢复日志中并且仔细的研究它们，这个数据库恢复日志在〈FILE〉中

C．查看DBSNMP的运行情况
1． 检查每个被管理机器的'DBSNMP'进程并将它们记录到日志中。
在UNIX中，在命令行中，键入 ps -ef | grep
dbsnmp,将回看到2个DBSNMP进程在运行。如果没有，重启DBSNMP。
D．查数据库备份是否成功


E．检查备份的磁带文档是否成功


F．检查对合理的性能来说是否有足够的资源
1. 检查在表空间中有没有剩余空间。
对每一个实例来说，检查在表空间中是否存在有剩余空间来满足当天的预期的需要。当数据库中已有的数据是稳定的，数据日增长的平均数也是可以计算出来，最小的剩余空间至少要能满足每天数据的增长。

A） 运行'FREE.SQL'来检查表空间的剩余空间。
B） 运行'SPACE.SQL'来检查表空间中的剩余空间百分率


2.检查回滚段
回滚段的状态一般是在线的，除了一些为复杂工作准备的专用段，它一般状态是离线的.
a) 每个数据库都有一个回滚段名字的列表。
b) 你可以用V$ROLLSTAT来查询在线或是离线的回滚段的现在状态.
c) 对于所有回滚段的存储参数及名字，可用DBA_ROLLBACK_SEGS来查询。但是它不如V$ROLLSTAT准确。


3.识别出一些过分的增长
查看数据库中超出资源或是增长速度过大的段，这些段的存储参数需要调整
a） 收集日数据大小的信息，可以用'ANALYZE5PCT.SQL'。如果你收集的是每晚的信息，则可跳过这一步。
b） 检查当前的范围，可用'NR.EXTENTS.SQL'。
c） 查询当前表的大小信息。
d） 查询当前索引大小的信息。
e） 查询增长趋势。


4.确定空间的范围。
如果范围空间对象的NEXT_EXTENT比表空间所能提供的最大范围还要大，那么这将影响数据库的运行。如果我们找到了这个目标，可以用'ALTER
TABLESPACE COALESCE'调查它的位置，或加另外的数据文件。
A）运行'SPACEBOUND.SQL'。如果都是正常的，将不返回任何行。
5. 回顾CPU，内存，网络，硬件资源论点的过程
A）检查CPU的利用情况，进到x:webphase2default.htm =>system
metrics=>CPU利用页，CPU的最大限度为400，当CPU的占用保持在350以上有一段时间的话，我们就需要查看及研究出现的问题。

G．将存档日志复制到备用数据库中
如果有一个备用数据库，将适当的存档日志复制到备用数据库的期望位置，备用数据库中保存最近期的数据。

F. 经常查阅DBA用户手册
如果有可能的话，要广泛的阅读，包括DBA手册，行业杂志，新闻组或是邮件列表。


二．晚间维护过程
大部分的数据库产品将受益于每晚确定的检查进程的运行。
A。收集VOLUMETRIC数据
1. 分析计划和收集数据
更准确的分析计算并保存结果，
a） 如果你现在没有作这些的话，用'MK VOLFACT.SQL'来创建测定体积的表。
b） 收集晚间数据大小的信息，用'ANALYZE COMP.SQL'。
c） 收集统计结果，用'POP VOL.SQL'。
d） 在空闲的时候检查数据，可能的话，每周或每个月进行。
我是用MS EXCEL和ODBC 的联接来检查数据和图表的增长


三．每周维护过程
A． 查找被破坏的目标


1.
对于每个给定表空间的对象来说，NEXT_EXTENT的大小是相同的，如12/14/98，缺省的NEXT_EXTENT的DATAHI为1G，DATALO为500MB，INDEXES为256MB。

A） 检查NEXT_EXTENT的设置，可用'NEXTEXT。SQL'。
B） 检查已有的EXTENTS，可用'EXISTEXT。SQL'。
2. 所有的表都应该有唯一的主键
a） 查看那些表没有主键，可用'NO_PK.SQL'。
b） 查找那些主键是没有发挥作用的，可用'DIS_PK.SQL'。
c） 所有作索引的主键都要是唯一的，可用'NONUPK。SQL'来检查。
3所有的索引都要放到索引表空间中。运行'MKREBUILD_IDX。SQL'
4 不同的环境之间的计划应该是同样的, 特别是测试环境和成品环境之间的计划应该相同。
d） 检查不同的2个运行环境中的数据类型是否一致，可用'DATATYPE.SQL'。
e） 在2个不同的实例中寻找对象的不同点，可用'OBJ_COORD.SQL'.
f） 更好的做法是，使用一种工具，象寻求软件的计划管理器那样的工具。


B． 查看是否有危害到安全策略的问题。


C． 查看报错的SQL*NET日志。
1. 客户端的日志。
2. 服务器端的日志。


D．.将所有的警告日志存档


E．.供应商的主页
1. ORACLE供应商
http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://www.oramag.com


2. Quest Software
http://www.quests.com


3. Sun Microsystems
http://www.sun.com


四．月维护过程
A．查看对数据库会产生危害的增长速度
1. 从以前的记录或报告中回顾段增长的变化以此来确定段增长带来危害


B． 回顾以前数据库优化性能的调整
1. 回顾一般ORACLE数据库的调整点，比较以前的报告来确定有害的发展趋势。


C． 查看I/O的屏颈问题
1. 查看前期数据库文件的活动性，比较以前的输出来判断有可能导致屏颈问题的趋势。


D． 回顾 FRAGMENTATION


E． 计划数据库将来的性能
1. 比较ORACLE和操作系统的CPU，内存，网络，及硬盘的利用率以此来确定在近期将会有的一些资源争夺的趋势
2. 当系统将超出范围时要把性能趋势当作服务水平的协议来看


F． 完成调整和维护工作
1.使修改满足避免系统资源的争夺的需要，这里面包括增加新资源或使预期的停工。


五、附录
A、 日常 程序
--
-- free.sql
--
-- To verify free space in tablespaces
-- Minimum amount of free space
-- document your thresholds:
-- = m
--

SELECT tablespace_name, sum ( blocks ) as free_blk , trunc (
sum ( bytes ) / (1024*1024) ) as free_m
, max ( bytes ) / (1024) as big_chunk_k, count (*) as
num_chunks
FROM dba_free_space
GROUP BY tablespace_name

1. Space.sql
--
-- space.sql
--
-- To check free, pct_free, and allocated space within a
tablespace
--
-- 11/24/98

SELECT tablespace_name, largest_free_chunk
, nr_free_chunks, sum_alloc_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') ||
'%'
AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
2. analyze5pct.sql
--
-- analyze5pct.sql
--
-- To analyze tables and indexes quickly, using a 5% sample
size
-- (do not use this script if you are performing the overnight

-- collection of volumetric data)
--
-- 11/30/98

BEGIN
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 )
;
END ;
/
3. nr_extents.sql
--
-- nr_extents.sql
--
-- To find out any object reaching
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
--
-- 11/30/98

SELECT e.owner, e.segment_type , e.segment_name , count(*) as
nr_extents , s.max_extents
, to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90')
as MB
FROM dba_extents e , dba_segments s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name ,
s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc

4. spacebound.sql
--
-- spacebound.sql
--
-- To identify space-bound objects. If all is well, no rows
are returned.
-- If any space-bound objects are found, look at value of NEXT
extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace coalesce.
-- Lastly, add another datafile to the tablespace if needed.
--
-- 11/30/98

SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk


B. 每晚处理程序

1. mk_volfact.sql
--
-- mk_volfact.sql (only run this once to set it up; do not run
it nightly!)
--
-- -- Table UTL_VOL_FACTS

CREATE TABLE utl_vol_facts
(
table_name VARCHAR2(30),
num_rows NUMBER,
meas_dt DATE
)
TABLESPACE platab
STORAGE (
INITIAL 128k
NEXT 128k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS unlimited
)
/

-- Public Synonym

CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts
/

-- Grants for UTL_VOL_FACTS

GRANT SELECT ON utl_vol_facts TO public
/

2. analyze_comp.sql
--
-- analyze_comp.sql
--
BEGIN
sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');
END ;
/
3. pop_vol.sql
--
-- pop_vol.sql
--
insert into utl_vol_facts
select table_name
, NVL ( num_rows, 0) as num_rows
, trunc ( last_analyzed ) as meas_dt
from all_tables -- or just user_tables
where owner in ('&OWNER') -- or a comma-separated list of
owners
/
commit
/

C. 每周处理程序

1. nextext.sql
--
-- nextext.sql
--
-- To find tables that don't match the tablespace default for
NEXT extent.
-- The implicit rule here is that every table in a given
tablespace should
-- use the exact same value for NEXT, which should also be the
tablespace's
-- default value for NEXT.
--
-- This tells us what the setting for NEXT is for these
objects today.
--
-- 11/30/98

SELECT segment_name, segment_type, ds.next_extent as
Actual_Next
, dt.tablespace_name, dt.next_extent as Default_Next
FROM dba_tablespaces dt, dba_segments ds
WHERE dt.tablespace_name = ds.tablespace_name
AND dt.next_extent !=ds.next_extent
AND ds.owner = UPPER ( '&OWNER' )
ORDER BY tablespace_name, segment_type, segment_name

2. existext.sql
--
-- existext.sql
--
-- To check existing extents
--
-- This tells us how many of each object's extents differ in
size from
-- the tablespace's default size. If this report shows a lot
of different
-- sized extents, your free space is likely to become
fragmented. If so,
-- this tablespace is a candidate for reorganizing.
--
-- 12/15/98

SELECT segment_name, segment_type
, count(*) as nr_exts
, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as
nr_illsized_exts
, dt.tablespace_name, dt.next_extent as dflt_ext_size
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dx.owner = '&OWNER'
GROUP BY segment_name, segment_type, dt.tablespace_name,
dt.next_extent


3. No_pk.sql
--
-- no_pk.sql
--
-- To find tables without PK constraint
--
-- 11/2/98

SELECT table_name
FROM all_tables
WHERE owner = '&OWNER'
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = '&&OWNER'
AND constraint_type = 'P'

4. disPK.sql
--
-- disPK.sql
--
-- To find out which primary keys are disabled
--
-- 11/30/98

SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = '&OWNER' AND status = 'DISABLED' AND
constraint_type = 'P'

5. nonuPK.sql
--
-- nonuPK.sql
--
-- To find tables with nonunique PK indexes. Requires that PK
names
-- follow a naming convention. An alternative query follows
that
-- does not have this requirement, but runs more slowly.
--
-- 11/2/98


SELECT index_name, table_name, uniqueness
FROM all_indexes
WHERE index_name like '&PKNAME%'
AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE'

SELECT c.constraint_name, i.tablespace_name, i.uniqueness
FROM all_constraints c , all_indexes i
WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness =
'NONUNIQUE'
AND c.constraint_type = 'P' AND i.index_name =
c.constraint_name

6. mkrebuild_idx.sql
--
-- mkrebuild_idx.sql
--
-- Rebuild indexes to have correct storage parameters
--
-- 11/2/98

SELECT 'alter index ' || index_name || ' rebuild '
, 'tablespace INDEXES storage '
|| ' ( initial 256 K next 256 K pctincrease 0 ) ; '
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = '&OWNER'
/

7. datatype.sql
--
-- datatype.sql
--
-- To check datatype consistency between two environments
--
-- 11/30/98


SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns -- first environment
WHERE owner = '&OWNER'
MINUS
SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns@&my_db_link -- second environment
WHERE owner = '&OWNER2'
order by table_name, column_name

8. obj_coord.sql
--
-- obj_coord.sql
--
-- To find out any difference in objects between two instances

--
-- 12/08/98

SELECT object_name, object_type
FROM user_objects
MINUS
SELECT object_name, object_type
FROM user_objects@&my_db_link

六、参考文献
1. Loney, Kevin Oracle8 DBA Handbook
2. Cook, David Database Management from Crisis to Confidence
[http://www.orapub.com/]
3. Cox, Thomas B. The Database Administration Maturity Model

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84664/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-84664/

cuixie2370
关注
【Oracle】Oracle故障处理-ORA-01578-Oracle坏块处理案例
tttzzzqqq2018的博客
09-20 2677
Oracle故障处理-ORA-01578-Oracle坏块处理案例
详细介绍 Oracle中的Materialized Views（物化视图/快照）
u011868279的博客
11-27 1649
详细介绍 Oracle中的Materialized Views（物化视图/快照）
云和恩墨oracle-dba的一天海报
12-14
云和恩墨oracle-dba的一天海报----。
一个生产库的DBA，你每天要做什么？
codetest1234的博客
11-13 231
目录 1， 日常维护程序 A． 检查已起的所有实例 B． 查找一些新的警告日志 C． 检查DBSNMP是否在运行 D． 检查数据库备份是否正确 E． 检查备份到磁带中的文件是否正确 F． 检查数据库的性能是否正常合理，是否有足够...
DBA的日常工作
congtang0640的博客
12-11 489
一、活动状态检查 通过查询基本视图，确认数据库和实例处于正常运行状态，可以对外提供数据服务。 1.1实例状态 ...
DBA 的一天
warden2010的专栏
10-03 681
<br />1、看日志/mail，AWR/Statspack报告<br /><br />2、检查机器/DB状况<br /><br />3、优化一个SQL，读懂一个存储过程<br /><br />4、追开发改程式<br /><br />5、处理乱七八糟需求<br /><br />6、物理设计优化（偶尔）<br /><br />7、充电<br /><br />8、下班<br /> <br />虽然离这个还很远，但我们可以从日常的工作中进行积累，并且有目的去学习一些东西，这样积累到一定程度量变可能就质变了。<br
dba的一天待细化
abby_db的博客
09-03 853
--查询返回实例名称，状态，正常状态应为open select instance_name,status from v$instance; --连接用户 select inst_id,username,count(*) from gv$session group by inst_id,username; --会话信息  实例当前会话数和启动最高连接会话数量 select sessions
oracle dbms调度程序,Oracle 调度程序作业( dbms_scheduler )(zt)
weixin_35835030的博客
04-09 576
整理scheduler这一部分,源于最近发现系统上自动采集统计信息的执行时间有些异常,执行时间被定义到了上午(这并不是一个很合理可靠的时间).在重新修改配置的同时,也顺便整理了这一块内容.首先简单讲一下oracle 10g scheduler,10g引入dbms_scheduler来替代先前的dbms_job,在功能方面,它比dbms_job提供了更强大的功能和更灵活的机制/管理.它主要由以下几大...
ORACLE 10G OCA 042 笔记(zt)
10-29
Oracle 10g OCA（Oracle Certified Associate）是Oracle公司为初学者提供的数据库管理认证，旨在验证个人在Oracle 10g数据库管理方面的基础知识和技术能力。这个笔记将深入探讨Oracle 10g的核心概念和操作，帮助理解...
故障处理：Oracle一体机磁盘故障时磁盘组重平衡失败的故障处理
最新发布
提供数据库技术支持(系统优化，故障处理，安装升级，数据恢复等）
09-29 1084
摘要： Oracle Exadata一体机出现磁盘故障案例，存储节点故障灯未亮但磁盘已产生大量坏块，导致重平衡失败。文章强调了一体机硬件巡检需检查6个关键维度：节点messages日志、存储cell告警日志、CellCLI日志、ASM告警日志、ILOM日志及服务器信号灯。故障分析显示硬盘物理故障导致磁盘组冗余受损，自动修复流程受阻。文中详细记录了通过CellCLI工具排查损坏磁盘的过程，并提供了完整的故障处理流程和命令输出。文章同步发布于微信公众号"IT民工的龙马人生"和博客网站(www
21天学习SQL V1.0
11-09
21天学习SQL V1.0.pdf 66 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 2 日期/时间函数......................................................................................................... 66 ADD_MONTHS................................................................................................ 67 LAST_DAY...................................................................................................... 68 MONTHS_BETWEEN....................................................................................... 69 NEW_TIME...................................................................................................... 70 NEXT_DAY...................................................................................................... 71 SYSDATE......................................................................................................... 72 数学函数................................................................................................................. 72 ABS ................................................................................................................. 73 CEIL 和FLOOR ............................................................................................... 73 COS COSH SIN SINH TAN TANH........................................................ 73 EXP................................................................................................................. 75 LN and LOG..................................................................................................... 75 MOD................................................................................................................ 76 POWER............................................................................................................ 77 SIGN ............................................................................................................... 77 SQRT ............................................................................................................... 78 字符函数................................................................................................................. 79 CHR................................................................................................................. 79 CONCAT .......................................................................................................... 79 INITCAP.......................................................................................................... 80 LOWER 和UPPER ........................................................................................... 81 LPAD 与RPAD................................................................................................. 82 LTRIM 与RTRIM............................................................................................. 83 REPLACE ........................................................................................................ 84 SUBSTR........................................................................................................... 85 TRANSLATE.................................................................................................... 88 INSTR ............................................................................................................. 88 LENGTH.......................................................................................................... 89 转换函数................................................................................................................. 89 TO_CHAR........................................................................................................ 90 TO_NUMBER................................................................................................... 91 其它函数................................................................................................................. 91 GREATEST 与LEAST...................................................................................... 91 USER............................................................................................................... 92 总结........................................................................................................................ 92 问与答.................................................................................................................... 93 校练场.................................................................................................................... 93 练习........................................................................................................................ 94 第五天SQL 中的子句........................................................................................................... 95 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 3 目标.................................................................................................................... 95 WHERE 子句.......................................................................................................... 96 STARTING WITH子句............................................................................................ 98 ORDER BY 子句..................................................................................................... 99 GROUP BY 子句....................................................................................................104 HAVING 子句.........................................................................................................109 子句的综合应用.....................................................................................................112 总结.......................................................................................................................117 问与答...................................................................................................................117 校练场...................................................................................................................117 练习.......................................................................................................................118 第六天表的联合..................................................................................................................119 介绍.......................................................................................................................119 在一个SELECT 语句中使用多个表.........................................................................119 正确地找到列..................................................................................................123 等值联合................................................................................................................124 不等值联合............................................................................................................129 外部联合与内部联合..............................................................................................130 表的自我联合.........................................................................................................132 总结.......................................................................................................................134 问与答...................................................................................................................134 校练场...................................................................................................................134 练习.......................................................................................................................135 第七天子查询内嵌的SQL 子句........................................................................................136 目标...................................................................................................................136 建立一个子查询.....................................................................................................136 在子查询中使用汇总函数.......................................................................................140 子查询的嵌套.........................................................................................................141 相关子查询............................................................................................................144 EXISTS ANY ALL 的使用.................................................................................147 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 4 总结.......................................................................................................................151 问与答...................................................................................................................151 校练场...................................................................................................................152 练习...................................................................................................................153 第一周回顾............................................................................................................................154 预览.......................................................................................................................154 第二周概貌............................................................................................................................155 这一周都讲些什么..................................................................................................155 第八天操作数据..................................................................................................................156 目标.......................................................................................................................156 数据操作语句.........................................................................................................156 插入语句................................................................................................................157 INSERT VALUES 语句.....................................................................................157 INSERT SELECT 语句.....................................................................................161 UPDATE语句........................................................................................................163 DELETE 语句.........................................................................................................166 从外部数据源中导入和导出数据.............................................................................169 Microsoft Access ..............................................................................................170 Microsoft and Sybase SQL Server ......................................................................171 Personal Oracle7...............................................................................................171 总结.......................................................................................................................172 问与答...................................................................................................................172 校练场...................................................................................................................173 练习.......................................................................................................................173 第九天创建和操作表...........................................................................................................174 目标...................................................................................................................174 CREATE DATABASE 语句......................................................................................174 建立数据库时的选项.......................................................................................175 设计数据库.....................................................................................................176 建立数据字典..................................................................................................176 建立关键字段..................................................................................................177 CREATE TABLE 语句.............................................................................................178 表名................................................................................................................179 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 5 FIRST NAME..................................................................................................179 空值属性.........................................................................................................180 唯一属性.........................................................................................................181 表的存储与尺寸的调整....................................................................................183 用一个已经存在的表来建表.............................................................................184 ALTER TABLE 语句...............................................................................................185 DROP TABLE 语句.................................................................................................186 DROP DATABASE语句..........................................................................................187 总结.......................................................................................................................188 问与答...................................................................................................................188 校练场...................................................................................................................189 练习.......................................................................................................................190 第10 天创建视图和索引.......................................................................................................191 目标.......................................................................................................................191 使用视图................................................................................................................192 列的重命名.....................................................................................................196 SQL 对视图的处理过程...................................................................................197 在SELECT 语句使用约束................................................................................201 在视图中修改数据...........................................................................................201 在视图中修改数据的几个问题.........................................................................203 通用应用程序的视图.......................................................................................204 删除视图语句..................................................................................................207 使用索引................................................................................................................207 什么是索引..................................................................................................207 使用索引的技巧..............................................................................................212 对更多的字段进行索引....................................................................................212 在创建索引时使用UNIQUE 关键字.................................................................214 索引与归并.....................................................................................................216 群集簇的使用...........................................................................................217 总结.......................................................................................................................218 问与答................................................................................................................219 校练场...................................................................................................................219 练习...................................................................................................................220 第11 天事务处理控制.........................................................................................................221 目标...................................................................................................................221 事务控制................................................................................................................221 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 6 银行应用程序..................................................................................................222 开始事务处理..................................................................................................223 结束事务处理..................................................................................................225 取消事务处理..................................................................................................228 在事务中使用保存点.......................................................................................231 总结.......................................................................................................................234 问与答...................................................................................................................234 校练场...................................................................................................................235 练习.......................................................................................................................235 第12 天数据库安全............................................................................................................236 前提数据库管理员..............................................................................................236 流行的数据库产品与安全.......................................................................................237 如何让一个数据库变得安全....................................................................................237 Personal Oracle7 与安全..........................................................................................238 创建用户.........................................................................................................238 创建角色.........................................................................................................240 用户权限.........................................................................................................242 为安全的目的而使用视图................................................................................247 总结.......................................................................................................................251 问与答...................................................................................................................252 校练场...................................................................................................................252 练习.......................................................................................................................253 第13 天高级 SQL.................................................................................................................254 目标...................................................................................................................254 临时表...................................................................................................................254 Title ................................................................................................................257 游标.......................................................................................................................259 创建游标.........................................................................................................260 打开游标.........................................................................................................260 使用游标来进行翻阅.......................................................................................261 测试游标的状态..............................................................................................262 关闭游标.........................................................................................................263 游标的适用范围..............................................................................................264 创建和使用存贮过程..............................................................................................265 在存贮过程中使用参数....................................................................................267 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 7 删除一个存贮过程...........................................................................................269 存贮过程的嵌套..............................................................................................270 设计和使用触发机制..............................................................................................272 触发机制与事务处理.......................................................................................273 使用触发机制时的限制....................................................................................275 触发机制的嵌套..............................................................................................275 在选择语句中使用更新和删除................................................................................275 在执行前测试选择语句....................................................................................276 嵌入型SQL............................................................................................................277 静态SQL 与动态SQL.....................................................................................277 使用SQL 来编程....................................................................................................279 总结.......................................................................................................................280 问与答...................................................................................................................280 校练场...................................................................................................................280 练习.......................................................................................................................281 第14 天动态使用SQL ........................................................................................................282 目标.......................................................................................................................282 快速入门................................................................................................................282 ODBC .............................................................................................................282 Personal Oracle 7..............................................................................................283 InterBase SQL ISQL ...................................................................................283 Visual C++ ......................................................................................................284 Delphi.............................................................................................................284 设置.......................................................................................................................284 创建数据库............................................................................................................285 使用MS QUERY 来完成链接..................................................................................290 将VISUAL C++与SQL 结合使用...........................................................................292 将DELPHI 与SQL 结合使用..................................................................................296 总结.......................................................................................................................302 问与答...................................................................................................................303 校练场...................................................................................................................303 练习.......................................................................................................................303 第二周回顾............................................................................................................................304 第三周概貌............................................................................................................................305 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 8 应用你对SQL 的知识.............................................................................................305 第15 天对SQL 语句优化以提高其性能...............................................................................306 目标.......................................................................................................................306 让你的SQL 语句更易读.........................................................................................307 全表扫描................................................................................................................308 加入一个新的索引..................................................................................................309 在查询中各个元素的布局.......................................................................................309 过程................................................................................................................311 避免使用OR...................................................................................................311 OLAP 与OLTP 的比较...........................................................................................313 OLTP 的调试...................................................................................................313 OLAP 的调试..................................................................................................314 批量载入与事务处理进程.......................................................................................314 删除索引以优化数据的载入....................................................................................316 经常使用COMMIT 来让DBA 走开........................................................................316 在动态环境中重新生成表和索引.............................................................................317 数据库的调整.........................................................................................................319 性能的障碍............................................................................................................322 内置的调整工具.....................................................................................................323 总结.......................................................................................................................323 问与答...................................................................................................................324 校练场...................................................................................................................324 练习.......................................................................................................................324 第16 天用视图从数据字典中获得信息................................................................................326 目标.......................................................................................................................326 数据字典简介.........................................................................................................326 用户的数据字典.....................................................................................................327 数据字典中的内容..................................................................................................327 Oracle 的数据字典...........................................................................................328 Sybase 的数据字典..........................................................................................328 ORACLE 数据字典的内部结构...............................................................................328 用户视图.........................................................................................................328 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 9 系统数据库管理员视图....................................................................................336 数据库对象.....................................................................................................339 数据库的生长..................................................................................................343 动态执行视图..................................................................................................347 总结.......................................................................................................................349 问与答...................................................................................................................349 校练场...................................................................................................................350 练习.......................................................................................................................350 第17 天使用SQL 来生成SQL 语句....................................................................................351 目标.......................................................................................................................351 使用SQL 来生成SQL 语句的目的..........................................................................351 几个SQL*PLUS 命令.............................................................................................352 SET ECHO ON/OFF.........................................................................................353 SET FEEDBACK ON/OFF................................................................................353 SET HEADING ON/OFF ..................................................................................353 SPOOL FILENAME/OFF..................................................................................353 START FILENAME..........................................................................................354 ED FILENAME................................................................................................354 计算所有的表中的行数...........................................................................................354 为多个用户赋予系统权限.......................................................................................359 将你的表的权限赋予其它的用户.............................................................................361 在载入数据时解除对数的约束................................................................................363 一次创建多个同义字..............................................................................................364 为你的表创建视图..................................................................................................368 在一个计划中清除其所有的表的内容......................................................................369 使用SQL 来生成SHELL 脚本................................................................................371 再建表和索引.........................................................................................................372 总结.......................................................................................................................373 问与答...................................................................................................................373 校练场...................................................................................................................373 练习.......................................................................................................................374 第18 天PL/SQL 简介..........................................................................................................376 目标.......................................................................................................................376 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 10 入门.......................................................................................................................376 在PL/SQL 中的数据类型........................................................................................377 字符串类型.....................................................................................................377 数值数据类型..................................................................................................378 二进制数据类型.....................................................................................................378 日期数据类型.........................................................................................................378 逻辑数据类型.........................................................................................................378 ROWID...........................................................................................................379 PL/SQL 块的结构...................................................................................................379 注释.......................................................................................................................380 DECLARE 部分......................................................................................................380 变量声明.........................................................................................................380 常量定义.........................................................................................................381 指针定义.........................................................................................................381 %TYPE 属性...................................................................................................382 %ROWTYPE 属性...........................................................................................382 %ROWCOUNT 属性........................................................................................383 Procdure 部分.........................................................................................................383 BEGIN … … END..............................................................................................383 指针控制命令..................................................................................................384 条件语句.........................................................................................................386 LOOPS 循环....................................................................................................387 EXCEPTION 部分..................................................................................................390 激活EXCEPTION 异常.............................................................................390 异常的处理.....................................................................................................391 将输入返回给用户..................................................................................................392 在PL/SQL 中的事务控制........................................................................................393 让所有的事在一起工作....................................................................................394 示例表及数据..................................................................................................394 一个简单的PL/SQL 语句块.............................................................................395 又一个程序.....................................................................................................398 存储过程包和触发机制.......................................................................................403 总结.......................................................................................................................406 问与答...................................................................................................................407 校练场...................................................................................................................407 练习.......................................................................................................................407 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 11 第19 天TRANSACT-SQL 简介............................................................................................408 目标.......................................................................................................................408 TRANSACT-SQL 概貌............................................................................................408 对ANSI SQL 的扩展..............................................................................................408 谁需要使用TRANSACT-SQL..........................................................................409 TRANSACT-SQL 的基本组件..........................................................................409 数据类型................................................................................................................409 使用TRANSACT-SQL 来访问数据库......................................................................411 BASEBALL 数据库.........................................................................................411 定义局部变量..................................................................................................414 定义全局变量..................................................................................................414 使用变量.........................................................................................................415 PRINT 命令....................................................................................................417 流控制...................................................................................................................417 BEGIN … … END 语句......................................................................................418 IF … … ELSE 语句............................................................................................418 EXIST 条件.....................................................................................................421 WHILE 循环....................................................................................................422 使用WHILE 循环在表中翻阅..........................................................................424 TRANSACT-SQL 中的通配符.................................................................................426 使用COMPUTE 来生成摘要报告............................................................................426 日期转换................................................................................................................427 SQL SERVER 的诊断工具— — SET 命令..................................................................427 总结.......................................................................................................................428 问与答...................................................................................................................428 校练场...................................................................................................................429 练习.......................................................................................................................429 第20 天SQL*PLUS .............................................................................................................430 目标.......................................................................................................................430 简介.......................................................................................................................430 SQL*PLUS 缓存.....................................................................................................430 DESCRIBE 命令.....................................................................................................435 SHOW 命令............................................................................................................436 文件命令................................................................................................................438 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 12 SAVE GET EDIT 命令................................................................................438 运行一个文件..................................................................................................439 查询的假脱机输出...........................................................................................440 SET 命令...............................................................................................................442 LOGIN.SQL 文件...................................................................................................445 CLEAR 命令..........................................................................................................446 将你的输出格式化..................................................................................................446 TTITLE 与BTITLE..........................................................................................446 格式化列COLUMN HEADING FORMAT ..............................................447 报表与分类汇总.....................................................................................................449 BREAK ON.....................................................................................................449 COMPUTE......................................................................................................450 在SQL*PLUS 中使用变量......................................................................................453 DEFINE ..........................................................................................................454 ACCEPT .........................................................................................................455 NEW_VALUE..................................................................................................457 DUAL 表........................................................................................................458 DECODE 函数.................................................................................................459 日期转换................................................................................................................462 运行一系列的SQL 文件.........................................................................................465 在你的SQL 脚本中加入注释..................................................................................466 高级报表................................................................................................................467 总结.......................................................................................................................469 问与答...................................................................................................................469 校练场...................................................................................................................469 练习.......................................................................................................................470 第21 天常见的SQL 错误及解决方法..................................................................................471 目标...................................................................................................................471 介绍.......................................................................................................................471 常见的错误............................................................................................................471 Table or View Does Not Exist.............................................................................471 Invalid Username or Password ...........................................................................472 FROM Keyword Not Specified...........................................................................473 Group Function Is Not Allowed Here ..................................................................474 Invalid Column Name........................................................................................475 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 13 Missing Keyword .............................................................................................475 Missing Left Parenthesis ....................................................................................476 Missing Right Parenthesis ..................................................................................477 Missing Comma................................................................................................478 Column Ambiguously Defined ...........................................................................478 Not Enough Arguments for Function...................................................................480 Not Enough Values............................................................................................481 Integrity Constraint Violated--Parent Key Not Found ............................................482 Oracle Not Available .........................................................................................483 Inserted Value Too Large for Column ..................................................................483 TNS:listener Could Not Resolve SID Given in Connect Descriptor .........................484 Insufficient Privileges During Grants...................................................................484 Escape Character in Your Statement--Invalid Character .........................................485 Cannot Create Operating System File ..................................................................485 Common Logical Mistakes.................................................................................485 Using Reserved Words in Your SQL statement .....................................................486 The Use of DISTINCT When Selecting Multiple Columns.....................................487 Dropping an Unqualified Table ...........................................................................487 The Use of Public Synonyms in a Multischema Database.......................................488 The Dreaded Cartesian Product ..........................................................................488 Failure to Enforce File System Structure Conventions ...........................................489 Allowing Large Tables to Take Default Storage Parameters....................................489 Placing Objects in the System Tablespace............................................................490 Failure to Compress Large Backup Files ..............................................................491 Failure to Budget System Resources ...................................................................491 Preventing Problems with Your Data...................................................................491 Searching for Duplicate Records in Your Database................................................491 总结...................................................................................................................491 校练场...................................................................................................................492 练习.......................................................................................................................492 第三周回顾............................................................................................................................494 附件A 在SQL 中的常见术语...............................................................................................495 ALTER DATABASE.........................................................................................495 ALTER USER..................................................................................................495 BEGIN TRANSACTION ..................................................................................495 CLOSE CURSOR.............................................................................................495 COMMIT TRANSACTION...............................................................................496 CREATE DATABASE.......................................................................................496 CREATE INDEX..............................................................................................496 CREATE PROCEDURE....................................................................................496 CREATE TABLE..............................................................................................497 CREATE TRIGGER..........................................................................................497 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 14 CREATE USER................................................................................................497 CREATE VIEW................................................................................................497 DEALLOCATE CURSOR.................................................................................498 DROP DATABASE...........................................................................................498 DROP INDEX..................................................................................................498 DROP PROCEDURE........................................................................................498 DROP TABLE..................................................................................................498 DROP TRIGGER .............................................................................................499 DROP VIEW....................................................................................................499 EXECUTE.......................................................................................................499 FETCH............................................................................................................499 FROM.............................................................................................................499 GRANT...........................................................................................................500 GROUP BY.....................................................................................................500 HAVING..........................................................................................................500 INTERSECT....................................................................................................500 ORDER BY.....................................................................................................500 ROLLBACK TRANSACTION ..........................................................................500 REVOKE.........................................................................................................500 SELECT..........................................................................................................501 SET TRANSACTION.......................................................................................501 UNION............................................................................................................501 WHERE..........................................................................................................501 *.....................................................................................................................501 附件B 在第14 天中的C++源代码清单................................................................................502 附件 C 第14 天中的Delphi 源代码清单...............................................................................521 附件D 参考内容..................................................................................................................524 书..........................................................................................................................524 Developing Sybase Applications ..................................................................524 Sybase Developer's Guide ...........................................................................524 Microsoft SQL Server 6.5 Unleashed, 2E ......................................................524 Teach Yourself Delphi in 21 Days ................................................................524 Delphi Developer's Guide ...........................................................................524 Delphi Programming Unleashed ..................................................................525 Essential Oracle 7.2 ...................................................................................525 Developing Personal Oracle7 for Windows 95 Applications ............................525 Teach Yourself C++ Programming in 21 Days ...............................................525 Teach Yourself Tansact-SQL in 21 Days .......................................................525 Teach Yourself PL/SQL in 21 Days ............................................................525 杂志...................................................................................................................526 DBMS.............................................................................................................526 Oracle Magazine...............................................................................................526 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 15 SQL 的互联网资源.................................................................................................526 附件E ACSLL 码表.............................................................................................................527 附件F 问题与练习答案........................................................................................................533 第一天SQL 简介.................................................................................................533 问题答案.........................................................................................................533 练习答案.........................................................................................................533 第二天查询— — SELECT 语句的使用..................................................................533 问题答案.........................................................................................................533 练习答案.........................................................................................................534 第三天表达式条件语句与运算.........................................................................535 问题答案.........................................................................................................535 练习答案.........................................................................................................535 第四天函数对获得数据的进一步处理...............................................................536 问题答案.........................................................................................................536 练习答案.........................................................................................................537 第五天SQL 中的子句..........................................................................................538 问题答案.........................................................................................................538 练习答案.........................................................................................................538 第六天表的联接..................................................................................................540 问题答案.........................................................................................................540 练习答案.........................................................................................................541 第7 天子查询内嵌的SELECT 语句............................................................542 问题答案.........................................................................................................542 练习答案.........................................................................................................544 第八天操作数据..................................................................................................544 问题答案.........................................................................................................544 练习答案.........................................................................................................546 第九天创建和操作表...........................................................................................546 问题答案.........................................................................................................546 练习答案.........................................................................................................548 第10 天创建视图和索引.......................................................................................549 问题答案.........................................................................................................549 练习答案.....................................................................................................550 第11 天事务处理控制.........................................................................................550 问题答案.........................................................................................................550 练习答案.........................................................................................................551 第12 天数据库安全............................................................................................552 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 16 问题答案.........................................................................................................552 练习答案.........................................................................................................552 第13 天高级 SQL.................................................................................................553 问题答案.........................................................................................................553 练习答案.........................................................................................................553 第14 天动态使用SQL ........................................................................................554 问题答案.........................................................................................................554 练习答案.........................................................................................................554 第15 天对SQL 语句优化以提高其性能...............................................................555 问题答案.........................................................................................................555 练习答案.........................................................................................................555 第16 天用视图从数据字典中获得信息................................................................557 问题答案.........................................................................................................557 练习答案.........................................................................................................557 第17 天使用SQL 来生成SQL 语句....................................................................558 问题答案.........................................................................................................558 练习答案.........................................................................................................560 第18 天PL/SQL 简介..........................................................................................561 问题答案.........................................................................................................561 练习答案.........................................................................................................561 第19 天TRANSACT-SQL 简介............................................................................562 问题答案.........................................................................................................562 练习答案.........................................................................................................562 第20 天SQL*PLUS .............................................................................................563 问题答案.........................................................................................................563 练习答案.........................................................................................................563 第21 天常见的SQL 错误及解决方法..................................................................564 问题答案.........................................................................................................564 练习答案.........................................................................................................
Oracle Life DBA的一天
ctaexpert的专栏
02-05 905
Oracle 日常 运维 维护 巡检脚本
DBA的主要工作是什么
cuancha6926的博客
06-06 5856
作为一个DBA，你的主要工作是什么呢？某天一个厂商给我们作培训，其中有一个ppt说明DBA的主要工作是什么：安装配置数据库占15%，管理数据库占30%等等，然后他就问我们是否也是这样，大家都笑了，回答是：No，我们80%的时间都...
Oracle Life -----DBA的一天
weedd的专栏
08-11 549
一、活动状态检查 通过查询基本视图，确认数据库和实例处于正常运行状态，可以对外提供数据服务。 1，实例状态： SQL>SELECT instance_name,status FROM v$instance; 查询返回实例名称、状态，正常状态应为open。 2，连接用户： SQL>SELECT inst_id,username,COUNT(*) FROM gv$session GROUP BY inst_id,username; 查询数据库以用户分组连接数。 ...
华为GaussDB DBA的一天
Hehuyi_In的博客
08-30 3086
https://forum.huawei.com/enterprise/zh/data/attachment/forum/201812/21/110043f6j5777uc76c2pn2.jpg
DBA一天干的活
weixin_34356138的博客
11-12 153
一.检查活动状态 通过查询基本视图，确认数据库和实例处于正常运行状态，可以对外提供数据服务。 1.1实例状态 1 SELECTinstance_name,statusFROMv$instance; 查询返回实例名称、状态，正常状态应为Open。 1.2会话信息 1 2 SELECT SESSIONS_...
小墨首秀 - DBA的一天【新】
weixin_54551388的博客
06-16 332
更多精彩，请点击上方蓝字关注我们！6月10日下午，云和恩墨成功举行了线上的产品发布会，zCloud产品负责人程昌明循序渐进地展示了如何通过zCloud从0到1建设企业级的DBaaS平台。今...
Oracle DBA日常工作内容和职责详解
Oracle DBA日常工作和职责 Oracle DBA 的日常工作和职责是 Oracle 数据库管理员的核心内容。作为一名 Oracle DBA，需要掌握丰富的知识和技能，以便更好地管理和维护 Oracle 数据库。本文将从 Oracle DBA 的日常工作...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值