SQL> CONN SYSTEM/MANAGER
郁屋給.
SQL> COL PROFILE FORMAT A20
SQL> COL RESOURCE_NAME FORMAT A30
SQL> COL LIMIT FORMAT A20
SQL> SELECT * FROM DBA_PROFILES
2 WHERE RESOURCE_TYPE = 'KERNEL';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
-------------------- ------------------------------ ---------------- -----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
YASI_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
YASI_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
YASI_PROFILE CPU_PER_SESSION KERNEL DEFAULT
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
YASI_PROFILE CPU_PER_CALL KERNEL DEFAULT
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
YASI_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
YASI_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
DEFAULT IDLE_TIME KERNEL UNLIMITED
YASI_PROFILE IDLE_TIME KERNEL DEFAULT
DEFAULT CONNECT_TIME KERNEL UNLIMITED
YASI_PROFILE CONNECT_TIME KERNEL DEFAULT
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
YASI_PROFILE PRIVATE_SGA KERNEL DEFAULT
18 螳 給.
SQL> SELECT * FROM DBA_PROFILES
2 WHERE RESOURCE_TYPE = 'KERNEL'
3 AND PROFILE = 'DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
-------------------- ------------------------------ ---------------- -----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
9 螳 給.
SQL> COL RESOURCE_NAME FORMAT A30
SQL> COL "る" FORMAT A60
SQL> COL "覯" FORMAT A15
SQL> SELECT RESOURCE_NAME,
2 DECODE(RESOURCE_NAME,
3 'COMPOSITE_LIMIT', ' 覲旧(螳 讌觸 螳)',
4 'SESSIONS_PER_USER', ' 螳 語',
5 'CPU_PER_SESSION', ' 語 豐 CPU 螳(豐)',
6 'CONNECT_TIME', '語 伎 豕 螳(覿)',
7 'IDLE_TIME', ' 語 覓企 讌蟇磯Μ 螻 螳(覿)',
8 'LOGICAL_READS_PER_SESSION', '語 朱Μ 所鍵襯 觚襦',
9 'PRIVATE_SGA', 'る狩 MTS 螳 PGA ',
10 'CPU_PER_CALL', ' 一危覯伎 CALL CPU TIME(1/100豐)',
11 'LOGICAL_READS_PER_CALL', ' 一危覯伎 CALL 朱Μ 所鍵襯 觚襦') "る",
12 DECODE(RESOURCE_NAME,
13 'COMPOSITE_LIMIT', 'COMPOSITE LIMIT',
14 'SESSIONS_PER_USER', 'SESSION LEVEL',
15 'CPU_PER_SESSION', 'SESSION LEVEL',
16 'CONNECT_TIME', 'SESSION LEVEL',
17 'IDLE_TIME', 'SESSION LEVEL',
18 'LOGICAL_READS_PER_SESSION', 'SESSION LEVEL',
19 'PRIVATE_SGA', 'SESSION LEVEL',
20 'CPU_PER_CALL', 'CALL LEVEL',
21 'LOGICAL_READS_PER_CALL', 'CALL LEVEL') "覯"
22 FROM DBA_PROFILES
23 WHERE RESOURCE_TYPE = 'KERNEL'
24 AND PROFILE = 'DEFAULT';
RESOURCE_NAME る 覯
------------------------------ ------------------------------------------------------------ --------
COMPOSITE_LIMIT 覲旧(螳 讌觸 螳) COMPOSITE LIMIT
SESSIONS_PER_USER 螳 語 SESSION LEVEL
CPU_PER_SESSION 語 豐 CPU 螳(豐) SESSION LEVEL
CPU_PER_CALL 一危覯伎 CALL CPU TIME(1/100豐) CALL LEVEL
LOGICAL_READS_PER_SESSION 語 朱Μ 所鍵襯 觚襦 SESSION LEVEL
LOGICAL_READS_PER_CALL 一危覯伎 CALL 朱Μ 所鍵襯 觚襦 CALL LEVE
IDLE_TIME 語 覓企 讌蟇磯Μ 螻 螳(覿) SESSION LEVEL
CONNECT_TIME 語 伎 豕 螳(覿) SESSION LEVEL
PRIVATE_SGA る狩 MTS 螳 PGA SESSION LEVEL
9 螳 給.
SQL>
る蟾讌 給.. 所 覲伎匡る 語覯 讀, CREATE SESSION
ろ れ 蟆願, 貊 覯 SQL覓 ろ
蟇瑚 蟆. 朱Μ 所鍵襯 る 覦 覓朱Μ 所鍵螳 企 覯 ろ
伎 蠍一 企 覓朱Μ 所鍵蟾讌 螳..
, PROFILE 豐蠍壱 朱誤一 RESOURCE_LIMIT = TRUE 伎殊襷 燕 ..
企 語 襦殊 燕 る
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
覃 ..
伎 CREATE PROFILE 覓碁 危ｴ覲願給..
CREATE PROFILE profile LIMIT
{ resource_parameters | password_parameters }
[ resource_parameters | password_parameters ]...;
resource_parameters::=
{ { SESSIONS_PER_USER
| CPU_PER_SESSION
| CPU_PER_CALL
| CONNECT_TIME
| IDLE_TIME
| LOGICAL_READS_PER_SESSION
| LOGICAL_READS_PER_CALL
| COMPOSITE_LIMIT
}
{ integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA { integer [ K | M ] | UNLIMITED | DEFAULT }
}
password_parameters::=
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME
}
{ expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
}
企Μ覯襴 譴... 覃伎殊 る..
SESSIONS_PER_USER
Specify the number of concurrent sessions to which you want to limit the user.
CPU_PER_SESSION
Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in
hundredths of seconds.
CONNECT_TIME
Specify the total elapsed time limit for a session, expressed in minutes.
IDLE_TIME
Specify the permitted periods of continuous inactive time during a session,
expressed in minutes. Long-running queries and other operations are not subject
to this limit.
LOGICAL_READS_PER_SESSION
Specify the permitted number of data blocks read in a session, including blocks
read from memory and disk.
LOGICAL_READS_PER_CALL
Specify the permitted the number of data blocks read for a call to process a
SQL statement (a parse, execute, or fetch).
PRIVATE_SGA
Specify the amount of private space a session can allocate in the shared pool of
the system global area (SGA), expressed in bytes. Use K or M to specify this limit
in kilobytes or megabytes.
SQL> CREATE PROFILE YASI_PROFILE LIMIT
2 SESSIONS_PER_USER UNLIMITED
3 CPU_PER_SESSION UNLIMITED
4 CPU_PER_CALL 3000
5 CONNECT_TIME 45
6 LOGICAL_READS_PER_SESSION DEFAULT
7 LOGICAL_READS_PER_CALL 1000
8 PRIVATE_SGA 15K
9 COMPOSITE_LIMIT 5000000;
CREATE PROFILE YASI_PROFILE LIMIT
*
1 る:
ORA-02379: YASI_PROFILE 襦殊 企 譟伎
SQL> ALTER PROFILE YASI_PROFILE LIMIT
2 SESSIONS_PER_USER UNLIMITED
3 CPU_PER_SESSION UNLIMITED
4 CPU_PER_CALL 3000
5 CONNECT_TIME 45
6 LOGICAL_READS_PER_SESSION DEFAULT
7 LOGICAL_READS_PER_CALL 1000
8 PRIVATE_SGA 15K
9 COMPOSITE_LIMIT 500000;
襦殊 覲蟆暑給.
SQL>
COMPOSITE_LIMIT 觜 一 豺..覲旧
CPU_PER_SESSION, CONNECT_TIME, PRIVATE_SGA, LOGICAL_READS_PER_SESSION 4螳讌
. 覲企 蟆豌 豺螳 伎 企 4螳讌 螳 螻壱
蟆願鍵 覓語..
蠍一 螳螳 蟯襴蠍 伎 ALTER RESOURCE COST 覓語 ..
願 COMPOSITE_LIMIT 豪 螳 螳れ 覈企手 螳覃 ..
讀, COMPOSITE_LIMIT レ 殊 蟆 CREATE PROFILE 螳れ
ALTER RESOURCE COST 蟆一 螳企手 給.
ALTER RESOURCE COST
{ CPU_PER_SESSION
| CONNECT_TIME
| LOGICAL_READS_PER_SESSION
| PRIVATE_SGA
}
integer [
{CPU_PER_SESSION
| CONNECT_TIME
| LOGICAL_READS_PER_SESSION
| PRIVATE_SGA
} integer
] ...
;
COMPOSITE_LIMIT 螳 螻 覦覯 れ螻 螳給. 襷 れ螻 螳
RESOURE_COST襯 れり 覃..
SQL> ALTER RESOURCE COST
2 CPU_PER_SESSION 500
3 LOGICAL_READS_PER_SESSION 100
4 CONNECT_TIME 10;
觜 覲蟆暑給.
襷.. A朱 螳 CPU襯 5豐 螻, 3000 觚襦 曙朱, 螳 60覿願,
MTS蠍磯 朱... れ螻 螳 螻磯. 譯殊 蟆 企..
CPU_PER_SESSION --> 1/100 豐
LOGICAL_READS_PER_SESSION --> 觚襦
CONNECT_TIME --> 覿
PRIVATE_SGA --> KB
TOTAL COST = (CPU_PER_SESSION + LOGICAL_READS_PER_SESSION + CONNECT_TIME + PRIVATE_SGA)
CPU_PER_SESSION --> 5豐 --> 500
LOGICAL_READS_PER_SESSION --> 3000 觚襦
CONNECT_TIME --> 60覿
SQL> SELECT RESOURCE_NAME, LIMIT
2 FROM DBA_PROFILES
3 WHERE RESOURCE_NAME = 'COMPOSITE_LIMIT'
4 AND PROFILE = 'YASI_PROFILE';
RESOURCE_NAME LIMIT
------------------------------ --------------------
COMPOSITE_LIMIT 500000
SQL>
SQL> SELECT (500* 500) + (3000*100) + (60*10) TOTAL_COST FROM DUAL;
TOTAL_COST
----------
550600
SQL>
豺襯 朱襦 A 襦る葦 . 譴 蟆
蟇碁Μ蠍 COMMIT 覈 朱 , 蟇碁れ 讌
COMMIT る 蟆.
谿瑚...
COL RESOURCE_NAME FORMAT A30
COL "る" FORMAT A60
COL "覯" FORMAT A15
SELECT RESOURCE_NAME,
DECODE(RESOURCE_NAME,
'COMPOSITE_LIMIT', ' 覲旧(螳 讌觸 螳)',
'SESSIONS_PER_USER', ' 螳 語',
'CPU_PER_SESSION', ' 語 豐 CPU 螳(豐)',
'CONNECT_TIME', '語 伎 豕 螳(覿)',
'IDLE_TIME', ' 語 覓企 讌蟇磯Μ 螻 螳(覿)',
'LOGICAL_READS_PER_SESSION', '語 朱Μ 所鍵襯 觚襦',
'PRIVATE_SGA', 'る狩 MTS 螳 PGA ',
'CPU_PER_CALL', ' 一危覯伎 CALL CPU TIME(1/100豐)',
'LOGICAL_READS_PER_CALL', ' 一危覯伎 CALL 朱Μ 所鍵襯 觚襦') "る",
DECODE(RESOURCE_NAME,
'COMPOSITE_LIMIT', 'COMPOSITE LIMIT',
'SESSIONS_PER_USER', 'SESSION LEVEL',
'CPU_PER_SESSION', 'SESSION LEVEL',
'CONNECT_TIME', 'SESSION LEVEL',
'IDLE_TIME', 'SESSION LEVEL',
'LOGICAL_READS_PER_SESSION', 'SESSION LEVEL',
'PRIVATE_SGA', 'SESSION LEVEL',
'CPU_PER_CALL', 'CALL LEVEL',
'LOGICAL_READS_PER_CALL', 'CALL LEVEL') "覯"
FROM DBA_PROFILES
WHERE RESOURCE_TYPE = 'KERNEL'
AND PROFILE = 'DEFAULT';