一、SqlPlus常用命令 @
save
a where…
c/t1/t2
l
Spool off(生成Log) 自动批处理生成
Select ‘analyze table ‘ …
Shift-Del 晴空屏幕
@c:\temp\xx.sql
二、SQl 常规使用 select owner,count(*) from t1 group by owner
select owner,count(*) from t1 group by rollup(owner)
select owner,count(*) from t1 group by cube(owner)
select … from dba_SEGMENTS (段)
select tabelspace_name,count(*) from dba_segments where tablespace_name=’user’ group by tablespace_name
select tabelspace_name,segment_type ,count(*) from dba_segments group by tablespace_name
select username,account_status,default_tablespace def_ts,temporary_tablespace temp_ts,to_char from dba_users
不建议应用System表空间
alter user dbsnmp account lock; 锁住某一账号，暂时不让其用。
Select .. from .. where .. group by .. order by .. having
Select * from t1,t2 where (如果Where中没有出现两表相关联的条件，则会产生笛卡尔积数量的结果) 用处：可以产生测试数据，进行压力测试！
Distinct 与Group By
三、SQl的一些注意事项 （DBA 督促程序员需要的注意事项。以及发现程序中的Sql问题）
1、 避免全表扫描
rowid/索引/直接访问表
避免以下几种：
select * from t1 where owner =123;
SQl 中，数字会隐性转换为字符串，其中索引就会永不到了。因此要避免这种隐性转换。
Select * from t1 where lower(owner)=’test’;//需要建立函数索引
Select * from t1 where owner=’’;
前导索引/后导索引
select .. from a, b where b=..; (where a=… and b=.. ;where a=..;这两种都会用到索引)
500万条记录，因维护，需要删除400万条记录。
Delete 与 truncate的区别：Delete 要写回滚，写到回滚站，写很多Log，开销很大
Delete 没有回收水位线（曾经用过的最高线）。会影响将来的全表扫描的效率
做法：1、重新创建临时表，Insert 100万条数据，然后Drop原来的表
2、采用Alter table move / exp . imp
（当前表的回收水位线）
degree 并行度 也会造成全表扫描
在Sql级来指定，而不再对象级来指定，可控性会很高
select * from dba_tables
create table可以指定Degree ，Alter Table 来指定Degree 默认Dgree为1 不推荐修改
四、系统架构 （一）、文件
物理架构 dbfile controlfile redo … os block (temporary segment，undo segment ,index seg,data seg)
逻辑架构 database tablespace segment extent block
-===================================================
数据库与实例的概念区别。
Show parameter block 默认值是8K
块大，全表扫描的效率会很高，而每次访问数据库，如果很少数据，就需要读取整个快的数据，对内存是个浪费。需要根据系统权衡利弊，选择合适的块大小。一般为4K的整数倍。是为避免额外的操作系统IO开销，是操作系统快的整数倍。
Dbfile：存储数据的物理文件，操作系统看到的文件。
分为几种：
1、临时段：进行一次数据排序的时候，在PGA中如果能完成，则在内存中完成，如果不够，则要在临时段中申请，临时段可重复利用。
创建方法：create temporary tablespace temp tempfile ‘….’ Size 1024M extent management local uniform size 1m;
不推荐将临时段切换到永久表空间。
2、 Undo段
Show parameter undo
在9i中一般为自动管理：Auto
3、 索引段
4、 数据段
文件系统和裸设备的选择?
逻辑架构：
扩展
select * from dba_tables
initial_extent next_extent 、Max_extent
Oracle 级的Block
(二)进程:：dbwr pmon smon checkpoint lgwr
PS ?EF | GREP pmon
1、 数据库写进程：dbwr，将脏数据写入到Dbfile
2、 pmon：
3、 smon ：会滚，空间回访，控制
4、 checkpoint：
5、 日志写进程：LogBuffer 中的脏数据写入到日志
（三）内存
SGA，PGA，UGA
1、SGA
并行备份，并发操作，共享池，大池是对共享池的保护，保留池是对小表的全表扫描时保留到保留池中
回收池：确定使用一次
循环池
把握住何种应用存放在哪一种池中！
2、PGA主要功能是排序
Show parameter sort
Sort_area_size 一般是5M即可，如果需要排序效率很大可以配置很高的值
3、UGA
(四)实例
实例运行过程中几大元素之间的作用
数据库的启动：
1、nomount：读取Intial文件中的参数，Dbbuffer，SGA中的参数，Block等配置
show parameter control指定了contral file 的位置
先从init.ora 中找Control File位置然
2、mount
从Contral File中找redo log, Dbfile以及其他scn等信息
3、open
都找到并确认Ok 才Open
五、数据库服务器 Oracle服务器可以通过以下的方式运行在不同的计算机上
客户端：
连接需要经历的过程：
net8-> 本地配置文件： $oracle_home/network/admin/ tnsnames.ora->Host Ip,Port,Listener,服务名-〉到服务器段端口：$oracle_home/network/admin/ Listener.ora,将服务请求告诉其他进程，Oracle Home 下的$oracle_home/bin/Oracle并运行，生成一个隐身进程，和客户端进程建立连接。
Oracle 9i 中的Spfile的作用
Create spfile from pfile=’…’
只有在数据库完全ShutDown的时候才能够执行。Spfile为二进制文件，可以转换为文本文件修改后再转换回来。
重做日志缓冲：
六、 表空间 （一）表空间的种类
从用途上分：undo tablespace,temporary tablespace,data/index tablespace
从Oracle管理机制不同来区分：
数据字典管理的表空间（DMT）：FET$/UET$ allocate/deallocate SMON
本地管理表空间（LMT）。
AUTO：表空间的扩展是成几何级数的增长，不推荐使用（空间被重用的机率高）
Next 1M,pctincrease 1%
50M,51M：此时申请需要51M大块的表空间，如果没有大块空间，则会溢出。而实际上的表空间可能不止51M
系统自动合并小块表空间的效果不明显.
UNIFORM：完全用同一大小空间扩展。理论上可以实现表空间完全的被重用。
（二）如何来创建本地管理的表空间:
create tablespace stk_ts datafile ‘/data…/stk_ts01.dbf’ size 1024M extent management local uniform size 512K segment space management auto;
8i与9i的默认local 属性不同
1、 脚本方式
2、 Oem方式
（三）表空间的删除
drop tablespace test including contents and datafiles;
（四）对表空间扩容的方法
（五）有关表空间数据字典的使用
表空间命名存储路径要有很好的规范
（六）数据文件的分布原则
理想情况使用不同的磁盘：
REDO log如果和其他系统文件放在一起会严重影响应用，竞争空间使用。
尽量使用不同的IO通道
分区：
了解系统管理员的系统架构，选择最优方案。
排序的表空间temp file ，也属于写密集型, 如果和Redo Log放在一起也会出现竞争。
把能够互补的操作的文件放在一起可以回避风险。比如连续性读写和随机性读写放在一起。
（七）数据储存管理
数据库，表空间，段，区，数据库酷块
数据文件，OS块之间的关系
show parameter read
每次IO动作所能访问的DB Block的块数，用来改变全表扫描的性能。但如果过大同时也会把原本属于索引的扫面演变成全表扫描
创建非标准块的表空间
show parameter cache
db_16k（2k…）_cache_size…就是为非标准块的创建而准备。
Create tablespace test datafile ‘d:\test01.dbf’size 10M blocksize 2k;
在应用当中，有些历史数据的大表，采用大一些Block Size
而一些比较小的数据表，适用随机访问的表，可能会使用小一些的Block Size效果会更好。
（八）Oracle对分区的管理
如果处理数据量增加，而性能不会明显下降的问题
按照某一特定条件，将
5000万条记录以上的表建议采用分区
create table t1
PARTITION BY RANGE (timestamp) (
PARTITION p1 VALUES LESS THAN (to_date('20040131235959','yyyymmddhh24miss')) TABLESPACE users1,
PARTITION p2 VALUES LESS THAN (to_date('20040229235959','yyyymmddhh24miss')) TABLESPACE users2,
PARTITION p3 VALUES LESS THAN (to_date('20040331235959','yyyymmddhh24miss')) TABLESPACE users3,
PARTITION p4 VALUES LESS THAN (to_date('20040430235959','yyyymmddhh24miss')) TABLESPACE users4,
PARTITION p5 VALUES LESS THAN (to_date('20040531235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p6 VALUES LESS THAN (to_date('20040630235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p7 VALUES LESS THAN (to_date('20040731235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p8 VALUES LESS THAN (to_date('20040831235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p9 VALUES LESS THAN (to_date('20040930235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p10 VALUES LESS THAN (to_date('20041031235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p11 VALUES LESS THAN (to_date('20041130235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p12 VALUES LESS THAN (maxvalue) TABLESPACE users)
as select * from t2;
create index idx_t1_timestamp on t1 (timestamp)
local
(
PARTITION idx_t1_p1 tablespace users,
PARTITION idx_t1_p2 tablespace users,
PARTITION idx_t1_p3 tablespace users,
PARTITION idx_t1_p4 tablespace users,
PARTITION idx_t1_p5 tablespace users,
PARTITION idx_t1_p6 tablespace users,
PARTITION idx_t1_p7 tablespace users,
PARTITION idx_t1_p8 tablespace users,
PARTITION idx_t1_p9 tablespace users,
PARTITION idx_t1_p10 tablespace users,
PARTITION idx_t1_p11 tablespace users,
PARTITION idx_t1_p12 tablespace users
);
alter table t1 truncate partition p1;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
create table t1
PARTITION BY RANGE (timestamp) (
PARTITION p1 VALUES LESS THAN (to_date('20040131235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p2 VALUES LESS THAN (to_date('20040229235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p3 VALUES LESS THAN (to_date('20040331235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p4 VALUES LESS THAN (to_date('20040430235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p5 VALUES LESS THAN (to_date('20040531235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p6 VALUES LESS THAN (to_date('20040630235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p7 VALUES LESS THAN (to_date('20040731235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p8 VALUES LESS THAN (to_date('20040831235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p9 VALUES LESS THAN (to_date('20040930235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p10 VALUES LESS THAN (to_date('20041031235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p11 VALUES LESS THAN (to_date('20041130235959','yyyymmddhh24miss')) TABLESPACE users,
PARTITION p12 VALUES LESS THAN (maxvalue) TABLESPACE users)
as select * from t2;
--create index idx_t1_timestamp on t1 (timestamp)
alter table t1 add constraint pk_t1 primary key (timestamp) using index
local
(
PARTITION idx_t1_p1 tablespace users,
PARTITION idx_t1_p2 tablespace users,
PARTITION idx_t1_p3 tablespace users,
PARTITION idx_t1_p4 tablespace users,
PARTITION idx_t1_p5 tablespace users,
PARTITION idx_t1_p6 tablespace users,
PARTITION idx_t1_p7 tablespace users,
PARTITION idx_t1_p8 tablespace users,
PARTITION idx_t1_p9 tablespace users,
PARTITION idx_t1_p10 tablespace users,
PARTITION idx_t1_p11 tablespace users,
PARTITION idx_t1_p12 tablespace users
);
alter table t1 truncate partition p1;
(九) 存储参数
1、 initial ：创建对象是初始分配的最大值
2、 next：在初始扩展用完后，下一个扩展是多大
3、 pctincrease：next扩展的百分比，默认0
已经将数据存储到系统表空间去之后，如何处理？
尽量将数据转出来。千万不要将数据保存到系统表空间。
Undo 表空间的自动管理转换为手动管理：
Manual
临时表空间不写Log
段管理:
desc dba_segments
select bytes from dba_segments…
安装
创建的前提条件
创建数据库，建议采用create tablespace
Oracle_sid
Show parameter name 显示当前会话
Set oracle_sid=…
数据仓库选项:
意味着大数据库，数据库两会很大，事物类型都是大而少的类型，SGA可能会大一些
BlockSize会大一些
相关的优化模式会不同，全表扫描会居多，chosen 或者all rows
联机事务处理，更注重随机访问，
Bdump 告警日志，要经常查看，清除，否则会影响速度。按月归档告警日志 bdump下的Alert文件
Cat ‘’ -> alert…
查看是不是归档日志满了。要清除或者转移归档日志！否则过大会引起挂起！、
globle_name:分布式的时候Dblink是否需要和远程dbname名字完全一致，如果需要则True
mts开头的都是共享进程有关系的
db_create …为OMF用到的参数
sort_area_size 是已经淘汰的，不应列出
show parameter pga
show parameter sort
如何在后期安装组件？
手工创建：
Windows 下的脚本
mkdir D:\oracle\admin\test\bdump
mkdir D:\oracle\admin\test\cdump
mkdir D:\oracle\admin\test\create
mkdir D:\oracle\admin\test\pfile
mkdir D:\oracle\admin\test\udump
mkdir D:\oracle\ora92\database
mkdir D:\oracle\oradata\test
set ORACLE_SID=test
D:\oracle\ora92\bin\oradim.exe -new -sid TEST -startmode m
D:\oracle\ora92\bin\oradim.exe -edit -sid TEST -startmode a
D:\oracle\ora92\bin\orapwd.exe file=D:\oracle\ora92\database\PWDtest.ora password=change_on_install
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\CreateDB.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\CreateDBFiles.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\CreateDBCatalog.sql //创建数据字典
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\JServer.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\ordinst.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\interMedia.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\context.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\xdb_protocol.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\test\scripts\postDBCreation.sql
UNIX 下的创建脚本：
RAC的建库脚本
RAC的难点在操作系统的部署上
单节点与双节点大同小异
spfile：（Server Parameter file ）：是一个二进制文件。9i开始出现。它能够给用户带来更好的维护性。
如何转换pfile 和 spfile
create pfile='c:\init.ora' from spfile;
conn / as sysdba
alter system checkpoint;
shutdown abort
create spfile from pfile=’c:\init.ora’;
startup
alter system set timed_statistics=false scope=both;
alter system set timed_statistics=false scope=memory;
alter system set timed_statistics=false scope=pfile;
数据库的启动和关闭
shutdown normal default
shutdown immediate 立刻切断当前session,并执行checkpoint,数据库是干净关闭,效果又好又快
shutdown about 切断当前session, 并不执行checkpoint相当于拔断电源,如果有大量的事物,数据库的负载相当严重,此时执行immediate可能还需要很长时间.在风险降到最低的时候,可以执行此操作.
shutdown transaction 等事务完成,执行checkpoint
一、数据表的管理 普通表：稳定
索引组织表： 适用于访问量比较大。读，Insert的操作会比普通表高很多，其它操作和普通表差不多。
如何创建表
select tname from tab;
create table tt as select * from dba_objects nologging;
alter table tt add (t date);
alter table tt modify (t varchar2(20));
如果t中已经有数据，修改类型会出错，应该写一个过程进行转化。
pctfrmae 10: 控制表当中相关的数据库，如果空闲量小雨10%，则认为已经满了。
用来控制存储的数据多少。
Initrans 1 : 用来控制并发
删除表；
对大表的删除操作(例如占用10G的空间的数据库) ，简单用Drop table很难实现。
必须要这样来实现（特别是在8i 的环境下）：
Truncate table tt reuse storage;
Alter table tt deallocate unused keep 8000M
Alter table tt deallocate unused keep 7000M
Alter table tt deallocate unused keep 6000M
Alter table tt deallocate unused keep 4000M
Alter table tt deallocate unused keep 2000M
Alter table tt deallocate unused keep 500M
Drop table tt;
对于不是很大的表：
truncate table;
drop table;
表中列可选的数据类型：
char varchar nchar
number int number(n,m)
lob:clob,blob /long
date
二、行迁移，行链接： 如果一个表中blog Size不是很大，而出现了大字段，则行迁移是不可避免的。
Create table ttt as select * from dba_objects nologging;
Alter table ttt add (t date default sysdate);
C/(t/(t1
C/t1/t2
C/t2/t3
C/t3/t4
Analyze table ttt compute statistics;
Select table_name,chain_cnt from user_tables where table_name=’ttt’
判断一个表是否有行链接，则chain_cnt对应的表非空，则表示有行连接。 行连接表示数据在该处已经存储不下，需要在另外一个地方保存。首先读该处，然后再根据连接到另外一个地方读取。
如何对表数据进行重组：
解决办法：
Create table as
Exp/imp
Backeup 相关行，然后重新insert 回来
最好的办法：
alter table ttt move
analyze table ttt compute statistics
Select table_name,chain_cnt from user_tables where table_name=’ttt’
如果当前应用中有大字段，则没有办法解决行连接。
因此此种方法只是在有限的范围内解决一些问题。
三、尝试用其他办法解决行连接 Create table ttt as select * from dba_objects nologging;
Alter table ttt add (t date default sysdate);
C/(t/(t1
C/t1/t2
C/t2/t3
C/t3/t4
Analyze table ttt compute statistics;
Select table_name,chain_cnt from user_tables where table_name=’ttt’
1、Create t4 as select * from ttt nologging;
Analyze table t4 compute statistics;//分析：
Select table_name,chain_cnt from user_tables where table_name=’t4’
2、imp test/test file=ttt t3 备份
alter table ttt rename to t3;
Analyze table t3 compute statistics;//分析：
Select table_name,chain_cnt from user_tables where table_name=’t3’
以上三种方法消除行连接，在实际应用中权衡最好的办法。
二、对索引的管理 B-树索引，位图索引介绍
Primary key (a,b)
需要获得表的count(*)
Primary key (a,b) 如果经常做修改update a=, b=,….则这种索引不好,如何改变这种问题？进行级联更新
索引的位置不会被重用！会造成索引的体积越来越大。大量的垃圾空间占用，会严重影响索引效率。
怎样的索引的效率是比较好的？区分度越高的数据创建的索引，效率越高。理想，一一对应的索引效率最高。
针对区分度不高的情况如何提高索引效率？提供位图索引的解决办法。位图索引会索引整个的一块。
如何创建索引：
索引的命名是很有讲究的，最好能做到一目了然，对应那一个表中的哪一个字段。
Idx：索引缩写
表名
字段名
1、create index idx_ttt_t1 on ttt(t1);
2、alter table ttt add constraint pk_ttt primary key (t1);
如果记录很多，如何很快加入索引？通过开大排序区等的大小可以加快速度。（针对8i）
Alter session sort_mutil…_count=128;
Alter session sort_area_size=400M;
Create index idx_…
同义词:相当于表的别名:修改表结构不容易,用同义词来修改表明.
Create synonym test for test.t1@dblink; //创建另外一个数据库上的表的同义词.可以实现隐藏数据库实际位置的目的.
Create table a;
Create table b;
用来区分不同的权限.
视图:
在执行计划上,视图被认为没有好处,可以优化Sql语句的复杂性.
在表当中定义一组逻辑,物力上没有实际存储.
Create or replace view v_t1 as select * from t1;
Desc v_t1;
在映射到原数据表上去后,会扭曲访问方式.
Drop view v_t1;
Create view v_t1 as select * from t1 where id=’zhou’;//可以限制某访问权限,比如工资查询对应用
物化视图实际占用存储空间,更多是用在数据仓库环境中,解决查询时间过长的应用,还可以用在分布式中,A数据库经常会读取B数据库中的数据,物化视图有集中刷新方式,增量刷新、全部刷新、强制刷新（先尝试增量刷新，当失败后，使用全部刷新）等刷新方式。
Create materialized view log on “account” tablespace “stk_ts” pctfree 5 pctused 95 with primary key excluding new values;
Dblink
分布式链接基本上采用DBLink来实现。
Set oracle_sid*rman
Sqlplus /nolog
Conn /as sysdba
Startup
Create user zhoubf identified by zhou default tablespace users temporary tablespace temp;
Grant connect.resource.dba to zhoubf
Conn zhoubf/zhou@rman
Show parameter name
Create database link totest connect to zhoubf identified by zhou using ‘test’;
Desc tab@test
Create snapshot mv_t1 as select * from t1@totest;
增加主键
Desc t1;
Alter table t1 add constraint pk_t1 primary key (owner ,object_id);
Select count(*) from t1;
注意两点
1、 globle name：（false ）可以用任意名字
2、 所引用的本地连接串：@conn zhoubf/zhou@test 配置本地连接串
dblink 如何管理：
desc user_db_links 可以看到当前用户数据库中的db_link
select * from user_db_links;
desc dba_db_links 可以看全部db_link
select * from dba_db_links;
select * from globle name
删除看dblink是属于public 还是Private
drop database link totest;
drop public database link totest;
总结： select * from user_synonyms;//同义词管理
select * from dba_synonyms;
drop synoym synn_ttt;
查看本文来源
京公网安备 11010802021500号