2022-01-23：实例管理和体系结构
1. 解锁用户：alter user hr identified by oracle_4U account unlock;
alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
select* from dba_users where username='HR';HR 106 OPEN 2022-07-22:16:26:59 SYSAUX TEMP TEMP 2022-01-23:11:57:40 DEFAULT DEFAULT_CONSUMER_GROUP 11G 12C N PASSWORD N NO N NO USING_NLS_COMP NO NO 2022-01-23:16:26:59
为何180天以后密码过期，是因为profile:
select* from dba_profiles t
where t.profile='DEFAULT';DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO NO NO
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO NO NO
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO NO NO
DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO NO NO
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO NO NO
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO NO NO
DEFAULT IDLE_TIME KERNEL UNLIMITED NO NO NO
DEFAULT CONNECT_TIME KERNEL UNLIMITED NO NO NO
DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO NO NO
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO NO NO
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO NO NO
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO NO NO
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO NO NO
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO NO NO
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO NO NO
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO NO NO
DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED NO NO NO
2. HR做Dedicate连接：
$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 16:50:49 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL>!ps
PID TTY TIME CMD
193168 pts/4 00:00:00 bash
193229 pts/4 00:00:00 sqlplus
193290 pts/4 00:00:00 ps
SQL>
select username, sid,serial#, sql_hash_value from v$session
where username='HR' and
terminal='pts/4';
HR 523 43098 0
hr再次连接：
$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 16:50:49 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL>!ps
PID TTY TIME CMD
193168 pts/4 00:00:00 bash
193229 pts/4 00:00:00 sqlplus
193290 pts/4 00:00:00 ps
SQL> conn hr/oracle_4U
Connected.
SQL>!ps
PID TTY TIME CMD
193168 pts/4 00:00:00 bash
193229 pts/4 00:00:00 sqlplus
193525 pts/4 00:00:00 ps
SQL>
select username, sid,serial#, sql_hash_value from v$session
where username='HR' and
terminal='pts/4';HR 523 43788 0
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 16:50:49 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL>!ps
PID TTY TIME CMD
193168 pts/4 00:00:00 bash
193229 pts/4 00:00:00 sqlplus
193290 pts/4 00:00:00 ps
SQL> conn hr/oracle_4U
Connected.
SQL>!ps
PID TTY TIME CMD
193168 pts/4 00:00:00 bash
193229 pts/4 00:00:00 sqlplus
193525 pts/4 00:00:00 ps
SQL> selectcount(*) from employees a , employees b , employees c , employees d;
在运行....
select username, sid,serial#, sql_hash_value from v$session
where username='HR' and
terminal='pts/4';HR 523 43788 3336337773
selectsql_fulltext from v$sql
where hash_value='3336337773';selectcount(*) from employees a , employees b , employees c , employees d
3. SDDR/PADDR/TADDR
select username, sid,serial#, saddr,paddr,taddr from v$session
where username='HR' and
terminal='pts/4';HR 523 43788 000000009F5E3EE0（共享池） 00000000A04831D8（PGA地址） 没有TADDR（insert/delete/update对应的旧值，也在共享池）
SQL> SQL> SQL> SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> update employees set salary=salary+1 where employee_id=100;
1 row updated.
SQL>
select username, sid,serial#, saddr,paddr,taddr from v$session
where username='HR' and
terminal='pts/4';HR 523 43788 000000009F5E3EE0 00000000A04831D8 000000009325E9E8
select* from v$transactionwhere addr='000000009325E9E8';000000009325E9E8 10 13 1720 4 17931 281 5 ACTIVE 01/23/22 17:09:02 2893199
4. TADDR控制的旧址，将来要写回硬盘（undo表空间---段---区---块）：
select* from dba_rollback_segs;SYSTEM SYS SYSTEM 0 1 128 114688 57344 1 32765 ONLINE 1
_SYSSMU1_1261223759$ PUBLIC UNDOTBS1 1 4 128 131072 65536 2 32765 ONLINE 4
_SYSSMU2_27624015$ PUBLIC UNDOTBS1 2 4 144 131072 65536 2 32765 ONLINE 4
_SYSSMU3_2421748942$ PUBLIC UNDOTBS1 3 4 160 131072 65536 2 32765 ONLINE 4
_SYSSMU4_625702278$ PUBLIC UNDOTBS1 4 4 176 131072 65536 2 32765 ONLINE 4
_SYSSMU5_2101348960$ PUBLIC UNDOTBS1 5 4 192 131072 65536 2 32765 ONLINE 4
_SYSSMU6_813816332$ PUBLIC UNDOTBS1 6 4 208 131072 65536 2 32765 ONLINE 4
_SYSSMU7_2329891355$ PUBLIC UNDOTBS1 7 4 224 131072 65536 2 32765 ONLINE 4
_SYSSMU8_399776867$ PUBLIC UNDOTBS1 8 4 240 131072 65536 2 32765 ONLINE 4
_SYSSMU9_1692468413$ PUBLIC UNDOTBS1 9 4 256 131072 65536 2 32765 ONLINE 4
_SYSSMU10_930580995$ PUBLIC UNDOTBS1 10 4 272 131072 65536 2 32765 ONLINE 4
5. 每个会话都有上下文：
SQL> selectsys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
SQL> conn hr/oracle_4U@classroom.example.com:1521/emrep
Connected.
SQL>selectsys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
172.25.250.254
SQL> conn hr/oracle_4U@emrep
Connected.
SQL> selectsys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
172.25.250.254
SQL>
附客户端的：
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = classroom.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep)
)
)
LISTENER_EMREP =
(ADDRESS = (PROTOCOL = TCP)(HOST = classroom.example.com)(PORT = 1521))
页:
[1]