ORACLE ARCHIVE LOG Mode 筆記

ORACLE DATABASE 切換到 Archive LOG 或 NO ARCHIVE LOG 模式必須關閉、重新啟動資料庫。

切換到ARCHIVE LOG 模式並不表示系統會自動執行 ARCHIVE LOG，必須下指令執行，如果希望一開機就自動執行必須在 spfile 中作設定。

切換到ARCHIVE LOG 模式後應立即作備份的動作，如果使用之前的備份回復資料，資料只能回復至 NOARCHIVE LOG Mode 時的狀況。

檢查是否為 Archive Log 模式
SQL>select archiver select * from v$log;
SQL>archive log list;

更改 ARCHIVE/NOARCHIVE LOG 模式步驟：
1.SQL>shutdown immediate
2.SQL>startup mount
3.SQL>alter database archivelog/noarchivelog;
4.SQL>alter database open;
5.backup full database and control file;

啟動 Archive LOG Mode
SQL>alter system archive log start/stop;

變更啟動 Parameter，讓資料庫一啟動就自動執行 ARCHIVE LOG
SQL>alter system set log_archive_start=true scope=spfile;
或是在 pfile 中加入 log_archive_start=true

查詢 ARCHIVE LOG 狀況
SQL>Archive Log List

其它一些相關的設定參數和查詢
SQL>show parameter log_archive_format
SQL>alter SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3 scope=spfile sid='*';;
SQL>alter SYSTEM SET log_archive_dest_1 = "location=C:Oracleoradataoradbarchive" scope=spfile sid='*';
SQL>alter SYSTEM SET log_archive_format = %%ORACLE_SID%%T%TS%S.ARC scope=spfile sid='*';

那些ORACLE 物件支援重新命名

物件	支援	說明
CLUSTER	X	-
CONSTRAINT	O	ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name
CONTROL FILE	o	Alter the control_files parameter using the ALTER SYSTEM comamnd. Shutdown the database. Rename the physical file on the OS. Start the database.
COLUMN	O	ALTER TABLE table_name RENAME COLUMN old_name TO new_name
DATAFILE	O	Shutdown the database. Rename the physical file on the OS. Start the database in mount mode. Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary. Open the database.
DATABASE NAME	O	SQL>alter system switch logfile; SQL>alter database backup controlfile to trace; SQL>shutdown Modify (and optionally rename) the created trace file: create new controlfile modify db_name in init.ora SQL>STARTUP
FUNCTION	X	-
INDEX	O	ALTER INDEX old_name RENAME TO new_name;
INDEX PARTITION	O	ALTER INDEX index_name RENAME PARTITION ptn_name TO new_name
INDEX SUB PARTITION	O	ALTER INDEX index_name RENAME SUBPARTITION ptn_name TO new_name
INSTANCE	O	SQL>SHUTDOWN change ORACLE_SID (方法參考DATABASE NAME 更名) SQL>STARTUP
LOB	O	ALTER TABLE T MOVE LOB(lob_column) STORE AS newlogseg_name;
LOGFILE	X	Shutdown the database. Rename the physical file on the OS. Start the database in mount mode. Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary. Open the database.
OUTLINE	O	ALTER OUTLINE old_name RENAME TO new_name
PACKAGE	X	-
PACKAGE BODY	X	-
PROCEDURE	X	-
SEQUENCE	O	RENAME oldseq_name TO newseq_name;
SYNONYM	X	-
SCHEMA	X	-
TABLE	O	RENAME old_table TO new_table;
TABLE PARTITION	O	ALTER TABLE table_name RENAME PARTITION ptn_name TO new_name;
TABLE SUB PARTITION	O	ALTER TABLE table_name RENAME SUBPARTITION ptn_name TO new_name
TRIGGER	O	ALTER TRIGGER old_name RENAME TO new_name
TABLESPACE	O	ALTER TABLESPACE old_name RENAME TO new_name [10g new]
VIEW	O	RENAME old_table TO new_table;

ORACLE SELECT 查詢指定傳回筆數

ORACLE 中 SELECT 指令沒有類似 MySQL 中有 LIMIT 的參數可以使用來限制傳回資料的筆數，但是可以利用 ORACLE 中 ROWNUM 的值作一點手腳來限制傳回值的範圍。

ROWNUM 說明：

1.	ORACLE 使用 ROWNUM 作為查詢結果行的編號，第一行是1，第二行是2， 以此類推，可以用於限制查詢返回的總行數。
2.	ROWNUM 的值在查詢結果輸出時自動產生，因此不能以任何表格名稱作為首碼，因此下面的結果查詢不到任何的記錄。
	SQL>select rownum, a, b from table_a where rownum=2; SQL>select rownum, a, b from table_a where rownum>5;

使用 ROWNUM 限制資料範例：
查詢表格 TABLE_A中欄位 ID，並以 ID 排序，限制第 5筆至第10筆。
SQL>SELECT * FROM (SELECT ROWNUM ROW_ID, ID FROM TABLE_A ORDER BY ID) WHERE ROW_ID BETWEEN 5 AND 10;

SQL*Plus 使用環境的一些限制

項目	限制
filename length	system dependent
username length	30 bytes
user variable name length	30 bytes
user variable value length	240 characters
command-line length	2500 characters
length of a LONG value entered through SQL*Plus	LINESIZE value
LINESIZE	system dependent
LONGCHUNKSIZE value	system dependent
output line size	system dependent
line size after variable substitution	3,000 characters (internal only)
number of characters in a COMPUTE command label	500 characters
number of lines per SQL command	500 (assuming 80 characters per line)
maximum PAGESIZE	50,000 lines
total row width	60,000 characters for VMS; otherwise, 32,767 characters
maximum ARRAYSIZE	5000 rows
maximum number of nested scripts	20 for VMS, CMS, Unix; otherwise, 5
maximum page number	99,999
maximum PL/SQL error message size	2K
maximum ACCEPT character string length	240 Bytes
maximum number of DEFINE variables	2048

SQL*Plus COPY 命令筆記

透過 SQL*Plus(9iR2) COPY 命令可以直接在不同資料庫間複製表格內容，支援模式如下：

由本地資料庫至遠端資料庫 (預設)
由遠端資料庫至本地資料庫
由遠端資料庫至遠端資料庫

來源表格欄位支援以下資料型態：CHAR、DATE、LONG、NUMBER、VARCHAR2，但不支援 LOB 這類資料型態。

SQL*Plus COPY 語法

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query

參數說明：

FROM database	指定資料來源資料庫
TO database	指定目標資料庫
database	以 username[/password] @connect_identifier 格式指定
APPEND	會直接將複製資料INSERT至指定目的表格，如果指定目的表格不存在會先建立指定目的表格。
CREATE	複製資料前會先建立指定目的表格，如果指定目的表格已經存在會傳回錯誤訊息。
INSERT	如果指定目的表格已經存在會傳回錯誤訊息，使用這個選項必須指定欄位名稱。
REPLACE	如果指定目的表格已經存在會刪除舊有的表格，如果不存在則複製資料前會建立表格。
destination_table	指定目的表格名稱。
(column, column, column, ...)	以逗點隔開的目的表格的資料欄別名。
USING query	任何有效的 SQL SELECT 敘述句。

通常 COPY 指令是用於 Oracle 和 non-Oracle 資料庫間的表格複製，因此如果同樣是在 ORACLE，應該使用 SQL 指令 (CREATE TABLE AS 和 INSERT) 。

Usage：
To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.

The SQL*Plus SET LONG variable limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.

SQL*Plus performs a commit at the end of each successful COPY. If you set the SQL*Plus SET COPYCOMMIT variable to a positive value n, SQL*Plus performs a commit after copying every n batches of records. The SQL*Plus SET ARRAYSIZE variable determines the size of a batch.

Some operating environments require that service names be placed in double quotes.

範例：

COPY FROM HR/your_password@HQ TO JOHN/your_password@WEST REPLACE WESTEMPLOYEES USING SELECT * FROM EMPLOYEES

參考文件：

SQL*Plus User's Guide and Reference Release 9.2：Part Number A90842-01：APPEND B:SQL*Plus COPY Command

將 Oracle 9i export 出的 dump file 匯入指定帳號、指定表格空間

如何將 Oracle 9i export 工具(exp) 輸出的 Oracle binary-format dump file 匯入指定帳號、並指定表格空間？

網路上有不少人提出了這樣奇怪的問題，這個問題很重要嗎？為什麼會有這樣的需求？在我看來有這樣的問題是一件很奇怪的事。

如果是客戶對軟體公司的產品或修補發生這樣的需求，可以推測很可能是軟體公司的開發人員直接將開發平台上的資料庫匯出來給客戶，而不是採用執行SQL Script的方式來更新資料庫中物件的Schema或資料，對很多公司有重要 Oracle 資料庫的 DBA，大概都很難接受這樣的事情，因為根本不曉得匯入的物件是不是有問題，也很難確定匯出的資料，轉到匯入的平台版本是否不會有問題(是否上了相同的修補檔)。萬一不幸匯入中的物件與原資料庫中有相同的物件名稱，又沒注意到一不小心可能就造成大災難了。如果資料庫大的話匯入的工程浩大又費時，當然匯入前要先作完整備份、匯入後要馬上作完整備份比較安全，如果是Archive Log Mode，那一堆的Archive Log 就要思考一下，匯入時是不是要暫時停止 Archive Log 的運作。

Oracle export 及 import 工具是用來轉移 Oracle Database 到另一個平台的工具，如果僅僅是想透過這個工具在不同的帳號和指定的表格空間中轉移，可能會很失望，因為他們不是為這個目的被設計出來的，不信邪，試到鬍鬚白了又打結也一樣。

以下是 Oracle Database Administration Fundamentals II 教材對 export 及 import 工具的說明：

The Export utility provides a simple way for you to transfer data objects between Oracle database, even if they reside on platforms with different hardware and software configurations. When you run Export against an Oracle database, objects(such as tables) are extracted, followed by their related objects(such as indexes, comments, and grants), if any. The extracted data is written to an Export file, which is an Oracle binary-format dump file that is typically located on disk or tape. The Import utility reads the object definitions and table data from an Export dump file. It inserts the data objects into an Oracle database.

Import 工具的執行順序過程：

1. 建立新表格。
2. 資料匯入表格。
3. 建立索引。
4. 匯入Trigger。
5. 設定新表格上的 constraints。
6. 建立其它 bimap，function，domain index。

首先使用 Oracle 9i export 和 import 工具有幾個觀念要釐清：

1. 用來執行 import 工具的版本不能低於 export 版本。
2. Oracle Export 及 Import 工具沒有任何選項、參數可以指定匯入的表格空間 (Tablespace)。
3. Import 時會在目的 "資料庫帳號下的表格空間(Tablespace)" 中建立物件，而不是在目的 "帳號預設的表格空間(Tablespace)" 中建立物件。匯入時 import 程式在建立表格時會指定使用的表格空間，而表格空間名稱就是原匯出時的名稱，大部份情況在指定的表格空間不存在時，會改用匯入帳號預設的表格空間，但當表格中有比較特別的資料型態時，則無法使用匯入帳號預設的表格空間建立表格，而造成匯入失敗，這也是最多人搞不清楚的一點。
4. 表格在表格空間裡並不是不可以被移動的，可以透過簡單的指令移動。
5. 索引在表格空間裡雖然無法被移動的，但是可以透過簡單的指令重建。

觀念釐清後我們可以利用 export 及 import 工具先將資料庫物件轉移到目標資料庫，再作一些工作來達成目標(指定帳號、並指定表格空間)，以下是簡單的步驟：

1. 建立所需表格空間及調整表格空間的存取權限讓 Oracle Import 工具能正確無誤的執行，匯入到指定的帳號下。imp 中加入INDEXES=N參數、INDEXFILE=index.sql，產生建立索引指令的檔案 index.sql，索引等到表格都搬到正確表格空間再重建。
2. 若由較低版本資料庫匯出的檔案，可能需要執行一些修正檔(略，自行處理)。
3. 使用 ALL_TABLES View 查詢那些表格需要被移動。
4. 使用 表格在表格空間移動的指令。移動指令：ALTER TABLE <table name> MOVE TABLESPACE <tablespace name>
5. 如果表格中有 LOB 這類資料類型的欄位，則還需將這些欄位的資料移至新的表格空間。查詢 ALL_TAB_COLUMNS View；
6. 移動LOB 這類資料類型的欄位資移至新的表格空間。移動指令：ALTER TABLE <table name> MOVE LOB(lobseg name) STORE AS (TABLESPACE <>tablespace name);
7. 編輯 index.sql 檔案中 tablespace 的設定。
8. 執行 index.sql 重建索引，或自行下指令重建需要移動表格空間的索引。重建索引的指令：ALTER INDEX <index name> REBUILD TABLESPACE <tablespace name>。

步驟 3、4，5、6，7、8 是可以寫成一個PL/SQL程序來執行的(略)。

其它參考資訊－Oracle 10g 如何變更 TABLESPACE NAME：

1. Oracle 10g 新增指令，可以透過指令變更 TABLESPACE NAME，這和之前的版本比較，舊版本要變更 TABLESPACE NAME 是件浩大的工程。
2. 指令：ALTER TABLESPACE <tablespace old name> RENAME TO <tablespace new name>;
3. 資料庫 compatibility level 最低必須設為 10.0.1。
4. 不能更改 SYSTEM、SYSAUX tablespace 表格名稱。
5. 不能更改 offline tablespace。
6. 不能更改 含有 offline datafiles 的 tablespace 名稱。
7. 更改 tablespace 名稱，不會變更 tablespace identifier。
8. 更改 tablespace 名稱，不會變更 tablespace 中 datafile 的名稱。