遊標是 SQL 的一個記憶體工作區，由系統或使用者以變數的形式定義。遊標的作用就是
用於臨時儲存從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁碟的表
中調到計算機記憶體中進行處理，最後將處理結果顯示出來或最終寫回資料庫。這樣資料
處理的速度才會提高，否則頻繁的磁碟資料交換會降低效率。
遊標有兩種型別：顯式遊標和隱式遊標，在前述程式中用到的 SELECT…INTO…查詢
語句，一次只能從資料庫中提取一行資料，對於這種形式的查詢和 DML 操作，系統都會
使用一個隱式遊標。但是如果要提取多行資料，就要由程式設計師定義一個顯式遊標，並通
過與遊標有關的語句進行處理。顯式遊標對應一個返回結果為多行多列的 SELECT 語句。
遊標一旦開啟，資料就從資料庫中傳送到遊標變數中，然後應用程式再從遊標變數
中分解出需要的資料，並進行處理。
MMON和MMNL：在Oracle10g中，有兩個後臺程式是新增的，
在Oracle不同的文件中，對這兩個程式的解釋存在歧義。
MMON 應該是 Memory Monitor 的縮寫，但是在有的文件中被記錄為Manageability Monitor ，這應當是10g早期版本中的稱呼，只不過後來發生了變更。
這個程式的主要作用如下：
The memory monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.
另外一個程式是 MMNL ，是 Memory Monitor Light (MMNL) 的縮寫，在部分文件中記錄為 Manageability Monitor Light .
這個程式的作用如下：
The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

orcl_mmon_3540.trc
error 1000 happened during job cache initialization
*** 2013-02-21 12:09:27.890
error 1000 happened during job cache initialization
*** 2013-02-21 12:09:51.906
*** KEWUXS - encountered error: (ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
)
*** KEWFADM: Error=13515 encountered by Auto DBFUS Main.
*** KEWUXS - encountered error: (ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
)
*** KEWFACM: Error=13515 encountered by Auto CPU Usage Main.
*** KEWUXS - encountered error: (ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
)
看下游標連結數、最大連線數
SQL> show parameter open_cursors; --&gt遊標最大連結數

NAME TYPE VALUE
------------------------------------ ----------- ------------------
open_cursors integer 50
SQL> select count(*) from v$open_cursor; --&gt遊標連結數

COUNT(*)
----------
207
SQL> alter system set open_cursors=1000 scope=both; --&gt修改ORACLE最大遊標數
系統已更改。
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------
open_cursors integer 1000

Error 0 in kwqmnpartition-update_aqs_instance, aborting txn
ORA-0 encountered when generating server alert SMG-3503
ORA-1000 encountered when generating server alert SMG-3503

再看alert_oral.log還是每十分鐘報錯一遍
Thu Feb 21 14:33:08 2013
ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;
Thu Feb 21 14:40:12 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 14:50:13 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 15:00:13 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 15:10:14 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 15:20:14 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 15:30:15 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 15:40:15 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 15:50:16 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 16:00:17 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 16:10:18 2013
ORA-1000 encountered when generating server alert SMG-3000
Thu Feb 21 16:14:10 2013
Starting background process EMN0
EMN0 started with pid=23, OS id=5820
Thu Feb 21 16:14:10 2013
Shutting down instance: further logons disabled
Thu Feb 21 16:14:10 2013
Stopping background process QMNC
Thu Feb 21 16:14:11 2013
Stopping background process CJQ0
Thu Feb 21 16:14:12 2013
Stopping background process MMNL
Thu Feb 21 16:14:13 2013
Stopping background process MMON
Thu Feb 21 16:14:14 2013
Shutting down instance (immediate)
License high water mark = 7
Thu Feb 21 16:14:21 2013
ALTER DATABASE CLOSE NORMAL
Thu Feb 21 16:14:21 2013
SMON: disabling tx recovery
SMON: disabling cache recovery
Thu Feb 21 16:14:22 2013
Shutting down archive processes
Archiving is disabled
Thu Feb 21 16:14:27 2013
ARCH shutting down
ARC1: Archival stopped
Thu Feb 21 16:14:32 2013
ARCH shutting down
ARC0: Archival stopped
Thu Feb 21 16:14:33 2013
Thread 1 closed at log sequence 18
Successful close of redo thread 1
Thu Feb 21 16:14:34 2013
Completed: ALTER DATABASE CLOSE NORMAL
Thu Feb 21 16:14:34 2013
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu Feb 21 16:15:18 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
__shared_pool_size       = 75497472
shared_pool_size         = 75497472
__large_pool_size        = 4194304
__java_pool_size         = 4194304
__streams_pool_size      = 0
sga_target               = 285212672
control_files            = E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CTLF01.CTL
__db_cache_size          = 192937984
compatible               = 10.2.0.1.0
db_recovery_file_dest    = E:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
audit_file_dest          = E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
background_dump_dest     = E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
user_dump_dest           = E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
core_dump_dest           = E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
db_name                  = orcl
open_cursors             = 1000
PMON started with pid=2, OS id=8108
PSP0 started with pid=3, OS id=7060
MMAN started with pid=4, OS id=5072
DBW0 started with pid=5, OS id=3380
LGWR started with pid=6, OS id=6356
CKPT started with pid=7, OS id=2100
SMON started with pid=8, OS id=5704
RECO started with pid=9, OS id=4716
MMON started with pid=10, OS id=3932
MMNL started with pid=11, OS id=6604
Thu Feb 21 16:15:18 2013
ALTER DATABASE   MOUNT
Thu Feb 21 16:15:23 2013
Setting recovery target incarnation to 1
Thu Feb 21 16:15:23 2013
Successful mount of redo thread 1, with mount id 1335725718
Thu Feb 21 16:15:23 2013
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Feb 21 16:15:23 2013
ALTER DATABASE OPEN
Thu Feb 21 16:15:23 2013
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=13, OS id=3720
Thu Feb 21 16:15:23 2013
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=14, OS id=6468
Thu Feb 21 16:15:23 2013
Thread 1 opened at log sequence 18
Current log# 1 seq# 18 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Thu Feb 21 16:15:23 2013
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
--&gt
Thu Feb 21 16:15:23 2013
ARC0: STARTING ARCH PROCESSES
Thu Feb 21 16:15:23 2013
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thu Feb 21 16:15:23 2013
SMON: enabling cache recovery
Thu Feb 21 16:15:23 2013
ARC2: Archival started
ARC2 started with pid=15, OS id=4020
Thu Feb 21 16:15:23 2013
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
Thu Feb 21 16:15:25 2013
Successfully onlined Undo Tablespace 1.
Thu Feb 21 16:15:25 2013
SMON: enabling tx recovery
Thu Feb 21 16:15:25 2013
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored. It may be necessary to add files to these
         tablespaces. That can be done using the SQL statement:

         ALTER TABLESPACE ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP  --&gt此告警雖然對資料庫沒啥大影響，但是生產還是要有tempfile的
*********************************************************************
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=16, OS id=5944
Thu Feb 21 16:15:29 2013
Completed: ALTER DATABASE OPEN
Thu Feb 21 16:15:30 2013
db_recovery_file_dest_size of 2048 MB is 8.33% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Feb 21 16:15:32 2013
Starting background process CJQ0
CJQ0 started with pid=19, OS id=7188
Thu Feb 21 16:21:23 2013
Shutting down archive processes
Thu Feb 21 16:21:28 2013
到這裡都一切正常改完之後沒有在報 ORA-01000: maximum open cursors exceeded錯
ARCH shutting down
ARC2: Archival stopped
Thu Feb 21 22:00:14 2013
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_5560.trc:
ORA-25153: Temporary Tablespace is Empty

Thu Feb 21 22:00:14 2013
GATHER_STATS_JOB encountered errors. Check the trace file.
Thu Feb 21 22:00:14 2013
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_5560.trc:
ORA-25153: Temporary Tablespace is Empty

Thu Feb 21 22:00:32 2013
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_5560.trc:
ORA-25153: Temporary Tablespace is Empty
的解決：
表空間存在，但資料檔案不在。物理檔案確實也是沒有的。
先檢視temp資料檔案
SQL> select * from v$tempfile;
no rows selected
temp表空間可以找到。
SQL> select tablespace_name from dba_tablespaces where tablespace_name like ‘TEMP’;
TABLESPACE_NAME
——————————
TEMP
嘗試新增個臨時檔案。
SQL> alter tablespace temp add tempfile ‘F:\ORADATA\RBCDNOZ\TEMP01.dbf’ size 10M;
Tablespace altered
SQL> select name from v$tempfile;
NAME
——————————————————————————–
F:\ORADATA\RBCDNOZ\TEMP01.DBF
問題解決。

ORA-1000 encountered when generating server alert SMG-3000;

相關文章