Kill session in AWS Oracle Database ... 

Oracle AWS RDS kill session
connections on an RDS Oracle DB
Kill session in AWS Oracle Database

[oracle@rac01 ~]$ sqlplus 'vihaan@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=************.compute.amazonaws.com)(Port=1521))(CONNECT_DATA=(SID=orcl)))'

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 5 14:58:57 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:

SQL>

*******************************************
This Will NOT work !!!!!!!!
set linesize 200 pagesize 300 
col kill for a70
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill,username,sql_id from gv$session 
where 1=1
-- and username='username'
-- and status = 'INACTIVE'
and type != 'BACKGROUND';
      

*********************************************

select LOGINS from V$INSTANCE;
 
we need to use this pakage rdsadmin.rdsadmin_util to kill session in oracle aws rds
 
exec rdsadmin.rdsadmin_util.kill(627,35083);
 

select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN
  

SQL> select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN

SQL> exec rdsadmin.rdsadmin_util.kill(40,61773);

PL/SQL procedure successfully completed.

SQL>
   

set linesize 200 pagesize 300
sql> select sid, serial#, status,username from v$session where status='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN

SQL> exec rdsadmin.rdsadmin_util.kill(40,61773);

PL/SQL procedure successfully completed.
 
 

begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid, 
 serial => &serial_number,
 method => 'IMMEDIATE');
end;
/

SQL> exec rdsadmin.rdsadmin_util.kill(40,61777,'IMMEDIATE');

PL/SQL procedure successfully completed.


to Kill session !
begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid, 
 serial => &serial_number,
 method => 'IMMEDIATE');
end;
/

Via Script 

set linesize 200 pagesize 300
col kill for a70
select 'exec rdsadmin.rdsadmin_util.kill('|| sid ||',' || serial# || ');' kill from v$session where username in ('&Username'); 


-- session with Immediate !!!

set linesize 200 pagesize 300
select 'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill , ' -- ',username ,status,blocking_session,state,final_blocking_session,sql_id
from v$session s
where 1=1 
-- and username = upper('')
 and STATUS != 'ACTIVE'
 and s.Type != 'BACKGROUND';
 
 
KILL '--' USERNAME STATUS BLOCKING_SESSION STATE FINAL_BLOCKING_SESSION SQL_ID
---------------------------------------------------------------------- ---- -------------------- -------- ---------------- ------------------- ---------------------- -------------
exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- RDSADMIN INACTIVE WAITING

 
SQL> SQL> exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE');

PL/SQL procedure successfully completed.



set linesize 300 
col username for a20
select 
'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill ,
 ' -- ',
 s.inst_id,
 -- s.sid,
 -- s.serial#,
 p.spid,
 s.username,
 s.program,
 s.sql_id
 from gv$session s, gv$process p
 where 1=1 
 and p.addr = s.paddr 
 and p.inst_id = s.inst_id
and s.type != 'BACKGROUND' ;

KILL '--' INST_ID SPID USERNAME PROGRAM SQL_ID
---------------------------------------------------------------------- ---- ---------- ------------------------ -------------------- ------------------------------------------------ -------------
exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- 1 27035 RDSADMIN JDBC Thin Client
exec rdsadmin.rdsadmin_util.kill(21,62867, 'IMMEDIATE'); -- 1 23889 VIHAAN sqlplus@rac01.*****.net (TNS V1-V3) 6v5f6v3upz5mu

Oracle How write into alert.log

Oracle How to write into alert.log

custom messages in alert log

package dbms_system.ksdwrt

        option 1- for trace file
        option 2- for alert log
        option 3- for both

connect / as sysdba

begin
    sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' --- Anuj test --- ');
end;
/

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location'); 
exec dbms_system.ksdwrt(2, 'This message goes to the alert log');
exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location')

begin
    exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Anuj---******');
end;
/

or 

exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Anuj---******');
exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Start---******');
exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Start Failover Change No: 7777777 ---******');


execute sys.dbms_system.ksdwrt(2,('ORA-07445: DB Monitoring Test'));

execute sys.dbms_system.ksdwrt(2,'ORA-07445: DB Monitoring Test');

Spfile asm in ASM  11gR2/12C

How to find out spfile in +ASM
How to find out OCR in +ASM 


SQL> !olsnodes -c

olsnodes -c  <<<< To Find Out cluster Name 

set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = LOWER('&CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;

with date

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER,CREATION_DATE  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type,c.CREATION_DATE
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE','PARAMETERFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
            where a.group_number = b.group_number
            and (mod(a.parent_index, power(2, 24))) = 0
--          and a.name = LOWER('CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;

ASMCMD [+] > find --type PARAMETERFILE '*' '*'
ASMCMD [+] > spget
ASMCMD> spcopy +DATA/racluster/asmparameterfile/registry.253.792630659 /tmp/spfilecopyASM.ora
ASMCMD> spbackup +DATA....   +LOGS/spfileBackASM.bak

Oracle CREATE ASM DISKGROUP manually in 11gR2/12C

Create ASM DiskGroup ....!!!!

Node 1

#ls -ltr  /dev/mapper/

lrwxrwxrwx 1 root root       8 Mar 29 10:01 ora_fra_0003p1 -> ../dm-29

lrwxrwxrwx 1 root root       8 Mar 29 10:01 ora_acfs_0001 -> ../dm-43

#oracleasm createdisk -v ORA_0001 /dev/mapper/ora_acfs_0001

# oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001  <<<<<< New disk 

ls -alrt /dev/oracleasm/disks/ORA*

scan and list 

# date ; hostname;  oracleasm scandisks ; for d in `oracleasm listdisks | sort`; do oracleasm querydisk -p $d; done

Node2

#oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "ORA_0001"

Scanning system for ASM disks...

#oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001  <<<<<< New disk 

Node3

#oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "ORA_0001"

Scanning system for ASM disks...

#oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001  <<<<<< New disk 

-- for  Candidate disk !!!

ASMCMD [+] > lsdsk --candidate -p

Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path

set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout  on trimspool on  verify  off

clear columns breaks computes

column disk_group_name        format a12           head 'disk group name'

column disk_file_path         format a45           head 'path'

column disk_file_name         format a12           head 'file name'

column disk_file_fail_group   format a12           head 'fail group'

column total_mb               format 999,999,999   head 'file size (mb)'

column used_mb                format 999,999,999   head 'used size (mb)'

column pct_used               format 999.99        head 'pct. used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name

compute sum label "grand total: " of total_mb used_mb on report

SELECT

   NVL(a.name, '[CANDIDATE]')                       disk_group_name

 , b.path                                           disk_file_path

 , b.name                                           disk_file_name

 , b.failgroup                                      disk_file_fail_group

 , b.total_mb                                       total_mb

 , (b.total_mb - b.free_mb)                         used_mb

-- , ROUND((1- (b.free_mb / b.total_mb))*100, 2)    pct_used

FROM v$asm_diskgroup a ,v$asm_disk b 

where a.group_number (+) =b.group_number 

ORDER BY a.name;

Disk Group N Path                                          File Name    Fail Group   File Size (MB) Used Size (MB)

------------ --------------------------------------------- ------------ ------------ -------------- --------------

************

[candidate]  /dev/oracleasm/disks/ORA_0001                                                        0              0

************                                                                         -------------- --------------

                                                                                                  0              0

SQL> CREATE DISKGROUP ORAFS EXTERNAL REDUNDANCY disk '/dev/oracleasm/disks/ORA_0001';

Check the disk group ...

[grid]$ asmcmd -p

ASMCMD [+] > lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512   4096  1048576     51200    51150                0           51150              0             N  ORAFS/

ASMCMD [+] > ls -l +         <<<< for DiskGroup !!!

ASMCMD [+] > lsdsk -t -G DATA

Create_Date  Mount_Date  Repair_Timer  Path

01-FEB-18    01-FEB-18   0             ORCL:DATA0

01-FEB-18    01-FEB-18   0             ORCL:DATA1

01-FEB-18    01-FEB-18   0             ORCL:DATA2

01-FEB-18    01-FEB-18   0             ORCL:DATA3

set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout  on

set trimspool on  verify  off

clear columns breaks computes

column disk_group_name        format a12           head 'disk group name'

column disk_file_path         format a45           head 'path'

column disk_file_name         format a12           head 'file name'

column disk_file_fail_group   format a12           head 'fail group'

column total_mb               format 999,999,999   head 'file size (mb)'

column used_mb                format 999,999,999   head 'used size (mb)'

column pct_used               format 999.99        head 'pct. used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name

compute sum label "grand total: " of total_mb used_mb on report

SELECT

   NVL(a.name, '[CANDIDATE]')                       disk_group_name

 , b.path                                           disk_file_path

 , b.name                                           disk_file_name

 , b.failgroup                                      disk_file_fail_group

 , b.total_mb                                       total_mb

 , (b.total_mb - b.free_mb)                         used_mb

 , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used

FROM v$asm_diskgroup a ,v$asm_disk b 

where a.group_number (+) =b.group_number 

ORDER BY a.name;

*********************************************************************

Imp ......

On all the Nodes mount the DiskGroup !!!!!!!!!!!!!!!!!!!!!

SQL>  alter diskgroup ORAFS mount ;

Diskgroup altered.

ASMCMD [+] > lsdg -g     <<<<<<<< check for all node 

*********************************************************************

If Required .. 

sqlplus> alter diskgroup ORACFS set attribute 'compatible.asm'='11.2.0.0.0'; 

set linesize 200 

col compatibility          form a15

col database_compatibility form a25

col name                   form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

set linesize 200  pagesize 300

col value for a10

col name  for a50

select group_number, name, value from v$asm_attribute

where 1=1

and group_number=1

;

GROUP_NUMBER NAME                                               VALUE

------------ -------------------------------------------------- ----------

           1 idp.type                                           dynamic

           1 idp.boundary                                       auto

           1 disk_repair_time                                   3.6h

 asmcmd lsattr -G DATA -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  1048576
cell.smart_scan_capable  FALSE
compatible.asm           12.1.0.0.0
compatible.rdbms         10.1.0.0.0
content.check            FALSE
content.type             data
disk_repair_time         3.6h
failgroup_repair_time    24.0h
idp.boundary             auto
idp.type                 dynamic
phys_meta_replicated     true
sector_size              512
thin_provisioned         FALSE




[grid@rac01 ~]$ asmcmd -p

ASMCMD [+] > lsattr -G DATA -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  1048576
cell.smart_scan_capable  FALSE
compatible.asm           12.1.0.0.0
compatible.rdbms         10.1.0.0.0
content.check            FALSE
content.type             data
disk_repair_time         3.6h
failgroup_repair_time    24.0h
idp.boundary             auto
idp.type                 dynamic
phys_meta_replicated     true
sector_size              512

thin_provisioned         FALSE

=====

set pagesize 0

select

'GROUP_NUMBER            : '||GROUP_NUMBER  ||chr(10)||

'NAME            : '||NAME    ||chr(10)||

'SECTOR_SIZE            : '||SECTOR_SIZE    ||chr(10)||

'BLOCK_SIZE            : '||BLOCK_SIZE    ||chr(10)||

'ALLOCATION_UNIT_SIZE      : '||ALLOCATION_UNIT_SIZE    ||chr(10)||

'STATE              : '||STATE    ||chr(10)||

'TYPE          : '||TYPE ||chr(10)||

'TOTAL_MB              : '||TOTAL_MB      ||chr(10)||

'FREE_MB      : '||FREE_MB ||chr(10)||

'HOT_USED_MB          : '||HOT_USED_MB    ||chr(10)||

'COLD_USED_MB    : '||COLD_USED_MB            ||chr(10)||

'REQUIRED_MIRROR_FREE_MB  : '||REQUIRED_MIRROR_FREE_MB  ||chr(10)||

'USABLE_FILE_MB    : '||USABLE_FILE_MB  ||chr(10)||

'OFFLINE_DISKS              : '||OFFLINE_DISKS    ||chr(10)||

'COMPATIBILITY            : '||COMPATIBILITY    ||chr(10)||

'DATABASE_COMPATIBILITY         : '||DATABASE_COMPATIBILITY     ||chr(10)||

'VOTING_FILES            : '||VOTING_FILES

from  v$asm_diskgroup

where 1=1

order by 1

;

set pagesize 100

Oracle 12c: Transportable Tablespaces


[oracle@vbgeneric ~]$ export TWO_TASK=

sqlplus / as sysdba



show pdbs

 CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 ORCL READ WRITE NO Move from tablespace>>>>>>>> 
 4 ORDS READ WRITE NO
 5 ANUJ READ WRITE NO <<<<< To Move tablespace
 7 ORCLDP READ WRITE NO




set linesize 300
col FILE_NAME for a90
select FILE_ID,FILE_NAME from cdb_data_files ;

 FILE_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------
 20 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_usertbs_dxk31j8j_.dbf
 19 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_users_dxk31j7b_.dbf
 18 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_sysaux_dxk31j9h_.dbf
 21 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anuj01.dbf
 17 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_system_dxk31j8o_.dbf
 23 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/tts01_.dbf <<< datafile 


******************

Demo ...


SQL> alter session set container=orcl;
Session altered.


set linesize 200 pagesize 100 serveroutput on
column "db details" format a100
select
 'db_name: ' ||sys_context('userenv', 'db_name') ||
 ' \cdb:-' ||(select cdb from v$database) ||
 ' \auth_id: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \user: ' ||sys_context('userenv', 'current_user') ||
 ' \container:' ||nvl(sys_context('userenv', 'con_name'), 'non-cdb') "db details"
 from dual ;
----------------------------------------------------------------------------------------------------
db_name: orcl12c \cdb:-YES \auth_id: oracle \user: SYS \container:ORCL



Create tablespace .. 

CREATE TABLESPACE tts datafile '/u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/tts01_.dbf' size 1024M autoextend on maxsize unlimited ;
Tablespace created.


SQL> create user ttsuser identified by tts ;

User created.

SQL> grant connect,resource to ttsuser ;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ttsuser;

Grant succeeded.

SQL> alter user ttsuser default tablespace tts ;

User altered.


Create table and row .. 

create table TTSUSER.table1_test ( EmpNO number(8), EmpDate date ) ;
create table TTSUSER.table2_test ( Emp_name varchar2(20), EmpDate date ) ;

declare
i number;
begin
for i in 1..1000 loop
insert into TTSUSER.table1_test values(i,sysdate);
insert into TTSUSER.table2_test values ('Number is LAST'||i, sysdate);
end loop;
commit;
end;


SQL> select count(*) from TTSUSER.table1_test;

 COUNT(*)
----------
 1000


SQL> select count(*) from TTSUSER.table2_test ;

 COUNT(*)
----------
 1000



SQL> alter tablespace tts read only ;

Tablespace altered.

Now copy datafile 

Copy datafile now from one pluggable database(orcl) to another pluggable database (anuj) dir .. 


[oracle@vbgeneric ~]$ ls -ltr /u01/app/oracle/oradata/orcl12c/ANUJ/
total 2078520
-rw-r----- 1 oracle oinstall 20979712 May 1 2017 pdbseed_temp012016-06-02_07-10-28-AM.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 25 11:45 users01.dbf
-rw-r----- 1 oracle oinstall 272637952 Mar 25 14:58 system01.dbf
-rw-r----- 1 oracle oinstall 775954432 Mar 25 14:58 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Mar 25 15:00 tts01_.dbf >>>>>> 


cp /u01/app/oracle/oradata/orcl12c/orcl/tts01_.dbf /u01/app/oracle/oradata/orcl12c/ANUJ/


ls -ltr /u01/app/oracle/oradata/orcl12c/ANUJ/
total 2078580
-rw-r----- 1 oracle oinstall 20979712 Mar 25 15:06 pdbseed_temp012016-06-02_07-10-28-AM.dbf
-rw-r----- 1 oracle oinstall 1073750016 Mar 25 15:07 tts01_.dbf <<<<<<
-rw-r--r-- 1 oracle oinstall 1014 Mar 25 15:07 import.log
-rw-r----- 1 oracle oinstall 5251072 Mar 26 02:22 users01.dbf
-rw-r----- 1 oracle oinstall 775954432 Mar 26 03:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 272637952 Mar 26 03:24 system01.dbf



Now On diffrent pluggable database i.e. anuj 

SQL> alter session set container=anuj;

Session altered.


create directory TMP as '/u01/app/oracle/oradata/orcl12c/ANUJ';


create database link tts connect to system identified by sys using '//localhost/orcl';

Database link created.



SQL> create user ttsuser identified by tts ;

User created.

SQL> grant connect,resource to ttsuser ;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ttsuser;

Grant succeeded.

SQL> alter user ttsuser default tablespace tts ;

User altered.



impdp '"sys/vihaan@//localhost/anuj as sysdba"' network_link=tts directory=TMP TRANSPORT_TABLESPACES=TTS transport_datafiles=/u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf

Import: Release 12.1.0.2.0 - Production on Sun Mar 25 15:04:45 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/********@//localhost/anuj AS SYSDBA" network_link=tts directory=TMP TRANSPORT_TABLESPACES=TTS transport_datafiles=/u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf
Source time zone is +00:00 and target time zone is -07:00.
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Mar 25 15:07:55 2018 elapsed 0 00:03:01




SQL> alter session set container=anuj;

Session altered.

SQL> select count(*) from TTSUSER.table2_test;

 COUNT(*)
----------
 1000

 oracle whoami.sql?

whoami.sql

oracle 12c 
set linesize 200 pagesize 100 serveroutput on
column "db details" format a100
select
 'db_name: ' ||sys_context('userenv', 'db_name') ||
 ' \cdb:-' ||(select cdb from v$database) ||
 ' \auth_id: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \user: ' ||sys_context('userenv', 'current_user') ||
 ' \container:' ||nvl(sys_context('userenv', 'con_name'), 'non-cdb') "db details"
 from dual ;

-----


set linesize 200 pagesize 0 serveroutput on
column "db details" format a200
select
'user:\'||sys_context('userenv','session_user')||' session_id:\ '||'current_schema:\ '||sys_context('userenv','current_schema')||' instance_name:\ '||sys_context('userenv','instance_name')||
' database role: '||sys_context('userenv','database_role')||' os user:\ '||sys_context('userenv','os_user')||' client ip address:\ '||sys_context('userenv','ip_address')||' server hostname:\ '||sys_context('userenv','server_host')||' client hostname:\ '||sys_context('userenv','host') "db details"
from dual ;



-- From Web ...

set serveroutput on
begin
dbms_output.put_line('USER: ' ||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID: ' ||sys_context('userenv','sid'));
dbms_output.put_line('CURRENT_SCHEMA: ' ||sys_context('userenv','current_schema'));
dbms_output.put_line('INSTANCE NAME: ' ||sys_context('userenv','instance_name'));
dbms_output.put_line('DATABASE ROLE: ' ||sys_context('userenv','database_role'));
dbms_output.put_line('OS USER: ' ||sys_context('userenv','os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv','ip_address'));
dbms_output.put_line('SERVER HOSTNAME: ' ||sys_context('userenv','server_host'));
dbms_output.put_line('CLIENT HOSTNAME: ' ||sys_context('userenv','host'));
end;
/