SESSION в Oracle — 100 практических примеров

SESSION в Oracle SQL. Введение

SESSION в Oracle это подключение пользователя к базе данных. Каждое подключение создаёт отдельную session со своими параметрами, транзакциями и настройками среды выполнения.

SESSION используется для:

изменения параметров выполнения
управления NLS
performance tuning
locking analysis
trace debugging
transaction monitoring

Основной синтаксис ALTER SESSION

ALTER SESSION SET parameter=value;

Базовый пример SESSION

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

100 примеров SESSION

1. Изменение формата даты SESSION

ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';

2. Установка timestamp формата

ALTER SESSION SET NLS_TIMESTAMP_FORMAT=
'YYYY-MM-DD HH24:MI:SS';

3. Изменение языка SESSION

ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

4. Установка территории

ALTER SESSION SET NLS_TERRITORY='FRANCE';

5. Изменение сортировки

ALTER SESSION SET NLS_SORT='BINARY';

6. Case insensitive поиск

ALTER SESSION SET NLS_COMP=LINGUISTIC;

7. Включение параллельных запросов

ALTER SESSION FORCE PARALLEL QUERY;

8. Отключение parallel

ALTER SESSION DISABLE PARALLEL QUERY;

9. Установка optimizer mode

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;

10. FIRST_ROWS оптимизация

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;

Еще 20 примеров

11. Установка CURRENT_SCHEMA

ALTER SESSION SET CURRENT_SCHEMA=HR;

12. Изменение time zone

ALTER SESSION SET TIME_ZONE='UTC';

13. Session trace enable

ALTER SESSION SET SQL_TRACE=TRUE;

14. Trace disable

ALTER SESSION SET SQL_TRACE=FALSE;

15. Установка statistics level

ALTER SESSION SET STATISTICS_LEVEL=ALL;

16. Ограничение PGA

ALTER SESSION SET PGA_AGGREGATE_TARGET=200M;

17. Установка cursor sharing

ALTER SESSION SET CURSOR_SHARING=FORCE;

18. Exact cursor mode

ALTER SESSION SET CURSOR_SHARING=EXACT;

19. Session cached cursors

ALTER SESSION SET SESSION_CACHED_CURSORS=100;

20. Установка workarea policy

ALTER SESSION SET WORKAREA_SIZE_POLICY=AUTO;

Еще 20 примеров

21. Просмотр своей session

SELECT SID,SERIAL#
FROM V$SESSION
WHERE AUDSID=USERENV('SESSIONID');

22. Просмотр активных sessions

SELECT SID,USERNAME
FROM V$SESSION;

23. Active sessions only

SELECT SID,STATUS
FROM V$SESSION
WHERE STATUS='ACTIVE';

24. Session machine

SELECT MACHINE
FROM V$SESSION;

25. Session program

SELECT PROGRAM
FROM V$SESSION;

26. Session user

SELECT USERNAME
FROM V$SESSION;

27. Session logon time

SELECT LOGON_TIME
FROM V$SESSION;

28. Session blocking

SELECT BLOCKING_SESSION
FROM V$SESSION;

29. Session event

SELECT EVENT
FROM V$SESSION;

30. Wait class

SELECT WAIT_CLASS
FROM V$SESSION;

Еще 20 примеров

31. Session SQL ID

SELECT SQL_ID
FROM V$SESSION;

32. Session executing SQL

SELECT SQL_ID,USERNAME
FROM V$SESSION
WHERE STATUS='ACTIVE';

33. Session OS user

SELECT OSUSER
FROM V$SESSION;

34. Session terminal

SELECT TERMINAL
FROM V$SESSION;

35. Session process

SELECT PROCESS
FROM V$SESSION;

36. Session type

SELECT TYPE
FROM V$SESSION;

37. Background sessions

SELECT SID
FROM V$SESSION
WHERE TYPE='BACKGROUND';

38. User sessions only

SELECT SID
FROM V$SESSION
WHERE TYPE='USER';

39. Session status

SELECT STATUS
FROM V$SESSION;

40. Inactive sessions

SELECT SID
FROM V$SESSION
WHERE STATUS='INACTIVE';

Еще 20 примеров

41. Blocking sessions

SELECT SID,BLOCKING_SESSION
FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL;

42. Sessions waiting locks

SELECT SID,EVENT
FROM V$SESSION
WHERE WAIT_CLASS='Application';

43. Long running sessions

SELECT SID,LAST_CALL_ET
FROM V$SESSION;

44. Session seconds active

SELECT SID,LAST_CALL_ET
FROM V$SESSION
WHERE STATUS='ACTIVE';

45. Session row wait

SELECT ROW_WAIT_OBJ#
FROM V$SESSION;

46. Session locks info

SELECT SID,LOCKWAIT
FROM V$SESSION;

47. Session hash value

SELECT SQL_HASH_VALUE
FROM V$SESSION;

48. Session child number

SELECT SQL_CHILD_NUMBER
FROM V$SESSION;

49. Session schema

SELECT SCHEMANAME
FROM V$SESSION;

50. Session module

SELECT MODULE
FROM V$SESSION;

Еще 20 примеров

51. Session action

SELECT ACTION
FROM V$SESSION;

52. Session client info

SELECT CLIENT_INFO
FROM V$SESSION;

53. Session identifier

SELECT CLIENT_IDENTIFIER
FROM V$SESSION;

54. Session service name

SELECT SERVICE_NAME
FROM V$SESSION;

55. Session edition

SELECT EDITION#
FROM V$SESSION;

56. Session resource consumer group

SELECT RESOURCE_CONSUMER_GROUP
FROM V$SESSION;

57. Session PDML status

SELECT PDML_STATUS
FROM V$SESSION;

58. Session PDDL status

SELECT PDDL_STATUS
FROM V$SESSION;

59. Session PQ status

SELECT PQ_STATUS
FROM V$SESSION;

60. Session temp usage

SELECT TEMP_SPACE_ALLOCATED
FROM V$SESSION;

Еще 20 примеров

61. Kill session example

ALTER SYSTEM KILL SESSION '10,200';

62. Kill session immediate

ALTER SYSTEM KILL SESSION '15,300'
IMMEDIATE;

63. Disconnect session

ALTER SYSTEM DISCONNECT SESSION
'12,100';

64. Disconnect immediate

ALTER SYSTEM DISCONNECT SESSION
'18,220' IMMEDIATE;

65. Mark session for kill

ALTER SYSTEM KILL SESSION
'20,400' POST_TRANSACTION;

66. Session transaction view

SELECT *
FROM V$TRANSACTION;

67. Session transaction join

SELECT S.SID
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.SADDR=T.SES_ADDR;

68. Session undo usage

SELECT USED_UBLK
FROM V$TRANSACTION;

69. Session redo size

SELECT USED_UREC
FROM V$TRANSACTION;

70. Session start transaction

SET TRANSACTION READ WRITE;

Еще 20 примеров

71. Read only session transaction

SET TRANSACTION READ ONLY;

72. Serializable session

SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;

73. Read committed session

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

74. Session enable tracing waits

ALTER SESSION SET EVENTS
'10046 trace name context forever, level 8';

75. Trace with binds

ALTER SESSION SET EVENTS
'10046 trace name context forever, level 12';

76. Disable trace

ALTER SESSION SET EVENTS
'10046 trace name context off';

77. Session optimizer index cost

ALTER SESSION SET
OPTIMIZER_INDEX_COST_ADJ=20;

78. Session optimizer dynamic sampling

ALTER SESSION SET
OPTIMIZER_DYNAMIC_SAMPLING=4;

79. Session result cache

ALTER SESSION SET
RESULT_CACHE_MODE=FORCE;

80. Disable result cache

ALTER SESSION SET
RESULT_CACHE_MODE=MANUAL;

Еще 20 примеров

81. Session ddl lock timeout

ALTER SESSION SET
DDL_LOCK_TIMEOUT=60;

82. Session nls numeric

ALTER SESSION SET
NLS_NUMERIC_CHARACTERS=',.';

83. Session nls currency

ALTER SESSION SET
NLS_CURRENCY='€';

84. Session date language

ALTER SESSION SET
NLS_DATE_LANGUAGE=FRENCH;

85. Session length semantics

ALTER SESSION SET
NLS_LENGTH_SEMANTICS=CHAR;

86. Session current user

SELECT USER
FROM DUAL;

87. Session environment

SELECT SYS_CONTEXT('USERENV',
'SESSION_USER')
FROM DUAL;

88. Session id

SELECT SYS_CONTEXT('USERENV',
'SID')
FROM DUAL;

89. Session database name

SELECT SYS_CONTEXT('USERENV',
'DB_NAME')
FROM DUAL;

90. Session host

SELECT SYS_CONTEXT('USERENV',
'HOST')
FROM DUAL;

91. Session IP

SELECT SYS_CONTEXT('USERENV',
'IP_ADDRESS')
FROM DUAL;

92. Session auth method

SELECT SYS_CONTEXT('USERENV',
'AUTHENTICATION_METHOD')
FROM DUAL;

93. Session instance

SELECT SYS_CONTEXT('USERENV',
'INSTANCE')
FROM DUAL;

94. Session service

SELECT SYS_CONTEXT('USERENV',
'SERVICE_NAME')
FROM DUAL;

95. Session module set

BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
'APP','LOAD');
END;

96. Session action set

BEGIN
DBMS_APPLICATION_INFO.SET_ACTION(
'PROCESS');
END;

97. Session client info set

BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(
'API');
END;

98. Session identifier set

BEGIN
DBMS_SESSION.SET_IDENTIFIER(
'CLIENT1');
END;

99. Reset package session state

EXEC DBMS_SESSION.RESET_PACKAGE;

100. Free unused memory session

EXEC DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

Oracle documentation

Oracle SESSION documentation