oracle基礎練習1－4章
/* 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  
＊蒙昭良
*環境：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  
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12778571/viewspace-343315/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle基礎練習5章 重做日誌Oracle
- oracle基礎練習6章 表空間和資料檔案Oracle
- MySQL基礎練習MySql
- JavaScript基礎練習JavaScript
- expdp基礎練習
- oracle基礎練習7－10章 儲存結構、回滾段、管理表Oracle
- oracle基礎練習11－15章 索引維護、資料庫完整性等Oracle索引資料庫
- JAVA 基礎練習題Java
- 基礎練習 Sine之舞
- IOS基礎-Masonry 練習iOS
- Java基礎 --- 綜合練習Java
- linux基礎練習題Linux
- Python基礎練習題Python
- 基礎練習 高精度加法(java)Java
- Java學習之基礎語法練習Java
- python基礎語句小練習Python
- 表單運用和基礎練習
- java基礎小練習（31-35）Java
- 9道python基礎練習題Python
- python基礎 while迴圈練習PythonWhile
- Day3--練習MySQL基礎語句MySql
- linux基礎練習題、面試題（二）Linux面試題
- 130701基礎練習-first
- rman 搭建，備份，恢復基礎練習
- python基礎（四）----列表、字典練習題Python
- Oracle 基礎溫習3Oracle
- java語言程式設計基礎篇第七章程式設計練習題Java程式設計
- Java程式設計基礎24——遞迴練習Java程式設計遞迴
- 藍橋杯：基礎練習 查詢整數
- 多執行緒基礎練習實踐篇執行緒
- Python3 （基礎練習）猴子吃桃Python
- javascript基礎（Dom查詢練習）（二十七）JavaScript
- 50道Java基礎程式設計練習題Java程式設計
- 藍橋杯 【基礎練習】 特殊迴文數
- oracle 基礎溫習8 包Oracle
- Oracle基礎學習筆記Oracle筆記
- 1章 基礎概念
- Java第十二章練習習題Java