月归档：三月 2012

关于9I中sga_max_size参数描述

发表于 2012 年 3 月 29 日由惜分飞

不设置sga_max_size参数

SQL> show sga;

Total System Global Area  420549952 bytes
Fixed Size                   451904 bytes
Variable Size             201326592 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes

SQL> select sum(bytes)from v$sgastat;

SUM(BYTES)
----------
 420538688

SQL> !ipcs -m       

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x9ba476a4 65536      oracle    640        440401920  65       

SQL> alter system set db_cache_size=300M;
alter system set db_cache_size=300M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

1)当sga_max_size不设置时，数据库启动时，会使用数据库默认分配sga大小为初始化值
2)当sga_max_size不设置时，不能在线扩展组件内存大小(使得sga大于当前大小)

设置sga_max_size参数

SQL>  alter system set sga_max_size=600M scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  638654020 bytes
Fixed Size                   452164 bytes
Variable Size             419430400 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> !ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x9ba476a4 98304      oracle    640        658505728  65                      

SQL> select sum(bytes)from v$sgastat;

SUM(BYTES)
----------
 420538948

SQL> alter system set db_cache_size=405M;

System altered.


SQL> select 638654020/1024/1024 from dual;

638654020/1024/1024
-------------------
         609.067936

说明sga中剩余空闲内存0.01M
SQL> select 609.067936-sum(bytes)/1024/1024 from v$sgastat;

609.067936-SUM(BYTES)/1024/1024
-------------------------------
                     .010742244

继续增加组件大小
SQL> alter system set db_cache_size=416M;

System altered.

SQL> select sum(bytes)/1024/1024 from v$sgastat;

SUM(BYTES)/1024/1024
--------------------
          609.057194

SQL>  select 638654020/1024/1024 from dual;

638654020/1024/1024
-------------------
         609.067936

SQL> alter system set db_cache_size=417M;   
alter system set db_cache_size=417M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

这里显示，当db_cache_size增加到415M的时候，sga只有0.01M剩余
但是直到db_cache_size增加到417的时候才报错

1)当sga有剩余时，可以动态调整sga中的部分组件(java_pool_size不能在线设置)
2)当sga没有剩余时，如果继续增加某组件的内存，在一定的范围内，sga会自动调整其他组件大小，以实用该值增加

关于sga_max_size总结
1)如果你的系统内存比较紧张，对停机时间要求不是特别严格，那可以不设置sga_max_size参数，这样在重启数据库设置sga组件的时候，不会因忘记设置sga_max_size而导致不能正常启动
2)如果你的系统内存充足，对停机有严格限制，那建议设置一个较大的sga_max_size，后续可以根据需求动态在线调整sga部分组件

发表在 Oracle | 评论关闭

DB2入门操作之一

发表于 2012 年 3 月 28 日由惜分飞

1.DB2启动关闭

--关闭db2
[db2inst1@xifenfei ~]$ db2stop
03/28/2012 09:23:39     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

--开启db2
[db2inst1@xifenfei ~]$ db2start
03/28/2012 09:23:55     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

2.查看DB2数据库

[db2inst1@xifenfei ~]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = TOOLSDB
 Database name                        = TOOLSDB
 Local database directory             = /home/db2inst1
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

3.连接DB2数据库

[db2inst1@xifenfei ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.4

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to TOOLSDB

   Database Connection Information

 Database server        = DB2/LINUX 9.7.4
 SQL authorization ID   = DB2INST1
 Local database alias   = TOOLSDB

4.查看数据库中包含包

db2 => list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------

  0 record(s) selected.

db2 => create table t_xff (id int,name varchar(100))
DB20000I  The SQL command completed successfully.
db2 => list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-03-28-09.29.54.572395

  1 record(s) selected.

5.常见DML操作

db2 =>  insert into t_xff values(1,'xifenfei')
DB20000I  The SQL command completed successfully.
db2 => insert into t_xff values(2,'www.xifenfei')
DB20000I  The SQL command completed successfully.
db2 => select * from t_xff 

ID          NAME                
----------- ---------------------------------------
          1 xifenfei           
          2 www.xifenfei       

  2 record(s) selected.

db2 => delete from t_xff where id=1
DB20000I  The SQL command completed successfully.
db2 => select * from t_xff

ID          NAME                                        
----------- -----------------------------------------
          2 www.xifenfei                             

  1 record(s) selected.

db2 => quit
DB20000I  The QUIT command completed successfully.