Oracle实验-优快云博客

SQL> create user zxd identified by zxd;

User created.

SQL> grant connect,resource to zxd;

Grant succeeded.

SQL> select A.spid dedicated_server,B.process clientpid from v$process A,v$session B where A.addr=B.paddr and B.sid=(select sid from v$mystat where rownum=1);

DEDICATED_SE CLIENTPID
------------ ------------
25659 25658

SQL> set linesize 180
SQL> select username,sid,serial#,server,paddr,status from v$session where username='SYS';

USERNAME SID SERIAL# SERVER PADDR STATUS
------------------------------ ---------- ---------- --------- ---------------- --------
SYS 159 43 DEDICATED 0000000070E62980 ACTIVE

SQL> set autot on
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL> create public synonym plan_table for plan_table;
create public synonym plan_table for plan_table
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL> grant plustrace to public;

Grant succeeded

SQL> select username,sid,serial#,server,paddr,status from v$session where username='SYS';

USERNAME                              SID    SERIAL# SERVER    PADDR            STATUS
------------------------------ ---------- ---------- --------- ---------------- --------
SYS                                   146        541 DEDICATED 0000000070E62980 INACTIVE
SYS                                   159        102 DEDICATED 0000000070E62980 ACTIVE                                 这里有2，因为一个进程是AUTOTRACE的撒

Execution Plan
----------------------------------------------------------
Plan hash value: 3733760267

--------------------------------------------------------------------------------------------
| Id | Operation                | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   140 |     0   (0)| 00:00:01 |
|   1 | NESTED LOOPS            |                 |     1 |   140 |     0   (0)| 00:00:01 |
|* 2 |   FIXED TABLE FULL       | X$KSUSE         |     1 |   127 |     0   (0)| 00:00:01 |
|* 3 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."KSUUDLNA"='SYS' AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
   3 - filter("S"."KSUSEOPC"="E"."INDX")

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        936 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

下面来个KILLSESSION的实验

重新打开一个会话：

SQL> set sqlp 'SEESION B> '
SEESION B> conn zxd/zxd
Connected.

在原来那个会话中执行：

SQL> set autot off
SQL> set sqlp 'SESSION A> '
SESSION A> select saddr,sid,serial#,paddr,user#,username,process,server,status from v$session where username ='ZXD';

SEESION B> set autot on

SESSION A> select saddr,sid,serial#,paddr,user#,username,process,server,status from v$session where username ='ZXD';

SADDR                   SID    SERIAL# PADDR                 USER# USERNAME                       PROCESS      SERVER    STATUS
---------------- ---------- ---------- ---------------- ---------- ------------------------------ ------------ --------- --------
0000000070F65320        137         83 0000000070E64138         58 ZXD                            26304        DEDICATED INACTIVE
0000000070F73E98        148       1537 0000000070E64138         58 ZXD                            26304        DEDICATED INACTIVE

KILL掉那个新的进程

SESSION A> alter system kill session '137,83';

System altered.

SEESION B> select * from dual;
Error ORA-22 while gathering statistics
SP2-0612: Error generating AUTOTRACE report

D
-
X

SP2-0612: Error generating AUTOTRACE report 报错了撒，因为这个进程已经被KILL掉了的嘛

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

SP2-0612: Error generating AUTOTRACE STATISTICS report

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-667355/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-667355/