一、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;
