Oracle 10g new feature - 여태 몰랐던..

Faster Startup

The days when dinosaurs ruled the earth and 2GB memory was considered large are gone. Now, it's not uncommon to see large buffer caches to the tune of 100GB. When the instance is started, it might take several minutes, or even hours, to initialize a buffer cache of this size.

If you look deeper into the situation, you will notice that the entire buffer cache need not be up when the database instance starts. After the instance starts, the buffer cache is empty, which gradually fills up when users select data from tables. So, there is no need to initialize the entire buffer cache when the instance starts.

In Oracle Database 10g Release 2, this behavior is accounted for in the startup logic. When you start the instance, only 10% of the buffer cache is initialized; the rest is initialized after the database is opened by the checkpoint process. This new approach reduces instance startup time significantly.

Bear in mind, however, that until the entire buffer cache is initialized, automatic buffer cache sizing is not available.

아시다 시피 SGA memory 영역은 Database가 nomount 단계에서 할당됩니다. 

이 new feature는 nomount 단계에서는 10%의 buffer pool만 할당하고 나머지는 instance가 open 된 후에 할당한다는 의미입니다.

Drop Empty Datafiles

Imagine that you just added a datafile to the wrong directory or tablespace—a fairly common error. All is not lost; the datafile doesn't contain any data yet, so you can easily drop it, right?

Unfortunately, you can't. Prior to Oracle Database 10g Release 2, your only clean option for removing a datafile is to drop the entire tablespace and then rebuild it without that particular file. If the tablespace contains data, you have to go through the time-consuming and laborious process of storing the data on a separate location and reinstating it. In addition to its inconvenience, this process makes the tablespace unavailable.

Thankfully, in Oracle Database 10g Release 2 the process has been simplified: You can just drop the datafile. For example, the following command will remove the indicated datafile from the tablespace as well as from the server.

alter tablespace users drop datafile '/tmp/users01.dbf'
 /

There are a couple restrictions, however: The datafile must be empty to be dropped. You can't drop the last datafile in a tablespace; the tablespace itself must be dropped. And the tablespace must be online and in read-write status.

10g 이전 버전에서는 데이터파일을 잘 못 붙일 경우 이거 띄어 내려면 tablespace 전체를 재구성해야 하죠. 

예전 OPS 쓸때 raw device가 아닌 filesystem에 datafile을 추가하는 등의 황당한 경우도 있었습니다.. ㅋ

drop empty datafiles new feature는 이런 유저 실수로 인한 막대한 작업을 더이상 안해도 되게 해주겠군요. 

Catch the Error and Move On: Error Logging Clause

Suppose you are trying to insert the records of the table ACCOUNTS_NY to the table ACCOUNTS. The table ACCOUNTS has a primary key on ACC_NO column. It's possible that some rows in ACCOUNTS_NY may violate that primary key. Try using a conventional insert statement:

SQL> insert into accounts
  2  select * from accounts_ny;
insert into accounts
*
ERROR at line 1:
ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated

None of the records from the table ACCOUNTS_NY has been loaded. Now, try the same with error logging turned on. First, you need to create a table to hold the records rejected by the DML statement. Call that table ERR_ACCOUNTS.

exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')

Next, execute the earlier statement with the error-logging clause.

SQL> insert into accounts
  2  select * from accounts_ny
  3  log errors into err_accounts
  4  reject limit 200
  5  /

6 rows created.

Note that the table ACCOUNTS_NY contains 10 rows yet only six rows were inserted; the other four rows were rejected due to some error. To find out what it was, query the ERR_ACCOUNTS table.

SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
 2  from err_accounts;

ORA_ERR_NUMBER$   ORA_ERR_MESG$                                       ACC_NO
 ---------------  --------------------------------------------------  ------
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi  9997
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS)vi  9998
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9999
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 10000
               olated

Note the columns ORA_ERR_NUMBER$, which show the Oracle error number encountered during the DML statement execution, and the ORA_ERR_MESG$, which shows the error message. In this case you can see that four records were rejected because they violated the primary key constraint PK_ACCOUNTS. The table also captures all the column of table ACCOUNTS, including the column ACC_NO. Looking at the rejected records, note that these account numbers already exist in the table; hence the records were rejected with ORA-00001 error. Without the error-logging clause, the whole statement would have failed, with no records rejected. Through this clause, only the invalid records were rejected; all others were successful.

이 기능은 sql*loader의 bad file 같은 기능입니다. 

특정 에러가 발생해도 이를 logging만 하고 나머지 데이터는 처리를 해주는 군요.  

PK constraint violation 발생하면 이거 처리하는 것도 좀 귀찮은 작업인데, 요 기능 쓰면 약간 편해지겠습니다. ^^

그나 저나 아직도 10g new feature를 모르고 있다니... 좌절이다~

참고: 

Oracle Database 10g: Top Features for DBAs

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!