oracle基础练习1－4章-优快云博客

本文介绍了Oracle数据库中关键参数的查看与调整方法，包括SGA最大尺寸、共享池大小及数据库缓存大小等，并展示了如何通过SQL命令进行监控与调整。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/* 2008/06/11 星期三
＊蒙昭良
*环境：linux5 + Oracle10gR2
*oracle基础练习1－4章
＊
*/
SQL> select name,value from v$sysstat
2 where name like 'sort%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                         1992
sorts (disk)                                                              0
sorts (rows)                                                           9102

SQL> show sga;

Total System Global Area 268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
SQL> desc v$parameter;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NUM                                                NUMBER
NAME                                               VARCHAR2(80)
TYPE                                               NUMBER
VALUE                                              VARCHAR2(512)
DISPLAY_VALUE                                      VARCHAR2(512)
ISDEFAULT                                          VARCHAR2(9)
ISSES_MODIFIABLE                                   VARCHAR2(5)
ISSYS_MODIFIABLE                                   VARCHAR2(9)
ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
ISMODIFIED                                         VARCHAR2(10)
ISADJUSTED                                         VARCHAR2(5)
ISDEPRECATED                                       VARCHAR2(5)
DESCRIPTION                                        VARCHAR2(255)
UPDATE_COMMENT                                     VARCHAR2(255)
HASH                                               NUMBER

SQL> select count(*) from v$parameter;

COUNT(*)
----------
       261

SQL> select name,type,value from v$parameter
2 where name='sga_max_size';

NAME
--------------------------------------------------------------------------------
      TYPE
----------
VALUE
--------------------------------------------------------------------------------
sga_max_size
         6
268435456


SQL> l
1 select name,type,value from v$parameter
2* where name='sga_max_size'
SQL> select name,type,value/1024/1024 "MB" from v$parameter
2 where name='sga_max_size';

NAME
--------------------------------------------------------------------------------
      TYPE         MB
---------- ----------
sga_max_size
         6        256


SQL> show parameter shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 256M
SQL> alter system set shared_pool_size=32M;

System altered.

SQL> show parameter shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 32M
SQL> show parameter db_cache_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0
SQL> alter system set db_cache_size=48M;

System altered.

SQL> show parameter db_cache_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 48M
SQL> show db_cache_advice;
SP2-0735: unknown SHOW option beginning "db_cache_a..."
SQL> show parameter db_cache_advice;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON

SQL> select id,name,block_size,advice_status from v$db_cache_advice;

        ID NAME                 BLOCK_SIZE ADV
---------- -------------------- ---------- ---
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON


21 rows selected.

SQL> alter system set db_cache_advice=off;

System altered.

SQL> select id,name,block_size,advice_status from v$db_cache_advice;

        ID NAME                 BLOCK_SIZE ADV
---------- -------------------- ---------- ---
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF
         3 DEFAULT                    8192 OFF

21 rows selected.

SQL> alter system set db_cache_advice=on;

System altered.

SQL> select id,name,block_size,advice_status from v$db_cache_advice;

        ID NAME                 BLOCK_SIZE ADV
---------- -------------------- ---------- ---
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON
         3 DEFAULT                    8192 ON


21 rows selected.

SQL> show parameter;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
_allow_resetlogs_corruption          boolean     TRUE
_corrupted_rollback_segments         string      _SYSSMU1$, _SYSSMU2$, _SYSSMU3
                                                 $, _SYSSMU4$, _SYSSMU5$, _SYSS
                                                 MU6$, _SYSSMU7$, _SYSSMU8$, _S
                                                 YSSMU9$, _SYSSMU10$
_offline_rollback_segments           string      true
active_instance_count                integer
aq_tm_processes                      integer     0
archive_lag_target                   integer     0
asm_diskgroups                       string


SQL> show sga;

Total System Global Area 268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
SQL> select name,type,value from v$parameter
2 where name='sga_max_size';

NAME
--------------------------------------------------------------------------------
      TYPE
----------
VALUE
--------------------------------------------------------------------------------
sga_max_size
         6
268435456


SQL> l
1 select name,type,value/1024/1024 "MB",value/1024/1024/256*100 "%" from v$parameter
2* where name like '%size%'

SQL> connect system/mzl as sysdba
Connected.
SQL> select sid,serial#,username,type from v$session;

       SID    SERIAL# USERNAME                       TYPE
---------- ---------- ------------------------------ ----------
       142          3                                BACKGROUND
       144          3                                BACKGROUND
       151          8                                BACKGROUND
       154          3                                BACKGROUND
       155          3                                BACKGROUND
       156          1                                BACKGROUND
       159          9 SYS                            USER
       160          1                                BACKGROUND
       161          1                                BACKGROUND
       162          1                                BACKGROUND
       163          1                                BACKGROUND

18 rows selected.

SQL> desc dba_users;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
LOCK_DATE                                          DATE
EXPIRY_DATE                                        DATE
DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
CREATED                                   NOT NULL DATE
PROFILE                                   NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
EXTERNAL_NAME                                      VARCHAR2(4000)

SQL> select username,account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
PERFSTAT                       OPEN
RMAN                           OPEN
HR                             OPEN
RISENET                        OPEN
SCOTT                          EXPIRED & LOCKED
TSMSYS                         EXPIRED & LOCKED
BI                             EXPIRED & LOCKED
PM                             EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
IX                             EXPIRED & LOCKED
SH                             EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
DIP                            EXPIRED & LOCKED
OE                             EXPIRED & LOCKED
DBSNMP                         OPEN
SYSMAN                         OPEN
MDSYS                          EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
DMSYS                          EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
ANONYMOUS                      EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
XDB                            EXPIRED & LOCKED                        &nbsp

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-343315/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-343315/