香蕉皮

對DBMS而言，如果是透過Index搜尋資料，當在Index的B-Tree中找到符合的資料時，最後都會透過該索引給予的指標，去得知實際
的Data Page(Data Block)的位置。該指標在SQL Server或Sybase ASE都是稱做RID(Row Identify)，而Oracle稱做ROWID(Row Identify)。
 
(1)SQL Server
   RID ==> 檔案編號、分頁編號、在該分頁裡的資料列編號
(2)Sybase
   RID ==> 檔案編號、分頁編號、在該分頁裡的資料列編號
(3)Oracle
   範例: OOOOOOFFFBBBBBBRRR
         [ROWID Format]
         OOOOOO ===> Data object number
         FFF ======> Relative file number
         BBBBBB ===> Block number
         RRR ======> Row number(或稱為Slot number)
 
 
======================================
Oracle的RowID結構

//1、為什使用ROWID
ORACLE把ROWID作為B-樹和其內部演算法標示ROW的唯一標示。
在ORACLE8以前的版本中，ROWID標示FILE、BLOCK、ROW NUMBER，只用一個數字代
表FILE號。
在ORACLE8中，一個DATAFILE有兩個數字代表：
一個絕對值，是整個數據庫唯一的。可以看DBA_DATA_FILES中的FILE_ID。
一個相對值，在TABLESPACE中是唯一的，可以看DBA_DATA_FILES中的RELATIVE_
FNO。
新的ROWID使用相對值，所以必須存放SEGMENT的標示，否則就會混淆。所以ORAC
LE8在ROWID中加入對象的SEGMENT號，用來標示TABLE或者PARTITION。
 
//2、ROWID的結構
使用base-64代碼，包括a-z，A-Z，0-9，+,-。一共18位。
1-6位：代表OBJECT
7-9位：文件相對值
10-15：文件中的BLOCK
16-18：BLOCK中的SLOT值
 
//3、TABLESPACE-Relative尋址方式
使用的是TABLESPACE-Relative尋址方式，多個檔可以有相同的相對值，因為它
們屬不同的TABLESPACE，所以不能從新的ROWID得到絕對位址，但是這沒有問題
，因為當要處理某個OBJECT時，已經能確定它屬哪個TABLESAPCE了。在TABLES
PACE中，檔相對值是唯一的，所以ROWID還是可以唯一標示一個OBJECT。TABLE
SPACE-Relative尋址方式是ORACLE8中支援超大數據庫的關鍵技術。
 
//4、DATA OBJECT NUMBER
DATA OBJECT NUMBER用指示SEGMENT，所有SEGMENT都有DATA OBJECT NUMBER，
存放在每個DATA BLOCK中，而且不重復。

 
退伍第十年的軍中朋友，能聯絡至今，難得！ 

通常Oracle的Table經過經年累月的Insert資料後，當做大量的Data Retention後，常常會發現效能還是不佳，那是因為High Water Mark(HWM)，並
不會HWM並不會往前移動，造成Full Table Scan時，還會從第一個Data Block掃瞄到HWM的位置，此時除了Table Move或Export再Import外，別
無他法，但Oracle 10g開始支援縮小區段(Shrink Space) 這個命令‧(文章內容和範例參考至申建忠的大作)
 
//shrink space compact
這個操作需要對表格加上表格層次的共用鎖定(TM:Row Exclusive)，並對搬移資料列加上獨佔鎖定(Exclusive)‧ 然後將表格的資
料列盡可能地向區段標頭(Segment Header)進中，方法是使用一組新增、刪除資料列操作‧先新增新的資料列，再刪除舊的資料列
，但新增的資料列將儘可能靠近區段標頭‧資料管理者可以線上重組表格，而不會妨礙其他使用者對該表格進行DML操作‧ 縮小區
段不會建立新的區段(Segment)，因而可以減少表格重組過程中需要額外的空間需求‧
但要注意的是該操作不會移動高水位標記(High Water Mark)，也不會回收位於高水位標記以後的區塊(Data Block)， 所以重組後
的表格，僅僅只是改善非Full Talbe Scan以外的情形(例Index Fast Full Scan、Index Range Scan、Index Unique Scan等等)‧
語法: ALTER TABLE table_name SHRINK SPACE COMPACT
 
//shrink space
取得表格獨佔鎖定(Exclusive)，將高水位標記(High Water Mark)移動到目前資料區塊(Data Block)中有資料列的最後一個資料區
塊的位置‧之後釋放新高水標記以後的資料區塊，因此，表格空間將標示那些回收的擴充區塊(Extent)為可用的擴充區塊‧
語法: ALTER TABLE table_name SHRINK SPACE
 

 
 
//[01]建立範例環境
SQL> create table scott.t3 as select * from dba_objects where object_id<=5000;
     已建立表格.
SQL> create index scott.t3_object_id_idx on scott.t3(object_id);
     已建立索引.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.
//[02]目前T2表格所在的Tablespace為USERS，以及共有4917筆資料列與使用52個Data Block
SQL> select table_name,tablespace_name,num_rows,blocks from dba_tables
  2  where owner='SCOTT' and table_name='T3';
     TABLE_NAME      TABLESPACE_NAME        NUM_ROWS     BLOCKS
     --------------- -------------------- ---------- ----------
     T3              USERS                      4917         67
//[03]目前Index的狀態為有效
SQL> select index_name,status from dba_indexes
  2  where owner='SCOTT' and index_name='T3_OBJECT_ID_IDX';
     INDEX_NAME                     STATUS
     ------------------------------ --------
     T3_OBJECT_ID_IDX               VALID
//[04]Table Move前，T3表格的物件編號為51356，以及區段編號為51356
SQL> select object_name,object_id,data_object_id from dba_objects
  2  where owner='SCOTT' and object_name='T3' and object_type='TABLE';
     OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID
     --------------- ---------- --------------
     T3                   51356          51356
//[05]目前區段所使用的擴充區塊(Extent)數量為9個，共有72個Data Block
SQL> select extent_id,file_id,block_id,blocks from dba_extents
  2  where owner='SCOTT' and segment_name='T3';
      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
     ---------- ---------- ---------- ----------
              0          4         57          8
              1          4         65          8
              2          4         73          8
              3          4         81          8
              4          4         89          8
              5          4         97          8
              6          4        105          8
              7          4        113          8
              8          4        121          8
     已選取 9 個資料列.
//[06]縮小區段前資料列的Rowid
SQL> select rowid,object_id,object_name from scott.t3
  2  where object_id=4999;
     ROWID               OBJECT_ID OBJECT_NAME
     ------------------ ---------- -----------------------------
     AAAMicAAEAAAAB7AA9       4999 ALL_SNAPSHOT_REFRESH_TIMES
//[07]object_id為4999的資料列所在的Data Block為123，資料列為該Data Block的第61筆資料列
SQL> select dbms_rowid.rowid_block_number(rowid) block_number,
  2         dbms_rowid.rowid_row_number(rowid) block_number
  3  from scott.t3 where object_id=4999;
     BLOCK_NUMBER BLOCK_NUMBER
     ------------ ------------
              123           61
//[08]進行測試的資料刪除，造成Data Block有部份空間浪費
SQL> DELETE scott.t3 where MOD(object_id,2)=0;
     已刪除 2457 個資料列.
SQL> COMMIT;
     確認完成.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.
//[09]雖然資料列筆數減少為2460筆，但所使用的Data Block數量與資料列筆數4917筆相同‧因此有Data Block空間浪費的現象
SQL> select table_name,tablespace_name,num_rows,blocks from dba_tables
  2  where owner='SCOTT' and table_name='T3';
     TABLE_NAME      TABLESPACE_NAME        NUM_ROWS     BLOCKS
     --------------- -------------------- ---------- ----------
     T3              USERS                      2460         67
//[10]進行縮小區段前，表格必須啟用資料列移動(Row Movement)功能
SQL> alter table scott.t3 enable row movement;
     已更改表格.
//[11]Shrink Space Compact只搬移資料列，但不重設高水位標記(HWM)
SQL> alter table scott.t3 shrink space compact;
     已更改表格.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.
//[12]縮小區段並不會建立一個新的區段，所以縮小區段後，區段編號並沒有改變‧
SQL> select object_name,object_id,data_object_id from dba_objects
  2  where owner='SCOTT' and object_name='T3' and object_type='TABLE';
     OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID
     --------------- ---------- --------------
     T3                   51356          51356
//[13]縮小區段後資料列的Rowid已經跟進行縮小區段之前的Rowid不一樣
SQL> select rowid,object_id,object_name from scott.t3
  2  where object_id=4999;
     ROWID               OBJECT_ID OBJECT_NAME
     ------------------ ---------- -----------------------------
     AAAMicAAEAAAAA8AAo       4999 ALL_SNAPSHOT_REFRESH_TIMES
//[14]縮小區段後object_id為4999的資料列所在的Data Block為60，資料列為該Data Block的第40筆資料列
SQL> select dbms_rowid.rowid_block_number(rowid) block_number,
  2         dbms_rowid.rowid_row_number(rowid) block_number
  3  from scott.t3 where object_id=4999;
     BLOCK_NUMBER BLOCK_NUMBER
     ------------ ------------
               60           40
//[15]因為縮小區段使用新增／刪除資料列的技巧來搬移資料列，因此Index的內容也一同被維護
SQL> select index_name,status from dba_indexes
  2  where owner='SCOTT' and index_name='T3_OBJECT_ID_IDX';
     INDEX_NAME                     STATUS
     ------------------------------ --------
     T3_OBJECT_ID_IDX               VALID
//[16]縮小區段並沒有建立新的區段(Segment)，所以擴充區塊(Extent)並未改變
SQL> select extent_id,file_id,block_id,blocks from dba_extents
  2  where owner='SCOTT' and segment_name='T3';
      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
     ---------- ---------- ---------- ----------
              0          4         57          8
              1          4         65          8
              2          4         73          8
              3          4         81          8
              4          4         89          8
              5          4         97          8
              6          4        105          8
              7          4        113          8
              8          4        121          8
     已選取 9 個資料列.
//[17]Shrink Space將會搬移資料列，並會重新設定高水位標記(HWM)，與釋放高水位標記以後的Data Block
SQL> alter table scott.t3 shrink space;
     已更改表格.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.
//[18]經過Shrink Space後，所使用的區塊減為27個
SQL> select table_name,tablespace_name,num_rows,blocks from dba_tables
  2  where owner='SCOTT' and table_name='T3';
     TABLE_NAME      TABLESPACE_NAME        NUM_ROWS     BLOCKS
     --------------- -------------------- ---------- ----------
     T3              USERS                      2460         27
//[19]區段所擁有的擴充區塊(Extent)也減少為4個，Data Block共32個
SQL> select extent_id,file_id,block_id,blocks from dba_extents
  2  where owner='SCOTT' and segment_name='T3';
      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
     ---------- ---------- ---------- ----------
              0          4         57          8
              1          4         65          8
              2          4         73          8
              3          4         81          8
//[20]關閉表格必的資料列移動(Row Movement)功能
SQL> alter table scott.t3 disable row movement;
     已更改表格.

老實說剛碰Oracle時，對V$PARAMETER、V$PARAMETER2、V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2、V$SPPARAMETER之
間有什麼差異，有什麼不同，完全搞不清楚，結果花了點時間Try一下，大概知道他們之間的差異‧
 
 
//V$PARAMETER、V$SYSTEM_PARAMETER、V$SPPARAMETER之間的差異
在 Oracle 10g 剛裝好時，Instance預設啟動是載入SPFILE參數檔，而SPFILE內有定義 db_file_multiblock_read_count=16 ，但
沒有定義 timed_statistics 該參數值的設定，由以下三個範例，可以看出‧
範例: //v$parameter
      SQL> select name,value,isdefault from v$parameter
         2 where name in ('timed_statistics','db_file_multiblock_read_count');
           NAME                           VALUE    ISDEFAULT
           ------------------------------ -------- ---------
           timed_statistics               TRUE     TRUE
           db_file_multiblock_read_count  16       FALSE
      備註: ISDEFAULT欄位為FALSE，代表該參數是定義在SPFILE或PFILE中‧TRUE代表該參數是未定義於SPFILE或PFILE，而是由
            Instance預設設定Value值(該Value值，也可以讓使用者在Instance啟動後，動態設定)‧
      //v$system_parameter
      SQL> select name,value,isdefault from v$system_parameter
         2 where name in ('timed_statistics','db_file_multiblock_read_count');
           NAME                           VALUE    ISDEFAULT
           ------------------------------ -------- ---------
           timed_statistics               TRUE     TRUE
           db_file_multiblock_read_count  16       FALSE
      備註: ISDEFAULT欄位為FALSE，代表該參數是定義在SPFILE或PFILE中‧TRUE代表該參數是未定義於SPFILE或PFILE，而是由
            Instance預設設定Value值(該Value值，也可以讓使用者在Instance啟動後，動態設定)‧
      //v$spparameter
      SQL> select name,value,isspecified from v$spparameter
         2 where name in ('timed_statistics','db_file_multiblock_read_count');
           NAME                           VALUE    ISSPEC
           ------------------------------ -------- ---------
           timed_statistics                        FALSE
           db_file_multiblock_read_count  16       TRUE
      備註: ISSPEC欄位為TRUE，代表該參數是定義在SPFILE中‧FALSE代表該參數未定義SPFILE或PFILE之中‧如果之後曾經執行
            命令對 timed_statistics 該參數做設定，該參數的ISSPEC將會變成TRUE‧
 
 
//V$PARAMETER、V$SYSTEM_PARAMETER之間的差異
範例: //Session1
      SQL> select name,value,isdefault from v$parameter where name ='timed_statistics';
           NAME                 VALUE      ISDEFAULT
           -------------------- ---------- ----------
           timed_statistics     TRUE       TRUE
      SQL> select name,value,isdefault from v$system_parameter where name ='timed_statistics';
           NAME                 VALUE      ISDEFAULT
           -------------------- ---------- ----------
           timed_statistics     TRUE       TRUE
      SQL> alter session set timed_statistics=false;
           已更改階段作業.
      SQL> select name,value,isdefault from v$parameter where name ='timed_statistics';
           NAME                 VALUE      ISDEFAULT
           -------------------- ---------- ----------
           timed_statistics     FALSE      TRUE
      SQL> select name,value,isdefault from v$system_parameter where name ='timed_statistics';
           NAME                 VALUE      ISDEFAULT
           -------------------- ---------- ----------
           timed_statistics     TRUE       TRUE
      //Session2(該Session是在Session1執行完alter session set timed_statistics=false;命令,方登入Oracle)
      SQL> select name,value,isdefault from v$parameter where name ='timed_statistics';
           NAME                 VALUE      ISDEFAULT
           -------------------- ---------- ----------
           timed_statistics     TRUE       TRUE
      SQL> select name,value,isdefault from v$system_parameter where name ='timed_statistics';
           NAME                 VALUE      ISDEFAULT
           -------------------- ---------- ----------
           timed_statistics     TRUE       TRUE
 
 
 
//V$PARAMETER、V$PARAMETER2、V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2之間的差異
由下面的範例可以知道 V$PARAMETER、V$PARAMETER2這二個Dynamic Performance View結果是一樣的，差異在於 V$PARAMETER對於
該參數的Value是多個時，會合併成一筆資料，而 V$PARAMETER2則會分成數筆資料‧
同樣情形在V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2該二個Dynamic Performance View的差異也僅只是顯示是否多筆‧
範例: //V$PARAMETER
      SQL> select name,value from v$parameter where name = 'control_files';
           NAME            VALUE
           --------------- ------------------------------------------------------
           control_files   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL,
                           C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL,
                           C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
      //V$PARAMETER2
      SQL> select name,value from v$parameter2 where name = 'control_files';
           NAME            VALUE
           --------------- ------------------------------------------------------
           control_files   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
           control_files   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
           control_files   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
 
 
<結論>
(一)V$PARAMETER、V$PARAMETER2這二個View都是顯示目前該Session下的參數設定，而二個View的差異在於，對於多值設定
    的參數，V$PARAMETER就算多個值設定，永遠一個Row，而V$PARAMETER2則是多個Row‧V$PARAMETER、V$PARAMETER2該二
    個View，不管是SPFILE或PFILE，的Value欄位都是會有值的‧
(二)V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2這二個View都是顯示目前該 Instance下的參數設定，而二個View的差異在
    於，對於多值設定的參數，V$SYSTEM_PARAMETER就算多個值設定，永遠一個Row， 而V$SYSTEM_PARAMETER2則是多個Row
    ‧V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2該二個View，不管是SPFILE或PFILE，的Value欄位都是會有值的‧
(三)如果是用SPFILE參數型式啟動Instance，V$SPPARAMETER該View的Value欄位是會有值的，但如果是 PFILE參數型式啟動
    Instance，則V$SPPARAMETER該View的Value欄位是Null的‧ 要注意的是V$SPPARAMETER是顯示SPFILE上設定的參數值，
    並不代表是目前Instance上設定的參數值，如果想要查看目前Instance上的參數值，請參考V$SYSTEM_PARAMETER‧
(四)請用如下範例即可以觀察三個Dynamic Performance View的差異‧
    SELECT * FROM V$PARAMETER WHERE NAME = 'control_files'
    SELECT * FROM V$PARAMETER2 WHERE NAME = 'control_files'
    SELECT * FROM V$SYSTEM_PARAMETER WHERE NAME = 'control_files'
    SELECT * FROM V$SYSTEM_PARAMETER2 WHERE NAME = 'control_files'
    SELECT * FROM V$SPPARAMETER WHERE NAME = 'control_files'

//查看目前累積已備存的Transaction Log檔案
SELECT * FROM V$ARCHIVED_LOG
//查看Archive一些備存位置的相關資訊
SELECT * FROM V$ARCHIVE_DEST_STATUS
SELECT * FROM V$ARCHIVE_DEST
//查看目前有幾個備存的背景程式正在運作
SELECT * FROM V$ARCHIVE_PROCESSES WHERE STATUS='ACTIVE'
//查看備存日誌的歷史資訊()
SELECT * FROM V$LOG_HISTORY
說明: (1)SEQUENCE#:代表日誌檔的序號，假設備存日誌檔案名稱是arch_3.arc，則SEQUENCE#欄位裡就是以3為代表‧
          (2)FIRST_TIME:代表此備存日誌檔是何時產生出來的‧
//查看目前資料庫上的一些系統參數
SELECT * FROM V$PARAMETER
SELECT * FROM V$PARAMETER2
SELECT * FROM V$SPPARAMETER
//查看目前資料庫上的一些系統設定
SELECT * FROM V$OPTION ORDER BY PARAMETER
//查看資料庫版本(SQL Server請使用sp_server_info)
SELECT * FROM V$VERSION
//查看Instance
SELECT * FROM V$INSTANCE
//查看資料庫回復的相關設定值(當執行CHECKPOINT後，請觀察recovery_estimated_ios、actual_redo_blks這二個欄位)
SELECT * FROM V$INSTANCE_RECOVERY
//查看超過三百個以上監控活動的基礎統計資料
SELECT * FROM V$SYSSTAT
//它彙總了各種可能造成交談期，或整個資料庫執行緩慢的問題
SELECT * FROM V$SYSTEM_WAIT_CLASS
//查看DataBase一些重要的狀態(像LOG_MODE、FLASHBACK_ON等等)
SELECT * FROM V$DATABASE
//查看Data File
SELECT * FROM V$DATAFILE
//查看目前資料庫上有那些使用者，和相關使用者的一些資訊
SELECT * FROM V$SESSION
//查看目前資料庫正有那些正在做交易的交易
SELECT * FROM V$TRANSACTION
//查看目前資料庫每10分鐘對UNDO Tablespace的使用情形(便於用來評估Undo Tablespace空間是否不足)
SELECT * FROM V$UNDOSTAT
//查看目前資料庫有那些Lock
SELECT * FROM V$LOCK;
//查看目前資料庫使用者最近下的一些SQL命令程式碼
SELECT * FROM V$SQLTEXT
//查看目前資料庫發生Lock的物件
SELECT * FROM V$LOCKED_OBJECT
//查看目前各LOG GROUP的狀態(CURRENT、INACTIVE)
SELECT * FROM V$LOG
//查看目前各LOG GROUP的所屬File
SELECT * FROM V$LOGFILE

Dynamic Performance View 有三百多個‧你常會聽到它們被稱為「V$」View，因為它們的名稱前面會是「V$」‧ 事實上，「V$」
View完全不是View，它們是以「V_$」開頭之View的Synonym‧                                                           
範例: SELECT OBJECT_NAME,OBJECT_TYPE                                                                             
          FROM DBA_OBJECTS                                                                                           
          WHERE OBJECT_NAME LIKE 'V$%' OR OBJECT_NAME LIKE 'V_$%';                                                   
結果: OBJECT_NAME                  OBJECT_TYPE                                                                             
          ---------------------------         -------------------                                                                          
          V_$MAP_LIBRARY            VIEW                                                                                     
          V$MAP_LIBRARY               SYNONYM                                                                                  
          V_$MAP_FILE                      VIEW                                                                                     
          V$MAP_FILE                        SYNONYM                                                                                  
          V_$MAP_FILE_EXTENT     VIEW                                                                                     
          V$MAP_FILE_EXTENT       SYNONYM                                                                                 
                                                                                                                 
Dynamic Performance View 對關於instance和資料庫驚人的資訊數量(某種程度上)，提供了存取方式‧ 多數 View 會被填入來自
instance的資訊，其餘的會被填入來看Control File的資訊‧它們都會提供即時資訊‧ 例如V$INSTANCE或V$SYSSTAT，隨時都可以
使用，即使instance是在NOMOUNT模式中‧例如V$DATABASE或V$DATAFILE，要等到資料庫已被掛載(MOUNT)後才能被查詢，
此時控制檔(Control File)已被讀取‧相較之下，Static Data Dictionary Views(開頭為DBA、ALL、USER)只能在資料庫已開啟後才能被
查詢‧                                                                                                              
                                                                                                                 
Static Data Dictionary Views會在啟動時建立，在instance存在期間被更新，並在關機後被捨棄。這代表它們只包含自啟動後所
累積的值；如果你的資料庫已經持續開啟了六個月，就會有那段期間建立累積的資料‧在關機／啟動後，就會從頭開始‧       
 
=====================================================================================================================
Oracle contains a set of underlying views that are maintained by the database server and accessible to the       
database administrator user SYS. These views are called dynamic performance views because they are continuously  
updated while a database is open and in use, and their contents relate primarily to performance.                 
                                                                                                                 
                                                                                                                 
Although these views appear to be regular database tables, they are not. These views provide data on internal disk
structures and memory structures. You can select from these views, but you can never update or alter them.       
                                                                                                                 
                                                                                                                 
//Note:                                                                                                          
(1)You can query the dynamic performance views to extract information from them. However, only simple queries are
   supported. If sorts, joins, GROUP BY clauses and the like are needed, then you should copy the information from
   each V$ view into a table (for example, using a CREATE TABLE ... AS SELECT statement), and then query from    
   those tables.                                                                                                 
                                                                                                                 
(2)Because the information in the V$ views is dynamic, read consistency is not guaranteed for SELECT operations on
   these views.                                                                                                  
                                                                                                                 
                                                                                                                 
The catalog.sql script contains definitions of the views and public synonyms for the dynamic performance views.  
You must run catalog.sql to create these views and synonyms. After installation, only user SYS or anyone with    
SYSDBA role has access to the dynamic performance tables.                                                         
                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//V$ Views                                                                                                       
The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the  
prefix V$. Database administrators and other users should access only the V$ objects, not the V_$ objects.       
                                                                                                                 
                                                                                                                 
The dynamic performance views are used by Oracle Enterprise Manager, which is the primary interface for accessing
information about system performance. After an instance is started, the V$ views that read from memory are       
accessible. Views that read data from disk require that the database be mounted, and some require that the       
database be open.                                                                                                 
                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//GV$ Views                                                                                                      
For almost every V$ view described in this chapter, Oracle has a corresponding GV$ (global V$) view. In Real     
Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In     
addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The     
INST_ID column displays the instance number from which the associated V$ view information was obtained. The      
INST_ID column can be used as a filter to retrieve V$ information from a subset of available instances.          
For example, the following query retrieves the information from the V$LOCK view on instances 2 and 5:            
SQL> SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5;                                                     
=====================================================================================================================

SQL> startup nomount pfile='c:\initorcl.ora'
ORACLE 執行處理已啟動.
 
SQL> startup force pfile='c:\initorcl.ora'
ORACLE 執行處理已啟動.
 
備註: 此方式只能應急，因為該Oracle Instaince每次啟動時，還是會去 Oracle_Home路徑下搜尋相關參數檔‧當然也有一種情形
         就是，當你需要嘗試參數檔內某個參數，但又不想要動到目前正常使用的參數檔，即可用這種方式，因為即使不小心設壞了
      ，只要Instance重帶即可‧

//刪除Oracle Instance
說明: net stop oracleserviceorcl
                                           ^^^^
                                              |
                                              +----------------> orcl為該Oracle中的Instance Name
步驟: C:\>net stop oracleserviceorcl
      OracleServicerockdb 服務正在停止....
      OracleServicerockdb 服務已經成功停止。
步驟: C:\>oradim -delete -srvc oracleserviceorcl
      執行處理已刪除.
 
//修改Oracle Instance的名稱
C:\>oradim -edit -sid orcl -newsid rockdb -intpwd rockdbpwd -pfile "c:\oracle\product\10.2.0\db_1\database\initrockdb.ora"
執行處理已刪除.
執行處理已建立.
備註: 當你改變Instance Name後，你需要確認，在Oracle_Home有置放相關的Initialization Parameter File、Password File，
          以本例，你就需要確定在Oracle_Home下有 initrockdb.ora 、 PWDrockdb.ORA 這二個檔案‧

Oracle的SQL-PLUS其實是有支援多國語系的回應，只要你設定好NLS_LANG環境變數即可‧
 
 
//使用美國語系
範例: C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
      C:\>sqlplus rock_dba/123s@orcl as sysdba
      SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 19 16:59:13 2009
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options
      SQL> archive log list
      Database log mode              No Archive Mode
      Automatic archival             Disabled
      Archive destination            USE_DB_RECOVERY_FILE_DEST
      Oldest online log sequence     11
      Current log sequence           13
      SQL>
 
//使用台灣繁體中文語系
範例: C:\>set NLS_LANG=TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
      C:\>sqlplus rock_dba/123s@orcl as sysdba
      SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 7月 19 16:58:32 2009
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
      連線到:
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options
      SQL> archive log list
      資料庫日誌模式              無儲存模式
      可自動儲存             關閉
      儲存目的地            USE_DB_RECOVERY_FILE_DEST
      最早的線上日誌順序     11
      目前日誌順序           13
      SQL>

有些參數在PFILE模式是無法透過alter system的方式修改，需要直接改文字模式的參數檔
 
(1)參數檔為SPFILE型態                                                                                          
   範例: SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') AS "Init File Type", NVL(value,'null') AS Path     
           2  FROM v$parameter WHERE name = 'spfile';                                                          
              Init File Type         PATH                                                                      
              -------------------    -------------------------------------------------------                   
              SPFILE                 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILEORCL.ORA                     
                                                                                                               
   alter system set log_archive_start=true scope=spfile; ======> 可以修改                                      
   alter system set log_archive_start=true scope=memory                                                        
   alter system set log_archive_start=true scope=both                                                          
   alter system set log_archive_start=true;                                                                    
                                                                                                               
   alter system set fast_start_mttr_target=60 scope=spfile; ===> 可以修改                                      
   alter system set fast_start_mttr_target=60 scope=memory ====> 可以修改                                      
   alter system set fast_start_mttr_target=60 scope=both ======> 可以修改                                      
   alter system set fast_start_mttr_target=60; ================> 可以修改                                      
                                                                                                               
                                                                                                               
                                                                                                               
(2)參數檔為PFILE                                                                                               
   範例: SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') AS "Init File Type", NVL(value,'null') AS Path     
           2  FROM v$parameter WHERE name = 'spfile';                                                          
              Init File Type         PATH                                                                      
              -------------------    -------------------------------------------------------                   
              PFILE                  null                                                                      
                                                                                                               
   alter system set log_archive_start=true scope=spfile;                                                       
   alter system set log_archive_start=true scope=memory                                                        
   alter system set log_archive_start=true scope=both                                                          
   alter system set log_archive_start=true;                                                                    
                                                                                                               
   alter system set fast_start_mttr_target=60 scope=spfile;                                                    
   alter system set fast_start_mttr_target=60 scope=memory ====> 可以修改                                      
   alter system set fast_start_mttr_target=60 scope=both                                                       
   alter system set fast_start_mttr_target=60; ================> 可以修改    

//參數檔載入順序(Oracle 10g for windows版)
Oracle 10g 裝好後，雖然去查v$parameter，顯示的是該 "C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA" 位置(見參考1
)，但經實驗得知，其實該Instance是去 "C:\oracle\product\10.2.0\db_1\database" 該 Oracle_Home 路徑下依序找相關參數檔(見參考2)，
但為什麼參數檔最終是去讀 "C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA" 呢? 因為Oracle_Home下的參數檔
initorcl.ora的內容(PFILE型態)，是指向另一個參數檔(SPFILE型態)，請見參考3‧
 
如果去更改 initorcl.ora的預設的內容，或是作相關更改動作後，請見參考4，該Instance將不會間接去讀另一個路徑下的參數檔
 "C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA" 了‧
 
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//參考1
SQL> select name,value from v$parameter where name like '%pfile%';
     NAME     VALUE
     ------   ------------------------------------------------------
     spfile   C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
 
//參考2
Oracle Instance會在Oracle_Home下會按照如下順序找出可以讀取的參數檔
(1)%ORACLE_HOME %\database \SPFILE<SID>.ORA
(2)%ORACLE_HOME %\database \SPFILE.ORA
(3)%ORACLE_HOME %\database \INIT<SID>.ORA
//參考3
檔案位置: C:\oracle\product\10.2.0\db_1\database\initorcl.ora
檔案內容: SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileorcl.ora'
 
//參考4
SQL> create pfile from spfile; ==> 將c:\oracle\product\10.2.0\db_1\dbs\spfileorcl.ora的內容轉至C:\oracle\product\10.2.0\db_1\database\initorcl.ora
SQL> create spfile from pfile; ==> 將C:\oracle\product\10.2.0\db_1\database\initorcl.ora的內容轉至c:\oracle\product\10.2.0\db_1\database\spfileorcl.ora
SQL> shutdown immediate;
SQL> startup;
SQL> select name,value from v$parameter where name like '%pfile%';
     NAME     VALUE
     ------   ------------------------------------------------------
     spfile   C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILEORCL.ORA
備註: 當然你也可以不用多此一舉，直接將C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS路徑下的SPFILEORCL.ORA，拷貝至Oracle_Home的
          路徑下 "c:\oracle\product\10.2.0\db_1\database" ‧

//找出參數檔
檔案位置: C:\oracle\product\10.2.0\db_1\database\initorcl.ora
檔案內容: SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileorcl.ora'
檔案位置: C:\oracle\product\10.2.0\db_1\dbs\SPFILEORCL.ORA
檔案內容: orcl.__java_pool_size=4194304
          orcl.__large_pool_size=4194304
          orcl.__shared_pool_size=192937984
          orcl.__streams_pool_size=0
          *.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
          *.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
          *.compatible='10.2.0.1.0'
          *.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control02.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
          *.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
          *.db_block_size=8192
          *.db_domain=''
          *.db_file_multiblock_read_count=16
          *.db_name='orcl'
          *.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
          *.db_recovery_file_dest_size=2147483648
          *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
          *.job_queue_processes=10
          *.open_cursors=300
          *.pga_aggregate_target=177209344
          *.processes=150
          *.remote_login_passwordfile='EXCLUSIVE'
          *.sga_target=533725184
          *.undo_management='AUTO'
          *.undo_tablespace='UNDOTBS1'
          *.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
說明: 因為Oracle從initorcl.ora(PFILE型態)的內容，得知真的參數是用spfileorcl.ora(SPFILE型態)，但spfile是binary ，無法修改，需先轉一下‧
//將參數檔從SPFILE轉成PFILE
步驟: SQL> CREATE PFILE FROM SPFILE;
      已建立檔案.
//修改參數檔內的 "db_name" 成你所要的名稱
檔案位置: C:\oracle\product\10.2.0\db_1\database\initorcl.ora
檔案內容: orcl.__db_cache_size=327155712
          orcl.__java_pool_size=4194304
          orcl.__large_pool_size=4194304
          orcl.__shared_pool_size=197132288
          orcl.__streams_pool_size=0
          *.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
          *.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
          *.compatible='10.2.0.1.0'
          *.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control02.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
          *.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
          *.db_block_size=8192
          *.db_domain=''
          *.db_file_multiblock_read_count=16
          *.db_name='rock_db'
          *.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
          *.db_recovery_file_dest_size=2147483648
          *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
          *.job_queue_processes=10
          *.open_cursors=300
          *.pga_aggregate_target=177209344
          *.processes=150
          *.remote_login_passwordfile='EXCLUSIVE'
          *.sga_target=533725184
          *.undo_management='AUTO'
          *.undo_tablespace='UNDOTBS1'
          *.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
//匯出重建control file的Trace Log File
步驟: SQL> alter database backup controlfile to trace;
      已更改資料庫.
//查目前的Trace File的正確檔案和位置
步驟: SQL> select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace
        2  from v$process a,v$session b, v$parameter c, v$instance d
        3  where a.addr = b.paddr
        4        and b.audsid = userenv('sessionid')
        5        and c.name = 'user_dump_dest';
      TRACE
      --------------------------------------------------------------------------------
      C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_2628.trc
//從Trace File取出重建Control File的Script，並將 "CREATE CONTROLFILE REUSE DATABASE"
//修改成 "CREATE CONTROLFILE SET DATABASE"，待後面執行使用
檔案位置: C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_2628.trc
修改內容: CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
              MAXLOGFILES 16
              MAXLOGMEMBERS 3
              MAXDATAFILES 100
              MAXINSTANCES 8
              MAXLOGHISTORY 292
          LOGFILE
            GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
            GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
            GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
          -- STANDBY LOGFILE
          DATAFILE
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
          CHARACTER SET ZHT16MSWIN950
//Shutdown Database
步驟: SQL> shutdown immediate;
      資料庫關閉.
      資料庫已卸載.
      已關閉 ORACLE 執行處理.
//刪除原始的Control File
步驟: C:\oracle\product\10.2.0\oradata\orcl>dir *.ctl
       磁碟區 C 中的磁碟沒有標籤。
       磁碟區序號:  98B9-B271
      C:\oracle\product\10.2.0\oradata\orcl 的目錄
      2009/06/21  22:54         7,061,504 CONTROL01.CTL
      2009/06/21  22:54         7,061,504 CONTROL02.CTL
      2009/06/21  22:54         7,061,504 CONTROL03.CTL
                     3 個檔案      21,184,512 位元組
                     0 個目錄     521,949,184 位元組可用
      C:\oracle\product\10.2.0\oradata\orcl>del *.ctl
//將DB開啟至nomount狀態下，並重建Control File
步驟: SQL> startup nomount;
      ORACLE 執行處理已啟動.
      Total System Global Area  536870912 bytes
      Fixed Size                  1250016 bytes
      Variable Size             201329952 bytes
      Database Buffers          331350016 bytes
      Redo Buffers                2940928 bytes
      SQL> CREATE CONTROLFILE SET DATABASE "ROCK_DB" RESETLOGS  NOARCHIVELOG
        2      MAXLOGFILES 16
        3      MAXLOGMEMBERS 3
        4      MAXDATAFILES 100
        5      MAXINSTANCES 8
        6      MAXLOGHISTORY 292
        7  LOGFILE
        8    GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
        9    GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
       10    GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
       11  -- STANDBY LOGFILE
       12  DATAFILE
       13    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
       14    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
       15    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
       16    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
       17  CHARACTER SET ZHT16MSWIN950;
      已建立控制檔.
//開啟Database並做Resetlogs
步驟: SQL> alter database open resetlogs;
      已更改資料庫.
//查看Instance Name                                                                                              
步驟: SQL> SELECT instance_name,status FROM V$INSTANCE;                                                          
           INSTANCE_NAME    STATUS                                                                               
           ---------------- ------------                                                                         
           orcl             OPEN                                                                                 
                                                                                                                 
//查看Database Name                                                                                              
步驟: SQL> SELECT name,created,resetlogs_time FROM V$DATABASE;                                                   
           NAME      CREATED        RESETLOGS_TIME                                                               
           --------- -------------- --------------                                                               
           ROCK_DB   21-6?  -09     21-6?  -09                                                                   
                                                                                                                 
說明: 由此可知修改db_name參數，並不會連動修改原始的instance name‧Oracle是允許Instance Name和Database Name不一樣‧